July 6, 2009 at 4:56 am
Hi there,
I was reading one of the SQL Server Internals books at the weekend (Query Tuning & Optimisation) and read about how SET commands can render a query un-cacheable, or at least that each time a query using SET is called it can/will create a new plan.
My questions are the following:
1. Did I understand it correctly, and a new plan will be created each time such a query is sent to the SQL server?
2. Am I right in thinking that this rule is for SET operations to do with the connection and not for local variables? e.g SET ANSI_NULLS ON
Example:
-- Code with SET (variable) inside the procedure
CREATE PROCEDURE Test AS
DECLARE @CustomerId INT
SET @CustomerId = 1234
SELECT Firstname ,
lastname
FROM dbo.Customer
WHERE CustomerId = @CustomerId
-- Code with SET outside the procedure
CREATE PROCEDURE Test2 @CustomerId INT AS
SELECT Firstname ,
lastname
FROM dbo.Customer
WHERE CustomerId = @CustomerId ;
DECLARE @CustomerId INT
SET @CustomerId = 1234
EXECUTE Test2 @CustomerId
The SP "Test2" is better in that it is reusable and is how i would expect this sort of thing to be done. However, where I work there are alot of SPs that have similar SET operations as seen in "Test".
Do these SET operations (variables) really have a detrimental effect, or does the warning in the SQL Internals book refer to SET operations at the Statement level?
Many thanks!
William
Regards,
WilliamD
July 7, 2009 at 9:27 pm
It doesn't stop the proc from being cached exactly, but it makes it inefficient. When a proc is first compiled (on first use) the parameters are known and can be used to determine the best plan. If variables are defined and then used in the proc, they are not known at compile time and the optimizer has to make educated guesses as to the best plan.
You should search for articles on parameter sniffing to give you some different looks at the topic, but the book is pretty exhaustive and you might understand better the next time through.
July 7, 2009 at 10:39 pm
Matt is correct about parameter sniffing but i dont think fully answers your question.
As you point out there are 'Two' set statements.
SET @Var = something is very different from SET ANSI_NULLS ON.
You are fine setting internal variables that wont cause an un-cacheable query / recompile.
SET options will cause problems however, as for each execution SQLServer will have to create a new query plan.
Best practice is to set the options at the connection level outside the batch.
Heres Microsofts description of the issues http://technet.microsoft.com/en-gb/library/cc966425.aspx. Please note the 'Factors that affect plan-reuse' and the discussion of set options within 'Causes of Recompilations'.
July 8, 2009 at 12:57 am
Thanks Matt and Dave.
I thought it was something like parameter sniffing for SET @variablename=value, but couldn't remember the name!
In this case a wrapper call to a procedure would be better, right?
Regards,
WilliamD
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply