January 24, 2013 at 11:45 am
Greetings. I'm running SQL Server 2008 R2 SP1 (build 10.50.2550.0). We've got some partitioned tables and are noticing in our stored proc code sometimes partition elimination happens and sometimes it does not. Upon further research we've found that partition elimination happens fine when literals are used in queries but that goes out the window when parameters are used (see examples and results below), which obviously makes stored proc programming / performance a major issue if true. I see there's a fix in CU 4 (http://support.microsoft.com/kb/2624527) that addresses some behavior surrounding partition elimination for Clustered Indexes (the column referenced in the where clause is indeed a Clustered index and also the column which the table is partitioned on). Is anyone aware if this or is also a known issue fixed in one of the cumulative updates?
Literals - partition elimination occurs correctly as expected.
StmtText
-------------------------------------------------------------------------
SELECT TOP 1 * FROM SALESDATA WHERE INSERTTIME > '1/23/2013';
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([SALESDB].[dbo].[SALESDATA].[IDX1_SALESDATA]), SEEK:([PtnId1000]=(14) AND [SALESDB].[dbo].[SALESDATA].[INSERTTIME] > '2013-01-23 00:00:00.000') ORDERED FORWARD)
Parameters - identical statement as above but partition elimination is not happening
StmtText
--------------------------------------------------------------------
DECLARE @MYDATE DATETIME = '1/23/2013';
SELECT TOP 1 * FROM SALESDATA WHERE INSERTTIME > @MYDATE;
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([SALESDB].[dbo].[SALESDATA].[IDX1_SALESDATA]), SEEK:([PtnId1000] >= RangePartitionNew([@MYDATE],(1),'2012-01-01 00:00:00.000','2012-02-01 00:00:00.000','2012-03-01 00:00:00.000','2012-04-01 00:00:00
I did get a suggestion to try OPTION(RECOMPILE) but that didn't help either, see below.
StmtText
---------------------------------------------------------------------------------------
DECLARE @MYDATE DATETIME = '1/23/2013';
SELECT TOP 1 * FROM SALESDATA WHERE INSERTTIME > @MYDATE
OPTION(RECOMPILE);
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([SALESDB].[dbo].[SALESDATA].[IDX1_SALESDATA]), SEEK:([PtnId1000] >= RangePartitionNew([@MYDATE],(1),'2012-01-01 00:00:00.000','2012-02-01 00:00:00.000','2012-03-01 00:00:00.000','2012-04-01 00:00:00
January 26, 2013 at 6:01 am
Probably the same reason that a parameterized query cannot always make use of a filtered index. When SQL Server generates a plan that accepts a DATETIME in the WHERE-clause it must ensure that whenever the plan is executed it returns the correct answer. Since there is no guarantee that the next time someone uses the plan generated in your second example will submit a value that corresponds to the same partition needed by the initial execution it cannot do the partition elimination. The situation is similar in the third example, since SQL Server cannot know that everyone executing the query will supply OPTION(RECOMPILE) it again must generate a plan that will always return the correct result for any parameter value.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply