6.0 Best Programming Practices

re: 6.0 Best Programming Practices

D@TPBS — Fri, 19 Jan 2007 14:46:13 GMT

I tested the example in "6.5 Exact Match of High Re-use Adhoc Query Text" and found that changing the comment had no effect. Only one plan was cached with whatever the first comment was. Inserting extra white space did however create a new plan. Presumably each comment is treated as a single 'white space' token so the text of the comment is ignored, but adding a 2nd comment would count as changing white space.

re: 6.0 Best Programming Practices

sangeethashekar — Fri, 19 Jan 2007 22:27:29 GMT

D@TPBS,

Your comment is incorrect. Changing the comment text will amount to a different sql handle. The sql handle is an MD5 hash of the entire batch including comments. If use the query pasted below you will be able to see 2 entries in the cache with different sql handles. This is the behavior on SQL Server 2005 and 2000 also.

What version of SQL Server do you have installed?

re: 6.0 Best Programming Practices

D@TPBS — Mon, 29 Jan 2007 19:28:16 GMT

@@version is:

I executed this:

use scratch

---this is an example of an adhoc query

if exists(select col1 from t1 where col2 > 5) declare @x int;

---this is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

select text, sql_handle, plan_handle

from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle)

where text like '%---this%'

...And get this...

text sql_handle plan_handle

select text, sql_handle, plan_handle from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) where text like '%---this%' 0x02000000247B772F7B42AAA94F7A0A7220E383AA855AEDAC 0x06000F00247B772FB841920A000000000000000000000000

use scratch ---this is an example of an adhoc query if exists(select col1 from t1 where col2 > 5) declare @x int; 0x0200000040BD6E030F37CC9BF7E531A29F4C7379BB6ABCF7 0x06000D0040BD6E03B8417E0A000000000000000000000000

...Going further, I tried...

use scratch

---this is an example of an adhoc query

if exists(select col1 from t1 where col2 > 5) declare @x int;

---this is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

---this really hacks the comment

-- and is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

-- OK

---this is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

-- more hacking

---this is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

--xxx

--bb

select text, sql_handle, plan_handle

from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle)

where text like '%---this%'

...Which gives...

text sql_handle plan_handle

select text, sql_handle, plan_handle from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) where text like '%---this%' 0x02000000102821342B23781363F8FC7784A4D2EA8E6F2537 0x06000F0010282134B861820A000000000000000000000000

...It seems that it's not as simple you describe. Changing the comment may produce a new hash and hence a new plan but I don't think the rules are simple.

re: 6.0 Best Programming Practices

sangeethashekar — Tue, 30 Jan 2007 00:05:00 GMT

D@TPBS, I am pasting the queries below from your example above:

use scratch

---this is an example of an adhoc query

if exists(select col1 from t1 where col2 > 5) declare @x int;

---this is an example of an adhoc query with different comment

if exists(select col1 from t1 where col2 > 5) declare @x int;

The first thing to note here is that these 2 batches are NOT identical. The first batch has an extra statement 'use scratch', and this is what will cause the sql handles of the 2 batches above to be different.

I see that you are working with SQL Server 2005 SP1. In SP1, queries with one or more context switches had a non zero cost. This is not the case in SQL Server 2005 Sp2. This might explain why you dont see an entry in the cache for the 2nd query with a different comment.

Even in SQL Server 2005 Sp1, if you look at the sys.dm_exec_cached_plans DMV, you will see that the cached plan for the 1st query (in your example above) has a usecount of 1 and not 2. This implies that the plan was not re-used for the 2nd query with a different comment.

I would recommend you try the example below here on SQL Server 2005 SP1:

---this is an example of an adhoc query

select t1.col1 from t1 join t2 on t1.col1 = t2.col1 where t2.col2 > 5

---this is an example of an adhoc query longer comments

select t1.col1 from t1 join t2 on t1.col1 = t2.col1 where t2.col2 > 5

select text, sql_handle, plan_handle

from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle)

where text not like '%select text%'

You will see 2 entries with different sql and plan handles

The example as in the blog article would work on SQL Server 2005 SP2.

The blog article: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx has more information on changes in SP2 and the exact cost formula.

OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature

Microsoft SQL ISV Program Management Team — Sat, 20 Dec 2008 03:27:28 GMT

Using parameterized queries is a well known SQL Server Best Practice. This technique ensures caching

OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature

SQL Programmability & API Development Team Blog — Sat, 20 Dec 2008 03:27:33 GMT

Using parameterized queries is a well known SQL Server Best Practice. This technique ensures caching