March 3, 2010 at 4:15 am
Are you saying that the execution plan shows a Clustered Index Scan rather than a Clustered Index Seek?
This is a common problem with catch all queries.
Even though this parameter is static throughout the query, the IS NULL condition is evaluated for each row and as such the index can not satisfy this.
There's some good articles here with a few options.:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
None of them are without problems in my opinion. It's something SQL Server could optimise better internally when static parameters are being evaluated.
With all of the methods, you have to be careful about the plans that will be cached in the stored procedure as there needs to be wildly different plans for returning 1 row compared to returning the entire table.
March 3, 2010 at 5:50 am
HowardW (3/3/2010)
It's something SQL Server could optimise better internally when static parameters are being evaluated.
How?
There's one fundamental rule that the optimiser has to follow. Plans must be safe for reuse. Remember those execution plans are cached and reused and any matching query will use the cached execution plan.
Consider if the first execution was with a non-null parameter and SQL generated an execution plan that involved an index seek, then the second execution the parameter was null. How would that exec plan be usable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 6:28 am
I don't disagree that it's difficult given SQL's architecture, but in this case (and it's not an unusual case) you're better off if plans aren't reused at all.
It was a throw away comment really and I didn't phrase it very well, but it's not impossible for the optimiser to have a special case for search condition=variable or variable is NULL
March 3, 2010 at 6:51 am
HowardW (3/3/2010)
I don't disagree that it's difficult given SQL's architecture, but in this case (and it's not an unusual case) you're better off if plans aren't reused at all.It was a throw away comment really and I didn't phrase it very well, but it's not impossible for the optimiser to have a special case for search condition=variable or variable is NULL
It does on SQL 2008.
If you specify OPTION(RECOMPILE) then the safe plan rule is ignored and optimal exec plans are generated for this form of query. It's not on all the builds of 2008, a bug was found early that caused the wrong plan to be used when two queries of the same form ran at the same time. I can't recall what build the feature was removed in and what, if any, it was put back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 7:30 am
Thanks Gail, good to know. FYI, I tried this scenario in my local 2008 SP1 instance and it doesn't appear to change the plan that's produced compared to 2k5
March 3, 2010 at 8:19 am
Yeah, that's due to the incorrect results bug I mentioned. The functionality was removed quite early, pre SP1. I have demoed this on 2008 RTM and there's a very obvious difference in plans just by adding the recompile hint. Pity about the bug, I honestly don't recall exactly what build it was remove in and when (if) it got fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 8:26 am
GilaMonster (3/3/2010)
Yeah, that's due to the incorrect results bug I mentioned. The functionality was removed quite early, pre SP1. I have demoed this on 2008 RTM and there's a very obvious difference in plans just by adding the recompile hint. Pity about the bug, I honestly don't recall when (if) it got fixed.
The fix was to revert to 2005 behaviour - a real fix is in the 'too hard' basket I think.
All fixed from SP1 Cumulative Update 5.
See http://www.sommarskog.se/dyn-search-2008.html#SPandCUs
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 3, 2010 at 8:54 am
The article you quoted seems to suggest that CU5 includes a 'real' fix:
http://support.microsoft.com/kb/976603/
Microsoft have now released this fix which is a thorough solution to this problem, which resolves the original incorrect results problem, but also allows the use of the enhanced functionality of the OPTION RECOMPILE syntax introduced in SQL Server 2008.
Which sounds like good news to me and it should be in SP2
March 3, 2010 at 9:02 am
HowardW (3/3/2010)
The article you quoted seems to suggest that CU5 includes a 'real' fix:
So it does. Never noticed that before. Good news!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 3, 2010 at 3:40 pm
That's good to know.
March 3, 2010 at 4:00 pm
nguyennd (3/2/2010)
Table
CREATE TABLE [dbo].[A]([K] [int] NOT NULL,
[V] [varchar](50) NOT NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[K] ASC,
[V] ASC
)
SQL
DECLARE @a int = 1 --or NULL,...
SELECT * FROM A WHERE @a is null or A.K = @a
I can't enable index seek in my SQL. How can I enable it?
You have to break it up, then conditionally execute the proper code. Here's an example:
CREATE PROCEDURE dbo.MyProcNoParam AS
select * from A
GO
CREATE PROCEDURE dbo.MyProcWithParam (
@a int)
AS
select * from A where K = @a
GO
CREATE PROCEDURE dbo.MyMainProc (
@a int = NULL)
AS
if @a IS NULL execute dbo.MyProcNoParam
else execute dbo.MyProcWithParam @a
GO
-- finally, run it like this
declare @a int
-- set @a = 1
execute dbo.MyMainProc @a
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 3, 2010 at 6:44 pm
Just to demonstrate the advantage of OPTION (RECOMPILE):
Note: SQL Server 2008 SP1 CU5 (build 10.00.2746) or later required
USE tempdb;
GO
-- Test table
CREATE TABLE [dbo].[A]
(
[K] [int] NOT NULL,
[V] [varchar](50) NOT NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED ([K] ASC, [V] ASC)
)
-- Add 10,000 rows of data
INSERT dbo.A WITH (TABLOCK)
(K, V)
SELECT Numbers.n,
Data.V
FROM (
SELECT TOP (10000)
ROW_NUMBER() OVER (
ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
) Numbers (n)
CROSS
APPLY (
SELECT REPLICATE(
CHAR(Numbers.n % 26 + 65)
, Numbers.n % 50 + 1)
) Data (V);
-- =====
-- TESTS
-- =====
DECLARE @a INTEGER;
SET @a = 1;
-- Index seek
SELECT A.K,
A.V
FROM A
WHERE @a IS NULL
OR A.K = @a
ORDER BY
A.K ASC
OPTION (RECOMPILE);
GO
DECLARE @a INTEGER;
SET @a = NULL;
-- Index scan
SELECT A.K,
A.V
FROM A
WHERE @a IS NULL
OR A.K = @a
ORDER BY
A.K ASC
OPTION (RECOMPILE);
GO
-- Index seek
DECLARE @a INTEGER;
SET @a = 6543;
SELECT A.K,
A.V
FROM A
WHERE @a IS NULL
OR A.K = @a
ORDER BY
A.K ASC
OPTION (RECOMPILE);
GO
DROP TABLE dbo.A;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2010 at 3:31 am
As an alternative for those not using SQL Server 2008 SP1 CU5 or working with SQL Server 2005, you can duplicate the query each with a different condition and UNION ALL the results together, i.e.
SELECT
A.K, A.V
FROM
A
WHERE
@a IS NULL
UNION ALL
SELECT
A.K, A.V
FROM
A
WHERE
ORDER BY
A.K ASC
This results in a query plan with a Clustered Index Scan and a Clustered Index Seek which are merged together. Notice that in SQL Server 2008 these operands have an (actual) Number of Executions property. Depending on the value of @a either one will be 0 or 1. Ok, it's a little bit ugly to duplicate the query and it only works for a single parameter but it works in SQL Server 2005 as well. Although there's only a property Estimated Number of Executions (which are both 1) you can see it works by the Actual Number of Rows property, the line thickness in the execution plan and the number of logical reads.
Peter
March 4, 2010 at 5:05 am
Peter Brinkhaus (3/4/2010)
As an alternative for those not using SQL Server 2008 SP1 CU5 or working with SQL Server 2005, you can duplicate the query each with a different condition and UNION ALL the results together, i.e.
SELECT
A.K, A.V
FROM
A
WHERE
@a IS NULL
UNION ALL
SELECT
A.K, A.V
FROM
A
WHERE
ORDER BY
A.K ASC
This results in a query plan with a Clustered Index Scan and a Clustered Index Seek which are merged together. Notice that in SQL Server 2008 these operands have an (actual) Number of Executions property. Depending on the value of @a either one will be 0 or 1. Ok, it's a little bit ugly to duplicate the query and it only works for a single parameter but it works in SQL Server 2005 as well. Although there's only a property Estimated Number of Executions (which are both 1) you can see it works by the Actual Number of Rows property, the line thickness in the execution plan and the number of logical reads.
Peter
A better evidence that only one branch of the execution plan is executed is the occurrence of a special Filter operand with a Startup Expression Predicate. If the startup expression evaluates to false then the branch is not executed at all. I cannot find anything about it in BOL but it seems to work that way (see http://www.fotia.co.uk/fotia/Blog/AllFiredUp...html).
Peter
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply