Missing Indexes

Index plays an important role in performance tuning. A proper index can improve the performance and a bad index can hamper the performance.

What Are Missing Indexes?

SQL Server keeps index statistics behind the scenes.  It knows how an index has been used, how often it is used, or if it is even being used at all.  But SQL Server doesn’t just monitor the indexes that we currently have on a database, it even keeps up with statistics for indexes that it wishes we had!

When SQL Server is processing a query, it makes a suggestion for an index that it believes will help that query run faster.  These are known as Missing Index Suggestions. 

Guidance and Recommendations

When SQL Server recommends a missing index, it does so based on the execution of that query at that specific point in time.  It doesn’t take into consideration any other factors such as current server load, or any existing indexes, so you should consider these index recommendations as just that, recommendations.  This is just for guidance. Please note that you should not create all the missing indexes this script suggests.

• Do not just blindly create whatever indexes SQL Server is recommending without any prior analysis.
• You should not create more than 10 indexes per table.
• The script is a good starting point. You should pay attention to Average User Impact and Average Total User Cost when you are going to create an index.

Script

The below query will show missing index suggestions for the specified database.  It pulls information from the [sys].[dm_db_missing_index_groups] [sys].[dm_db_missing_index_group_stats] and [sys].[dm_db_missing_index_details] DMVs. 

You can also narrow it down to a specified table by un-commenting the WHERE clause statement and specifying the table name.  This script also generates a CREATE INDEX script for each record, so that you can take a better look at the index and decide if you want to use it.

Here is the script, which we can use to identify missing indexes on a database.

SELECT db.[name] AS [DatabaseName]

    ,id.[object_id] AS [ObjectID]

       ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]

    ,id.[statement] AS [FullyQualifiedObjectName]

    ,id.[equality_columns] AS [EqualityColumns]

    ,id.[inequality_columns] AS [InEqualityColumns]

    ,id.[included_columns] AS [IncludedColumns]

    ,gs.[unique_compiles] AS [UniqueCompiles]

    ,gs.[user_seeks] AS [UserSeeks]

    ,gs.[user_scans] AS [UserScans]

    ,gs.[last_user_seek] AS [LastUserSeekTime]

    ,gs.[last_user_scan] AS [LastUserScanTime]

    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  — Average cost of the user queries that could be reduced by the index in the group.

    ,gs.[avg_user_impact] AS [AvgUserImpact]  — The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

    ,gs.[system_seeks] AS [SystemSeeks]

    ,gs.[system_scans] AS [SystemScans]

    ,gs.[last_system_seek] AS [LastSystemSeekTime]

    ,gs.[last_system_scan] AS [LastSystemScanTime]

    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]

    ,gs.[avg_system_impact] AS [AvgSystemImpact]  — Average percentage benefit that system queries could experience if this missing index group was implemented.

    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]

    ,’CREATE INDEX [IX_’ + OBJECT_NAME(id.[object_id], db.[database_id]) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) + CASE

        WHEN id.[equality_columns] IS NOT NULL

            AND id.[inequality_columns] IS NOT NULL

            THEN ‘_’

        ELSE ”

        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) + ‘_’ + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ‘]’ + ‘ ON ‘ + id.[statement] + ‘ (‘ + ISNULL(id.[equality_columns], ”) + CASE

        WHEN id.[equality_columns] IS NOT NULL

            AND id.[inequality_columns] IS NOT NULL

            THEN ‘,’

        ELSE ”

        END + ISNULL(id.[inequality_columns], ”) + ‘)’ + ISNULL(‘ INCLUDE (‘ + id.[included_columns] + ‘)’, ”) AS [ProposedIndex]

    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]

FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)

INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]

INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]

INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]

WHERE  db.[database_id] = DB_ID()

–AND OBJECT_NAME(id.[object_id], db.[database_id]) = ‘YourTableName’

ORDER BY ObjectName, [IndexAdvantage] DESC

OPTION (RECOMPILE);

Note: – It’s important to mention that these Missing Index statistics get reset every time SQL Server is restarted, so if you’ve recently rebooted the server you may not have many index stats accumulated yet.

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-groups-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver15

Service Broker Information

