How to troubleshoot slow queries in RDS for SQL Server

What will affect the speed of queries?

1. Index fragmentation
   Fragmentation 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.
2. Old statistics
   SQL 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/

3. No suitable index (to specific slow query)
4. Instance configurations

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

1. 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_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER 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_id
WHERE 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 REORGANIZE
30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

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

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

FROM sys.[stats] AS s
JOIN 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.

1. 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
  '' END
• ISNULL (dm_mid.inequality_columns, '')
• ')'
• ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
  FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

1. 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.
2. statistics io on

set statistics time on
set statistics profile on
select a,b from Demotable where a>1
set statistics io off
set statistics time off
set statistics profile off

1. Check the configurations of RDS for SQL Server instance, and upgrade the instance if needed.