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
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
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?