QTip: Maintenance plan for performance issues demo
1-Requirements 2-Considerations 3-Update statistics 4-Rebuild indexes 5-Missing indexes 6-More information Requirements Azure SQL Database SQL Server Management Studio (SSMS) Considerations Is not recommended to execute in business hours In update statistics and rebuild indexes there is not status of the process it means you will not see % of the process you will see an update when process is finished. Is important to consider number of lines to execute according to size of the tables because SQL is going to execute everything at the same time and may impact performance of the database by instance you can select 5 lines for small tables but 1 line in huge tables 1-Update statistics with fullscan (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Update ALL Statistics WITH FULLSCAN -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will update all the statistics on all the tables in your database. 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' PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below. -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM updatestats INTO @tableSchema, @tableName END CLOSE updatestats DEALLOCATE updatestats GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 2-Rebuild indexes (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Rebuild indexes -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will rebuild all the indexes on all the tables in your database. SET NOCOUNT ON GO DECLARE rebuildindexes CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN rebuildindexes DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'ALTER INDEX ALL ON ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD' PRINT @Statement -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName END CLOSE rebuildindexes DEALLOCATE rebuildindexes GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 3- Missing indexes (optional review with your development team) SELECT CONVERT (varchar, getdate(), 126) AS runtime, migs.avg_user_impact, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement, 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.avg_total_user_cost,migs.user_seeks,migs.user_scans, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC Based on improvement or avg_user_impact you may decide indexes to create keeping in mind that needs to be analyzed remember adding index increase work with Insert / update / delete operations Copy create_index text of desired commands and copy in a new window to execute Now you are ready to start troubleshooting good luck! More Information How to maintain Azure SQL Indexes and Statistics https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787 AUTO_CREATE_STATISTICS Option https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#AutoUpdateStats Statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16 Query execution times are slow https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#query-execution-times-are-slow Statistics options https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16&redirectedfrom=MSDN#statistics-options When to update statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#when-to-update-statistics Index maintenance strategy https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#index-maintenance-strategy Reorganize an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#reorganize-an-index Rebuild an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#rebuild-an-index SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild https://learn.microsoft.com/en-us/archive/technet-wiki/28182.sql-server-in-depth-what-can-cause-index-to-be-still-fragmented-after-rebuild
