Find missing and unused indexes in SQL Server on Jitterbit App Builder

Introduction

For database administrators or advanced users, you can use SQL Server's Dynamic Management Views (DMVs) to get detailed information about your database's performance. To run these queries, you must have VIEW DATABASE STATE permission.

Use Dynamic Management Views (DMVs)

DMVs return server state information that can be used to monitor the health of a database server and diagnose problems. The following example queries can help you identify issues with indexes.

Find long-running queries

This query identifies the top 20 longest-running queries by total duration. This can help you find inefficient queries that may benefit from better indexing:

FindingLongRunningQueries.sql

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, 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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC

Identify the most important missing indexes

This query uses the missing index DMVs to identify which missing indexes would provide the most benefit to your query workload:

IdentifyMostImportantMissingIndexes.sql

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
    ROUND(s.avg_total_user_cost *s.avg_user_impact*  (s.user_seeks + s.user_scans),0) AS [Total Cost]
    , d.[statement] AS [Table Name]
    , equality_columns
    , inequality_columns
    , included_columns
FROM sys.dm_db_missing_index_groups g
    INNER JOIN sys.dm_db_missing_index_group_stats s
        ON s.group_handle = g.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details d
        ON d.index_handle = g.index_handle
WHERE d.database_ID = DB_ID()
ORDER BY [Total Cost] DESC

Identify unused indexes

Unused indexes add overhead to INSERT, UPDATE, and DELETE operations without providing any benefit to SELECT queries. This query helps you find indexes that are not being used and could potentially be removed:

IdentityUnusedIndexes.sql

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , s.user_updates
    , s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
INTO    #TempUnusedIndexes
FROM    sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i
        ON s.[object_id] = i.[object_id]
        AND s.index_id   = i.index_id
    INNER JOIN sys.objects o
        ON i.object_id   = O.object_id
WHERE     1=2

EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempUnusedIndexes
SELECT TOP 20
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , s.user_updates
    , s.system_seeks + s.system_scans + s.system_lookups
    AS [System usage]
FROM    sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i
        ON s.[object_id] = i.[object_id]
        AND s.index_id = i.index_id
    INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE     s.database_id = DB_ID()
AND     OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND     s.user_seeks = 0
AND     s.user_scans = 0
AND     s.user_lookups = 0
AND     i.name IS NOT NULL
ORDER BY s.user_updates DESC'

SELECT     TOP 20 *
FROM     #TempUnusedIndexes
ORDER BY [user_updates] DESC
DROP TABLE #TempUnusedIndexes

Other methods

In addition to using DMVs, you can also identify missing indexes in these ways:

Run the Database Engine Tuning Advisor.
View execution plans in SQL Server Management Studio (SSMS), which may include prompts about missing indexes.