How to troubleshoot slow queries in RDS for SQL Server

What will affect the speed of queries?

Index fragmentationFragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

Old statisticsSQL Server collects statistical information of index and column data. SQL Server query optimizer uses it to estimate cost of query execution plans and chooses a quality-high execution plan. Large number of insertupdatedelete operations will make the statistics out of date which will cause SQL Server generates a bad execution plan.

If you want to know details about how statistics affect SQL Server, please refer to https://blogs.msdn.microsoft.com/ggaurav/2015/04/10/how-statistics-in-sql-server-determines-performance/

No suitable index (to specific slow query)Instance configurations

Now you get the general ideas of why queries are slow. Let's start the troubleshooting steps.

Using the following statements to list index fragmentation in a databases.SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',dbindexes.[name] as 'Index'indexstats.avg_fragmentation_in_percent,indexstats.page_countFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent desc

After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.avg_fragmentation_in_percent value Corrective statement

5% and < = 30% ALTER INDEX REORGANIZE30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

For more details, please refer to https://msdn.microsoft.com/en-us/library/ms189858.aspx .

Check the statistics update time in this databaseSELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated

FROM sys.[stats] AS sJOIN sys.[tables] AS t

ON [s].[object_id] = [t].[object_id]

WHERE t.type = 'u'

A simple way to know whether the statistics are out of date is to compare the actual rows and estimated rows in an actual execution plan. If different, you need to update statistics manually. Please refer to https://msdn.microsoft.com/en-us/library/ms187348.aspx.

The following statement will help check missing index in a database and give suggestions about new indexes. SELECT

dm_mid.database_id AS DatabaseID,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,dm_migs.last_user_seek AS Last_User_Seek,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +CASE

WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'ELSE ''END

REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')']'' ON ' + dm_mid.statement' (' + ISNULL (dm_mid.equality_columns,'')CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE'' ENDISNULL (dm_mid.inequality_columns, '')')'ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_StatementFROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migsON dm_migs.group_handle = dm_mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details dm_midON dm_mig.index_handle = dm_mid.index_handleWHERE dm_mid.database_ID = DB_ID()ORDER BY Avg_Estimated_Impact DESCGO

When a specific query is slow, we can open statistics io, time and profile on to collect execution io, time and execution plan to check the bottle neck.statistics io on

set statistics time onset statistics profile onselect a,b from Demotable where a>1set statistics io offset statistics time offset statistics profile off

Check the configurations of RDS for SQL Server instance, and upgrade the instance if needed. 相关资源：Murach's SQL Server 2016 for Developers