SQL Server doesn't use index in stored procedure

Question

I haven't solved this issue by using the stored procedure, yet we've decided to surpass the SP and just execute the plain ol' SQL

Please see the extended table scheme below
Edit 2: Updated the index (to not use actieGroep anymore)
NB. SQL Server 2005 Enterprise 9.00.4035.00
NB2. Seems related to http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx

I've got two indices on a table:

• A clustered PK index on statistiekId
• An non-clustered index on foreignId

And I have the following piece of code:

DECLARE @fid BIGINT
SET @fid = 873926

SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid

This executes just the way it should; it points to the correct index, and all it does is scanning the index.

Now I am creating a stored procedure:

ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
    SELECT foreignId
    FROM STAT_Statistieken
    WHERE foreignId = @fid
END

Running the thing:

EXEC MyProcedure @fid = 873926

Now it's running a clustered index scan on my PK index! Wtf is going on?

So I changed the SP to

SELECT foreignId
FROM STAT_Statistieken
    WITH (INDEX(IX_STAT_Statistieken_2))
WHERE foreignId = @fid

And now it gives: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. While the same function is running just like it should when executing this directly.

---

Extra info: full scheme which can reproduce this behaviour (English names in commentary)

Table

CREATE TABLE [dbo].[STAT_Statistieken](
    [statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
    [foreignId] [bigint] NOT NULL,
    [datum] [datetime] NOT NULL, --date
    [websiteId] [int] NOT NULL,
    [actieId] [int] NOT NULL, --actionId
    [objectSoortId] [int] NOT NULL, --kindOfObjectId
    [aantal] [bigint] NOT NULL, --count
    [secondaryId] [int] NOT NULL DEFAULT ((0)),
    [dagnummer]  AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
    [actieGroep]  AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
    CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
    (
        [statistiekId] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

Index

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] ASC,
    [dagnummer] ASC,
    [actieId] ASC,
    [secondaryId] ASC
)WITH (PAD_INDEX  = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]

Execution

SET NOCOUNT ON;

    DECLARE @maand INT, @jaar INT, @foreignId BIGINT
    SET @maand = 9
    SET @jaar = 2009
    SET @foreignId = 828319


DECLARE @startDate datetime, @endDate datetime
SET @startDate = DATEADD(month, -1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
SET @endDate = DATEADD(month, 1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))

DECLARE @firstDayDezeMaand datetime
SET @firstDayDezeMaand = CONVERT(datetime, CAST(@jaar AS VARCHAR(4)) + '/' + CAST(@maand AS VARCHAR(2)) + '/1')

DECLARE @daynumberFirst int
set @daynumberFirst = DATEDIFF(day, '2009/01/01', @firstDayDezeMaand)

DECLARE @startDiff int
SET @startDiff = DATEDIFF(day, '2009/01/01', @startDate)

DECLARE @endDiff int
SET @endDiff = DATEDIFF(day, '2009/01/01', @endDate)

SELECT @foreignId AS foreignId,
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden, 
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken, 
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact,
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige, 
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige, 
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige
FROM STAT_Statistieken
WHERE
    dagnummer >= @startDiff
    AND dagnummer < @endDiff
    AND foreignId = @foreignId 
OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300))

DBCC Statistics

Name                                                          | Updated               | Rows      | Rows smpl | Steps | Density | Avg. key | String index
IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId    Oct  6 2009  3:46PM 1245058    1245058    92    0,2492834    28    NO

All Density  | Avg. Length | Columns
3,227035E-06    8    foreignId
2,905271E-06    12    foreignId, dagnummer
2,623274E-06    16    foreignId, dagnummer, actieId
2,623205E-06    20    foreignId, dagnummer, actieId, secondaryId
8,031755E-07    28    foreignId, dagnummer, actieId, secondaryId, statistiekId

RANGE HI | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE ROWS
-1         0            2         0                     1
1356       3563         38        1297                  2,747109
8455       14300        29        6761                  2,115072

And the index is used as shown in the execution plan. When I wrap this up in a procedure with this params:

@foreignId bigint,
@maand int, --month
@jaar int --year

And run it with _SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009

It does a clustered index scan!

Answer 1

[EDIT]

The PERSISTED-not-being-used issue below occurs only with actieGroep/actieId on my system (SQL 2008). But it's possible that the same problem could be happening on your SQL 2005 system with the dagnummer/datum columns as well. If indeed that's happening, it would explain the behavior you're seeing, since a clustered index scan would be required to filter for values of datum. To diagnose whether this is indeed happening, simply add the datum column as an INCLUDE-d column to your index, like this:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken]  
(  
    [foreignId] DESC,  
    [dagnummer] DESC,  
    [actieId] ASC,   
    [aantal] ASC    
) INCLUDE (datum)  ON [PRIMARY]

