I started this series with the post titled: Little Bobby Tables, SQL Injection and EXECUTE AS. I then moved to discussing some of the differences with the post titled: EXEC and sp_executesql – how are they different?

Today, I want to address a few of the comments as well as continue with a few tips and tricks using these commands.

First off – could we have helped the performance of the sp_executesql statement?

Yes…

If we know that a statement returns a varying amount of data (based on the parameters supplied) then we can use the SQL Server 2005 feature WITH RECOMPILE to tell SQL Server that the statement being executed should have it's own plan created and that prior plans (if they exist) should not reuse the statement. It also tells SQL Server that this particular plan is a "single-use plan" that should not be reused for subsequent users. To see the combination of all of these things – I'll use some of the DMVs that track plan cache and plan utilization.

DBCC FREEPROCCACHE
go

SELECT st.text, qs.EXECUTION_COUNT –, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like '%FROM dbo.member%'
go

Right now, this query returns 0 rows.

I'll execute the following and then recheck the plan cache:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Tripp'
go

Now, we have a row for our parameterized query plan:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              1

So, what is this showing us… it's showing us that there's a plan in cache for this statement. And, if we're interested in seeing the plan, we can remove the commented out cp.* in the query above to get the cp.query_plan column. It shows the following:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" …LOTS OF OTHER STUFF THAT I… </ShowPlanXML>  (here's the FULL text of this: query plan xml.sqlplan (11.98 kb))

OK, I know that's not overly helpful… but, you're more than welcome to copy it and save it as a .sqlplan file. Once saved as a .sqlplan file – you can open it directly into SQL Server 2005 or SQL Server 2008's Management Studio. However, the very *nice* feature of SQL Server 2008's Management Studio is that a single click on an XML showplan - will go DIRECTLY into a graphical query plan window:

 

query%20plan%20sp executesql%20with%20tripp Using the OPTION (RECOMPILE) option for a statement

And, once again, we see the optimal plan (to use the index and do a bookmark lookup) because this query is highly selective (only 1 row).

We'll execute the exact same statement again – using the value of Anderson just to get setup to the point where we were last week:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
go

And, we see that it uses the EXACT same plan (looking at showplan). In fact, we can see that from checking our plan cache as well:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              2

And, while we know that this plan (to use the index) is good for the highly selective value of 'Tripp' it's is not good for the value of Anderson as there are many rows that match. If we suspected this and/or knew this when we were executing (from the client) then we could use OPTION (RECOMPILE) to force SQL Server to get a new plan:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
go

The query plan as a result of this execution is:

query%20plan%20sp executesql%20with%20andrerson%20option%20(recompile) Using the OPTION (RECOMPILE) option for a statement

And, this is the more optimal plan given this parameter. At ths point, the questions (IMO) are:

  1. Would someone really be able to programmatically guestimate that a parameter submitted from the client is "atypical" and/or that it warrants recompilation? And, I guess I can answer yes – for some parameters – like those with a wildcard. But, if we're just talking about two different values against a single column, this would be guessing statistics of the data.
  2. Should this particular statement always be executed to recompile and never save a plan?
  3. What did SQL Server do with the plan itself?

I'll go with answering #3 first as that one is easy to answer. Using the same statement, I'll again query the plan cache:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              2

Even though this is the third time we have executed this statement, this final execution was NOT put in cache. It was used solely for the execution with OPTION (RECOMPILE). And, it will NOT affect future executions. If I go back and execute without the OPTION (RECOMPILE) then I will get the prior plan (to use the index).

Now, the other two questions – these are a lot more interesting and this is where I think that stored procedures should be used. Personally, I think that developers that know the data and know the application – will be a lot better at creating the RIGHT code especially when they understand all of the options available to them.

Here's the way I think about stored procedures and recompilation:

  • If I know that a particular statement always returns the same number of rows and uses the same plan (and, I'd know this from testing), then I'll create the stored procedure normally and let the plan get cached.
  • If I know that a particular statement wildly varies from execution to execution and the optimal plan varies (again, I should know this from testing multiple sample executions), then I'll create the stored procedure normally and I'll use OPTION (RECOMPILE) to make sure that the statement's plan is not cached or saved with the stored procedure. On each execution that stored procedure will get different parameters and the particularly nasty statement will get a new plan on each execution.

However, this is also where things get more challenging. I've often seen stored procedures where people try and use conditional logic to break up the different types of parameters and this doesn't usually work out as well as expected (I'll blog this next). And, this is always where some decide that they want to dynamically build the statement that gets executed – now, we need to determine whether or not we should use sp_executesql or EXEC. And, there are really a couple of options at this point. Ultimately, in one or two more posts – I'll finally show you where EXEC is a clear winner over sp_executesql because even the OPTION (RECOMPILE) doesn't always help ALL kinds of plans (and especially one of the more common types of plans I see).

So, I'm getting closer… at least two more to go here. Thanks for the great questions/comments!
kt