So if your primary access path is by question, then the unique clustered index that makes the most sense will be
Adding a second index to
EventId may not be useful as the index might not be selective enough and the query engine will decide it's just faster to read the entire table instead of doing a lot of work to read a large portion of it.
Alternately, if you always query based wholly or in part on
EventId, the clustered index of
(EventId,Questionid) is more suitable, and has the added benefit of making your updates based on
EventId require less I/O to complete.
I would not include
Value to an additional index, as this will essentially copy the entire table (just clustered on different columns) and your updates will take even longer as
Value must be kept in sync across both the clustered index and the additional index.
At a certain point there is no free lunch, and the correct solution is probably choosing the clustered index with the leading column that supports the most use cases, then adding RAM/CPU/faster storage to deal with cases where the entire table (or large chunks of it) must be read or written to. With 40 million rows and such a narrow table I can't imagine this being something that more RAM wouldn't fix.
Depending on your edition of SQL Server, you can also look into if page compression would reduce the table size significantly, as this would reduce the number of reads/writes to disk (the additional CPU overhead is offset by fewer disk operations). My guess in your case is it would not, but it's work looking at.
So if I understand correctly (with the current knowledge) your gut
feeling would be Clustered Index on
QuestionId,EventId and then a
normal Index on
EventId for the batch update?
Only if the primary use is to return specific
QuestionIds without regard to
EventId. You can try the additional index in
EventId, but you may find it isn't being used often (or at all) for updates (or updates still take longer than you'd like), depending on how EventIds are distributed through your data with respect to
You also have to determine what is more important to you overall - select performance or update performance. If the update is the pain point,
(EventId,QuestionId) will undoubtedly be the better choice. Given the number of unique values for
QuestionId, adding an index on that column could be useful for
SELECT performance, but that's going to depend on how
QuestionId is distributed and how many you are searching for at a time.
Keeping statistics up to date will be crucial in either case.
An extremely simple example (for the sake of completeness):
Let's say we have a DBMS that maintains a clustered index and stores 4 rows per page. We have a table with a primary key of
(QuestionId, EventId) and one additional column,
If we create the clustered index as
(QuestionId, EventId), the data in our imaginary DBMS is (roughly speaking) stored like so:
Page | QuestionId | EventId | Value
A | 1 | 2 | ...
A | 1 | 3 | ...
A | 1 | 6 | ...
A | 1 | 7 | ...
B | 1 | 8 | ...
B | 1 | 10 | ...
B | 1 | 11 | ...
B | 2 | 2 | ...
C | 3 | 2 | ...
C | 4 | 1 | ...
C | 5 | 6 | ...
C | 5 | 7 | ...
D | 6 | 1 | ...
D | 7 | 2 | ...
D | 7 | 6 | ...
D | 7 | 8 | ...
So if I need to perform an operation based on
QuestionId, the engine will not have to read more pages than necessary.
However, if I need to perform an operation based on
EventId, I will have to read the entire table (clustered index scan), unless I add an additional index, which will look like this (and require four pages):
EventId | QuestionId
1 | 4
1 | 6
2 | 1
2 | 2
2 | 3
2 | 7
3 | 1
6 | 1
6 | 5
6 | 7
7 | 1
7 | 5
8 | 1
8 | 7
10 | 1
11 | 1
This index be selective for some
EventIds, but in the extreme case (
EventId = 2) the whole table would still need to be read, and for some cases (
EventId = 6) our optimizer might decide that searching the index and reading the table is more expensive than just reading the entire table.
If we instead cluster on
EventId, QuestionId our table looks like this:
Page | EventId | QuestionId | Value
A | 1 | 4 | ...
A | 1 | 6 | ...
A | 2 | 1 | ...
A | 2 | 2 | ...
B | 2 | 3 | ...
B | 2 | 7 | ...
B | 3 | 1 | ...
B | 6 | 1 | ...
C | 6 | 5 | ...
C | 6 | 7 | ...
C | 7 | 1 | ...
C | 7 | 5 | ...
D | 8 | 1 | ...
D | 8 | 7 | ...
D | 10 | 1 | ...
D | 11 | 1 | ...
Any operation based on
EventId will only read the necessary portions of the table, and like our first instance, any operation based on
QuestionId will require a scan without an additional index. If we create an index on
QuestionId, the index will be:
QuestionId | EventId
1 | 2
1 | 3
1 | 6
1 | 7
1 | 8
1 | 10
1 | 11
2 | 2
3 | 2
4 | 1
5 | 6
5 | 7
6 | 1
7 | 2
7 | 6
7 | 8
So as in the first example, this index will be more useful for some questions and less useful for others. For
QuestionId = 1 the optimizer will probably say the cost of reading half the index then looking up half the table isn't worth the cost and will just read the entire table instead of utilizing the index.
If we include
Value on the index, we now have to change the table AND the index within the same transaction. Best case, this doubles the work for any operation. Worst case this requires the entire table or index (which is just a copy of the table) to be read and possibly locked.
Now it's possible with your actual data that adding an additional index on
EventId will provide a lot of benefit. But it's not going to solve every problem, and it might not be worth the overhead imposed on inserts/updates/deletes.