I have a databases where I am performing some house cleanup. I ran DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES on a database 258 out of 604 indexes reported scan density below 70. I ran a job DBCC REINDEX and sp_update stats and the numbers didnt change much. What are exceptable numbers for Scan Density? And how do I go about raising these fragmentation levels? Do I need to create drop existing all CLUSTERED INDEXES for it to work?
When you run DBCC SHOWCONTIG first index ID 0 is table itself. There is no way, execpt recreate table and export/import data. Look ID different than 0, those can defragement using DBCC RBINDEX. Also search in this Forum for similars topics about defragmentation. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
By definition, scan density if the ratio of Extents Scanned to Extents Switches. For example, if the Extents Scanned is 2,374 and the Extent Switches is 9,239, then the scan density is 25.7%. To make sense out of this, we need to know what Extents Scanned and Extent Switches mean. Extents Scanned is the number of extents in a table or index. Extent Switches is the number of times that the DBCC commands moved from one extent to another extent while transversing all the pages of the table or index. In other words, this is the number of times a page in the scan was on a different extent than the previous page in the scan. Also, Scan Density is only a valuable indicator of density if the table or index does not span multiple files. If multiple file are involved, then Scan Density is not a reliable piece of data and should be discarded. To resolve fragmentation issues, you can either rebuild your indexes, or run DBCC INDEXDEFRAG. So if you have done this already, and it appears you have, and you still have a low Scan Density value, then it may be possible that your tables or indexes span one more than one file. ----------------------------- Brad M. McGehee, MVP Webmaster SQL-Server-Performance.Com
Thanks for the response Luis and Brad. All indexes and files are in the same file. I gues my question is which method is proved to produce better results DBCC DBREINDEX, DBCC INDEXDEFRAG OR REBuilding the indexes entirely?
There is a lot of documentation and discussion, even in this forum, about DBREINDEX and DBCC INDEXDEFRAG. There is nothing better like rebuilding (delete and create) entirely. The question is: is neccesary?. If no way to reduce fragmentation, go ahead. But if were you delete and create is last chance. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
What is the fill factor of the indexes on these tables? A specified low fillfactor will affect scan density. Tom Pullen DBA, Oxfam GB
All fill factors are set to default (0). I am still analyzing which tables are static and which are produce a lot of activity Insert, update, delete, so that I can adjust accordingly. I dont think that should of mattered much since I am doing all this on a test server where there is not activity to cause additional fragment.
How many index per table do you have?. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Is it a dedicated SQL server or OS resources are shared by other applications? Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
If the tables have very small numbers of rows, they are likely to be on shared extents, which means they will have low scan densities. Typical values such as 50, 25 and so on will be seen if that is the case. Tom Pullen DBA, Oxfam GB
Sorry for not getting back sooner. Yeah most of the tables that had a low scan density were smaller tables. It is a dedicated SQL server, but satya can you please explain how that would affect scan density results? The tables are well indexed.
Small tables has severals indexs or only one cluster index? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Did you try using DBREINDEX insteaD REINDEX?, some times I found differences. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
How about DBREINDEX and INDEXDEFRAG, any gain in performance? Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi ya, DBCC DBREINDEX is the same as dropping and recreating all indexes. This won't alter the data in the table unless there is a clustered index on the table. DBCC INDEXDEFRAG is a lighter weight method. It won't tidy up the index as well as DBREINDEX Cheers Twan
Could you post DBCC SHOWCONTIG after RBREINDEX of any of those tables? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Here are the results from one of the tables after running dbbc dbreindex. This database has about 100 tables so i just showed one example DBCC SHOWCONTIG scanning 'PM30200' table... Table: 'PM30200' (864722133); index ID: 0, database ID: 10 TABLE level scan performed. - Pages Scanned................................: 471 - Extents Scanned..............................: 70 - Extent Switches..............................: 69 - Avg. Pages per Extent........................: 6.7 - Scan Density [Best Count:Actual Count].......: 84.29% [59:70] - Extent Scan Fragmentation ...................: 94.29% - Avg. Bytes Free per Page.....................: 1602.4 - Avg. Page Density (full).....................: 80.20% DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC SHOWCONTIG scanning 'PM30200' table... Table: 'PM30200' (864722133); index ID: 2, database ID: 10 LEAF level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 5 - Extent Switches..............................: 6 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 42.86% [3:7] - Logical Scan Fragmentation ..................: 10.00% - Extent Scan Fragmentation ...................: 40.00% - Avg. Bytes Free per Page.....................: 944.1 - Avg. Page Density (full).....................: 88.34% DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC SHOWCONTIG scanning 'PM30200' table... Table: 'PM30200' (864722133); index ID: 3, database ID: 10 LEAF level scan performed. - Pages Scanned................................: 18 - Extents Scanned..............................: 5 - Extent Switches..............................: 6 - Avg. Pages per Extent........................: 3.6 - Scan Density [Best Count:Actual Count].......: 42.86% [3:7] - Logical Scan Fragmentation ..................: 11.11% - Extent Scan Fragmentation ...................: 60.00% - Avg. Bytes Free per Page.....................: 1084.3 - Avg. Page Density (full).....................: 86.60% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If that was after RBINDEX, well this is infrequent. Read the following to find out what I mean. http://www.sql-server-performance.com/rd_index_fragmentation.asp HTH Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
