February 7, 2011 at 10:22 am
I usually run the below query to find fragmentation level:
select p.database_id,p.object_id,p.index_id,p.index_type_desc,p.page_count,p.avg_fragmentation_in_percent,o.name objectnames,o.type_desc,o.modify_date
from sys.dm_db_index_physical_stats (8,NULL,NULL,NULL,NULL) p
join sys.objects o
on o.object_id=p.object_id
where page_count>1000 and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc
Why is there a lock on the objects to find the fragmentation levels? I am not able to use with nolock option? Can someone please suggest me?
February 7, 2011 at 11:27 am
With (nolock) will give you dirty data. There's no way to query system data like that in that manner. It has to lock the table to find out what the current fragmentation level is, otherwise you'll get the wrong data back.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 12:00 pm
hmm..so what is best way to find fragmentation level on indexes during heavy usage?
February 7, 2011 at 1:26 pm
Generally speaking, you don't want to. You usually do that during quiet periods. The only real use for the data is to determine if you should defrag/rebuild the index, and you usually don't want to do that while the table is under heavy use.
You could probably use Snapshot Isolation to mitigate the impact of this, if you're using Enterprise Edition. Enterprise Edition has a number of features that are built for high availability situations, where you don't have quiet periods to do maintenance in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2011 at 6:00 pm
I tested this out today , running a trace all i saw is shared locked. Are you sure this will lock the table , like keep an X lock? Mentioned below is the query i am using.
select p.database_id,p.object_id,p.index_id,p.index_type_desc,p.page_count,p.avg_fragmentation_in_percent,o.name objectnames,o.type_desc,o.modify_date
from sys.dm_db_index_physical_stats (37,375672386,NULL,NULL,NULL) p
join sys.objects o
on o.object_id=p.object_id
where page_count>1000 and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc
Please clarify
March 8, 2011 at 9:57 am
Anyone?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply