SQLServer 断片化状況および allow_page_locks の状況確認クエリ

use [databasename]
go
 
select d.name schema_name, b.name table_name,c.name index_name, c.allow_page_locks , page_count , avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) as A
    JOIN  sys.objects AS B
      ON  A.object_id = B.object_id
    JOIN  sys.indexes AS C
      ON  A.object_id = C.object_id  AND A.index_id = C.index_id
    JOIN  sys.schemas D
      ON  B.schema_id = D.schema_id
WHERE B.type = 'U'
     and C.index_id > 0
     and A.page_count > 1000
     and A.avg_fragmentation_in_percent > 15 ORDER BY A.avg_fragmentation_in_percent DESC