--: Service Broker Information
--Queue rows and Queue table size
SELECT '--: Service Broker information'
SELECT SCHEMA_NAME(q.schema_id) AS SchemaName, q.name AS QueueName, p.row_count AS QueueRows,i.name as QueueSystemTableName,p.reserved_page_count * 8. / 1024. AS [QueueTableSize(MB)]
FROM sys.service_queues AS q with(nolock)
JOIN sys.objects AS o with(nolock)ON o.object_id = q.object_id
JOIN sys.objects AS i with(nolock)ON i.parent_object_id = q.object_id
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock)ON i.object_id = p.object_id AND p.index_id IN(0, 1)
Order by QueueRows desc, [QueueTableSize(MB)] Desc

--Service Broker size and rows
Select case when o.name = 'sysxmitqueue' then 'transmission_queue' else 'conversation_endpoints' END as ServiceBrokerTable,o.name as SystemTable,
p.reserved_page_count * 8. / 1024. AS [TableSize(MB)],p.row_count as [RowCount]
from sys.objects as o with(nolock)
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock) ON o.object_id = p.object_id AND p.index_id IN(0, 1)
where o.name in ('sysdercv','sysdesend','sysxmitqueue')

--Endpoints rows
Select s.name as [EndPointsSourceServices],e.far_service as [EndPointsTargetServices], state_desc as [EndPointsState],count(*) as [EndPointsCount]
from sys.conversation_endpoints as e with(nolock)
inner join sys.services as s WITH(NOLOCK) on s.service_id = e.service_id
Group by s.name,e.far_service,e.state_desc
Order by [EndPointsCount] desc, far_service,state_desc

--Transmission Queue rows
select from_service_name as [SourceServices],to_service_name as [TargetServices],count(to_service_name) as QueueCount
from sys.transmission_queue with(nolock)
group by from_service_name,to_service_name
Order by QueueCount desc,from_service_name,to_service_name

--Normally the Service Broker numbers should be small, otherwise it indicates a Service Broker issue

Database Health Check – Database Level

--1: Database property information
SELECT '--1: Database property information'
SELECT db.[name] AS [DatabaseName], db.recovery_model_desc AS [RecoveryModel], db.log_reuse_wait_desc AS [LogReuseWaitDescription],
db.[compatibility_level] AS [CompatibilityLevel],db.is_auto_create_stats_on,is_auto_create_stats_incremental_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, db.snapshot_isolation_state_desc,
db.is_read_committed_snapshot_on, is_auto_shrink_on, is_auto_close_on,db.is_ansi_null_default_on,db.is_ansi_nulls_on,db.is_ansi_padding_on,
db.is_ansi_warnings_on,db.is_trustworthy_on,db.is_broker_enabled, db.log_reuse_wait_desc
FROM sys.databases AS db WITH (NOLOCK)
WHERE db.database_id = db_id();

If (SELECT Compatibility_level FROM sys.databases WHERE database_id = DB_ID()) >=130
BEGIN
SELECT name as ConfiguratioName, [value] AS ConfiguratioValue
FROM sys.database_scoped_configurations WITH (NOLOCK)
END

--2: Database file size and space available
SELECT '--2: Database file size and space available'
SELECT name AS [FileName], physical_name AS [PhysicalPath], size/128 AS [TotalSize(MB)], Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128 AS [UsedSpace(MB)],
Convert(decimal(9,2),100 * (Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128.0)/(size/128.0)) AS [UsedSpace(%)]
FROM sys.database_files WITH (NOLOCK);

--3: Last statistics update for all indexes
SELECT '--3: Last statistics update for all indexes'
SELECT o.name as TableName, i.name AS IndexName,STATS_DATE(i.[object_id], i.index_id) AS [StatisticsDate], st.row_count as RowCounts
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

--4: Indexes Fragmentation
-- Note: This could take some time on a very large database
SELECT '--4: Indexes Fragmentation'
SELECT DB_NAME(database_id) AS [DatabaseName],OBJECT_NAME(ps.OBJECT_ID) AS [ObjectName],i.name AS [IndexName], ps.index_id as IndexID, index_type_desc as IndexType,
avg_fragmentation_in_percent as [AvgFragmentation(%)], fragment_count as FragmentationCount, page_count as PageCount
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND page_count > 500 and avg_fragmentation_in_percent > 20
ORDER BY avg_fragmentation_in_percent DESC;

--5: Cached queries and SPs
SELECT '--5: Cached queries and SPs'
IF Substring(Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')),1,CHARINDEX('.',Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')))-1) = 12
and Convert(nvarchar(128),SERVERPROPERTY('ProductLevel')) < 'SP2'
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END
ELSE
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, total_grant_kb/execution_count as [GrantMemory/Execution(KB)],qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END

SELECT p.name AS [SP Name], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time, qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;

Database Health Check – Instance Level

