August 30, 2009 at 9:53 am
Hi,
We have Share point databases in SQL Server 2005 Enterprise edition 64 bit.I'm analyzing how our Indexes are getting fragmented and appropriate action to defrag them. Before Index reorganize/rebuild, I have ran the below query to know current index fragmentation.
select database_id, object_id, index_id, page_count,avg_fragmentation_in_percent from
sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL,NULL)
where page_count >1000 AND avg_fragmentation_in_percent >10
Results:
Databasedatabase_idobject_idIndex_idPage_countavg_fragmentation_in_percent
SharedServices1_Search206135772240273082.05128205
We have 21 databases but I got only one index that got fragmented in one database.
This is means, our databases indexes are not getting fragmented right? Is it enough, if I rebuild the above index only?
In this scenario, please advice me what should I do?
August 30, 2009 at 12:40 pm
gmamata7 (8/30/2009)
Hi,This is means, our databases indexes are not getting fragmented right?
Keep in mind that the query you are currently using for your index analysis is only considering indexes that have more than 1000 data pages.
So you could still potentially have indexes within your databases that have fragementation but are being excluded from your analysis.
Given that your current investigations have only turned up a single index candidate for defragmentation, you may wish to consider reducing the page threshold of your query to identify smaller indexes (those with <1000 data pages) for consideration.
August 30, 2009 at 12:51 pm
thanks,
I'm considering page count> 1000, because as Experts says, Indexes having pages less than 1000 pages require NO index De-fragment.
Correct me I understood wrong?
August 30, 2009 at 1:04 pm
You are correct in your understanding. The school of thought on this one is that because the size of the indexes in question is so small, there will be either negligible or no noticeable performance improvement to be had. That said, it does not take away from the fact that the underlying physical indexes themselves are still fragmented, just because they are smaller.
Conversely then, the overhead in optimizing such indexes is often so small that some administrators decide to go ahead and do so anyway. It's about covering all your bases if you will. If there is the potential for a performance improvement, no matter how small, why not take advantage of it.
August 30, 2009 at 1:09 pm
Smaller indexes tend not to defragment well, there'll often be fragmentation after the rebuild. Also, fragmentation's only an issue when doing large range scans of an index from disk. Small indexes are very often fully in memory and hence the fragmentation's immaterial.
There's nothing wrong with defragging them, it's just that it's often a waste of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 1:21 pm
There's nothing wrong with defragging them, it's just that it's often a waste of time.
It's like an OCD thing for some us 🙂
August 30, 2009 at 1:47 pm
thanks,
Can I have the script to know the fragmentation level for all the indexes in a database.Because
1. I want to make a note of Fragmentation level for all the indexes in database
2. and then want to rebuild the indexes
3. and then again want to see how much the fragmentation is reduced so that I can have how the indexes are getting fragmented.
thanks for your help.
August 30, 2009 at 1:51 pm
gmamata7 (8/30/2009)
thanks,Can I have the script to know the fragmentation level for all the indexes in a database.Because
1. I want to make a note of Fragmentation level for all the indexes in database
2. and then want to rebuild the indexes
3. and then again want to see how much the fragmentation is reduced so that I can have how the indexes are getting fragmented.
thanks for your help.
Lookup DBCC ShowContig especially the TABLERESULTS option. Yeah, I know... they're moving away from it. Unless you're planning on upgrading to something else tomorrow, just do it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2009 at 1:56 pm
Take the query from your first post and just remove the where clause. That will give you the fragmentation levels of all indexes in the entire server, regardless of page count or fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 3:31 pm
To pre-empt the question: bear in mind that the current versions of SQL Server make it almost impossible to fully defragment indexes which use less than 25 pages at a particular index level. For index levels of 24 pages or less, the first 8 pages will be allocated from mixed extents rather than uniform extents. The first eight pages are therefore very unlikely be allocated contiguously, and the fragmentation counters reflect that fact.
August 30, 2009 at 4:05 pm
thanks,
I have the below query to the fragmentation for all databases and as soon as I ran the query, I got Very High Disk Queue length alarm for Data drive from Spot light, saying High Disk Queue length 20.3.
select database_id, object_id, index_id, page_count,avg_fragmentation_in_percent from
sys.dm_db_index_physical_stats (null,NULL,NULL,NULL,NULL)
Is this normal of getting High Disk Queue length of 20.3 when running this query?
August 30, 2009 at 4:14 pm
Yes, because in the mode selected SQL Server has to examine every page.
See http://msdn.microsoft.com/en-us/library/ms188917.aspx for details of the mode parameter (the Remarks section in particular).
The same page is available in Books Online - look up 'sys.dm_db_index_physical_stats'.
Paul
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply