I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query.
First the table. Column names changed to protect the innocent :-)
CREATE TABLE [myTable] (
[id] [int] IDENTITY(1,1) NOT NULL,
[serialNumber] [varchar](12) NOT NULL,
[sName] [varchar](64) NOT NULL,
[meanValue] [int] NOT NULL,
[range] [int] NOT NULL,
[modifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED ( [id] ASC )
)
Create the index in question:
CREATE NONCLUSTERED INDEX [IDX_myIndex]
ON [myTable] ([serialNumber], [sName], [meanValue], [range])
INCLUDE ([modifiedDate])
Add data for testing using your generator of choice ;-) Run the following query (table only has a few million records)
SELECT TOP 1000
[serialNumber],
[sName],
[meanValue],
[range],
[modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'
The query optimizer recommends using a new index where the additional where clauses are covered in the INCLUDE instead of part of the key:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([sName])
INCLUDE ([serialNumber],[meanValue],[range],[modifiedDate])
I was under the impression that a broader index that encompasses more columns will be used as the index as long as the order of the WHERE clauses represents the order of the columns indexed.
If I also WHERE on the modifiedData the index gets used and the query optimizer doesn't complain:
SELECT TOP 1000
[serialNumber],
[sName],
[meanValue],
[range],
[modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'
AND ([modifiedDate] >= '2000-04-25' AND [modifiedDate] < '2019-04-30')
The DBA link
SQL Server 2008R2 - Why is my index not used suggests a closer correlation between the index key and includes with the SELECT statement helps determine index use (but in my example they are basically the same). I have lots of rows, which probably satisfies the row use probability test, and there are no NULLs - thus negating the NULL effect of the index.
I thought, maybe incorrectly, that an index A, B, C, D
would cover an query where A, B, C
, or A, B
, or A
would be run. Is this assumption wrong? I realize there could be edge conditions that throw this basic concept off-kilter, but at a fundamental level, isn't this roughly how it's supposed to work?
Thanks in advance for any help, pointing out the stupidity in my ways, recognizing I need to go (back) to DB school etc... :-)
SerialNumber
and the other issName
. Also for cases where two indexes could equally satisfy a query, and where the indexes are essentially the same size, it's essentially a coin flip for SQL Server. The fact that it recommended a different index probably means that it could have used a different plan (this is just a comment because I haven't looked into the actual details or tried to repro your scenario).IDX_myIndex
not used? What is the actual query plan?WHERE [serialNumber] = 137802
while the column is a varchar. I'd bet that's the reason for the complaints.serialNumber
avarchar
in the first place if the values it contains are numeric? If it always stores numeric values and leading zeros arent significant then storing as a numeric type will be more compact.