SQL Fool Adventures in SQL Tuning – a blog for the rest of us

Use sandbox;
Go
 
/* Create a table to promote fragmentation */
Create Table dbo.fragmented
(
    myGuid uniqueidentifier Not Null
 
    Constraint PK_fragmented Primary Key Clustered (myGuid)
);
 
 
/* Populate our table */
Set NoCount On;
Declare @iterations int = 0;
 
While @iterations < 250000
Begin
 
    Insert Into dbo.fragmented
    Select NewID();
 
    Set @iterations += 1;
 
End;
 
 
/* Verify the number of records in our table */
Execute sp_spaceused 'dbo.fragmented';
 
 
/* Let's take a look at sys.dm_db_index_physical_stats */
Select Object_Name(ddips.object_id) As 'tableName'
    , i.name As indexName
    , ddips.*
From sys.dm_db_index_physical_stats (DB_ID(), Object_ID(N'fragmented'), NULL, NULL , 'DETAILED') As ddips
Join sys.indexes As i
    On ddips.object_id = i.object_id
    And ddips.index_id = i.index_id;
 
 
Select *
From sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fragmented'), NULL, NULL , 'LIMITED');
 
 
/* ShowContig should return us the same information */
DBCC ShowContig ('fragmented');
 
 
/* ExtentInfo is an undocumented command that will show us the number of allocated extents */
DBCC ExtentInfo (0, 'fragmented', 1);
 
 
/* Grab our page number */
SELECT OBJECT_NAME(p.OBJECT_ID) AS 'tableName'
    , i.name AS 'indexName'
    , p.partition_number
    , au.type_desc
    , CONVERT (VARCHAR(6),
      CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
         SUBSTRING (au.first_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
         SUBSTRING (au.first_page, 3, 1) +
         SUBSTRING (au.first_page, 2, 1) +
         SUBSTRING (au.first_page, 1, 1))) AS 'firstPage'
    , CONVERT (VARCHAR(6),
      CONVERT (INT, SUBSTRING (au.root_page, 6, 1) +
         SUBSTRING (au.root_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.root_page, 4, 1) +
         SUBSTRING (au.root_page, 3, 1) +
         SUBSTRING (au.root_page, 2, 1) +
         SUBSTRING (au.root_page, 1, 1))) AS 'rootPage'
    , CONVERT (VARCHAR(6),
      CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) +
         SUBSTRING (au.first_iam_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) +
         SUBSTRING (au.first_iam_page, 3, 1) +
         SUBSTRING (au.first_iam_page, 2, 1) +
         SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'
FROM sys.indexes AS i
JOIN sys.partitions AS p
    ON i.OBJECT_ID = p.OBJECT_ID
    AND i.index_id = p.index_id
JOIN sys.system_internals_allocation_units AS au
    ON p.hobt_id = au.container_id
WHERE OBJECT_NAME(p.OBJECT_ID) = 'fragmented'
ORDER BY tableName;
 
 
/* View our pages */
DBCC TraceOn (3604);
DBCC Page (sandbox, 1, pageNumberHERE, 3);
DBCC TraceOff (3604);
 
 
/* Rebuild our index */
Alter Index PK_fragmented On dbo.fragmented REBUILD;
 
 
/* Clean-Up! */
Drop Table dbo.fragmented;