There are many methods to investigate
performance issues in SQL Server database. But in this article I am trying to
explain the way to find the missing indexes in a database based on the
available workload (statements which are getting executed inside the database).
Below Query helps in finding the
missing indexes of a particular database.
SELECT '[' + Sch.NAME + '].[' + Tab.[name] + ']' AS TableName,
Ind.[name] AS IndexName,
Substring((SELECT ', ' + AC.NAME
FROM sys.[tables] AS T
INNER JOIN
sys.[indexes]
I
ON T.[object_id] = I.[object_id]
INNER JOIN
sys.[index_columns]
IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN
sys.[all_columns]
AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id
= I.index_id
AND IC.is_included_column
= 0
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000) AS KeyCols,
Substring((SELECT ', ' + AC.NAME
FROM sys.[tables] AS T
INNER JOIN
sys.[indexes]
I
ON T.[object_id] = I.[object_id]
INNER JOIN
sys.[index_columns]
IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN
sys.[all_columns]
AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id
= I.index_id
AND IC.is_included_column
= 1
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000) AS
IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
ORDER BY
TableName
GO
Query Result
The query
will give you proposed indexes on the database to improve the performance.
Below Query will help in finding the missing indexes and create the syntax as well.
Below Query will help in finding the missing indexes and create the syntax as well.
SELECT Cast(Serverproperty('ServerName') AS [NVARCHAR](256)) AS [SQLServer],
db.[database_id]
AS [DatabaseID],
db.[name]
AS [DatabaseName],
id.[object_id] AS
[ObjectID],
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],
gs.[avg_user_impact]
AS [AvgUserImpact],
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],
gs.[user_seeks]
* gs.[avg_total_user_cost]
* ( gs.[avg_user_impact]
* 0.01 ) AS [IndexAdvantage],
'CREATE INDEX
[IXNC_'
+ 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 id.[database_id]
= 6 -- Remove this to
see for entire instance
ORDER BY
[IndexAdvantage] DESC
OPTION (RECOMPILE);
My
suggestion is to go through the proposed indexes first and analyse whether it
will help or not. More indexes may kill the performance some times.
No comments:
Post a Comment