sql-server - T-SQL查询获取索引碎片信息

Question

我一直在开发一个查询来使用 DMV 获取索引碎片信息。

但是，查询给出的结果比预期多。我相信问题出在连接上。

有什么想法吗？

select distinct '['+DB_NAME(database_id)+']' as DatabaseName,
    '['+DB_NAME(database_id)+'].['+sch.name+'].['
    + OBJECT_NAME(ips.object_id)+']' as TableName,
    i.name as IndexName,
    ips.index_type_desc as IndexType,
    avg_fragmentation_in_percent as avg_fragmentation,
    SUM(row_count) as Rows
FROM
    sys.indexes i INNER JOIN
    sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'LIMITED') ips ON
        i.object_id = ips.object_id INNER JOIN
    sys.tables tbl ON tbl.object_id  = ips.object_id INNER JOIN
    sys.schemas sch ON sch.schema_id = tbl.schema_id INNER JOIN
    sys.dm_db_partition_stats ps ON ps.object_id = ips.object_id
WHERE
    avg_fragmentation_in_percent <> 0.0 AND ips.database_id = 6
    AND OBJECT_NAME(ips.object_id) not like '%sys%'
GROUP BY database_id, sch.name, ips.object_id, avg_fragmentation_in_percent,
    i.name, ips.index_type_desc
ORDER BY avg_fragmentation_in_percent desc

Answer 1

我认为你需要在 sys.dm_db_partition_stats 的连接中使用index_id和sys.indexes .

最好使用 sys.dm_db_index_physical_stats 的第一个参数对 db 进行过滤，而不是使用 where 子句 ips.database_id = 6 .

我不明白distinct , group by或sum(row_count)条款。

这是一个查询，您可以尝试看看它是否符合您的要求。

select
  db_name(ips.database_id) as DataBaseName,
  object_name(ips.object_id) as ObjectName,
  sch.name as SchemaName,
  ind.name as IndexName,
  ips.index_type_desc,
  ps.row_count
from sys.dm_db_index_physical_stats(6,NULL,NULL,NULL,'LIMITED') as ips
  inner join sys.tables as tbl
    on ips.object_id = tbl.object_id
  inner join sys.schemas as sch
    on tbl.schema_id = sch.schema_id  
  inner join sys.indexes as ind
    on ips.index_id = ind.index_id and
       ips.object_id = ind.object_id
  inner join sys.dm_db_partition_stats as ps
    on ps.object_id = ips.object_id and
       ps.index_id = ips.index_id and
       ps.partition_number = ips.partition_number