Answers to some SQL Server questions
Posted by decipherinfosys on September 26, 2008
A friend recently asked some questions that we thought we would post over here and provide answers for those so that in case there are similar questions that you have, you can also benefit from those:
Q1) If you pass a parameter in a procedure, it will execute in a few seconds. If you pass a different set of parameters, it would take a longer time. Why and how this can be fixed.
A1) We have covered this before in our blog posts – this is called parameter sniffing in SQL Server and bind variable peeking in Oracle. There are multiple ways to fix it – plan guides, hints, usage of a constant instead of a parameter, using the optimize for clause. You can read more here:
Q2) Why there are stored procedure recompilations ? When the query/stored procedure will recompile and generate a new plan?
A2) There are a lot of reasons for re-compilation. We have covered some of those in our posts before – too many to list here. But if you see this post, it also has the link to MSFT whitepaper which talks about all the different scenarios and resolutions:
Both the questions from above are answered in there. Also, remember that in SQL Server 2000, the unit of compilation was the whole stored procedure. One could not choose to recompile just one single query within the stored procedure. If one used the RECOMPILE option, the entire procedure got recompiled. SQL Server 2005 supports the statement level recompile – which is good because the other queries in the stored procedure can still use the cached execution plans rather than recompiling them again when there is no need to (and only a single query needs to be recompiled).
Q3) When we create a procedure what kind of query plan it generates?
A3) None. The plan does not get generated when we create the stored procedure. It gets generated when we execute it for the first time. Here is a test: Let’s create a stored procedure called dbo.usp_test. Prior to the run, let’s check in sys.syscacheobjects (there are other DMVs and DMFs as well that we can check but this will suffice for this post) … when we check in sys.syscacheobjects, of course there is nothing in there for usp_test since the object does not even exist right now. Let’s create it now:
create proc dbo.usp_test as select * from dbo.user_master
And now if we check the data again, we will still not see anything in there since we have just created the procedure. Now, let’s go ahead and execute the procedure and then check the data again. And this time, we will see a compiled plan with the objType as Proc. We can even check this using the query on the DMVs/DMFs:
DB_NAME(deqp.dbid) as Database_Name,
OBJECT_NAME(deqp.objectid, deqp.dbid) as OBJNAME,
deqs.statement_start_offset / 2
) + 1,
WHEN -1 THEN DATALENGTH(dest.text)
END – deqs.statement_start_offset
) / 2
) + 1
) as Query_Text,
dest.text as Statement_Text,
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan (deqs.plan_handle) deqp
WHERE OBJECT_NAME(deqp.objectid, deqp.dbid) = ‘usp_test’
And this will show the data which will show the execution count, the query text, the query plan etc..