I have a table that gets thousands of select queries per hour. SQL server is suggesting that I create four indexes, with 99 % improvement estimate for each:
Types are as follows:
nvarchar(20)
nvarchar(20)
tinyint
int
The table is not updated as frequently, select queries seem to outnumber updates/inserts about 1000 to 1. The question is then, should I create all indexes or will one index serve to improve all queries? And if I would want to add one at a time, and check performance after each, in which order should I add them, and should they be added separately or in some combinations? And if the question cannot be answered, is there anything I could do to find out, besides trial and error, which is not an option because I can't modify these myself.
EDIT (queries linked to indexes suggested):
Query 1:
SELECT TOP (1)
* --enumerated, but still all columns selected
FROM
"TableName" WITH(UPDLOCK)
WHERE
("Line Code"=@0 AND "Code Qualifier"=@1 AND "Line No_"=@2)
ORDER BY
"Message Batch Number" ASC,
"Message Line Number" ASC OPTION(OPTIMIZE FOR UNKNOWN)
Query 2:
SELECT
* --enumerated, but still all columns selected
FROM
"TableName" WITH(UPDLOCK)
WHERE
("Line Code"=@0 AND "Code Qualifier"=@1 AND "Status"=@2 AND "Line No_"=@3)
ORDER BY
"Message Batch Number" ASC,
"Message Line Number" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
Query 3:
UPDATE
"TableName"
SET
"Status"=@0
WHERE ("Code Qualifier"=@1)
So, one of the was actually an update. This is done also many times per hour, but not as frequently as the selects. I couldn't find one of these queries immediately, but hopefully these will do.
EDIT 2 (Existing indexes): So these are already in place, but not used in hardly any queries:
I managed to test this by replicating our production database and running these queries in a loop with and without different indexes. Surprisingly, it seemed that adding a nonclustered index only on Code Qualifier
dropped execution times to practically zero on all queries with a few dozen iterations. So in answer to my own question, a single index for a single column seems quite sufficient for practical purposes.