sql-serverindexingsqlperformance

Multiple index suggestions with same columns


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:

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:


Solution

  • 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.