November 15, 2005 at 10:27 am
First off I can't post the actual code - but I have a proc which does a simple select like below:-
SELECT
ISNULL(dbo.Table_One.col1,0),
dbo.Table_One.col2,
dbo.Table_One.col3 ,
dbo.Table_One.col4 ,
dbo.Table_One.col5
FROM
dbo.Table_Two with (NOLOCK) LEFT OUTER JOIN
dbo.Table_Three with (NOLOCK) ON dbo.Table_Two.CID = dbo.Table_Three.ID INNER JOIN
dbo.Table_One with (NOLOCK) ON dbo.Table_Two.SID = dbo.Table_One.ID
WHERE
dbo.Table_Three.MNUM = @intP1 and dbo.Table_Three.ANO = @intP2
The query will return 1 row, all join columns are indexed and where columns are indexed. where the col name is ID this si the clustered index.
The problem I have is that the proc table scans, does not use the secondary indexes and uses parallel threads .. however - if I recompile the proc from EM and comment out the lines
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
between the proc drop and proc create I get a correct index seek plan.
I can recreate this every time.
Ideas much appreciated!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 15, 2005 at 11:07 am
Shooting a bit in the dark: could it be that with ANSI_NULLS ON it will have to join less data (since = NULL always gives false) and somehow the optimizer finds this out?
November 15, 2005 at 2:17 pm
The query itelf doesn't make sense.
If you LEFT JOIN to a table, then apply a WHERE clause to that table, you effectively convert it to an INNER join.
Also, you are only returning columns from Table_One, so why not just select from Table_One, and add an EXISTS or IN sub-query to limit the results based on data in the other tables ?
November 15, 2005 at 3:00 pm
Are @intP1 and @intP2 parameters to the stored procedure ?
Are the values changed within the sp ?
If the answer to either question is true, that might be the cause.
For an detailed explanation, search for "parameter sniffing" but here a short one:
When a stored procedure is compiled for the first time, the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure. This process is known as "parameter sniffing." If these values are typical, then most calls to that stored procedure will benefit from an efficient query plan. The are many reasons why a sub-optimal query plan could be generated, but include:
1.If the parameters passed have no value (null) and are then defaulted within the stored procedure logic.
2.If the values of the parameters are changed within the stored procedure logic.
3.If the values of the parameters are used to derive variables that are then used in a SQL statement.
SQL = Scarcely Qualifies as a Language
November 16, 2005 at 4:56 am
I'm sorry I can't actually post the actual code - the query is the correct query ( with names changed ) where the two variables are the parameters passed to the proc. I didn't write the code, it is part of our production system and returns 1 row of data. If you see the real tables etc. the query makes sense.
It seems that when the procedure was created the ansi nulls were set to off in the sql script, it appears that procs hang on to certain set options outside of the procs ( there's a reference from Kalen Delaney about this ) my question is about the somewhat strange effect of a set command outside the procedure .. The query is fine - 12 i/o for the proper execution using index seeks vs 30k i/o and table scans when the ansi nulls are set to off on proc creation e.g.
if exists drop proc
go
set ansi null off
go
create proc
go
produces a different proc behaviour to
if exists drop proc
go
set ansi null on
go
create proc
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply