6

Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY on). Each table has around 100k rows with only 2–8 columns. Several JOINs and UNION ALLs are then done on the temporary tables. Every field in all of the temporary tables are used. Furthermore, most of the time every row is also used—worst case scenario, half of the rows is used somewhere and the other half is used somewhere else. Once the query is finished, the results are saved on the disk so that individuals can access the data for the rest of the day.

Question: Which of the following approaches should be the best fastest:

• Have no indexes on the temporary tables.

• Have a clustered index on all of the temporary tables (via the PRIMARY KEY declaration on the unique identifier).

• Have a nonclustered index on the unique identifier while including the remaining columns on all of the temporary tables.

• The last two bullet points together.

Thoughts: While running all four options simultaneously, each one had a query cost of 25% (relative to the batch); however, when the first bullet point is run, the execution plan (on the SELECT query) states that I should create a nonclustered index on the unique identifier while including the remaining columns for each of the temporary tables.

I'm a little perplexed by this. If I effectively use every field and row in all of the temporary tables, why would it suggest this? Wouldn't a heap or a clustered index be better?

To me, a heap would minimize the overhead in creating an index and sorting the data; and since I essentially need every row, there is no harm in doing a table scan.

A clustered index, on the other hand, should improve the temporary table that is created via a JOIN on two of the other temporary tables as well as improve the final SELECT query which relies on JOINs and UNION ALLs.

Related Note: On a large table with 400 columns and 70M rows—don't ask me why we have an absurd amount of columns—a query that only grabbed the PRIMARY KEY was ≈50 times faster when utilizing a non-relevant nonclustered index over the clustered index.

If anyone has any insight, I'd be grateful.

  • 1
    You may get better responses if you post the query plans, etc. The company I work for hosts a free service for that: brentozar.com/pastetheplan – Erik Darling Feb 24 '17 at 22:48
  • 2
    @basketballfan22 a clustered index is all of the table data ordered by the key you choose. The nonclustered index is a separate structure that only contains the columns you choose to put in it (plus the clustered index key column(s)) – Erik Darling Feb 24 '17 at 23:31
  • 1
    @sp_BlitzErik If that's the case, then wouldn't including all of the columns in the nonclustered index effectively be the same as an unsorted clustered index? – basketballfan22 Feb 24 '17 at 23:33
  • 1
    It would still be sorted by the key column(s) of the nonclustered index, and the HEAP structure would still exist as well. So you would need roughly double the space. Adding a clustered index replaces the HEAP. – Erik Darling Feb 24 '17 at 23:36
  • 3
    No, missing index requests will never ask for a clustered index. – Erik Darling Feb 24 '17 at 23:50
6

To summarize (and include some information from the comments), you have a process that runs once per day early in the morning that populates several 100k row temp tables with 2 - 8 columns each. This is a broad question but my initial reaction would be to create primary keys on all of the tables. If performance is good enough then I would be satisfied. If performance isn't good enough I would investigate further to find ways to improve the code. Removing the primary keys would technically be an option but in general I would expect to find performance gains by changing the queries that populate the temp tables.

A heap is a table without a clustered index. Note that a primary key does not need to be defined the same way as a clustered index. In fact, you can define a heap with a primary key. As far as I know this isn't a very common thing to do. There are a few advantages in using heaps for your workload:

  1. Inserting the data will not lead to a sort. If you insert 100k rows into a table with a clustered index it is possible that the data will need to be sorted depending on the query that populates the table. All of the columns or just the clustering key could be included in the sort.
  2. As of SQL Server 2014 the insert can run in parallel if you use the SELECT INTO syntax. Note that the query optimizer may choose not to use parallel inserts depending for a variety of reasons including the estimated size of the data, and MAXDOP settings.

In general, I would not worry about a sort of 100k rows, especially if this process runs early in the morning with nothing else. Parallel inserts are unlikely to be a big help as well for such a small amount of data.

