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). – Aaron Bertrand Oct 28 '14 at 20:42IDX_myIndex
not used? What is the actual query plan? – ypercubeᵀᴹ Oct 29 '14 at 0:09WHERE [serialNumber] = 137802
while the column is a varchar. I'd bet that's the reason for the complaints. – ypercubeᵀᴹ Oct 29 '14 at 0:13serialNumber
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. – Martin Smith Oct 29 '14 at 8:47