Month: October 2019

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