T-SQL query to get Index fragmentation information
I've been developing a query to get index fragmentation information using DMVs.
However, the query gives more results than expected. I believe the problem is in joins.
Any thoughts?
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.tab开发者_JAVA百科les 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
I think you need index_id in the joins against sys.dm_db_partition_stats
and sys.indexes
.
It is probably better to use the first parameter of sys.dm_db_index_physical_stats
to filter on db instead of the where clause ips.database_id = 6
.
I do not understand the distinct
, group by
or sum(row_count)
clauses.
Here is a query you can try and see if it does what you want.
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
精彩评论