. 

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)

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