There's a lot of confusion around duplicate indexes and I think a lot of it has to do with how sp_helpindex (or even SSMS) display what's in an index. What's actually in the index - and how it's structured - are not always what they seem. This was the original motivation behind my rewrite of sp_helpindex but even since then, I've still seen a lot of confusion. In today's blog post I'm going to first explain EXACTLY which indexes are the same and which aren't - as well as the faults in the tools.

So, starting with index structures... (it all starts with internals :) )

The clustered index IS the data. The KEY to the clustered index (what I often refer to as the clustering key) defines the way that the data is ordered (not necessarily truly, physically ordered on disk - but ordered LOGICALLY). And, no, I'm not going to rehash every bit of internals here... just a few reminders.

A nonclustered index is duplicated data (similar to an index in the back of a book). This duplicated data can be used to help reference the actual data (exactly like an index in the back of a book) OR can be used to actually respond to requests (for example, if you're solely looking for a count of people with a name that begins with 'S' then an index that has LastName in the index could be used to count them - without actually "looking up" the actual data). So, there are some very powerful uses to indexes. But, alas, this isn't a post on using indexes or indexing strategies - this is ALL about internals (and understanding the structure of an index). So, I'm going to cut to the chase!

A nonclustered index always has:

• Key (this is what defines the order of the index)
• A leaf-level entry (this is the actual data stored in the index + the lookup value* + any included columns) - however, ALL of these columns are only stored once (and they've ALWAYS only been stored once here so, even if you reference a column that's part of the lookup value, SQL Server will NOT duplicate it again).

*So, what is this lookup value?

The lookup value is what SQL Server uses to reference the actual data row. If a table has a clustered index then the lookup value is the clustering key (and EVERY column defined by it). If a table does not have a clustered index (it's then a HEAP) then SQL Server uses something called a RID. A RID is an 8-byte structure consisting of 2:4:2 bytes which breakdown into 2 for the FileID, 4 for the PageID and 2 for the slot number. And while RID's (and their history) are very interesting - it's not entirely relevant here (how they specifically work and/or their structures) but if/when they're in an index, I'll list is as RID.

Let's now put all of this together with an example (or two) starting with a relatively easy one.

USE JunkDB
go

CREATE TABLE Test
(
    TestID  int identity,
    [Name]  char(16)
)
go

CREATE UNIQUE CLUSTERED INDEX TestCL ON Test (TestID)
go

CREATE INDEX TestName ON Test([Name])
go

sp_helpindex Test
go

The output shows:

index_name      index_description                               index_keys
TestCL          clustered, unique located on PRIMARY            TestID
TestName        nonclustered located on PRIMARY                 Name

And, while that looks right, it's horribly misleading. The index that's on TestName also includes TestID in the index as well. And, not just in the leaf level but in the tree (for ordering purposes). So, that should really show Name, TestID. But, it's a bit more confusing if you throw in this:

CREATE UNIQUE INDEX TestNameUnique ON Test([Name])
go

sp_helpindex Test
go

The output shows:

index_name      index_description                               index_keys
TestCL          clustered, unique located on PRIMARY            TestID
TestName        nonclustered located on PRIMARY                 Name
TestNameUnique  nonclustered, unique located on PRIMARY         Name

And, at this point, it doesn't look like there's any difference at all between the second and third indexes (well, except that the third index requires that the values be unique - shown in the description). But, as for the "index_keys" they look the same. However, they're actually not the same (in all parts of the tree). So, this is why I tend to differentiate between the "leaf" and the non-leaf levels of an index (when I describe them). And, it only gets more complicated when you throw in included columns (2005+).

So, how to do you tell the difference? Unfortunately, there are NO tools within SQL Server (or even any 3rd party tools that I know of) that display this through the UI, etc. But, you could start by using my replacement to sp_helpindex. I've updated and rewritten it a few times but the latest one will always be in the sp_helpindex reqrites category here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx. I personally think that's the easiest (and is the reason why I originally wrote it!). Using it you can see how the output is more detailed.

The output shows (specifically showing ONLY the last 2 columns):

columns_in_tree              columns_in_leaf
[TestID]                     All columns "included" - the leaf level IS the data row.
[Name], [TestID]             [Name], [TestID]
[Name]                       [Name], [TestID]

Now, we're getting somewhere. We can definitely see that these two indexes ARE different. A nonunique nonclustered needs to have the lookup value pushed up into the tree (for navigation). A unique nonclustered index does not.

Next, let's go with a [just slightly more challenging] example:

USE JunkDB
go

CREATE TABLE Member
(
    MemberNo    int     identity,
    FirstName   varchar(30)     NOT NULL,
    LastName    varchar(30)     NOT NULL,
    RegionNo    int
)
go

CREATE UNIQUE CLUSTERED INDEX MemberCL ON Member(MemberNo)
go
   
CREATE INDEX MemberIndex1
ON Member(FirstName, RegionNo, MemberNo)
INCLUDE (LastName)
go

CREATE INDEX MemberIndex2
ON Member(FirstName, RegionNo)
INCLUDE (LastName)
go

CREATE INDEX MemberIndex3
ON Member(FirstName, RegionNo)
INCLUDE (MemberNo, LastName)
go

CREATE UNIQUE INDEX MemberIndex4
ON Member(Firstname, RegionNo)
INCLUDE (MemberNo, lastname)
go

First, let's review with sp_helpindex:

sp_helpindex Member
go

The output shows:

index_name      index_description                           index_keys
MemberCL        clustered, unique located on PRIMARY        MemberNo
MemberIndex1    nonclustered located on PRIMARY             FirstName, RegionNo, MemberNo
MemberIndex2    nonclustered located on PRIMARY             FirstName, RegionNo
MemberIndex3    nonclustered located on PRIMARY             FirstName, RegionNo
MemberIndex4    nonclustered, unique located on PRIMARY     FirstName, RegionNo

Looking solely at sp_helpindex it LOOKS like the first nonclustered index is different from the others and that the 2nd, 3rd and 4th nonclustered indexes are the same. However, that's actually NOT true. Next, let's use my version of sp_helpindex.

sp_SQLskills_SQL2008_helpindex Member
go

The output (again, just showing the last two columns of output):

columns_in_tree                           columns_in_leaf
[MemberNo]                                All columns "included" - the leaf level IS the data row.
[FirstName], [RegionNo], [MemberNo]       [FirstName], [RegionNo], [MemberNo], [LastName]
[FirstName], [RegionNo], [MemberNo]       [FirstName], [RegionNo], [MemberNo], [LastName]
[FirstName], [RegionNo], [MemberNo]       [FirstName], [RegionNo], [MemberNo], [LastName]
[FirstName], [RegionNo]                   [FirstName], [RegionNo], [MemberNo], [LastName]

From this, you can see that all 4 indexes have the same leaf level but index 4 has a slightly different tree structure. Ultimately, it's indexes 1, 2 and 3 that are the same and index 4 is actually [slightly] different. How they're different (outside of the fact that the 4th index guarantees uniqueness) is a bit beyond the scope of this post. But, yes, there are some [relatively minor in this case] differences. And, since I'm ONLY looking for indexes that are the same then only 1, 2 and 3 fit that requirement.

And, things get MUCH more complicated if you have multicolumn clustering key and/or more complicated INCLUDEs.

Having said that - how do you find duplicate indexes?

Well... I had started this by putting together a simple way for you to check for dupes with my version of sp_helpindex but then I figured out an issue with included columns. I'm showing the structures as they're defined (and stored). But, in terms of usage - the order of the columns in the INCLUDE does not matter. As a result, two indexes with different orders for their included columns will show as two different indexes (technically they are and they are stored differently). However, there are absolutely NO differences (in terms of usage) so I'll need to write code to adjust for that (to truly find duplicates).

For now, here's some quick code to get you closer. I had written some of this when we discussed this in our London Immersion Event. However, I've tweaked it even further here after thinking about some interesting exceptions. This code will find absolute duplicates (where the order of the structure is completely identical). To use it you'll need to enter in the schemaname and tablename (in bold below).

IF (SELECT count(*) FROM #FindDupes) IS NOT NULL
    DROP TABLE #FindDupes
go

CREATE TABLE #FindDupes
(
 index_id           int,
 is_disabled        bit,
 index_name         sysname,
 index_description  varchar(210),
 index_keys         nvarchar(2126),
 included_columns   nvarchar(max),
 filter_definition  nvarchar(max),
 columns_in_tree    nvarchar(2126),
 columns_in_leaf    nvarchar(max)
)
go

DECLARE @SchemaName sysname,
        @TableName  sysname,
        @ExecStr    nvarchar(max)

SELECT @SchemaName = N'schemaname',     -- MODIFY
       @TableName = N'tablename'        -- MODIFY
       
SELECT @ExecStr = 'EXEC sp_SQLskills_SQL2008_helpindex '''
                    + QUOTENAME(@SchemaName)
                    + N'.'
                    + QUOTENAME(@TableName)
                    + N''''

INSERT #FindDupes
EXEC (@ExecStr)

SELECT t1.index_id, COUNT(*) AS 'Duplicate Indexes w/Lower Index_ID',
        N'DROP INDEX '
            + QUOTENAME(@SchemaName, N']')
            + N'.'
            + QUOTENAME(@TableName, N']')
            + N'.'
            + t1.index_name AS 'Drop Index Statement'
FROM #FindDupes AS t1
    JOIN #FindDupes AS t2
        ON t1.columns_in_tree = t2.columns_in_tree
            AND t1.columns_in_leaf = t2.columns_in_leaf
            AND ISNULL(t1.filter_definition, 1) = ISNULL(t2.filter_definition, 1)
            AND PATINDEX('%unique%', t1.index_description) = PATINDEX('%unique%', t2.index_description)
            AND t1.index_id > t2.index_id
GROUP BY t1.index_id, N'DROP INDEX ' + QUOTENAME(@SchemaName, N']')
                    + N'.'
                    + QUOTENAME(@TableName, N']')
                    + N'.' + t1.index_name
go

I'm planning to bulid a sproc around this next as well as get rid of cases where the included columns are just in different orders next. It will be another sp_ for master and I do hope to wrap it up shortly! That code will REALLY find ALL unnecessary duplicates and help you to drop them! Stay tuned!!

Cheers and thanks for reading!!
kt

Well, it's been a crazy few weeks! Paul and I have been booked solid with client engagements, Immersion Events, conferences and other things (like chapter editing for the upcoming SQL Server Deep Dives Volume 2) and so I've been a bit behind with blogging. I have a ToDo list of items to blog about but I've got one that's fairly urgent. I'll mention a few issues here but above all - don't trust anyone.

OK, I know that sounds a bit paranoid. Not meaning to be but this is a post that I'm adding to my "Just because" series as well as my "Are you kidding me?" series. That's how bad this is... almost as bad as these "stupid bacon related tatoos" - really, someone has this (forever!):

As a DBA (and/or SQL Architect/Database Designer/Developer) we're often tasked with making changes to databases and ideally, we want to use tools to make it easier. While I do agree that many tools help tremendously, I've been shocked lately by a few that have done the wrong thing. Paul blogged about a third party tool that didn't analyze indexes correctly here: Beware of advice from 3rd-party tools around dropping indexes (ultimately, the recommendations for what to drop - were wrong). And, recently, I've been shocked to learn that some of the schema change options in the database designer (in SSMS) are less than optimal when changing index definitions (especially those that handle changes to the clustering key).

Specifically, we had a customer that was doing everything right by testing the changes on a secondary system but SSMS was doing everything wrong (the order in which it made the changes and how it made the changes - were horrible). The end result is that if you're going to make schema changes, you really need to get more insight into what the application is doing. And, many tools have an option to script out the changes rather than execute them. So, my "quick tip" is to do that and see what's going on.

And, if you're going to make schema changes in ANY tool - then work through what those changes are going to do. Most important - consider re-arranging the SQL (especially wrt indexes). If you're ever changing the CL index you need to do the following:

1. Drop nonclustered indexes first
2. Drop the clustered next
   • IMPORTANT NOTES: If you're completely changing it you definitely need to drop the nonclustered and then drop the CL. BUT, if you're keeping the name and only changing the definition (and it's NOT a constraint-based index) then you can use WITH DROP_EXISTING. If you use DROP_EXISTING you do NOT need to drop the nonclustered indexes first.
3. Create the new clustered
4. Recreate the nonclustered indexes

Some of the weird things that we've seen:

1. Some changes to table structure (changing from a heap to a clustered or a clustered to a heap) cause disabled nonclustered to get rebuilt (and re-enabled). That's not even SSMS - that's a general SQL Server problem. In my opinion this means that disabling indexes is somewhat broken. So... gotta use drop/create (which is a bummer!).
2. Changes to the clustering key in SSMS are sometimes done by dropping the clustered and then recreating the clustered. This will cause your nonclustered indexes to be rebuilt TWICE!
3. If you have col1 that's currently clustered and col2 is currently nonclustered but you want to swap them... you can do that in the UI and then save but here's what they do:
   1. They drop the nonclustered
   2. They drop the clustered (this causes all of the remaining nonclustered to get rebuilt)
   3. They create the nonclustered
   4. They create the clustered (this causes all of the remaining AND the new nonclustered to get rebuilt... again)

This is only a short list of some of the crazy things that we've seen. I'm working on a more comprehensive overview of all of these things but I wanted to get a few of them out there. BEWARE OF THE TOOLS and always use scripting and testing to make sure that things are doing what you think. If you're EVER going to make changes to a critical system - this is exactly what I'd do:

1. Script out *JUST* the objects and their definitions from the production environment
2. Take *just* the schema and then go through and make your changes in the designer. Instead of saving the changes (which would immediately implement them), script out the changes and review the SQL.
3. Run through the SQL and see what it does (but, this is an empty database so time isn't going to be as much of a factor here). If you think there's something wrong - ask around (colleagues/twitter/forums)...
4. Then, once you feel you have a good version of the script THEN backup/restore the production database to your test system (I hope that you have this??) and THEN see how long it takes. If you have new concerns then ask around again!
5. Next, before you consider if for production - you need to thoroughly test your applications. Are they affected by these changes?
6. Then, and only when you've thoroughly tested it - you can consider it for production.

Picky yes... surprises NO. On a critical system you CANNOT afford surprises that create downtime - or worse, data loss.

Thanks for reading!!
kt

I realize that I've created quite a few posts (and rewrites) around sp_helpindex and recently I've been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it's not so obvious where to get it and/or what to do to install it. To make it easier... I'm just going to do a post like this EVERY TIME I have a new version and so there won't be anything else to review/read.

All versions use a base procedure that builds the columns needed to produce the detailed output. So, you need to setup TWO procedures.

Step 1: setup sp_SQLskills_ExposeColsInIndexLevels

Create this procedure first: sp_SQLskills_ExposeColsInIndexLevels.sql (6.73 kb).

This is what gives us the tree/leaf definitions. And, this works for both SQL Server 2005 and SQL Server 2008.

OK, I've talked about the clustering key many times. Here, I thought I'd bring together one final series of details (and links) to help you really understand why I'm so adamant about DESIGNING a clustering key and not just letting SQL Server pick it for you (for example when it defaults to making the primary key clustered). Just because SQL Server defaults to making the primary key clustered doesn't actually mean it's a GOOD clustering key!

The key things that I've always recommended about a GOOD clustering key is that it is: unique, narrow, static and ever-increasing. For more details on the reasons behind this, check out these posts:

• Ever-increasing clustering key - the Clustered Index Debate..........again!
• The Clustered Index Debate Continues...
• GUIDs as PRIMARY KEYs and/or the clustering key
• Disk space is cheap...
• How much does that key cost?
  

And, today, there are two additional items that I want to add to this list: your clustering key should be non-nullable and fixed-width!

First, why non-nullable?

In a regular data row you will ALWAYS have a null block. This is 2 bytes for a column count (in that row) and 1 bit per column (to store the actual null values). If you want to get more details on the internals of a data row, see Paul's post: Inside the Storage Engine: Anatomy of a record. However, the btree of the clustered index and the nonclustered index leaf/non-leaf levels do NOT have to have this "null block" of information if the columns in the index do NOT allow nulls. While this may be only 3 or 4 bytes (depending on the number of columns), it's still 3 or 4 bytes that would have to be added to EVERY nonclustered index's leaf level (for every row). And, it just doesn't have to be there. Why waste space when you don't have to!

Second, and along the same lines, is the need to use fixed-width columns!

Just like in data rows, index rows only have a variable block (offsets + end of row) when there are variable-width columns. If a clustered index is chosen that has one or more variable-width columns then you are adding at least 4 bytes to every row that might not have otherwise needed to be there. In fact, often people forget that uniquifiers are stored in the variable block portion of the row and as a result, a uniquifier really needs 8 bytes (4 bytes for the int + 2 bytes in the variable block for the offset + 2 bytes for the end of row marker).

To show you this, I've created four tables each with the same 3 columns DATA TYPES (table 4 has col1 as an int that's nullable and you can't do that with an identity so I used INSERT/SELECT to copy the data over):

col1 int identity not null,
col2 datetime2(7) not null default sysdatetime(),
col3 datetime2(7) null default sysdatetime()

And, each of the tables has these four nonclustered indexes:

• Non-unique nonclustered on col2
• Unique nonclustered on col2
• Non-unique nonclustered on col3
• Unique nonclustered on col3

The difference is solely within the definition of the clustering key as well as weather or not the values are unique (or nullable)!

CLTable1 is clustered and non-nullable but NOT defined as unique (the values are unique)

CLTable2 is clustered and non-nullable but there are duplicate values in the clustering key

CLTable3 is defined as a unique clustered index

CLTable4 is clustered, nullable and there are duplicate values in the clustering key

The end result is the sizes of all of the keys and the wasted space from different keys!

For CLTable1, CLTable2 and CLTable3 - all of the minimums are the same. For CLTable1 and CLTable3 - the maximums are the same as the minimums. In CLTable3 it's because the records MUST be unique. In CLTable1 it's because the records just happen to be unique right now (meaning there's no overhead unless there are actual dupes).

For CLTable2 the maximums are each 8 bytes larger (highlighted in yellow) EXCEPT where in the btrees of UNIQUE nonclustered indexes where SQL Server does NOT need to duplicate the clustering key (pale pink):

NOTE: If you're not familar with index internals and when/why SQL Server duplicates the clustering key in nonclustered indexes, check out my Index Internals chapter from the SQL Server 2008 Internals title (here's the Amazon link: http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&qid=1291001371&sr=8-1) and my companion content from Chapter 6 here: Companion content for Chapter 6 (Index Internals) of SQL Server 2008 Internals. And, if you really want to see what's in your indexes, check out my updated versions of sp_helpindex here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx.

Finally, CLTable4 has the most interesting results...

Nonclustered indexes 2 and 3 (and the clustered index's btree) are all 3 bytes larger... why? Because the clustering key allows nulls and each of these structures has only 2-3 columns. As a result, the space needed for the null block is 3 bytes (2 bytes for the column count [NCol] and 1 byte for the actual null bitmap [less than 9 columns]).

The btree for index id 3 only needs the nonclustered key column (col2) which does not allow NULLs and the index is unique so the tree is the same as before.

Nonclustered indexes 4 and 5 already had a nullable column and therefore already had a null block.

My point - it all adds up and if it's not truly necessary, then avoid it!

Thanks for reading!
kt

OK, two in two days? What's wrong with me (is what Paul said) after he said - who are you and where is my wife?

But, this one is a short one, specifically a follow-on to my prior post about "Disk space is cheap..."

I did a bit of simple math on the internal overhead and costs of inefficient clustering keys and I wanted to share those as well. If a table is clustered then each and every nonclustered index must include ALL columns of the clustering key in [at least] the leaf level of the clustered index. When a nonclustered index is non-unique then it's even worse in that all columns of the clustering key must go all the way up the tree (into the non-leaf levels). I re-wrote sp_helpindex to give you better insight into what was happening in your nonclustered indexes here: A new and improved sp_helpindex (jokingly sp_helpindex8). I've actually re-written it again to include a column that shows whether or not an index is disabled but it still needs the base procedure [sp_SQLskills_ExposeColsInIndexLevels] from the other post - make sure you download that first before trying to use this sp_helpindex9: sp_SQLskills_SQL2008_helpindex (sp_helpindex9).sql (11.06 kb).

Having said that - what's the overhead in terms of some of your bigger tables?

If you have a 10 million row table with 8 nonclustered indexes then *just* the internal overhead is going to cost you the following with keys of the varying sizes:

And, what if you have a bigger table - say a 100 million row table with 12 nonclustered indexes... the internal overhead is as follows:

So, if you're talking about disk space, IOs, backups, etc. and you have a very inefficient clustering key (say a 5 column "natural key") that's 64 bytes then you have 71.53GB of OVERHEAD where it could have been only 4.47GB. That's a tad over 67GB of wasted disk space, memory (really - do you have 67GB+ of memory to waste??) and what about the long term costs of backing all of this up?? And, I haven't even started to talk about the inefficiencies with the FKs too. Or, the inefficiencies within the nonunique nonclustered index trees as well.

It's a COMPLETE NIGHTMARE!

Please... everyone repeat after me................. DESIGN MATTERS!!! :-)

Thanks for reading!
kt

. 

THAT'S NOT THE POINT!!!

. 

The simple point is that bad (or sloppy/lazy) design cannot be tuned. If you think that data type choice, nullability, keys - don't really matter - you won't scale. It is possible that you may completely fail because of this. Have you ever heard (or possibly said?), let's just get this done - we'll worry about performance later? If you haven't heard it, I'm surprised! I hear this all the time...

Yesterday I gave a lecture at SQLPASS about GUIDs. It wasn't the most well attended (under 200 people) but I suspect that's because of two things: first, our good friend Bob Ward was speaking at the same time (and there were actually a bunch of really good sessions!) AND the simple fact that GUIDs aren't sexy (I agree!). Also, I think that a few folks may have presumed that what I was going to talk about (maybe even solely talk about?) was fragmentation. And, while fragmentation is the most outwardly visable problem with GUIDs - it's by NO MEANS the only problem. And, so I thought I'd blog a few things to think about/remember when trying to design/architect a SQL Server system. Clearly there's got to be a balance between the amount of time you're going to spend on design vs. just "getting it done" but that doesn't mean that NOTHING MATTERS or that you can just do anything with a table and "SQL Server will just 'handle' it." OK, I take that back - SQL Server won't have a choice other than to "just handle it" but some things it just CANNOT handle well. Perform and scalability will suffer and again, your application may fail.

One of the resounding principles of my session (and most of my design talks in general ;-), is that design matters. In fact, in my summary, I said that 3 things really matter in terms of good SQL Server database design/archictecture:

1. Know your data - this helps you make the right decisions in terms of data types/nullability and churn helps with long term maintenance goals (and initial maintenance plans) 
2. Know your workload - this helps you make the right decisions about locking/isolation, optimizing procedural code and indexing strategies (and these are the KEY to a FAST and scalable system)
3. Know how SQL Server works  - this is the one that's often overlooked. And, without information such as "the primary key is enforced by a clustered index and the clustering key is added to ALL nonclustered indexes" then you may inadvertently create a database that grows faster and larger than anticipated where performance slows to a crawl and even management/maintenance becomes a [really HUGE] challenge.

So, while I could go on for ages here I just want to expand on that last point: Know how SQL Server works. Specifically, I want to tie together the PK -> CL Key -> NC Indexes along with the "disk space is cheap" mantra that I also hear ALL THE TIME.

OK - so let's break this down a bit... No matter what your clustered index is - it should be narrow. I do not choose my clustered index for range queries and my choice for the clustering key is NEVER accidental.

Why - because it has a HUGE impact on overall performance. To prove the point (and this was the concept around which my session focused), I created 3 different versions of the SAME "Sales" database. I wanted to show ALL of the impacts of a poorly chosen key - both as CL and really just as a size issue. It's only 12 more bytes than an int, right? What harm can it cause... just wait!

So - to start, I loaded all three databases with roughly 6.7 million rows... and, I made sure everything was clean and contigious so that I'd have the same starting point for all of the tables. I actually strategically started things in one filegroup and then moved things over to another filegroup with 2 files so that I could get some benefits from having multiple files as well (see Paul's excellent post on why a RW filegroup should generally have 2-4 files here: Benchmarking: do multiple data files make a difference?). So, at the initial start I have three databases:

SalesDBInts (inital size with Sales at 6.7 million rows = 334MB):

• Customers - has an ever-increasing identity (int) PK (4 bytes)
• Employees - has an ever-increasing identity (int) PK (4 bytes)
• Products - has an ever-increasing identity (int) PK  (4 bytes)
• Sales - has an ever-increasing identity (int) PK and FKs to Customers, Employees and Products (row size = 27 bytes)

SalesDBGUIDs (inital size with Sales at 6.7 million rows = 1000MB):

• Customers - has a randomly generated (using the NEWID() function) GUID PK (16 bytes)
• Employees - has a randomly generated (using the NEWID() function) GUID PK (16 bytes)
• Products - has a randomly generated (using the NEWID() function) GUID PK (16 bytes)
• Sales - has a randomly generated (using the NEWID() function) GUID PK (16 bytes) and FKs to Customers, Employees and Products (row size 75 bytes)

SalesDBSeqGUIDs (inital size with Sales at 6.7 million rows = 961MB):

• Customers - has a sequentially generated (using the NEWSEQUENTIALID() function) GUID PK (16 bytes)
• Employees - has a sequentially generated (using the NEWSEQUENTIALID() function) GUID PK (16 bytes)
• Products - has a sequentially generated (using the NEWSEQUENTIALID() function) GUID PK (16 bytes)
• Sales - has a sequentially generated (using the NEWSEQUENTIALID() function) GUID PK (16 bytes) and FKs to Customers, Employees and Products (row size 75 bytes)

OK, so here's where the session really starts... I run 10K inserts into the Sales table in each database and then I check and see what happens:

• 10K rows in SalesDBInts takes 00:17 seconds
• 10K rows in SalesDBGUIDs takes 05:07 minutes
• 10K rows in SalesDBSeqGUIDs takes 01:13 minutes

This is already SCARY and should go down into the "Are you kidding me category?" but I also have to add that the hardware and setup for these first few tests are just highlighting a whole myriad of problems. First, I was running with a somewhat crummy setup - a dual-core laptop with only 3GB of memory and this database was on an external USB drive. Certainly not enterprise storage but also not an enterprise size either. For the size of the db we should have been able to do better... wait, we did - with the int-based database things went really well. Only the other two really stunk and the sequential GUID based database definitely faired better than the random (of course - fragmentation, right?). And, yes, that's a part of it... but there's more. And, I thought... no, this can't be right. Let me try again... run 2:

Well, that seems pretty consistent but wow - the random GUID db is really NOT fairing very well... let's try it again:

OK, so you have GOT to be wondering why things are going so horribly wrong? The fragmentation is leading to more page IOs and those also have be put in cache so we're needing a larger and larger cache to handle our GUID database... none of this is good and means you need bigger machines and/or something else to help you out. With the ever-increasing patterns created by the other database we're requiring fewer pages to be read and fewer pages to be cached - these databases are performing somewhat consistently...

OK - so what can we do... let's try FIRST dealing with the fragmentation. To keep it simple, I went to the Books Online for sys.dm_db_index_physical_stats - example D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes. This is pretty good but since these databases had never seen a REBUILD (and definitely not a FILLFACTOR setting, I had to tweak the script slightly to include a generic 90% fillfactor). Here's the line that I modified:

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'

I ran this in ALL three databases but there wasn't much to do in any of them except for the GUID-based database:

SalesDBInts (5 seconds)
Executed: ALTER INDEX [ProductsPK] ON [dbo].[Products] REBUILD WITH (FILLFACTOR = 90)

SalesDBGUIDs (7:51 minutes)
Executed: ALTER INDEX [SalesPK] ON [dbo].[Sales] REBUILD WITH (FILLFACTOR = 90)
Executed: ALTER INDEX [IX_SalesToProductsFK] ON [dbo].[Sales] REBUILD WITH (FILLFACTOR = 90)
Executed: ALTER INDEX [CustomersPK] ON [dbo].[Customers] REBUILD WITH (FILLFACTOR = 90)
Executed: ALTER INDEX [ProductsPK] ON [dbo].[Products] REBUILD WITH (FILLFACTOR = 90)

SalesDBSeqGUIDs (9 seconds)
Executed: ALTER INDEX [ProductsPK] ON [dbo].[Products] REBUILD WITH (FILLFACTOR = 90)
Executed: ALTER INDEX [CustomersPK] ON [dbo].[Customers] REBUILD WITH (FILLFACTOR = 90)

Then, I ran my inserts again...

The first hardware change that I made was that I moved these to an internal SSD drive... and, ran my test again. Let's get rid of all the random IO problems. That's got to help, eh?

• 10K more rows in SalesDBInts takes 00:04 seconds
• 10K more rows in SalesDBGUIDs takes 01:15 minutes
• 10K more rows in SalesDBSeqGUIDs takes 01:02 minutes

WOW - THAT's AWESOME... killing it with iron brings it VERY close to the speed of the Sequential GUIDs as we're completely eliminating the random IOs. This makes our backups faster, etc. but it still doesn't reduce the memory required because of the pages that are going to be required on insert. And, if you have a very large table with a lot of historical data that wouldn't have otherwise needed to be brought into cache this is a BIG problem especially for much larger tables.

I had quite a few more stuff in my demos but it brings us to a really good point... what are our options and what should we consider? First and foremost, how much control do you have? Did you design/architect your system and if so - how much work are you willing to put into it from here? Or, is this an application over which you have no control? Let's take the obvious...

If this is an application over which you have no control then you really have only 2 options:

1. MAINTENANCE (with a good FILLFACTOR)
2. Consider killing it with iron where the most obvious gains are going to be disk IOs (ie. SSDs for the data portion) and memory...

If this is a system over which you do have control... then, I'd suggest changing the CL key at a minimum. Then, I'd make sure you have good maintenance setup for your nonclustered indexes because those will most certainly be fragmented. Then, I'd slow consider changing over your FKs to use the CL key (identity ideally) and then maybe - eventually - you can remove those GUIDs altogether. But, this is NOT an easy thing to do...

If your CL key is a PK then here are your steps:

1. Take the database offline (sorry, I’m just the messenger!!)
2. Disable the FKs
3. Disable the nonclustered indexes
4. Drop the clustered PK (alter table)
5. Optionally, add an identity column?
6. Create the new clustered index
7. Create the PK as nonclustered
8. Enable the nonclustered indexes (alter index…rebuild)
9. Enable the FKs with CHECK (this is very important)
10. Bring the database online

And, there are certainly derivitives of this but the long story short is that it's going to be a painful process. And, I know some of you have work to do... so, I'll end this post here as well as give you a few links to help you continue learning about these issues. The more you know about SQL Server the better your database design and the longer your database will be healthy and scalable.

I've spoken about this in many posts and many of our SQLMag Q&A posts:

• GUIDs as PRIMARY KEYs and/or the clustering key
• Where does that clustering key go?
• Changing the Definition of a Clustered Index (this can help with generating the code above)
• Isn’t the Clustering Key Redundant?
• Should I index my foreign key columns?
• How many indexes should I create?
• How to choose a good index fill factor (this one is a SQLMag post by Paul)
• Is It a Bad Idea to Rebuild All Indexes Every Night? (also by Paul)

However, as much as I've used that - I've always been frustrated by its output. And, so, over the months+, I wrote sp_helpindex3 (this solely added the index_id as the first column and then ordered the output; I've always hated the weird way in which sp_helpindex/2 ordered the output). Then, I started writing sp_helpindex4 which listed the clustering key columns with each index so that I could more easily identify columns on large tables without a lot of scrolling. Ultimately, I didn't like this because the clustering key (or some of the columns in a multi-column clustering key - might already be in the nonclustered index and so I still had to figure things out more manually). And, so I finally sat down and created what's jokingly being called sp_helpindex8.

Geeky note: Er, you might wonder with how many geeky SQL people I've joked about sp_helpindex8 but in fact, in my last few events (both onsite with a couple of customers as well as when teaching the Indexing Strategies and Performance Tuning portion of the Microsoft Certified Masters - SQL Server course (two weeks ago)), I've actually joked - and promised - that I'd have this blogged before this particular 3 week MCM rotation ends. And, well, that's this week. So, I've actually joked with quite a few folks on this and I'm close to the end of time on blogging it. So, here I am - sitting in a hotel room in Houston, after having presented at the Houston Area User Group (7-9pm) (yes, with Paul) and after having been onsite all day with a customer - blogging this darn thing. I just have to get it out there. ;-)

Having said that - I'm pretty happy with this version but it was pretty painful to write. And, before I get into what it does - and why I wanted this version - let me explain what I didn't do and why. There are a lot of other things that could be added. And, quite a few folks have said - why don't you do this or why don't you do that... Specific suggestions have been made such as adding index usage stats (from the current state shown by the DMV) or add index operational stats (to show if an index is suffering from latch IO waits, for example) or add index physical stats (to show if an index is fragmented), etc. And while many of these suggestions would make for lots of interesting uses of this output, I've stayed away from these. Why? Some are expensive to run - such as physical stats - even a limited scan can be painful on a really large table. Or, the results might not be able to be truly trusted  - such as with index_operational_stats. Why? Because the information from that DMV might only cover a short period of time. The information help by them is cleared whenever SQL Server is restarted (or if a database is taken offline or restored, etc.). So, when were those usage or operational stats last cleared? Moreover, is this single "look" at the DMV information really the best on which to base our decisions (even just in general).

In the end, I think that physical stats, operational stats and even usage stats - should be used wisely and carefully (definitely used but not by this). In fact, we often recommend that the best way to use these is by studying them over time and then analyzing their trends not just the point in time values. And, there are LOTS of posts that can help you with understanding this information; here's just a couple to get you started:

• Craig Freedman's (blog) post: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?
• Paul's blog: Indexes From Every Angle: How can you tell if an index is being used?
• Brent Ozar's (blog | twitter) SQLServerPedia post + video: http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

So, having said that - I've kept my sp_helpindex8 more pure ;-). What I want to see is EXACTLY what's stored in that index. In fact, this has always been my main goal. The first version (sp_helpindex2) added INCLUDED columns (2005+) as well as filter definitions (2008) to the output. So, what was missing? The added columns that SQL Server might have to add if a nonclustered is nonunique and/or when the clustering key is nonunique. The reason this is so important to me is that there are many things coming together and recommending indexes (DTA, the missing index DMVs, application tools, etc.) and while many of these tools are solely there to "help" us get better performance and "help" us create more effective indexing strategies - these tools sometimes recommend the same index over and over again (see Paul's recent blog post: Missing index DMVs bug that could cost your sanity... about the bug in the missing index DMVs) OR the tools might recommend similar indexes to what we already have.

Additionally, the output from sp_helpindex/2 only shows the columns that are EXPLICITLY defined in the key and in the leaf (using INCLUDE) but they don't show what SQL adds for you. Most folks know that SQL Server adds the clustering key to the nonclustered indexes and when you have a simple single-column clustering key it's not difficult to know that this IS "there" in your index even though it doesn't show up. But, even with that, I think there are still a few things that are lesser known. For example, if your nonclustered index is nonunique - did you know that the clustering key is not only added to the leaf level of the index (like INCLUDE does) but that the clustering key is added into the tree (as part of the key and ordering?). And, if have one index that lists this clustering key explicitly and another that doesn't - are you actually going to be able to easily tell that these are in fact the SAME. Ultimately, I want you to be able to better recognize what's REALLY in that index. What you might end up seeing is that two indexes are more similar than you first thought and as a result - maybe you can drop one of them. Or, you might be able to combine them to reduce your overall indexes (I call this index consolidation). And, all of this becomes significantly more challenging when you have more complex clustering keys (and, yes, they do exist!). The other thing I did is delimit the columns and index names properly using brackets. This is also nice because you might have identifiers that make the output of sp_helpindex/2 hard to read. This should help. However, I do have to admit that you probably have other problems if your index/column names have commas, spaces or brackets in them. But, this should still help/work.

And, that's why I wrote "sp_helpindex8" it's a MUCH better version of sp_helpindex but it requires a few other components. And, it was a lot more challenging to write. Basically, this information is more internal to SQL Server and not directly exposed anywhere. So, we had to do a bit of digging. Paul started the process (thanks!) but, I ended up spending about 5 hours getting it exactly the way I wanted it. And, I also ended up breaking it up into two parts: one sp that gets column definitions and the other that builds the final output. And, I've actually named it with a longer name (sorry!). But, I rarely type in the name anyway. What I typically do is go to SSMS, Tools | Options, then under Environment | Keyboard, I set the keyboard shortcut for Ctrl+F1 to my sp_SQLskills_SQL2008_helpindex.

Now, when I want to see the true defintions of indexes on a table - I highlight the tablename and hit Ctrl+F1. But, if you want to rename this to sp_helpindex8 - I won't be offended. ;-)

So, how do you create this?

1. You need to create sp_SQLskills_ExposeColsInIndexLevels (sp_SQLskills_ExposeColsInIndexLevels.sql (6.73 kb)). This is what gives us the tree/leaf definitions. And, this works for both SQL Server 2005 and SQL Server 2008.
2. You need to create the version specific sp_helpindex8. These are named: sp_SQLskills_SQL2005_helpindex (sp_SQLskills_SQL2005_helpindex.sql (10.50 kb)) and  sp_SQLskills_SQL2008_helpindex (sp_SQLskills_SQL2008_helpindex.sql (10.73 kb)). Again, I know they're long names but using the keyboard shortcuts can help. And, if you do rename these make sure you do a replace ALL. There are multiple places where there are comments and/or statements that need to execute to get everything to work (for example the sp_MS_marksystemobject execution).

NOTE: The very first version of the 2005 script had a bug in it (thanks Calvin!). Funnily one I already knew about and thought I had caught (it was fixed in the 2008 version). Anyway, as of 3:15pm CT on May 19, it's fixed in the scripts here. There could be others but so far - it's looking good!

Enjoy!
kt

Expanding on the topic of "are you kidding me"... one of the MOST PREVALENT problems I see today is the dreaded "GUIDs as PKs" problem. However, just to be clear, it's not [as much of a] problem that your PRIMARY KEY is a GUID as much as it is a problem that the PRIMARY KEY is probably your clustering key. They really are two things BUT the default behavior in SQL Server is that a PRIMARY KEY uses a UNIQUE CLUSTERED INDEX to enforce entity integrity. So, I thought I'd take this post to really dive into why this is a problem and how you can hope to minimize it.

Relational Concepts - What is a PRIMARY KEY? (quick and basic reminder for what is what and why)

Starting at the very beginning... a primary key is used to enforce entity integrity. Entity integrity is the very basic concept that every row is uniquely identifiable. This is especially important in a normalized database because you usually end up with many tables and a need to reference rows across those tables (i.e. relationships). Relational theory says that every table MUST have a primary key. SQL Server does not have this requirement. However, many features - like replication - often have a requirement on a primary key so that they can guarantee which row to modify on a related database/server (like the subscriber in a replication environment). So, most people think to create one. However, not always...

What happens when a column(s) is defined as a PRIMARY KEY - in SQL Server?

The first thing that SQL Server checks is that ALL of the columns that make up the PRIMARY KEY constraint do not all NULLs. This is a requirement of a PRIMARY KEY but not a requirement of a UNIQUE KEY. They also check to make sure (if the table has data) that the existing data meets the uniqueness requirement. If there are any duplicate rows, the addition of the constraint will fail. And, to check this as well as to enforce this for [future] new rows - SQL Server builds a UNIQUE index. More specifically, if you don't specify index type when adding the constraint, SQL Server makes the index a UNIQUE CLUSTERED index. So, why is that interesting...

What is a clustered index?

In SQL Server 7.0 and higher the internal dependencies on the clustering key CHANGED. (Yes, it's important to know that things CHANGED in 7.0... why? Because there are still some folks out there that don't realize how RADICAL of a change occurred in the internals (wrt to the clustering key) in SQL Server 7.0). It's always (in all releases of SQL Server) been true that the clustered index defines the order of the data in the table itself (yes, the data of the table becomes the leaf level of the clustered index) and, it's always been a [potential] source of fragmentation. That's really not new. Although it does seem like it's more of a hot topic in recent releases but that may solely because there are more and more databases out there in general AND they've gotten bigger and bigger... and you feel the effects of fragmentation more when databases get really large.

What changed is that the clustering key gets used as the "lookup" value from the nonclustered indexes. Prior to SQL Server 7.0, SQL Server used a volatile RID structure. This was problematic because as records moved, ALL of the nonclustered indexes would need to get updated. Imagine a page that "splits" where half of the records are relocated to a new page. If that page has 20 rows then 10 rows have new RIDs - that means that 10 rows in EACH (and ALL) of your nonclustered indexes would need to get updated. The more nonclustered indexes you had, the worse it got (this is also where the idea that nonclustered indexes are TERRIBLY expensive comes from). In 7.0, the negative affects of record relocation were addressed in BOTH clustered tables and heaps. In heaps they chose to use forwarding pointers. The idea is that the row's FIXED RID is defined at insert and even if the data for the row has to relocate because the row no longer fits on the original page - the rows RID does not change. Instead, SQL Server just uses a forwarding pointer to make one extra hop (never more) to get to the data. In a clustered table, SQL Server uses the clustering key to lookup the data. As a result, this puts some strain on the clustering key that was never there before. It should be narrow (otherwise it can make the nonclustered indexes UNNECESSARILY wide). The clustering key should be UNIQUE (otherwise the nonclustered indexes wouldn't know "which" row to lookup - and, if the clustering key is not defined as unique then SQL Server will internally add a 4-byte uniquifier to each duplicate key value... this wastes time and space - both in the base table AND the nonclustered indexes). And, the clustering key should be STATIC (otherwise it will be costly to update because the clustering key is duplicated in ALL nonclustered indexes).

In summary, the clustering key really has all of these purposes:

1. It defines the lookup value used by the nonclustered indexes (should be unique, narrow and static)
2. It defines the table's order (physically at creation and logically maintained through a linked list after that) - so we need to be careful of fragmentation
3. It can be used to answer a query (either as a table scan - or, if the query wants a subset of data (a range query) and the clustering key supports that range, then yes, the clustering key can be used to reduce the cost of the scan (it can seek with a partial scan)

However, the first two are the two that I think about the most when I choose a clustering key. The third is just one that I *might* be able to leverage if my clustering key also happens to be good for that. So, some examples of GOOD clustering keys are:

• An identity column
• A composite key of date and identity - in that order (date, identity) 
• A pseudo sequential GUID (using the NEWSEQUENTIALID() function in SQL Server OR a "homegrown" function that builds sequential GUIDs - like Gert's "built originally to use in SQL 2000" xp_GUID here: http://sqldev.net/xp/xpguid.htm

But, a GUID that is not sequential - like one that has it's values generated in the client (using .NET) OR generated by the newid() function (in SQL Server) can be a horribly bad choice - primarily because of the fragmentation that it creates in the base table but also because of its size. It's unnecessarily wide (it's 4 times wider than an int-based identity - which can give you 2 billion (really, 4 billion) unique rows). And, if you need more than 2 billion you can always go with a bigint (8-byte int) and get 2⁶³-1 rows. And, if you don't really think that 12 bytes wider (or 8 bytes wider) is a big deal - estimate how much this costs on a bigger table and one with a few indexes...

• Base Table with 1,000,000 rows (3.8MB vs. 15.26MB)
• 6 nonclustered indexes (22.89MB vs. 91.55MB)

So, we're looking at 25MB vs 106MB - and, just to be clear, this is JUST for 1 million rows and this is really JUST overhead. If you create an even wider clustering key (something horrible like LastName, FirstName, MiddlieInitial - which let's say is 64bytes then you're looking at 427.25MB *just* in overhead..... And, then think about how bad that gets with 10 million rows and 6 nonclustered indexes - yes, you'd be wasting over 4GB with a key like that.

And, fragmentation costs you even more in wasted space and time because of splitting. Paul's covered A LOT about fragmentation on his blog so I'll skip that discussion for now BUT if your clustering key is prone to fragmentation then you NEED a solid maintenance plan - and this has it's own costs (and potential for downtime).

So............... choosing a GOOD clustering key EARLY is very important!

Otherwise, the problems can start piling up!

kt

Well, I've promised to blog more and I'm really going to try to do so. This morning I got the perfect question/comment (in email) to respond to and after working through a response that was taking me upwards of 3 hours (you'll learn later why I have 3 "spare" hours :)......... I figured that it was time to turn the response into a blog post. ;)

Background: The Clustered Index Debate
In the years since the storage engine was re-architected (SQL Server 7.0+) there's been constant debate on how to appropriately choose the clustered index for your tables. I've generally recommended an ever-increasing key to use as a clustered index and many find that counterintuitive. The primary reason people feel it's counterintuitive is that it creates a hotspot of activity. [If "hotspot" is not a familar term - a hotspot is solely an active place within your table.] Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there. In fact (and probably even more counterintuitive), the opposite is true. Hotspots (specifically hot PAGES not hot ROWS) can be very beneficial because they; minimize the number of pages needed in cache, improve the likelihood of the required page already being in cache and in general, they minimize the overall amount of cache required. So, this is why many of us have changed our recommendation on where to create the clustering key in 7.0+. Instead of focusing on range queries we now focus on placing the clustering key on an ever-increasing key. In earlier releases, focusing on range queries for the clustered index reduced hotspots for insert/update and this in fact was the PRIMARY motivation to choose them, NOT range query performance! But - there are even MORE reasons to choose an ever-increasing key and they are based on internals as well. These internals are based on the significant changes made in the storage engine for 7.0+. For a quick start on these, I went through them in the Blog entry here.

And, today's email is not uncommon. This is the basis for the title clustered index debate. In general, there are still a lot of questions related to creating clustered indexes to improve "range query" performance. Don't get me wrong, there's definitely a benefit in performance for some range queries but the first thing to remember is that you get only one CL index per table (therefore only one type of range query can benefit). In the real world, t's not likely that you want to see your data exactly in the same way all the time. Therefore it's very challenging to come up with the "right clustered" index if you're using range queries as your strategy. Even worse, the affect of choosing the clustering key to improve range queries causes problems for modifications against that table (INSERTs/DELETEs and UPDATEs). So.............. this is what started my day today. A great email from a reader that brought up these points. The question/comment (modified to hit only the highlights and to protect their identity :) was this:

The most important characteristic for a Clustered Index key is to satisfy range queries. More often than not, if a sufficient range of data will be scanned, the Optimizer will choose the Clustered Index over all others due to the excessive cost of Bookmark Lookup operations. As such, the table KEY is a more suitable clustered index candidate than any surrogate (few every query a database by range of surrogate keys).  [kt note: this second sentence is not entirely true... SQL Server will certainly choose a clustered index over non-clustered that require table scans but there are A LOT of algorithms that SQL Server can use instead of either of these and my examples later show this... non-clustered covering seekable indexes, non-clustered scanable indexes, index-intersection, etc. ] 

Now, when the default behavior for SQL Server was designed such that the PRIMARY KEY was chosen as the default clustered index, it was exactly for this reason.  It is the business key.  It would satisfy uniqueness (by definition of logical KEY).  And, it is well suited for a wide variety of range scans.  However, this is when the PRIMARY KEY is defined on the Business Key of the data.But, when you introduce the usage of surrogate keys (i.e., IDENTITY) as a physical implementation, and thus transfer the PRIMARY KEY definition to it, two things must be considered.  First, the Business Key this IDENTITY will be a proxy for must still exist as it is still apart of the logical design.  As part of the physical design, the logical key needs to be implemented as a physical constraint to maintain logical uniqueness.  Second, just because a proxy has been defined does not make it a natural candidate for the clustered index.  The business key still maintains this distinction.What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits.  However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list.  Page Splits are managed by proper FILLFACTOR not increasing INSERTS.  Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting.

In short, the argument runs shallow.

Luckily, this email arrived with perfect timing for me as I'm sitting in a "bootcamp" event on Always On technologies and I'm not speaking this morning (my colleague Bob Beauchemin is doing lectures on Scale Out technologies: Scalable Shared Databases, Service Broker, DPVs, etc.). Anyway, in addition to listening to Bob, I've decided to continue the blog series on "the clustered index debate". The first and most important point to stress is that minimizing page splits is NOT the only reason nor is it the most important. In fact, the most important factors in choosing a clustered index are that it's unique, narrow and static (ever-increasing has other benefits to minimizing splits).

The Clustered Index Debate Continued
First, there are many angles to look at wrt to "the clustered index debate" and it's not until all of the issues are reviewed, that this strategy (a monotonically increasing key) becomes obvious. So, I think it will probably take a couple of blog posts to really prove this. I'll start up this debate again here...... When you look at a general purpose table (which is most) where the table has ALL DML (S/I/D/U) then you are best off with an ever-increasing key (again, you have to look at the overall impact of all operations against the table - not just select... because I/D/U will also impact select in the long term). So, I'll break this down into each DML operation here. If you don't look at the overall impact, then large tables can end up having a tremendous number of problems once they're put into production. I've certainly heard this concern/debate before (and most people are skeptical at first glance) but when you look at the situation overall, you'll find that "finding the right balance" includes not just looking at range queries. In fact, here's a quick list of the things/tests/numbers/scenarios that help to prove my strategy:

• Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing. I have some simple numbers but I'm thinking about creating a much larger/complex scenario and publishing those. Simple/quick tests on a laptop are not always as "exciting". But - this is a well documented issue (IAM/PFS lookups) and poor performance on a heap is also referenced in this KB: PRB: Poor Performance on a Heap. note: this KB is quite dated and I don't actually agree with everything in this article however, the general concern of poor performance for inserts is still true on SQL Server 2005.
• Updates are often faster (when the row needs to be relocated) and for the same reason (IAM/PFS lookups) BUT there are many types of updates and not all updates cause records to be relocated. Here are a few things to think about wrt to updates:
  • Updates that are completely in-place (some examples are where the update is updating a fixed-width column OR to variable-width columns where the row size doesn't change, etc.). These types of updates don't really care.
  • Updates that cause record relocation (where the row size changes) are definitely better by having a clustering key because the record relocation (which will be handled by a split) is defined by the clustering key
  • Updates to the clustering key are the WORST (in this case) which is one of the key reasons for having a cl key that is static (so we have to keep this in mind when we choose a clustering key).
• Deletes aren't nearly as big of a concern BUT deletes in heaps create more gaps and more gaps creates more work in PFS/IAM lookups and while this helps to reduce wasted space, it still requires the time to find the space........ hence the slowed performance of Inserts/Updates. I've also written some blog entries that cover very interesting test cases for large scale deletes and why you'd want to consider partitioning to optimize for the "sliding window scenario" in this blog entry: MSDN Webcast Q&A: Index Defrag Best Practices - Fragmentation, Deletes and the “Sliding Window” Scenario and it's the LAST one!.
• Selects.............. now this is the hardest one to go through in just a couple of bullets (ah, I guess this will lead to another one or two posts :) BUT I'll start by saying that the best way to tune the vast majority of range queries is through non-clustered [covering] indexes. But, it's also important for me to stress that I do NOT advocate covering every query (it's impossible to do). What's important to realize in terms of covering is that SQL Server 7.0 and up continues to include internal algorithms to improve performance when you don't have the "perfect" non-clustered covering seekable index and instead still gives better performance than going to the base table (or performing bookmark lookups - as mentioned in the mail...and I completely agree that these [bookmark lookups] can be evil!). To start this discussion, I'll give one of my favorite examples of a large-scale aggregate. The absolute best way to improve the performance is through an indexed view but the data can be gathered through many other algorithms - ideally through a non-clustered covering index that is in order by the group by and that includes the column(s) being aggregated. For example, take this query:

SELECT c.member_no AS MemberNo,
 sum(c.charge_amt) AS TotalSales
FROM dbo.charge AS c
GROUP BY c.member_no

On a charge table of 1.6 million rows here are the performance numbers to handle this aggregation:

• Clustered table scan (CL PK on Charge_no) with a hash aggregate = 2.813 seconds
• Index scan (non-clustered covering but NOT in order of the group by) with a hash aggregate = 1.436 seconds
• Index scan (non-clustered covering in order of the group by) with a hash aggregate = .966 seconds
• Indexed view = .406 seconds

Now this was a pretty small table (narrow rows and only 1.6 million rows) AND I didn't have any concurrent activity. The concurrent activity would have caused this to be even slower for hash aggregates, etc. Regardless, it proves the point (at least generally). Now, if I wanted to improve this range query then I'd have to cluster on the member_no column (and this is an ideal example because I often hear people say that clustering on a foreign key column helps to improve range/join queries - which can be true as well)......... But - this strategy has a few problems in addition to a few benefits (and we have to look at everything to be sure of our choice/decision). First, member_no is not unique (in the charge table) so SQL Server has to "uniquify" the rows. The process of "uniquification" impacts both time (on insert) and space (the rows will be wider to store each duplicate row's uniqufier). Also, theoretically it could change (in this case that's not true). Anyway, the time it takes for the clustered index is 2.406 seconds which is better than the clustered on the PK (of course) but if I were to also start modifying the rows (which creates splits) or even just insert 15% more rows........ then my table would become fragmented. At that point, the query performance should get worse in the table clustered by member_no table and it will continue to get even worse in the table clustered by charge_no (because of the worktable created in tempdb by the hash aggregate) BUT it won't be all that much worse in the non-clustered index examples (especially the covering index that's in the order of the group by - because this doesn't require a worktable).........

• CL on member_no = 4.906 seconds
• CL on charge_no = 6.173 seconds
• Index scan (non-clustered covering but NOT in order of the group by) with a hash aggregate = 3.906 seconds
• Index scan (non-clustered covering in order of the group by) with a hash aggregate = 1.250 seconds
• Indexed view = .516 seconds

This is a great start to furthering the clustered index debate but I do have to admit that it's a counterintuitive and difficult issue to tackle because often isolated tests lead you to different conclusions. In this case though, the non-clustered indexes are better for this range query and the indexed view is the best (but I wouldn't consider the Indexed unless this were more of a read focused database rather than read/write). [and - of course, that statement warrants yet another blog post :)]

So, depending on the tests that you do - especially if you focus only on selects and you don't have modifications (i.e. fragmentation) - then they will make "creating the clustered index for range queries" appear to be best. Again, I'm not just saying this to prevent fragmentation, I'm saying this because I wouldn't use the clustered index OR a non-clustered index with bookmark lookups to handle this query. I'd consider a non-clustered covering that's seekable OR even a non-clustered covering that's scanable before I'd even choose the clustered (and that's what the optimizer would prefer as well). In the end it's really a bit of an art and a science to "finding the right balance" of indexing.

Oh - and if you arbitrarily add a column to use for clustering (maybe not as the primary key) that can help but many would prefer to use actual data... which means [potentially] creating your primary key with a new identity [or similar] column and this can impact your business logic (absolutely). I'm certain that certain tests can show that range queries are faster and it's absolutely correct that business application/usage can be a concern but when you look at the big picture (and the impact on I/D/U) then the benefits of the monotonically increasing key significantly outweigh these concerns. Simply put, a small/narrow key can help join performance and an ever increasing key can also help lookups for rows! (yes, definitely more coming)

Happy Friday! Have a great weekend. I'll try to continue more threads on this debate shortly!
kt

As I'm preparing for my Tech*Ed session on Indexing Best Practices in SQL Server 2005, I'm reminded that there are a ton of best practices that really apply to both SQL Server 2000 as well as SQL Server 2005. When it comes to indexing, there are many dependencies on the storage structures. These dependencies are the basis for why I recommend a very specific type of clustering key - for all versions of SQL Server, 7.0 and higher!

I'm going to start with my recommendation for the Clustering Key - for a couple of reasons. First, it's an easy decision to make and second, making this decision early helps to proactively prevent some types of fragmentation. If you can prevent certain types of base-table fragmentation then you can minimize some maintenance activities (some of which, in SQL Server 2000 AND less of which, in SQL Server 2005) require that your table be offline. OK, I'll get to the rebuild stuff later.....

Let's start with the key things that I look for in a clustering key:

• Unique
• Narrow
• Static

Why Unique?
A clustering key should be unique because a clustering key (when one exists) is used as the lookup key from all non-clustered indexes. Take for example an index in the back of a book - if you need to find the data that an index entry points to - that entry (the index entry) must be unique otherwise, which index entry would be the one you're looking for? So, when you create the clustered index - it must be unique. But, SQL Server doesn't require that your clustering key is created on a unique column. You can create it on any column(s) you'd like. Internally, if the clustering key is not unique then SQL Server will “uniquify” it by adding a 4-byte integer to the data. So if the clustered index is created on something which is not unique then not only is there additional overhead at index creation, there's wasted disk space, additional costs on INSERTs and UPDATEs, and in SQL Server 2000, there's an added cost on a clustereD index rebuild (which because of the poor choice for the clustering key is now more likely).

Why Narrow?
A clustering key should be narrow for some of the same reasons it should be unique. If the clustering key is used as the lookup key from all non-clustered indexes, then the clustering key is duplicated in all non-clustered indexes. If the clustering key is really wide, then all of the non-clustered indexes will be [unnecessarily] wide. This will waste disk space, create additional costs on INSERTs and UPDATEs, and require more time (because of size) when rebuilding these index structures. So, what does narrow mean - as few bytes as possible to help uniquely define your rows. A narrow numeric when possible.

Why Static?
A clustering key should be static for some of the same reasons it should be unique and narrow. If the clustering key is used as the lookup key from all non-clustered indexes, then the clustering key is duplicated in all non-clustered indexes. In fact, for a given table the clustering key will be the most duplicated data. If this data changes then they'll need to update the value in the base table as well as in EVERY non-clustered index. And, if the key changes, it will cause the record to move. When a record moves, it creates fragmentation. This will waste disk space, create additional costs on INSERTs and UPDATEs, and require more time (because of record relocation and [the likely] subsequent splits) and require more maintenance. 

OK, so it sounds like I want a narrow, unique and static value... What about a guid?
Typically, I recommend a numeric IDENTITY column as the clustering key but I always get this question. In fact, I often wait to see how long it's going to take before I get this question ;). Anyway, a guid does meet the criteria fairly well - it's certainly unique, it's usually static and it's relatively narrow. So, what's wrong with it? In SQL Server 2000, the guid function (newid()) is built using a value that does not create an ever increasing pattern (an IDENTITY column would). But wait, I didn't say that you needed to have an ever-increasing pattern.....

OK, so the final criteria I look for in a clustering key is: an ever-increasing pattern!
If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity). But wait, what about that a guid.

Well, in SQL Server 2000 the only SQL Server function for guids is newid - that does not create an ever increasing pattern. In SQL Server 2005, you can use a new guid function called newsequentialid() to populate your uniqueidentifier column. Here's an example of how you can use it:

Is there a way to create a sequential guid in SQL Server 2000?
YES, use Gert Drapers wrote an xp to generate sequential guids! Check it out here. He just published this recently (May 2005) and it's a good change for you in SQL Server 2000 databases/applications.
Full title with link: XPGUID.DLL - Sequential GUID generation and GUID helper functions XP

OK, so I've tackled a few things here today and I've answered a few questions related to indexes before. Check out my Indexes Category of blog entries here and if you're at Tech*Ed today, I hope to see you at 5PM.

Thanks for reading,
kt