DBCC SHOWCONTIG

DBCC SHOWCONTIG

--Run following code for example--

USE [AdventureWorks2012]
GO
DBCC SHOWCONTIG('Purchasing.ProductVendor')

-------------------------------------------------
DBCC SHOWCONTIG scanning 'ProductVendor' table...
Table: 'ProductVendor' (722101613); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 5
总页数
- Extents Scanned..............................: 3        
总区数
- Extent Switches..............................: 2         
区读取跳转次数
- Avg. Pages per Extent........................: 1.7        
平均页数/区
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]  
扫描密度【最少区读取次数:实际区读取次数】(官方解释为区切换/跳转次数)
- Logical Scan Fragmentation ..................: 40.00%      
乱序页
- Extent Scan Fragmentation ...................: 66.67%       
乱序区
- Avg. Bytes Free per Page.....................: 1472.0       
每页空余空间字节数
- Avg. Page Density (full).....................: 81.81%       
每页已用空间量(%)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- Messages (WITH FAST)

-------------------------------------------------

DBCC SHOWCONTIG scanning 'ProductVendor' table...

Table: 'ProductVendor' (722101613); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 5
- Extent Switches..............................: 2
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 40.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.





/********* Read The Result ************/


Data space: 0.039 MB 
Page   = 页 (8 KB)
Extent = 区 (8 Pages)


- Pages Scanned................................: 5

  0.039 MB ==> 40 KB ==> "5" Pages is enough.


- Extents Scanned..............................: 3

  5 pages in "3" extents.


- Extent Switches..............................: 2

  Jump "2" times across 3 extents.


- Avg. Pages per Extent........................: 1.7

  5 pages / 3 extents = "1.7" 


- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
  
  ***Important
  Best Count:  The minimal Extent Number that can hold all pages. There is only 5 pages. "1" extent is enough. Therefore, best read count is "1".
  (最少区跳转次数。或者说，能装下所有Page的最少的Extent数，即最少的区读取次数。)

  Actual Count: Extent Switches + 1 = "3". It is possible that "Actual Count > Extents Sanned". (means at least one extent has been read more than once.)
  (实际区跳转次数。如果Page有乱序，则会导致区之间的跳转次数增加。有的区可能会被读取多次。)


- Logical Scan Fragmentation ..................: 40.00%
  
  ***(Important) The ratio of Pages with a different physical order from the logical order.
  (有40%的Page是“乱放”的，即逻辑上的下一页数据，物理上没有放在隔壁页的位置，而放到别处去了。)
  (打比方说，全家住旅馆时，你家人没住你隔壁房间，而被安排到远处的另一个房间去了。)


- Extent Scan Fragmentation ...................: 66.67%

  ***(Important) The ratio of Extents with a different physical order from the logical order.
  (同理，有66.67%，即3个中有2个Extent是“乱放”的。)


- Avg. Bytes Free per Page.....................: 1472.0

  The free/empty/unused/available/redundant/usable/spare/unspent/wasted space in a page.


- Avg. Page Density (full).....................: 81.81%

  Here, [ (8192 - 1472) / 8192 ] * 100% ==> 81.81% 




/********** Defragment ************/


EXEC sp_helpindex 'table_name'   ---- check index name

ALTER INDEX index_name ON table_name REBUILD



http://msdn.microsoft.com/en-us/library/ms175008(v=sql.110).aspx


/**************************************************************/
------ Test --------


IF OBJECT_ID('ProInv', 'U') IS NOT NULL
  DROP TABLE [ProInv];
GO

select ISNULL(ROW_NUMBER() OVER(ORDER BY [ProductID],[LocationID]), -1 )as RowNum, *
into ProInv
from [AdventureWorks2012].[Production].[ProductInventory]

    ----(1069 row(s) affected)

dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 468.0
- Avg. Page Density (full).....................: 94.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



alter table ProInv add constraint PK_rn Primary key (RowNum); 

dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 44.44%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



alter index PK_rn on ProInv REBUILD;

dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.8
- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
- Logical Scan Fragmentation ..................: 66.67%
- Extent Scan Fragmentation ...................: 60.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


/*

囧.....    

 I found this...

https://connect.microsoft.com/SQLServer/feedback/details/244214/index-rebuild-doesnt-affect-fragmentation

Description

We've got a few small tables (~1000) rows that have a high degree of fragmentation. When we run ALTER INDEX indexname ON schema.tablename REBUILD, no change happens in the fragmentation.


Posted by Microsoft on 12/8/2006 at 4:03 PM
This is by-design. 

For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature. 

If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.

When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation. 

Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve). 

If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).

*/