If the problem goes away with this index revision, then you know that dagnummer is the issue-- you can probably even remove dagnummer from the index since SQL isn't using it anyways.

Also, revising your index to add actieId is a good idea since it evades the issue noted below. But in the process you also need to leave the aantal column in the index, so that your index will be a covering index for this query. Otherwise SQL will have to read your clustered index to get the value of that column. This will slow down your query since lookups into the clustered index are quite slow.

[END EDIT]

Here's a bunch of ideas which may help you fix this, with most likely/easiest things first:

• When I tried to repro your using schema and queries (with fake generated data), I see that your PERSISTED computed column actieGroep is re-copmputed at runtime instead of the persisted value being used. This looks like a bug in the SQL Server optimizer. Since the underlying column value actieGroep is not present in your covering index IX_STAT_Statistieken_1 index (only the computed column is there), if SQL Server decides that it needs to fetch that additional column, SQL may consider a clustered index to be cheaper than using your non-clustered index and then looking up actieId for each matching row in the cluster index. This is because clustered index lookups are very expensive relative to sequential I/O, so any plan which requires more than a few percent of rows to be looked up is probably cheaper to do with a scan. In any case, if this is indeed the problem you're seeing, then adding actieGroep as an INCLUDE-d column of your IX_STAT_Statistieken_1 index should work around the issue. Like this:

  CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken]
(
[foreignId] DESC,
[secondaryId] ASC,
[actieGroep] ASC,
[dagnummer] DESC,
[aantal] ASC
) INCLUDE (actieId) ON [PRIMARY]

• the data type of the computed column actieGroep is a string but you're comparing it to integers (e.g. IN (1,2,3)) in your WHERE clause and CASE statements. If SQL decides to convert the column instead of the constant, it will hurt query perf and may make the computed-column-expansion problem (described above) more likely. I'd strongly suggest changing your computed column definition to an integral type, e.g.

  CASE WHEN actieId BETWEEN 0 AND 9 THEN actieId
WHEN actieId BETWEEN 10 AND 99 THEN actieId/10
WHEN actieId BETWEEN 100 AND 999 THEN actieId/100
WHEN actieId BETWEEN 1000 AND 9999 THEN actieId/1000
WHEN actieId BETWEEN 10000 AND 99999 THEN actieId/10000
WHEN actieId BETWEEN 100000 AND 999999 THEN actieId/100000
WHEN actieId BETWEEN 1000000 AND 9999999 THEN actieId/1000000
ELSE actieId/10000000 END

• you're doing a GROUP BY a column which only has one possible value. Therefore, the GROUP BY is unnecessary. Hopefully the optimizer would be smart enough to know this, but you can never be sure.

• Try using an OPTIMIZE FOR hint instead of directly forcing indexes, that may work around the error you get with your hint

• Craig Freedman's post http://blogs.msdn.com/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx which describes common causes of the hint-related error message that you're getting when RECOMPILE is used. You may want to review that post and make sure you're running the latest updates to SQL Server.

• I'm sure you've already done this, but you may want to build a "clean room" version of your data, by doing what we're doing: creating a new DB, use the DDL in your question to create the tables, and then populating the tables with data. If the results you get are different, look closley at the schema in your real table and indexes, and see if they're different.

If none of this works, comment and I can suggest some more wild ideas. :-)

Also, please add the exact version and update level of SQL Server to your question!

Answer 2

Try creating your index like this:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)
INCLUDE (actieGroep);       
WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

And recreate your procedure afterwards

Answer 3

I've seen similar behavior before, where it would actually take the index hint and do something worse with it (unfiltered index scan with bookmark lookup).

One of these four should help:

1) Append ;-T4102;-T4118 to SQL Server 2005 startup parameters (might apply to SQL 2008). Note: this brings back the SQL 2000 bad handling of IN and NOT IN queries in SQL 2005.

2) UPDATE STATISTICS [dbo].[STAT_Statistieken] WITH FULLSCAN

3) OPTION (MAXDOP 1) -- sometimes the parallelism causes really stupid queries to be generated

4) Ensure the index is online.

Also note that if you are creating an index on a table created in a stored procedure, that index does not exist when compiling the stored procedure queries so it will not be used. Since your table is created globally in dbo I assume that is NOT the case here.

EDIT: sometimes I wish there was a true forceplan where you could key in the plan directly and any possible plan will be executed: sort of an assembly-like language for the DB.

