5

OK, I need this spelled out one more time. I've read the articles on-line, and I still haven't found a definitive answer.

In SQL Server 2008, I have a "core" table with about 50k records and lots of read activity that is used in the same way in all queries. This data is updated once a month, and read hundreds of times a second.

The data has a clustered index on the fields as they are frequently accessed. Let's say that the clustered index is:

CLUSTERED INDEX

Field1 int
Field2 int
Field3 int
Field4 int
Field5 int

Now, there is not a whole lot more data than that, so it would make sense to just put the extra couple of columns into "Included Columns", but SQL Server doesn't allow included columns on the Clustered Index.

So, we have a second index with essentially the same fields as the Clustered Index, with the other columns as "Included Columns". However, from what I've read, I believe this may be redundant?

COVERING INDEX (non-clustered)

Field1 int
Field2 int
Field3 int
Field4 int
Field5 int

INCLUDED COLUMNS

Field6 varchar(96)
Field7 varchar(96)

Does the non-clustered Index ALREADY have the columns from the clustered index defined in it?

If so, how could this second index be created with NO columns at all (besides what is already in the clustered index)? In other words, I'd like to say "This index is exactly the same as the clustered index... with a couple of Included Columns".

Or, would it be better to just put ALL of the columns into the clustered index (including the two that don't identify the record)? The varchar columns do get updated more frequently (a few times a day instead of once a month), so I would have liked to keep them out of the clustered index, but I think that they are deep enough that they won't affect the index tree enough to cause any rebalancing when a change occurs.

So, is there an efficient way to set up these indexes so that all of the columns of this table are available through the index without going back to the table?

Flipster
  • 4,373
  • 4
  • 28
  • 36

4 Answers4

5

Yes - a NonClustered Index accesses the data in the table via the clustered key (when the table has a clustered key, and the Row ID when it does not), so it will include the clustered index fields automatically. This is also a reason why a change to the clustered index, forces a rebuild of all non-clustered indexes.

The additional NC index with the 2 included fields could be valid if that index satisfies a large number of queries, but I'm not sure that is solving the right problem.

Including 2 more fields within the Clustered Key is not ideal, now it is confirmed within the NC index, you can see every index on that table includes the clustered key within it bulking each index out.

This is the main reason you want your clustered key to be as narrow as possible, if anything, you should examine your clustered key as ask why you are choosing a 5 field clustered key, and will that choice lead to fragmentation?

You may be better off with an artificial value (Identity) for the clustered key, and use a unique NC index to enforce the uniqueness you have with the 5 field clustered key.

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • Great responses. In this example, the table does have an "ID" column, but it is basically meaningless and unused. All queries request the data from this table through these indexed fields. – Flipster Jan 02 '11 at 17:11
  • Assuming you can live with any fragmentation, then you can use it as the clustered index. The NC index with the 2 additional fields might provide a performance boost (depending on the width of the Clustered Table vs Width of the Index) - but also you have to know the ratio of selection / insertion etc. There is no one size fits all approach to indexes and tuning – Andrew Jan 02 '11 at 17:15
  • I mentioned above: selection = 100 times / second. insertion = 1 time / month. It's read-only data for most practical purposes. – Flipster Jan 02 '11 at 17:18
  • With the read 100s of times per second but once a month update you listed, I would definately use an NC covering index if it was narrower that the main table. The key is retrieving only what you want. After each load, you want to make sure you have not got a fragmented clustered index either. – Andrew Jan 02 '11 at 17:19
  • 100s of times a second..., 2.88 million access per 8 hours, 8.64 million accesses per 24hours (assuming just 100 per second) - make sure the database has sufficient memory so all 50k records will be sitting in cache. – Andrew Jan 02 '11 at 17:22
  • Actually, I even considered taking it one step further by caching the entire table in a memory based Hashtable and "querying" that instead of going to the database at all (except on start-up). Great idea! – Flipster Jan 02 '11 at 17:25
  • BTW, the 100 times per second is a bit conservative, as 3-4 records are requested per query. This is definitely the "hot spot" in our application. – Flipster Jan 02 '11 at 17:26
  • Cached in memory seems the most appropriate, whether cached by the application or cached within SQL Server data cache. – Andrew Jan 02 '11 at 17:29
  • In our 3rd party database they created these huge compound clustered indexes - which leads to all other indexes being huge because they must include all the referenced clustered-index fields. For those that don't believe me, include a field from the clustered-index to a new index and rebuild. You will see the size of the index does not change. – MikeTeeVee May 06 '13 at 01:16
4

A clustered index does not need includes. Includes means stored extra data at the lowest level of the index tree. This is the data in a clustered index. So you don't need an overlapping index

However if memory footprint is your concern, then you need to shrink the table. With 50k rows I would consider a smallint surrogate key starting at -32768. Then, you remove the overhead of the C key in every NC index. This means you can have a covering index as mentioned in your question.

Note that once your execution plans are cached and the data is in cache, then your queries will come from memory. Your usage means it will stay in cache for some time. Lack of updates means you won't get statistics-driven recompiles.

However, if your data is almost static, then why call SQL Server at all if performance is a concern? Cache it. Remove the network round trip which is probably your biggest overhead based on my caching comments. We outsource some lookups and caching to our clients to reduce server load (we have 50k writes in around 20 seconds at peak load)

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Although all of the answers here are pretty similar, I think that you distilled the question down to its essentials and gave us the "actionable" information that we needed without too much RDBMS theory. Thanks! – Flipster Jan 02 '11 at 19:56
1

it would make sense to just put the extra couple of columns into "Included Columns", but SQL Server doesn't allow included columns on the Clustered Index

Including extra columns is impossible, because a clustered index already contains all columns. That's why the index is called clustered.

So, we have a second index with essentially the same fields as the Clustered Index, with the other columns as "Included Columns". However, from what I've read, I believe this may be redundant?

Yeah, it's probably redundant. There are some rare exceptions where the clustered index does not fit into memory.

Does the non-clustered Index ALREADY have the columns from the clustered index defined in it?

Probably: a non-clustered index contains a pointer to the clustered index. If the clustered index is unique, this pointer consists of all clustered index fields. (In most situations, these fields correspond with the primary key.)

So, is there an efficient way to set up these indexes so that all of the columns of this table are available through the index without going back to the table?

In the example you post it looks like the clustered index is sufficient, and you do not need any other indexes to avoid a table lookup. You can verify this by running queries and looking for "key lookup" or "rid lookup" operations.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Would it make sense to put all of the columns into the clustered index to give SQL Server a "hint" as to how to organize the data? There is a certain hierarchy to the data, and I'd like SQL Server to organize according to this hierarchy, as that it how the data is accessed. – Flipster Jan 02 '11 at 17:16
1

I think you need a better understanding of CLUSTERED and NONCLUSTERED indexes. The clustered index is a balanced-tree (B-tree) where each node contains the key column(s) for the index. Normally, and frequently the best option, one column is the key column for the index. all data for each row is stored at the leaf level (i.e., the bottom level) of the clustered index. This is why, you can't have included columns in a clustered index; all columns are included by definition.

a non-clustered index is also a B-tree structure. Each node contains the key column(s) for the index. The leaf level for non-clustered indexes contains any included columns. Note the difference between a key column and the included column is that the key column values appear at each level of the index and included columns only appear at the leaf level. The leaf level also contains the key column(s) from the clustered index, which are used to link the index to the table data.

The more columns you include in any index, the larger the index becomes. And, this can degrade performance.

So, for a clustered index, you don't need to include all columns, or even many columns, as keys in the index. The data is already part of the index.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • So, for our specific case, are you saying that the data is already available through the clustered index and that the NC index is irrelevant and should be deleted? – Flipster Jan 02 '11 at 17:12
  • 1
    The NC index is irrelevant if the non-clustered is nearly identical to the clustered index. Then NC index will never be used because the clustered index will be a better choice, because it has all the data. – bobs Jan 02 '11 at 17:25