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-rebuildQTip: Getting timeouts and exceptions using Azure SQL DB Query Store
Requirements: Azure SQL Database SQL Server Management Studio (SSMS) Tables testcodes and testinvoice used for this demo 1-Configure Query Store to collect data and save as soon as possible to be able to review it * Data flush interval is the time to send information to disk ** Statistics collection interval is the time range to collect data , query store is not collecting one record by execution is collecting statistics of each execution and you can get max, min, avg but not excecution by excecution *** Query store capture mode ALL will collect information of all executions query store will not make any kind of filter 2-Create table testcodes CREATE TABLE [dbo].[testcodes]( [Code] [char](5) NULL, [Description] [nchar](10) NULL ) ON [PRIMARY] GO 3-Create table testinvoice CREATE TABLE [dbo].[testinvoice]( [code] [char](5) NULL, [pieces] [int] NULL ) ON [PRIMARY] GO 4-Add some codes to be used 5-Add some records to invoice Exception ... 6-Run query to get information from invoices and get description from codes select *,description=(select testcodes.Code from testcodes where testcodes.code=invoice.code) from testinvoice invoice Is possible to see execution without error 7-Now add in codes a second code BBB to force exception 8-Run query from point 6 again Result is an exception 9-Run query to see queries with exceptions or timeouts starting 2 days ago (declare @datestart as datetime = dateadd(D,-2,getdate());) declare @datestart as datetime = dateadd(D,-2,getdate()); declare @datefinish as datetime = getdate(); /* if you want to set to specific time */ --set @datestart = '2025-04-09 00:00:00'; --set @datefinish = '2025-04-09 23:59:59'; select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount, rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop, p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish and (rs.execution_type=3 or rs.execution_type=4) -- 3 timeout, 4 error --and qt.query_sql_text like '%actual%' --and q.query_hash=0x009C458D20394C37 --and p.plan_id=12 ORDER BY rs.last_execution_time DESC 10-In query there are some commented lines that you can use to add more filters or modify them To see all records comment line 20 declare @datestart as datetime = dateadd(D,-2,getdate()); declare @datefinish as datetime = getdate(); /* if you want to set to specific time */ --set @datestart = '2025-04-09 00:00:00'; --set @datefinish = '2025-04-09 23:59:59'; select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount, rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop, p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish --and (rs.execution_type=3 or rs.execution_type=4) -- 3 timeout, 4 error --and qt.query_sql_text like '%actual%' --and q.query_hash=0x009C458D20394C37 --and p.plan_id=12 ORDER BY rs.last_execution_time DESC Now you can see all records 11-Reproduce error several times within 1 minute to see value in execution count (number of executions inside of statistics collection interval) Timeout ... 12-Configure command timeout different to 0 for this demo use 10 (seconds) in parameters previous to connect Second option 12-Use query below that will run 1000 times and at some point will fail INSERT INTO [dbo].[testinvoice] SELECT * FROM [dbo].[testinvoice] GO 1000 13-Run query from point 9 to see data in query store Now you can reproduce and get data about all excecutions , exceptions and timeouts good luck!