There are a few disadvantages of using heaps for this workload:

  1. No data integrity. There might be a bug in your process or corrupt data which could have been caught earlier with a primary key on the temp tables.
  2. The data is not sorted, so any query plan operator which requires sorted data (such as a merge join operator or inserting into a clustered table) will require an explicit sort. Let's say you reference one table in three queries and all three times the query optimizer sorts the data. Why not sort the data on disk at the start to avoid those three sorts in the query plans?

Hard to say more on these points because I don't know anything about your process. It's possible that your queries against the temp tables could benefit from a MERGE JOIN but I doubt it will make a big difference at your volume.

Now let's talk about tables with a clustered index. As stated before, a clustered index does not need to match the primary key but I'll assume the most common default which is they are both the same. That is what you end up with when you define a PRIMARY KEY inline as part of the table definition.

There are a few advantages of tables with clustered indexes for your workload:

  1. Data integrity!
  2. Query plan operators benefit from a sort may already have the data sorted in the correct order. For example, this makes merge joins more attractive, especially if you are getting all of the data from the involved tables.
  3. The query optimizer may be able to pick a more efficient plan if it knows that certain columns are unique. One example of this is SQL Server may know that the joins are not many to many which can lead to more accurate cardinality estimates and costing of join operators.

There are a few disadvantages of tables with clustered indexes for your workload:

  1. Parallel inserts into the temp tables are not available in any version of SQL Server.
  2. Populating the tables may require an explicit sort.

The advantages and disadvantages are mostly the exact opposite of a heap table, which should not be surprising.

The third and four options aren't really worth considering. You can create an index that includes all columns but it will duplicate the data. Just define your primary key the right way and you should have no use for an additional nonclustered index that includes all columns. Note that SQL Server Management Studio will not recommend that you should create a clustered index on a table.

One difference between the clustered index/primary key and the nonclustered index is that the clustered index/primary will not allow NULL values in the key columns and automatically enforces a uniqueness constraint. Of course, it is possible for you to create a nonclustered index with the same restrictions.

Ultimately, all that you can do is test your workload with the different options. So we cannot definitely say which approach will be fastest. With one set of data and queries the heap approach may be fastest. With a different set of data and queries the clustered table approach may be fastest. Do not discount the value of data integrity, but you may have other ways of enforcing data integrity.

  • There are some potential benefits around minimal logging that can occur with a clustered index that can't occur with nonclustered indexes on insert. That might be secondary to select query performance, but if you're going long-form :) – Erik Darling Feb 25 '17 at 2:12
  • I don't know what the best practice is, so I'll make several comments that are really one instead of making an "answer" to my own question. While I was aware that tables can have PRIMARY KEYs defined without a clustered index, I believe that defining a PRIMARY KEY in a temporary table forces a clustered index. I also apologize for the vague nature of my question, but I didn't want to be too specific for fear of getting in trouble with my employer. – basketballfan22 Feb 25 '17 at 3:51
  • The reasons I asked the question stemmed from 1. the execution plan suggesting a nonclustered index instead of a clustered index and 2. having run a SELECT query on only the PRIMARY KEY/clustered index in another table and seeing a seemingly unrelated nonclustered index outperform the clustered index by a factor of 50. The fact that the execution plan will never suggest a clustered index quickly resolved my first source of confusion because as what was said here, I would essentially be creating two tables instead of one. – basketballfan22 Feb 25 '17 at 3:56
  • The second source of confusion stemmed from a misunderstanding of what a clustered index is and how it works. While I understood that a clustered index sort the entire table, I naïvely thought that it was separate from the table. This led to me believe that when accessing the fields the clustered index is defined on, the server would access a single-sorted column (or multiple if the clustered index is composite). Thanks to @sp_BlitzErik I know that that is not true. – basketballfan22 Feb 25 '17 at 4:02
  • The reason the nonclustered index outperformed the clustered index in my "Related Note" was that the nonclustered index only contained 2 columns—an unrelated field and the clustered field that is always defined on a nonclustered index if it exists—while also not "missing" any value. A question that comes to mind now is "For large tables, does it make sense to to define a nonclustered index on the PRIMARY KEY if that field(s) is commonly queried even if there already exists a clustered index?" I'd guess so since the index will be so much smaller than the entire table (i.e., clustered index). – basketballfan22 Feb 25 '17 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.