Azure SQL update statistics (Manual)
I recently run into a case where I needed to update statistics of an Azure SQL Database because of poor performance and deadlocks. Preventing disruptions is key, so it is important to do something about it. With a simple script we can update the statistics easaly.
Why should I update statistics?
SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows. The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choose costly operators such as index scan over index seek and it might cause high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually causes trouble to the underlying queries, resources.
The script
Just execute the following query on your database and you should be good to go! Keep in mind, depending on your database this might take a while. During this script your database will get slow, but will remain online.
SET NOCOUNT ON GO DECLARE updatestats CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN updatestats DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM updatestats INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'UPDATE STATISTICS ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN' EXEC sp_executesql @Statement FETCH NEXT FROM updatestats INTO @tableSchema, @tableName END CLOSE updatestats DEALLOCATE updatestats GO SET NOCOUNT OFF GO