Is there any way to test which clustered index is better for specific queries?

Question

We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we can do to improve that.

I've created a few test tables to test different indexes:

SELECT TOP 1000000 * INTO IndexTest1
FROM Events

SELECT TOP 1000000 * INTO IndexTest2
FROM Events

SELECT TOP 1000000 * INTO IndexTest3
FROM Events

Next I added different clustered indexes on columns that are often used to query the events or process them. Most of the time we use the Timestamp or the EventType.

CREATE CLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

CREATE CLUSTERED INDEX Timestamp
ON IndexTest2 (Timestamp)

CREATE CLUSTERED INDEX EventType
ON IndexTest3 (EventType)

Next I tested two different types of queries. However I currently have no way of noticing which performs best.

SELECT * FROM IndexTest1
WHERE EventType = 'String'

SELECT * FROM IndexTest2
WHERE EventType = 'String'

SELECT * FROM IndexTest3
WHERE EventType = 'String'

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

What I expected: I expected IndexTest1 to perform best for both queries, seeing as it is the only one that includes both EventType and Timestamp in its clustered index.

Results second query set: When turning on the live query statistics, I get varying times for execution for all queries, ranging between 8 - 20 seconds.

When looking at the execution plans I do notice that IndexTest1 has a much lower Estimated I/O Cost and Estimated Operator Cost for the second set of queries than IndexTest2 & IndexTest3. The difference is relatively large, IndexTest1 has an I/O Cost of about 0.003 whereas IndexTest2 & IndexTest3 are around 25 and 40 respectively.

Question: Can I conclude that for the second query set IndexTest1 is the best index?

Results first query set: When looking at the execution plans for the first set of queries IndexTest3 actually has the best performance (although barely). All of them are around an I/O cost of 40, with IndexTest2 being the worst and IndexTest 3 being the best.

Questiong: Does that mean that the indexes I added have no real effect on the first query set? And if so, why would that be if EventType is included in the clustered index of IndexTest1?

I have very little experience with testing indexes and creating them. Is there any other way I can test which index would perform best for the above queries? Or is there something else I should try out?

Or does anyone have any reading material they could link me to for more information about indexes and testing them?

Answer 1

The Clustered Index should be defined with a narrow and ever increasing value (this is why so many of them use an auto-incrementing integer).

Having worked with large tables like this before, your best bet is to continue to use an auto-incrementing primary key as the definition for the clustered index. Then create non-clustered indexes that support your queries, with the understanding that other ad-hoc queries will probably perform terribly. You get the added benefit that the index on your large table should never need defragmentation.

Given that, creating a non-clustered index on Timestamp, EventType should give sufficient performance. You typically want the most specific column referenced first, and timestamps are good candidates for this, especially if you always (or almost always) include it in your query.

The advantage of non-clustered indexes is of course that they are typically much narrower than their base table and so require much less memory.

CREATE NONCLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

You can also benefit from casting to the proper datatype (I'm assuming DATETIME)

SELECT * FROM IndexTest1
WHERE Timestamp >= CAST('2018-03-14' AS DATETIME) AND Timestamp <= CAST('2018-03-20' AS DATETIME) AND EventType = 'String'

Finally, check that statistics are up to date for your query and that the execution plan is actually doing an index seek or range scan with a key lookup back to the main table.

Answer 2

You likely can't conclude anything from your tests because you've introduced too many differences between test and production. You're testing with only a million rows from your table, with queries that likely don't match what is run in production (do you really always select all columns), and displaying the live query stats view in SSMS. Displaying the live query stats view and the result set in SSMS takes time. The most reliable method of testing is to use production queries that you care about against a full size copy of the data. That will reduce uncertainty because questions such as "This clustered index works okay with 1 million rows but how will that apply to a much larger table?" are no longer relevant.

You said that there's a "decrease in performance". How can that be quantified? In other words, why does it matter that there's a decrease in performance? What parts of the application suffer from the decrease in performance? For example, there could be an important query that end users run that used to take 1 second but now takes 5 seconds. If so, test the performance of that query with different options for the clustered index. You've quantified the impact and now have something that you can measure. Currently what you're doing is a bit of an abstract test and you're attempting to apply those results to your application, but not surprisingly it's difficult to do that.

On the topic of how clustered indexes work, you can't get an index seek predicate on just the second column of a clustered index. You also can't get index seek predicates with a range predicate on the first key and an equality key on the second predicate. Going through your queries in order:

SELECT * FROM IndexTest1
WHERE EventType = 'String';

This will result in a clustered index scan because EventType is the second key of the index.

SELECT * FROM IndexTest2
WHERE EventType = 'String';

This will result in a clustered index scan because EventType is not a key column.

SELECT * FROM IndexTest3
WHERE EventType = 'String';

This will result in a clustered index seek because EventType is the first key in the index. You should expect this query to perform the best out of the set.

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

This will result in a clustered index seek on the Timestamp column only because the equality predicate on EventType can't be used due to the inequality predicate on the first key column.

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

This will result in a clustered index seek on the Timestamp key column.

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

This will result in a clustered index seek on EventType.

The query that performs the best out of this set depends on the selectivity of the columns.

Answer 3

The principle is simple. Start with the =.

(EventType, Timestamp)

works best for both of your queries. (Pity you did not test it.)