Search code examples
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:

  • [TableName] ([Code Qualifier]) (Query 3)
  • [TableName] ([Line Code], [Code Qualifier], [Line No_]) (Query 1)
  • [TableName] ([Line Code], [Code Qualifier], [Status])
  • [TableName] ([Line Code], [Code Qualifier], [Status], [Line No_]) (Query 2)

Types are as follows:

  • [Code Qualifier] nvarchar(20)
  • [Line Code] nvarchar(20)
  • [Status] tinyint
  • [Line No_] 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:

  • [Message Batch Number] (int), [Message Line Number] (int) (Clustered)
  • [Status], [Message Batch Number], [Message Line Number]
  • [Control Reference] (nvarchar(30), [Message Batch Number], [Message Line Number]

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.