Databases
StackOverflow2013
Postsdbo
POSQL Server doesn't use index in stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server doesn't use index in stored procedure
    primarykey
    data
    text
    <p><strong><em>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</em></strong></p> <p><strong>Please see the extended table scheme below</strong><br> <em>Edit 2: Updated the index (to not use actieGroep anymore)</em><br> <em>NB. SQL Server 2005 Enterprise 9.00.4035.00</em><br> <em>NB2. Seems related to <a href="http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx" rel="noreferrer">http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx</a></em></p> <p>I've got two indices on a table:</p> <ul> <li>A clustered PK index on <em>statistiekId</em></li> <li>An non-clustered index on <em>foreignId</em></li> </ul> <p>And I have the following piece of code:</p> <pre><code>DECLARE @fid BIGINT SET @fid = 873926 SELECT foreignId FROM STAT_Statistieken WHERE foreignId = @fid </code></pre> <p>This executes just the way it should; it points to the correct index, and all it does is scanning the index.</p> <p>Now I am creating a stored procedure:</p> <pre><code>ALTER PROCEDURE MyProcedure (@fid BIGINT) AS BEGIN SELECT foreignId FROM STAT_Statistieken WHERE foreignId = @fid END </code></pre> <p>Running the thing: </p> <pre><code>EXEC MyProcedure @fid = 873926 </code></pre> <p>Now it's running a <strong>clustered index scan on my PK index</strong>! Wtf is going on?</p> <p>So I changed the SP to</p> <pre><code>SELECT foreignId FROM STAT_Statistieken WITH (INDEX(IX_STAT_Statistieken_2)) WHERE foreignId = @fid </code></pre> <p>And now it gives: <em>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.</em> While the same function is running just like it should when executing this directly.</p> <hr> <h2>Extra info: full scheme which can reproduce this behaviour (English names in commentary)</h2> <p><strong>Table</strong> </p> <pre><code>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] </code></pre> <p><strong>Index</strong> </p> <pre><code>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] </code></pre> <p><strong>Execution</strong></p> <pre><code>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 &gt;= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden, SUM(CASE WHEN dagnummer &gt;= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken, SUM(CASE WHEN dagnummer &gt;= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact, SUM(CASE WHEN dagnummer &lt; @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige, SUM(CASE WHEN dagnummer &lt; @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige, SUM(CASE WHEN dagnummer &lt; @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige FROM STAT_Statistieken WHERE dagnummer &gt;= @startDiff AND dagnummer &lt; @endDiff AND foreignId = @foreignId OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300)) </code></pre> <p><strong>DBCC Statistics</strong></p> <pre><code>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 </code></pre> <p>And the index is used as shown in the execution plan. When I wrap this up in a procedure with this params:</p> <pre><code>@foreignId bigint, @maand int, --month @jaar int --year </code></pre> <p>And run it with <code>_SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009</code></p> <p>It does a clustered index scan!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJan Jongboom
    1. USJan Jongboom
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. USkristof
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. USJan Jongboom
      1. VTBountyStart
    1. COIs the point of the sproc to figure out if a link exists to that other table, from STAT_Statistieken, or how many such links exists?
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. USLasse Vågsæther Karlsen
    2. CONo this is just an abstract version of the problem. I just want to do a SELECT on the table with using the index. The table only contains a bunch of bigints
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. USJan Jongboom
    3. CO@Jan: what's the running time of your query when using the clustered and the regular index?
      singulars
      1. POSQL Server doesn't use index in stored procedure
      1. USLieven Keersmaekers
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload