April 3, 2010 at 7:26 pm
Hi,
We have MOSS 2007 SP1 databases in SQL Server 2005 and I'm trying find the Index Fragmentation for the Content database of size 10 GB using below query:
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (8, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>100
order by page_count desc
Whenever I ran this query, the Page Life Expectancy (PLE) drops to 3 Seconds.
Is this normal? Or can I improve the above query?
Thanx so much
April 4, 2010 at 4:40 am
gmamata7 (4/3/2010)
Whenever I ran this query, the Page Life Expectancy (PLE) drops to 3 Seconds. Is this normal? Or can I improve the above query?
Pretty normal, yes. The command likely has to read a lot of pages from disk, which naturally reduces the average age of pages in the data cache.
This type of query is normally run during maintenance periods, when page life expectancy is not really important, since there is little user demand on the system. You should, of course, avoid running this type of query during production hours or busy periods in general.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 7:53 am
gmamata7 (4/3/2010)
Hi,We have MOSS 2007 SP1 databases in SQL Server 2005 and I'm trying find the Index Fragmentation for the Content database of size 10 GB using below query:
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (8, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>100
order by page_count desc
Whenever I ran this query, the Page Life Expectancy (PLE) drops to 3 Seconds.
Is this normal? Or can I improve the above query?
Thanx so much
You can improve your action (as far as leaving some server overhead to actually do useful work) by building a cursor on sysobjects for each User table and calling the DMV for each object in turn, with a WAITFOR DELAY of some number of seconds to allow for other backed-up spids to do their thing. NOTE: I am not 100% sure specifying an object ID in the DMV gets it to read JUST that object and I don't have time to do a test right now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 5, 2010 at 8:04 am
TheSQLGuru (4/5/2010)
NOTE: I am not 100% sure specifying an object ID in the DMV gets it to read JUST that object and I don't have time to do a test right now.
It does, yes. This is a great suggestion.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 8:55 am
Paul White NZ (4/5/2010)
TheSQLGuru (4/5/2010)
NOTE: I am not 100% sure specifying an object ID in the DMV gets it to read JUST that object and I don't have time to do a test right now.It does, yes. This is a great suggestion.
I get lucky sometimes ... 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply