SQL Server doesn't use index in stored procedure

indexing, sql-server, t-sql

Solution

[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!

Problem

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!

Original source