Answer 4

First, I should say that the indexes you have created are not optimal, since they can only be used to filter on foreignId.

SQL Server is not able of doing SKIP SCAN and you have a secondaryId in your index which is not being filtered with a range condition.

Therefore your condition on foreignId, actieGroep, dagNummer does not yield a limited number of ranges and is not completely sargable. It can filter only on foreignID, not on the whole set.

Now, to your current index.

I just created your tables and filled them with the random data using this script:

DROP TABLE STAT_Statistieken

CREATE TABLE [dbo].[STAT_Statistieken](
    [statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
    [foreignId] [bigint] NOT NULL,
    [datum] [datetime] NOT NULL, --date
    [websiteId] [int] NOT NULL,
    [actieId] [int] NOT NULL, --actionId
    [objectSoortId] [int] NOT NULL, --kindOfObjectId
    [aantal] [bigint] NOT NULL, --count
    [secondaryId] [int] NOT NULL DEFAULT ((0)),
    [dagnummer]  AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
    [actieGroep]  AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
    CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
    (
        [statistiekId] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieGroep] ASC,
    [dagnummer] DESC, 
    [aantal] ASC --count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,  ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

;WITH    nums AS
        (
        SELECT  1 AS num
        UNION ALL
        SELECT  num + 1
        FROM    nums
        )
INSERT
INTO    STAT_Statistieken (
        [foreignId], [datum], [websiteId], [actieId],
        [objectSoortId], [aantal])
SELECT  TOP 100000
        500, GETDATE(), num, num, num, num % 5
FROM    nums
UNION ALL
SELECT  TOP 100000
        num % 1000, GETDATE(), num, num, num, num % 5
FROM    nums
OPTION (MAXRECURSION 0)

UPDATE STATISTICS STAT_Statistieken

, and it uses INDEX SEEK no matter what, which most probably means that the problem is with your data distribution.

I'd recommend you to create an additional index with secondaryID removed, like this:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_3] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [actieGroep] ASC,
    [dagnummer] DESC, 
    [aantal] ASC --count
)

IF you still want to use your current index, could you please run these commands:

DBCC SHOW_STATISTICS ('STAT_Statistieken', 'IX_STAT_Statistieken_1')
DBCC SHOW_STATISTICS ('STAT_Statistieken', 'IX_STAT_Statistieken_2')

Each command will output three resultsets.

Could you please post resultsets 1 and 2 from each command, and three rows from resultset 3 with value of RANGE_HI just above, just below and equal to 873926?

Answer 5

select AU.*
FROM SYS.Allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID = object_id('STAT_Statistieken')

Try this and check if the NON CLUSTERED Index Has more pages than the CLUSTERED INDEX (THIS WOULD MEAN THAT IS CHEAPER TO READ THE CLUSTERED INDEX)

Answer 6

When you pass in the parameter how many rows in the table match the JOIN relative to the total number of rows in the table? SQL Server chooses an index using among other things a ratio of matching rows returned by the JOIN to the total number of rows in the table. If there are a large number of rows returned relative to the total number in the table the index may be ignored as SQL Server preferences indexes where the number of matching rows is lower relative to the total.

So if your SELECT and your Stored Procedure call used different values for @fid then you might sometimes use the index and other times not. If this sounds like your issue take a look at "selectivity ratio" in google.

Good luck!

Answer 7

Try this and tells us the result:

DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.

You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results.

Example:

DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID)

Answer 8

What data type is foreignId in the table? If it's int then you're likely getting an implicit conversion which prevents index seeks. If the data type in the table is int then redefine the parameter to be int as well and you should get an index seek (not an index scan) for this query.

Answer 9

it could be parameter sniffing, so try something like this:

ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
    DECLARE @fid_sniff BIGINT
    SET @fid_sniff=@fid
    SELECT foreignId
    FROM STAT_Statistieken
    WHERE foreignId = @fid_sniff
END

read more anout parameter sniffing: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Answer 10

The error message you have received can be generated when there are conflicting query hints within your query.

Can you run the query, including the hint, outside of the stored procedure?

An alternative train of thought, have you been testing/running your stored procedure with a different parameter value? It is possible that the parameter value used to create the original execution plan is not an appropriate candidate for all activity. You may wish to consider recompiling your stored procedure to see if a different execution plan is produced between different runs with different parameters.

Should you wish to ensure that a new query plan is calculated for each execution of your stored procedure then you can make use of the WITH RECOMPILE clause. This should be the exception and NOT the norm. Validate the behaviour of your procedure and it's plan generation through testing.