Geek asks and answers

Why is this query not using the correct index?

Table definition:

CREATE TABLE [dbo].[AllErrors](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [DomainLogin] [nvarchar](50) NULL,
  [ExceptionDate] [datetime] NULL,
  [ExceptionDescr] [nvarchar](max) NULL,
  [MarketName] [nvarchar](50) NULL,
  [Version] [nvarchar](50) NULL,
  CONSTRAINT [PK_AllErrors] PRIMARY KEY CLUSTERED ([ID] ASC)
)

-- Add an index on the date
CREATE NONCLUSTERED INDEX [IX_ExceptionDate] ON [dbo].[AllErrors] ([ExceptionDate] ASC)

I run this query:

declare @yesterday datetime
select @yesterday = getdate() - 1

SELECT * INTO #yst
from AllErrors 
where ExceptionDate between @yesterday and @yesterday + 1

enter image description here

This code does not use mine IX_ExceptionDate(as gleaned from the execution plan). It performs a cluster scan of the primary key index. However, the code below uses the index IX_ExceptionDate:

SELECT * INTO #yst
from AllErrors 
where ExceptionDate between @yesterday and @yesterday + 1
  AND ExceptionDate = ExceptionDate

enter image description here

Why is this?

EDIT: Added Visual Execution Plan.

EDIT: text execution plans below.

Request 1:

| --Table Insert (OBJECT: ([# yst]), SET: ([# yst]. [ID] = [Expr1006], [# yst]. [DomainLogin] = [MarketStats]. [dbo] [AllErrors]. [DomainLogin], [# yst]. [ExceptionDate] = [MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate], [# yst]. [ExceptionDescr] = [MarketStats]. [Dbo] [AllErrors]. [ExceptionDescr], [# yst]. [MarketName] = [MarketStats]. [Dbo]. [AllErrors]. [MarketName], [# yst]. [] = [MarketStats]. [Dbo]. [AllErrors]. [Version]))        | --Top (ROWCOUNT est 0)             | --Compute Scalar (DEFINE: ([Expr1006] = setidentity ([MarketStats]. [Dbo]. [AllErrors]. [ID], (- 7), (0), N '# yst')))                  | -- (OBJECT: ([MarketStats]. [Dbo]. [AllErrors]. [PK_AllErrors]), WHERE: ([MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate] >= [@ ] AND [MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate] <= [@] + '1900-01-02 00: 00: 00.000'))

2:

| --Table Insert (OBJECT: ([# yst]), SET: ([# yst]. [ID] = [Expr1006], [# yst]. [DomainLogin] = [MarketStats]. [dbo] [AllErrors]. [DomainLogin], [# yst]. [ExceptionDate] = [MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate], [# yst]. [ExceptionDescr] = [MarketStats]. [Dbo] [AllErrors]. [ExceptionDescr], [# yst]. [MarketName] = [MarketStats]. [Dbo]. [AllErrors]. [MarketName], [# yst]. [] = [MarketStats]. [Dbo]. [AllErrors]. [Version]))        | --Top (ROWCOUNT est 0)             | --Compute Scalar (DEFINE: ([Expr1006] = setidentity ([MarketStats]. [Dbo]. [AllErrors]. [ID], (- 7), (0), N '# yst')))                  | -- ( , : ([MarketStats]. [Dbo]. [AllErrors]. [ID], [Expr1008]) )                       | --Index Seek (OBJECT: ([MarketStats]. [Dbo]. [AllErrors]. [IX_ExceptionDate]), SEEK: ([MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate] >= [@yesterday] [MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate] <= [@yesterday] + '1900-01-02 00: 00: 00.000'), WHERE: ([MarketStats]. [Dbo]. [ AllErrors]. [ExceptionDate] = [MarketStats]. [Dbo]. [AllErrors]. [ExceptionDate]) ORDERED FORWARD)                       | -- (OBJECT: ([MarketStats]. [Dbo]. [AllErrors]. [PK_AllErrors]), SEEK: ([MarketStats]. [Dbo]. [AllErrors]. [ID] = [MarketStats]. [dbo]. [AllErrors]. [ID]) LOOKUP ORDERED FORWARD)

+3
2

, . OPTION (RECOMPILE).

, AND ( ) , , !

, ExceptionDate = ExceptionDate 88234.8 , 8823.48

, SQL Server , .

, > 30% , , = 10% , , . , , !

c.f. -

+6

: - "SELECT *" : , .

.

declare @yesterday datetime
select @yesterday = getdate() - 1

SELECT * INTO dbo.#yst
from AllErrors WITH (INDEX = IX_ExceptionDate)
where ExceptionDate between @yesterday and @yesterday + 1


declare @yesterday datetime
select @yesterday = getdate() - 1

SELECT * INTO dbo.#yst
from AllErrors
where ExceptionDate between @yesterday and @yesterday + 1


declare @yesterday datetime
select @yesterday = getdate() - 1

SELECT ExceptionDate INTO dbo.#yst
from AllErrors
where ExceptionDate between @yesterday and @yesterday + 1
+5

More articles:


All Articles