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!