People always ask me how I keep up to date with SQL Server and how I could possibly know so many intricate details about SQL Server… Well, I test\/play\/figure it out until it’s obvious (painfully sometimes and sometimes it’s not obvious). So, this blog entry is based on a specific question that made me want to see if I could come up with a test and positive proof in patterns and performance – when working with large tables and range deletes. I generally recommend a clustered index be created on a unique, narrow and static column – and best if it’s an identity column – yet there is an exception to this when the table has VERY specific administrative\/operational\/archival patterns a.k.a. the “sliding window” scenario. The question starts by asking about my point that “deletes aren’t as big of a deal” in fragmentation. While my point was solely referring to the internal v. external fragmentation issue that led us to take some more time evaluating delete performance. In talking about delete performance, we arrive at some best practices in index creation as well as horizontal partitioning! (fyi – the scripts used to create\/build this scenario are fairly cool in and of themselves – not a bad idea to spend some time reviewing that!<\/I>)<\/FONT><\/P> <\/FONT><\/SPAN> <\/P> Q: You mention that deletes are not as “big of a deal” since they leave gaps. I had a situation where I had a table with around 50 million rows, about a million rows per day. After about a month and a half, I delete some old ones. I can delete a day’s worth of data (1 million rows) in about 2 minutes with a delete statement, if there are no indexes. If I have a clustered index based on the date, it took about 22 minutes. Inserts were instantaneous, about two pages worth of rows every second, and even updates were quicker. Is there anything obvious that would cause this? OK, so I need to clarify first. My comment that deletes aren’t as “big of an issue” is specifically regarding fragmentation (not really performance of the actual delete)… A delete (when it’s a range delete – as opposed to a lot of singleton deletes) typically de-allocates the entire page freeing it back to the table (remember, it’s still “reserved” by this table) or if the entire extent is freed then it goes back to the DB. However, remember, this is only when the range is contiguous or grouped, per se. So, looking solely at the result and impact to the base table – we would say that range deletes aren’t that big of a deal because they free entire pages and even entire extents – effectively, the table doesn’t have gaps. OK, so having said that, you make a good point: what about performance? <\/FONT><\/P> First, I’m somewhat intrigued by the fact that you say a delete was faster with no indexes rather than with a clustered especially if the clustered was date BUT I have a feeling that there might have been nonclustered indexes as well? (In the upcoming test matrix, you may see some patterns in timing that corroborate this idea<\/I>). <\/SPAN>Without knowing whether you had non-clustered indexes or not, let me at least speculate and test (and hopefully prove<\/I>) a few points? First, I would ask what the exact definition of the CL index was – was it just date? Remember, I really feel as though there are three types of tables – tables with the wrong<\/I> clustered index, tables with NO clustered index (i.e. HEAP) and tables with the right<\/I> clustered index. However, the “right” clustered index CAN vary based on pattern usage of the table. In a table that has range deletes based on dates – as well as an identity column – I often create a clustered index on the composite combination of these two columns. If the CL Key is on the date itself then this should not be that much slower (that the heap – in fact, I would have expected it to be faster even though there will be some<\/I> overhead in maintaining the index). I really think there’s something else there – probably nonclustered indexes, maybe a blocking scenario? So, to help – I think I can prove a few points (in terms of perf) with a script\/test to see if I can give you some rough numbers (I’m really, really intrigued to see if I can’t prove my point<\/I> as well <\/I><\/FONT>J<\/SPAN><\/SPAN>). So, I decided to try an interesting combination – and really test the numbers. My test (albeit not entirely scientific<\/I>) should show a pattern! First, I’m going to take the 1.6 million row charge table (in my “pumped up version of the credit database) and I’m going to copy it 10 times into slightly different table structures. Details and the creation for each table follow:<\/FONT><\/FONT><\/FONT><\/P> ChargeHeap<\/B> – A table with NO Clustered index and NO non-clustered indexes. <\/FONT><\/FONT><\/FONT><\/P> SELECT * INTO ChargeHeap FROM Credit.dbo.Charge ChargeCLPKForDelete<\/B> – Is really NOT that bad of a choice… I typically recommend this choice for everyday tables where there are lots of inserts\/updates\/deletes. However, with the specific “sliding window” scenario (where data comes in at the end AND is periodically archived off the other end) then I might make a different choice. But – still a good table to create and compare against. This will certainly improve inserts and minimize fragmentation BUT it might not be ideal for range deletes and archiving. This table has a clustered index on the Primary Key; this column is an ever-increasing identity value on charge_no. <\/FONT><\/FONT><\/FONT><\/P> SELECT * INTO ChargeCLPKForDelete FROM Credit.dbo.Charge go ALTER TABLE ChargeTesting.dbo.ChargeCLPKForDelete ADD CONSTRAINT ChargeCLPKForDeletePK <\/SPAN>PRIMARY KEY CLUSTERED (Charge_NO) go Again, because this table will follow a “sliding window” scenario then I would change the clustering key to include the date and then the ID. I don’t typically chose date (as a single column primary key) because dates alone are rarely, if ever, unique (<\/FONT>for more details on datetime data check out the articles I’ve written on Datetime data for SQL Magazine <\/FONT>here<\/A><\/I>). Because you already have an identity column in the table (which uniquely identfies every row), there’s no need to add a “uniqifier.” <\/SPAN>The process of “uniqification” happens internally if the clustereing key is defined on a non-unique column <\/I>(<\/FONT>see last month’s <\/FONT>webcast<\/a> if you’re not familiar with this “uniqification” process<\/FONT><\/I>). In the case where you already have a row identifier, it’s best to use it in the clustering key with date – especially as it has useful data in it. So – this leads us to the next TWO tables…<\/FONT><\/FONT><\/FONT><\/P> ChargeCLDateForDelete<\/B> – Is a clustered index on the date ALONE.<\/FONT><\/FONT><\/FONT><\/P> SELECT * INTO ChargeCLDateForDelete FROM Credit.dbo.Charge go CREATE CLUSTERED INDEX ChargeCLDateForDelete ON ChargeTesting.dbo.ChargeCLDateForDelete (Charge_DT) go ChargeCLDateForDeleteWithCompPK<\/B> – Is a composite clustered primary key index on the date, charge_no together. <\/FONT><\/FONT><\/FONT><\/P> SELECT * INTO ChargeCLDateForDeleteWithCompPK FROM Credit.dbo.Charge go ALTER TABLE ChargeTesting.dbo.ChargeCLDateForDeleteWithCompPK ADD CONSTRAINT ChargeCLDateForDeleteWithCompPK_PK <\/SPAN>PRIMARY KEY CLUSTERED (Charge_DT, Charge_NO) go <\/FONT><\/SPAN> <\/P> The last base table will have a poor choice for the clustering key (for these deletes) as they will not have “ranges” with which to work. <\/FONT><\/P> ChargeCLReallyBadForDelete <\/B>– This clustered index will be on something NOT used at all in this delete. The deletes will result in lots of internal fragmentation. In other words, the table will be larger and “internally” fragmented. The table will essentially look like “swiss cheese” meaning that the table will have lot of little gaps within the pages themselves. <\/FONT><\/FONT><\/FONT><\/P> SELECT *
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n