![]() ![]() 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.The query plan as a result of this execution is:Īnd, this is the more optimal plan given this parameter. SELECT = ‘SELECT * FROM dbo.member WHERE lastname LIKE OPTION (RECOMPILE)’ĮXEC sp_executesql varchar(15)’, ‘Anderson’ 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: Text varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE 2Īnd, 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. In fact, we can see that from checking our plan cache as well: SELECT = ‘SELECT * FROM dbo.member WHERE lastname LIKE sp_executesql varchar(15)’, ‘Anderson’Īnd, we see that it uses the EXACT same plan (looking at showplan). 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: Click on an XML showplan and SSMS will go DIRECTLY into a graphical query plan window (2008 onward):Īnd, 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). 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. So, what is this showing us… it’s showing us that there’s a plan in cache for this statement. Text varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE 1 Now, we have a row for our parameterized query plan: SELECT = ‘SELECT * FROM dbo.member WHERE lastname LIKE sp_executesql varchar(15)’, ‘Tripp’ I’ll execute the following and then recheck the plan cache: ![]() SELECT st.text, qs.EXECUTION_COUNT –, qs.*, cp.*ĬROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stĬROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp To see the combination of all of these things – I’ll use some of the DMVs that track plan cache and plan utilization. It also tells SQL Server that this particular plan is a “single-use plan” that should not be reused for subsequent users. 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. Today, I want to address a few of the comments as well as continue with a few tips and tricks using these commands.įirst off – could we have helped the performance of the sp_executesql statement? I then moved to discussing some of the differences with the post titled: EXEC and sp_executesql – how are they different? I started this series with the post titled: Little Bobby Tables, SQL Injection and EXECUTE AS. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |