- 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
Saturday, 10 June 2017
How to check SQL Database missing Index
Subscribe to:
Post Comments (Atom)
Upload valid file in C#
protected bool CheckFileExtandLength(HttpPostedFile HtmlDocFile) { try { Dictionary<string, byte[]...
-
Setting Header Programatically You can also set the cache headers programmatically. This can be useful for generated content and allows m...
-
CREATE TABLE dbo.M_Bank ( ID INT IDENTITY NOT NULL, MB_NAME NVARCHAR (50), MB_SNAME NVARCHAR (20), MB_ADDUSER ...
-
SELECT distinct(volume_mount_point), total_bytes/1048576 as Size_in_MB, total_bytes/1048576/1024 as Size_in_GB, available_bytes/1048576 ...
No comments:
Post a Comment