Some scripts about the SQL Server index

--Judging useless index: SELECT TOP db_name () as DatabaseName, ' [' + schema_name (o.schema_id) + '] ' + '. ' + '         + object_name (s.[object_id]) + '] ' as TableName, i.name as IndexName, I.type as Indextype, 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 are not NULL for ORDER by s.user_updates DESC;---Determine which indexes are missing: SELECT TOP 30                                                             ROUND (S.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + S.user_scans), 0) As [Total cost], S.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + S.user_scans) as Improvement_measure, db_name () as          DatabaseName, D.[statement] as [Table Name], equality_columns, Inequality_columns, Included_columns from Sys.dm_db_missing_index_groups G INNER joins 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_hand                                                                Le WHERE s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + S.user_scans) > ORDER by [Total cost] DESC, S.avg_total_user_cost * s.avg_user _impact * (S.user_seeks + S.user_scans) DESC---Look at those index maintenance costs are high. Popular That is, the number of times the update is greater than the use of this index select TOP db_name () as DatabaseName, ' [' + schema_name (o.schema_id) + ' + ' + ' + ' + ' + ' + ' + object_n AME (s.[object_id]) + '] ' as TableName, i.name as IndexName, I.type as Indextype, (s.user_up Dates) as Update_usage, (S.user_seeks + S.user_scans + s.user_lookups) as Retrieval_usage, (s.user  _updates)-(s.user_seeks + User_scans + s.user_lookups) as Maintenance_cost, S.system_seeks + S.system_scans  + s.system_lookups as System_usage, S.last_user_seek, S.last_user_scan, S.last_user_lookup                                      From Sys.dm_db_index_usage_stats s INNER joins 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 (' {0} ') and I.name is not NULL and OBJECTPROPERTY (s.[object_id], ' ismsshipped ') = 0 and (S.user_seeks + S.user_scans + s.user_lookups) > 0 ORDER by Maintenance_cost DESC;----frequently used indexes to see if the index you are using is set up reasonably select TOP 2 0 db_name () as DatabaseName, ' [' +schema_name (o.schema_id) + '] ' + '. ' + ' [' +object_name (s.[object_id]) + '] ' as TableName, i.name as IndexName, I.type as Indextype, (S.user_seeks + s.user_s Cans + s.user_lookups) as Usage, s.user_updates from Sys.dm_db_index_usage_stats s INNER joins sys.indexes I on S.[obje CT_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.databa    se_id = db_id () and I.name is no NULL and OBJECTPROPERTY (s.[object_id], ' ismsshipped ') = 0 ORDER by Usage DESC

　　

Some scripts about the SQL Server index