Index Fragmentation PotW Webcast – Now Available

The Pain-of-the-Week webcast I did last Thursday is now online! You can find it here: http://www.quest.com/common/registration.aspx?requestdefid=23092.

The topic of the webcast is index fragmentation: what is it, how to identify it, and how to fix it. You can find the materials for this webcast in my previous blog post. I’ll also be updating SQLServerPedia with the materials, hopefully tonight.

Thanks to the great feedback I’ve received, I’ll be doing another Pain-of-the-Week webcast, this time with Brent Ozar. More info to follow as we hammer out the specifics.

In a related note, I have updated my index defrag script. I’m just waiting for my awesome beta testers to let me know everything works for them. It works just fine on my servers (lol) but I don’t have access to any Standard editions, etc., so I just want to be extra sure before I unleash it on the SQL world. I should have it online tomorrow.

Source: http://sqlfool.com/2009/03/index-fragmentation-potw-webcast/

PoTW Webcast Materials

For those interested, here’s the links I mentioned during my presentation:

… and here’s the scripts that I used during my presentation:

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;

Thank you for watching the webcast! :)

Michelle

Source: http://sqlfool.com/2009/03/potw-webcast-materials/