March 14, 2014 at 12:22 am
Hi Experts,
how can i get the fragmentation level of particular indexes(around 1000) alone?
March 14, 2014 at 12:39 am
Can you try the below query.i hope it will help you.
select tbl.[name] TableName,
ind.[name] IndexName,
mn.index_type_desc IndexType,
mn.avg_fragmentation_in_percent [FRAG_%]
from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
where [database_id] = db_id('Databasename')
order by mn.avg_fragmentation_in_percent desc
--------------------------------------------------------
I am Learner -- SQL
March 14, 2014 at 3:34 am
The answer is in the sys.dm_db_index_physical_stats as was outlined in the query above. You can get targeted statistics on a given index, or get all statistics on all indexes. Read more about it here in the Books Online. One important thing to know, you can control how it samples the data to show the distribution. While DETAILED is going to be more accurate, it's also very costly on the system. I would generally stick to LIMITED or SAMPLED depending on how accurate I need it to be. Also, don't worry about fragmentation of indexes less than 100 pages (some say 1000).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2014 at 7:54 am
Thanks Grant and Selva.
What I am looking for is i need to get fragmentation details of particular indexes says indexes with name-A,B,C,D,E.......
March 17, 2014 at 10:03 am
The query above can do that. You just have to filter by the name if that's what you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2014 at 4:08 am
Thanks Grant.
I didnt find an option in that query to specify the index names .
eg:
1.A
2.B
3.C
4.PK_ASSETID
5.ix_customer
6.ix_assetid
7.ix_placeid........
March 19, 2014 at 4:51 am
You'd have to modify the WHERE clause, the name is right there in the query:
ind.[name] IndexName,
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply