SQL Server Performance Forum – Threads Archive
SP Date parameter issue
Hi, I am having problems getting consistent query plans for some of my Stored Procedures when using a datetime parameter. We are using SQL Server 2000 standard edition. The stored procedures that exhibit these issues are complex so rather than cutting hundreds of lines of T-SQL I’ll start by explaining the issue we have. The stored procedures in question have a datetime parameter and go on to use the parameter in the "where" clause, often in more than one block of T-SQL.We have found that the query plan in some of these cases changes, the new plan looks incorrect and the proc takes longer to run (10+ times slower) than normal. This can be corrected by running sp_updatestats prior ro running the proc however we already run this in our overnight optimisation jobs. The stats should also not get too out of sink as the db grows by about 1% per day. The workaround that we have for this is to write the parameter into a local variable and then use this in the "where" clause, e.g. CREATE PROCEDURE test_proc
(
@COBDate DATETIME
)
AS
BEGIN
DECLARE @COBDateX DATETIME SET @COBDateX = @COBDate
….. Could anyone offer advice on why this may be happening or other ways to solve the problem? Thanks,
Martin.
There have been comments that when you add variables to the procedures, and copy the parameter values into the variables, and only refer to the variables within the procedure, then you can get better performance.
Hi, do you know why this is? if this is the case, is it best practice to always copy parameters into local variables?
It is known as "parameter sniffing" – here’s an explanation:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611
Excellent, thanks very much, there’s also dozens of good links from Google on Parameter Sniffing, cheers, Martin.
e.g.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
Yes, the blog of Ken Henderson is definitely a must-read. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Well, I must add that in most cases using dateTime sp parameter is actually better then using local variable. The reason is that dateTime parameter value is known before execution plan is compiled so statistics can be used against known parameter values. Parameter sniffing is IMO less frequent problem and in most cases it causes problems when parameter value passed is changed inside sp before applying it to actual query. Typical scenario is when null is defined as parameter default value and in case value passed is really null it is change to an arbitrary ‘special’ value. In your case it looks like query optimizer tends to make wrong decision when statistics are not up-to-date. My guess is that allways the same execution plan is optimal. This is one query optimizer consider optimal when dateTime value is unknown. That type of problem can also be solved fixing execution plan using hints.
]]>