sqlserver查看索引使用方式以及建立丟失的索引

標籤：des   使用   io   資料   ar   art   資料庫   sql   

--查看錶的索引使用方式
SELECT TOP 1000
o.name AS 表名
, i.name AS 索引名
, i.index_id AS 索引id
, dm_ius.user_seeks AS 搜尋次數
, dm_ius.user_scans AS 掃描次數
, dm_ius.user_lookups AS 尋找次數
, dm_ius.user_updates AS 更新次數
, p.TableRows as 表行數
, ‘DROP INDEX ‘ + QUOTENAME(i.name)
+ ‘ ON ‘ + QUOTENAME(s.name) + ‘.‘ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘刪除語句‘
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,‘IsUserTable‘) = 1
AND dm_ius.database_id = DB_ID()
--AND i.type_desc = ‘nonclustered‘--這裡指定了索引的類型，叢集索引或者非叢集索引
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
and o.name=‘testtable‘   --需要尋找的表名
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC



--查看資料庫裡表丟失的索引並產生建立索引的語句
SELECT t4.name,t1.[statement],t1.object_id, t2.user_seeks, t2.user_scans,
       t1.equality_columns, t1.inequality_columns,t1.included_columns,
    case 
       --when t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns)=0 and t1.included_columns is null
       --    then   ‘create UNIQUE NONCLUSTERED INDEX IX_‘ + replace((replace((replace(t1.[statement],‘[‘,‘_‘)),‘]‘,‘_‘)),‘.‘,‘_‘) +‘_‘+ replace((replace((replace(isnull(t1.equality_columns,‘1‘),‘[‘,‘_‘)),‘]‘,‘_‘)),‘.‘,‘_‘) +‘_‘ 
       --           +replace((replace((replace(isnull(t1.inequality_columns,‘_2‘),‘[‘,‘_‘)),‘]‘,‘_‘)),‘.‘,‘_‘) + ‘ ON ‘+ t1.[statement] + ‘ (‘ + t1.inequality_columns + ‘ ASC )‘  
       when --t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns)>0 and
       t1.included_columns is null
           then   ‘create  NONCLUSTERED INDEX IX_‘ + replace((replace((replace((replace(t1.[statement],‘[‘,‘_‘)),‘]‘,‘_‘)),‘.‘,‘_‘)),‘,‘,‘_‘) +‘_‘  
                  +replace(replace(replace(replace(replace(isnull(t1.equality_columns,‘2‘),‘ [‘,‘‘),‘[‘,‘‘),‘.‘,‘‘),‘,‘,‘‘),‘]‘,‘‘)
  +replace((replace((replace((replace(isnull(t1.inequality_columns,‘2‘),‘[‘,‘‘)),‘]‘,‘‘)),‘.‘,‘‘)),‘,‘,‘_‘) + ‘ ON ‘+ t1.[statement] + ‘ (‘ + 
    case  when t1.equality_columns is null then ‘ ‘
          when charindex(‘,‘,t1.equality_columns)=0 then t1.equality_columns +‘ ASC ‘
  when charindex(‘,‘,t1.equality_columns)>0 then replace(t1.equality_columns,‘,‘,‘ ASC,‘) + ‘ ASC ‘ 
 end
       +   
    case  when t1.equality_columns is not null and charindex(‘,‘,t1.inequality_columns)=0 then  ‘ ,‘+t1.inequality_columns + ‘ ASC )‘
when t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns)=0 then  ‘ ‘+t1.inequality_columns + ‘ ASC )‘
  when  t1.inequality_columns is null then ‘ )‘
  when charindex(‘,‘,t1.inequality_columns) > 0 then ‘ ,‘+ replace(t1.inequality_columns,‘,‘,‘ ASC,‘) + ‘ ASC )‘ 
  when  t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns) > 0 then ‘ ‘+ replace(t1.inequality_columns,‘,‘,‘ ASC,‘) + ‘ ASC )‘
     end
   when t1.included_columns is not null
        then   ‘create NONCLUSTERED INDEX IX_‘ + replace((replace((replace((replace(t1.[statement],‘[‘,‘_‘)),‘]‘,‘_‘)),‘.‘,‘_‘)),‘,‘,‘_‘) +‘_‘  
                  +replace(replace(replace(replace(replace(isnull(t1.equality_columns,‘2‘),‘ [‘,‘‘),‘[‘,‘‘),‘.‘,‘‘),‘,‘,‘‘),‘]‘,‘‘)
  +replace((replace((replace((replace(replace(isnull(t1.inequality_columns,‘2‘),‘ [‘,‘‘),‘[‘,‘‘)),‘]‘,‘‘)),‘.‘,‘‘)),‘,‘,‘_‘) + ‘ ON ‘+ t1.[statement] + ‘ (‘ + 
    case  when t1.equality_columns is null then ‘ ‘
          when charindex(‘,‘,t1.equality_columns) = 0 then t1.equality_columns +‘ ASC ‘
  when charindex(‘,‘,t1.equality_columns) > 0 then replace(t1.equality_columns,‘,‘,‘ ASC,‘) + ‘ ASC ‘ 
 end
       +   
    case  when  t1.equality_columns is not null and charindex(‘,‘,t1.inequality_columns)=0 then ‘ ,‘+t1.inequality_columns + ‘ ASC )‘
when  t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns)=0 then ‘ ‘+t1.inequality_columns + ‘ ASC )‘
  when  t1.inequality_columns is null then ‘ )‘
  when  t1.equality_columns is not null and charindex(‘,‘,t1.inequality_columns) > 0 then ‘ ,‘+ replace(t1.inequality_columns,‘,‘,‘ ASC,‘) + ‘ ASC )‘ 
  when  t1.equality_columns is null and charindex(‘,‘,t1.inequality_columns) > 0 then ‘ ‘+ replace(t1.inequality_columns,‘,‘,‘ ASC,‘) + ‘ ASC )‘ 
     end
  + ‘ INCLUDE ( ‘ + t1.included_columns + ‘ )‘
   
    end  as  ‘建立索引的語句‘


      FROM sys.dm_db_missing_index_groups AS t3
      join sys.dm_db_missing_index_details AS t1
       on  t1.index_handle = t3.index_handle
          join sys.dm_db_missing_index_group_stats AS t2
            on t2.group_handle = t3.index_group_handle
              join sys.databases AS t4 
                on t1.database_id = t4.database_id
      WHERE t1.database_id = DB_ID() --AND object_id = OBJECT_ID(‘interface.商戶裝置表‘)
      order by t2.user_seeks desc 
      
      --t4.name,t1.object_id