SQL Server: ~2000 Heap Tables all using GUID Uniqueidentifier - Possible Clustered Indexing?

user_5263670 0
Viewed 229

I have just taken over a database which has around 2200 tables. Over 2000 of these have no clustered index (some have no indexes at all).

All of the tables have been configured to use a GUID as the uniqueidentifier.

Just looking at the query plans, I can see that there are many table scans occurring. Most searches use the uniqueidentifier to search on.

I am wondering if it is better to have a clustered index on the GUID than not to have a clustered index at all. I imagine that a clustered index on a 16-byte column will inevitably lead to fragmentation.

I could arguably cluster on other columns but the majority of searches tend to search by or join via the GUIDS.

Any advice would be very much welcomed. I've never seen so many GUID's!!

clustered-index indexing guid sql-server
3 Answers

In generally, I would recommend having an identity column as the primary key and use that for clustering. This is also a better choice for joins.

Why? First, identity keys are generally shorter that unique ids. So, foreign key references and indexes are smaller.

More importantly, inserts would always go at the "end" of the table. When using GUIDs, inserts are often going to cause fragmentation. If you are inserting rows, I would say that a secondary index on the GUID might be better than a clustered index (the fragmentation is only in the index).

With 2000 tables, I doubt you will change the structure. You can ameliorate the fragmentation using newsequentialid().

user_11440350

GUID column with random values usually is not the best choice for a clustered index because it could be the root cause of an index fragmentation:

  1. Read ahead opportunity of the database won't be effective;
  2. The cost of insert operations will be too expensive, because in this case you'll got lots of page split overhead;

There are 3 ways how you can live with that:

  1. Schedule planning index reorganizing and rebuilding which will reduce index fragmentation and improve your statistics automatically;
  2. Use newsequantialid for generating values of this column;
  3. Generate GUID value sequantialy outside of the database (Guid.Comb Identifier is a great example of solving this issue in NHibernate).
user_25243040

This is a really a comment on your question to Gordon's good answer:

Firstly, don't forget to check the index DMVs to see which ones are being used (or not used) and have a look at the expensive query plans in the cache to focus on the tables and queries that will be causing most pain. I would expect that many of those 2200 tables are relatively small & the queries are able to look up pretty quickly even from the guid clustered index.

For those tables that aren't clustered, clustering on the guid would reduce fragmentation, since it forces all the data for the table to be colocated rather than allowing pages to be put in the next free extent & spreading tables all over the disk. This should make some of the I/O more efficient.

Check you have a low enough fillfactor so that your regular index rebuilds avoid page splitting in advance, although it will also be workload dependent (OLTP vs DW and read/write ratio of table)

If you have applications that are doing explicit column selects/inserts then you may be able to add an identity column without breaking anything. That allows you cluster around the identity & add an index to the guid. Whether this really helps depends on the relative (in)efficency of the new plans.

You could consider clustering around a non-guid field where queries will lookup against it fairly regularly (eg, a date range) and index the guid separately. You'd have to look at the queries & relative performance for that more closely.

user_21269050
Related