Two weeks ago at SQL Connections in Orlando, FL, I got to participate in a session that Paul and Kimberly do at the end of the conference titled, \u201cFollow the Rabbit.\u201d The premise of the session is that Paul and Kimberly throw a big list of topics up on the screen and anyone in the audience can ask any question they have about one of those topics and Paul and Kimberly will try to answer it. I wasn\u2019t the only person to end up participating in this session answering questions, Maciej Pilecki another MVP who also recently passed the Microsoft Certified Masters Exam for SQL Server 2008 answered a number of questions as well. One of my favorite questions that was asked during this session was \u201cDoes index fragmentation matter with SSD\u2019s anymore?\u201d Paul\u2019s answer to the question was very practical and dealt with the wasted space utilization that excessive index fragmentation can cause in a database that uses a uniqueidentifier column with newid() as the primary key, and given the cost per gigabyte for SSD storage this could be quite significant. Paul pointed out a couple of other points that escape me, primarily because my mind immediately started churning ideas about how to test the impact that index fragmentation actually has on a database.<\/p>\n
It should be no surprise that this blog post is going to use Extended Events, it has after all been my favorite feature in SQL Server since it was first released in an early CTP for SQL Server 2008. One of the sessions that I presented at SQL Connections last month was on Extended Events and I made some pretty heavy use of the I\/O related events as a part of that session. I also made heavy usage of the I\/O related events back in December for a couple of my XEvent a Day blog posts<\/a>, so I knew that I could really get some detailed information back from the system about what kind of impact fragmentation really had on a database, and since I have a SSD in my laptop, which while not enterprise class is more than worthy of performing the tests on, I thought I\u2019d give a whirl at figuring out how much impact fragmentation really had.<\/p>\n When I began working on this problem, I started out using Windows Server 2008R2 and SQL Server 2008R2, primarily because it is my standard VM platform for the Immersion Training<\/a>, and for presentations and it was immediately available. I originally intended to use the I\/O size information from the file_read_completed event to aggregate the I\/O sizes being read from disk, and I soon learned that this was not available in SQL Server 2008 as I had originally thought. I knew I had used it in the past so I went back to my XEvent a Day blog series and found An XEvent a Day (29 of 31) \u2013 The Future \u2013 Looking at Database Startup in Denali<\/a>. <\/p>\n Bummer!<\/p>\n I happened to have my old Denali CTP1 VM that I used to create that blog post on a external hard disk so I decided to make use of it for the investigation. I had originally hoped to be able to create a reproducible demo that anyone could use in their SQL Server 2008 environment to see the impact of fragmentation, but since it is not possible this post will be based on the information available through SQL Server Denali instead.<\/p>\n The first thing that we will need is a database with two tables that have identical data in them; one fragmented and the other not. To create this, we\u2019ll use a uniqueidentifier with newid() as the primary key for the first table and a uniqueidentifier with newsequentialid() as the primary key for the second table. Then we will load approximately 1000 pages worth of data into the first table and then copy that data into the second table, and rebuild the indexes on the second table to remove any residual fragmentation from it.<\/p>\n\n
CREATE<\/span> DATABASE<\/span> FragmentationTest<\/pre>\nGO<\/span><\/pre>\nUSE<\/span> FragmentationTest<\/pre>\nGO<\/span><\/pre>\nCREATE<\/span> TABLE<\/span> GuidHighFragmentation<\/pre>\n(UniqueID UNIQUEIDENTIFIER DEFAULT<\/span> NEWID() PRIMARY<\/span> KEY<\/span>,<\/pre>\n FirstName nvarchar(50) NOT<\/span> NULL<\/span>,<\/pre>\n LastName nvarchar(50) NOT<\/span> NULL<\/span>)<\/pre>\nGO<\/span><\/pre>\n <\/pre>\n
CREATE<\/span> NONCLUSTERED<\/span> INDEX<\/span> IX_GuidHighFragmentation_LastName<\/pre>\nON<\/span> GuidHighFragmentation(LastName)<\/pre>\nGO<\/span><\/pre>\n <\/pre>\n
CREATE<\/span> TABLE<\/span> GuidLowFragmentation<\/pre>\n(UniqueID UNIQUEIDENTIFIER DEFAULT<\/span> NEWSEQUENTIALID() PRIMARY<\/span> KEY<\/span>,<\/pre>\n FirstName nvarchar(50) NOT<\/span> NULL<\/span>,<\/pre>\n LastName nvarchar(50) NOT<\/span> NULL<\/span>)<\/pre>\nGO<\/span><\/pre>\n <\/pre>\n
CREATE<\/span> NONCLUSTERED<\/span> INDEX<\/span> IX_GuidLowFragmentation_LastName<\/pre>\nON<\/span> GuidLowFragmentation(LastName)<\/pre>\nGO<\/span><\/pre>\n <\/pre>\n
INSERT INTO<\/span> GuidHighFragmentation (FirstName, LastName)<\/pre>\nSELECT<\/span> TOP<\/span> 1000<\/pre>\n a.name, b.name<\/pre>\n
FROM<\/span> master.dbo.spt_values AS<\/span> a<\/pre>\nCROSS<\/span> JOIN<\/span> master.dbo.spt_values AS<\/span> b<\/pre>\nWHERE<\/span> a.name IS<\/span> NOT<\/span> NULL<\/span> <\/pre>\n AND<\/span> b.name IS<\/span> NOT<\/span> NULL<\/span><\/pre>\nORDER<\/span> BY<\/span> NEWID()<\/pre>\nGO<\/span> 70<\/pre>\n <\/pre>\n
INSERT INTO<\/span> GuidLowFragmentation (FirstName, LastName)<\/pre>\nSELECT<\/span> FirstName, LastName<\/pre>\nFROM<\/span> GuidHighFragmentation<\/pre>\nGO<\/span><\/pre>\n <\/pre>\n
ALTER<\/span> INDEX<\/span> ALL<\/span> ON<\/span> GuidLowFragmentation REBUILD<\/pre>\n<\/p><\/div>\n<\/blockquote>\n