USE [master]
GO

--1: SQL Server and operating system information
SELECT '--1: SQL Server and operating system information'
SELECT @@VERSION AS [SQLServer&OSVersionInfo];

--2: Hardware information
--SQL SERVER 2014 and higher
SELECT '--2: Hardware information'
Declare @InstantFileInitializationEnabled nvarchar(10) = 'Unknown'
IF SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')),0,CHARINDEX('.',CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')))) >=13
BEGIN
EXEC sp_executesql N'select @IFI = instant_file_initialization_enabled from sys.dm_server_services where servicename like ''SQL Server (%''',
N'@IFI nvarchar(10) OUTPUT',@IFI=@InstantFileInitializationEnabled OUTPUT;
END

SELECT cpu_count AS [LogicalCPUCount], hyperthread_ratio AS [HyperthreadRatio],cpu_count/hyperthread_ratio AS [PhysicalCPUCount],
physical_memory_kb/1024 AS [PhysicalMemory(MB)],affinity_type_desc as [CPUAffinityType],
virtual_machine_type_desc as [VirtualMachineType],sqlserver_start_time as [LastStartTime],@InstantFileInitializationEnabled as InstantFileInitializationEnabled
FROM sys.dm_os_sys_info WITH (NOLOCK);

--3: Instance configuration values
-- Get configuration values for instance
SELECT '--3: Instance configuration values'
SELECT name as [ConfigName], value as [ConfigValue], value_in_use as [ConfigValueInUse], minimum, maximum, [description] as [ConfigDescription],
is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Focus on
-- Automatic soft-NUMA disabled (should be 0 in most cases)
-- backup compression default (should be 1 in most cases)
-- Backup checksum default (should be 1)
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism (recommended setting is zero)
-- max server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to the SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. Be sure to leave sufficient memory to the OS. On a server that is dedicated to SQL, at least 4GB is required for the OS to function. 
-- max worker threads (should be zero)
-- min server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. This value should be equal to the “Max Server Memory” value. 
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)

--4: Database filenames, paths and growth
-- File Names and Paths for Tempdb and all user databases in instance
SELECT '--4: Database filenames, paths and growth'
SELECT DB_NAME([database_id])AS [DatabaseName], [file_id] as FileID, name as [DataFileName], physical_name as [PhysicalPath],
type_desc as [FileType],state_desc as [DatabaseState], CONVERT( bigint, size/128.0) AS [TotalSize(MB)],
Case when is_percent_growth = 0 Then CONVERT(varchar(10), CONVERT(bigint, growth/128.0)) + ' MB'
else CONVERT(varchar(10), growth) + '%' END AS [GrowthSize(MB)]
FROM sys.master_files WITH (NOLOCK)
WHERE ([database_id] > 4 AND [database_id] <> 32767) OR [database_id] = 2
ORDER BY DB_NAME([database_id]);
-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?
-- Are there percentage growth?

--5 Disk Space Monitoring
SELECT '--5 Disk Space Monitoring'
SELECT DISTINCT 
volume_mount_point [Disk Mount Point], 
file_system_type [File System Type], 
logical_volume_name as [Logical Drive Name], 
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB], 
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

--6: Login count information
-- Get logins that are connected and how many sessions they have
SELECT '--6: Login count information'
SELECT DB_NAME(database_id) as DBName,login_name as LoginName, COUNT(session_id) AS [SessionCount]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY DB_NAME(database_id),login_name
ORDER BY DB_NAME(database_id),COUNT(session_id) DESC;
-- This can help characterize the workload

SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, 
COUNT(ec.session_id) AS [connection count] 
FROM sys.dm_exec_sessions AS es WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name 
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

--7: Memory information
SELECT '--7: Memory information'
SELECT total_physical_memory_kb/1024 as [TotalPhysicalMemory(MB)], available_physical_memory_kb/1024 as [AvailablePhysicalMemory(MB)],
system_memory_state_desc as [SystemMemoryState]
FROM sys.dm_os_sys_memory WITH (NOLOCK);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure

SELECT physical_memory_in_use_kb/1024 as [SQL Server Memory Usage (MB)], memory_utilization_percentage as [MemoryUtilization%],
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure

--8:CPU Usage By Database
SELECT '--8:CPU Usage By Database'
;WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

--9:I/O Usage By Database
SELECT '--9:I/O Usage By Database'
;WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);

--10:Total Buffer Usage By Database
SELECT '--10:Total Buffer Usage By Database'
;WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);