September 22, 2015 at 8:51 am
We use Ola Hallengren’s maintenance on all of our servers, which is a really nice, robust package. I highly recommend using Ola's maintenance. It uses a low, medium, and high fragmentation threshold to determine what to do with an index, based on what options are available for that particular instance, and edition of SQL Server, as follows:
Fragmentation Level Task
LOW (less than 5%) : Do nothing
MEDIUM (5% - 10%): INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE
HIGH (10% or more): INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE
Recently, a vendor has challenged our indexing strategy, citing the Scan Density percentage that can be seen when running DBCC SHOWCONTIG. All of the indexes on the tables cited by the vendor were below 10% fragmentation, but the scan density percentage indicated a lot of extent fragmentation.
It seems that rebuilding an index using the ALTER INDEX ALL <table_name> command will rebuild all the indexes in a table, but it seems to have little or no effect on scan density. However, DBCC DBREINDEX(table_name,’’,90) handily addresses both the indexes, and the scan density percentage. Scan density is basically how fragmented the extents containing the index have become.
Is there a way to include or force scan density improvements with the ALTER INDEX ALL <table_name> command?
Things to note:
All of the databases files are on SAN storage. Does extent fragmentation even matter?
None of the indexes reside on multiple files.
This is not about HEAPS, which are a separate issue.
September 22, 2015 at 9:18 am
Not sure I can answer your question, but I would like to point out that both DBCC SHOWCONTIG and DBCC DBREINDEX have been deprecated by Microsoft and will be removed from an as yet determined future version of SQL Server.
You should use sys.dm_db_index_physical_stats in place of DBCC SHOWCONTIG and ALTER INDEX in place of DBCC DBREINDEX.
September 22, 2015 at 9:35 am
Thanks, Lynn. I am aware of this, but did not want to use that argument when trying to convince the vendor and customer that we are doing our job despite what the vendor says.
September 22, 2015 at 9:54 am
Looking back at your original post and seeing that your databases reside on a SAN, I am thinking that extent fragmentation may not be as critical. I base this on the fact that your database files (mdf/ldf) are probably spread across multiple disks meaning that physically the extents won't be physical adjacent to each other but may be logically adjacent.
Since using the new dmvs for determining fragmentation and using ALTER INDEX to accomplish the rebuild or reorganization of indexes, I have had no issues with performance. Looking closely at the BOL entry DBCC SHOWCONTIG and sys.dm_db_index_physical_stats I find that the later can provide more information and the fact that scan density is no longer reported makes me wonder if Microsoft dropped that item of information because it was not as relevant as the additional information provided in the new dmvs.
The best way to determine if you are doing the most you can is to actually run tests using both methods. Your client and the vendor need to understand, however, that at any time the DBCC methods can go a way and there may not be much notice that it is happening.
At this, I'll try to get someone with more knowledge of indexing to drop by and add their thoughts.
September 22, 2015 at 10:16 am
Again, thank you Lynn.
That is completely in line with what we are seeing. There is no performance problem, and the vendor is going by a guideline that is still based on SQL 2000 guidelines. We will point out the deprecation of his methods, and focus on the performance going forward. Wish us luck.
Thanks
Jeff B
St. Louis, MO
September 23, 2015 at 3:03 am
Scan density was removed because it was confusing. Extent fragmentation is only reported for heaps now, for much the same reasons. In the old Show_Contig, there were too many values being reported, not all being meaningful and hence you got disagreements like the one mentioned here.
Don't mix up logical fragmentation/extent fragmentation and physical fragmentation. The latter is what has little meaning on a SAN because of how the files are spread out across the drives.
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
September 23, 2015 at 6:40 am
Thanks, Gail.
So scan density was basically physical fragmentation? Is this correct?
September 23, 2015 at 6:41 am
chudman (9/23/2015)
So scan density was basically physical fragmentation? Is this correct?
No.
SQL's not aware of physical fragmentation, of how the files are placed on the drives. Which is why I said not to mix up logical/extent fragmentation (position of things in the data file) and physical fragmentation (position of files on drives)
Scan density was just confusing.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply