6.0 Best Programming Practices



 


In this section we will outline some programming practices for efficient plan cache usage:


 


6.1 Client Side Parameterization of Queries


 


If your application has repeated execution of the same query with only parameter values changing from query to another, then parameterizing the query in the client application code before execution gives some significant performance gains. The performance gains come from the fact that the query is compiled just once versus once for every query issued for execution. Prepared execution also provides advantage from the fact that it helps reduce network traffic by eliminating the need to send the sql query text to the server each time. In cases where the statement text is fairly large the cost of transmitting this text from client to server repeatedly can quickly add up. In the case of prepared execution however all that needs to be sent over the network are parameter values, and the prepared handle (there is a list of prepared handles in a session). The prepared handle has the hash value associated with the query text which needs to be computed only once (versus once per query execution). Also since the server does not do any parameterization, there is no chance for plan cache bloating due to caching the shell queries.


 


Client side parameterization if applied incorrectly can cause more grief than gain. Since the server cannot parameterize the queries, and the advantage of simple or forced parameterization is lost. We will try to illustrate some of the common mistakes made, and also recommend some best practices. Consider the example below where the client code relies entirely on server side parameterization:


 


command.CommandText = “select * from t1 where col1 = 1 and col2 = ‘abcd'”;


command.ExecuteNonQuery();


command.CommandText = “select * from t1 where col1 = 1 and col2 = ‘abc'”;


command.ExecuteNonQuery();


 


This results in one parameterized plan:


 


(@1 tinyint,@2 varchar(8000))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2


 


Notice that in the absence of any client side parameterization, the server side forced bucketization applies. All queries are bucketized to varchar(8000) or nvarchar(4000). If the parameter is greater than varchar(8000) then nvarchar(max) is used. While this may seem acceptable, if the parameter data is always limited to say 50 characters (limited by the column data type and length) then it is not an optimal solution. Now consider the example below where the query has been parameterized incorrectly:


 


command.CommandText = “select * from t1 where col1 = @id and col2 = @str”;


command.Parameters.Add(“@id”, 1);


command.Parameters.Add(“@str”, “abc”);


command.ExecuteNonQuery();


 


command.Parameters[0].Value = 2;


command.Parameters[1].Value = “abcd”;


command.ExecuteNonQuery();


 


This results in two parameterized queries:


 


(@1 tinyint,@2 varchar(3))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2


 


(@1 tinyint,@2 varchar(4))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2


 


Since the parameter type and lengths are not specified, different parameter values lengths can potentially generate different parameterized plans. This can cause a significant performance degradation in SQL Server 2005 RTM and SP1 since all these parameterized queries will hash into the same hash bucket. The reason they all hash into the same bucket is because they have the exact same parameterized query text but for the declarations, and the hash value is only computed in SQL Server 2005 and RTM based on the parameterized query text not including the declarations.


 


For legacy applications where change to client side code is not an option, use TF 144 to force server side bucketization. When the application has queries that are improperly parameterized, this trace flag forces server side bucketization. This will result in only one parameterized plan:


 


(@1 tinyint,@2 varchar(8000))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2


 


Note however this a server wide option and will apply to all parameterized queries executed. In some cases this may not be suitable, and hence we advise use of this trace flag with caution.


 


The recommended method to parameterize queries in client application code has been illustrated below:


 


SqlConnection connection = new SqlConnection (“context connection = true”);


connection.Open();


SqlCommand command = connection.CreateCommand(); 


 


command.CommandText = “select * from t1 where col1 = @id and col2 = @str”;


command.Parameters.Add(“@id”, SqlDbType.Int);


command.Parameters.Add(“@str”, SqlDbType.VarChar, 50);


 


command.Parameters[0].Value = 1;


command.Parameters[1].Value = “abc”;


command.ExecuteNonQuery();


 


command.Parameters[0].Value = 2;


command.Parameters[1].Value = “abcd”;


command.ExecuteNonQuery();


 


This results in only one prepared compiled plan for both executions for the select query:


 


(@id int,@str varchar(50))select * from t1 where col1 = @id and col2 = @str


 


Some key points to note here include that we have parameterized the query and carefully specified the data type and the max length for the parameters (we specify the max length of the varchar parameter the same as the table column data type). Note that the query optimizer will try to ‘sniff’ parameter values and choose an optimal plan appropriately. While in most cases this works to our advantage, in some cases where the first parameter values are atypical it can actually cause a suboptimal plan to be chosen.


 


We can still do one better than the example above by deferring query preparation to execution time:


 


command.CommandText = “select * from t1 where col1 = @id and col2 = @str”;


command.Parameters.Add(“@id”, SqlDbType.Int);


command.Parameters.Add(“@str”, SqlDbType.VarChar, 50);


 


command.Parameters[0].Value = 1;


command.Parameters[1].Value = “abc”;


command.Prepare();


command.ExecuteNonQuery();


 


command.Parameters[0].Value = 2;


command.Parameters[1].Value = “abcd”;


command.ExecuteNonQuery();


 


Here query preparation is deferred to execution time and we gain all the advantages of parameterization including the fact that we don’t have to send the sql query text over the network everytime. For ODBC the SQL_SOPT_SS_DEFER_PREPARE attribute (for OLEBD it is SSPROP_DEFERPREPARE) determines whether the statement is prepared immediately or deferred until execution. BOL has more information.


 


It is important to note that when specifying query parameters all statements in the batch should be parameterized. Consider the example where we will use sp_executesql and parameterize the queries:


 


declare @param_value int, @sqlstring nvarchar(500),


@param_definition nvarchar(500), @col2 int;


set @param_value = 5;


 


set @sqlstring = N‘select @col2_out = col2 from t1 where col1 = @param; update t1 set col1 = col1 + 1 where col2 = 6’;


set @param_definition = N‘@param int, @col2_out int output’;


exec sp_executesql @sqlstring, @param_definition, @param = @param_value, @col2_out = @col2 output;


 


set @sqlstring = N‘select @col2_out = col2 from t1 where col1 = @param; update t1 set col1 = col1 + 1 where col2 = 7’;


exec sp_executesql @sqlstring, @param_definition, @param = @param_value, @col2_out = @col2 output;


go


 


The select statement has been parameterized, while the update statement has not been parameterized making each batch unique. When these 2 batches are executed, there is a compiled plan for each batch in the cache.


 


(@param int, @col2_out int output)select @col2_out = col2 from t1 where col1 = @param; update t1 set col1 = col1 + 1 where col2 = 7


 


(@param int, @col2_out int output)select @col2_out = col2 from t1 where col1 = @param; update t1 set col1 = col1 + 1 where col2 = 6


 


With no reuse of the prepared compiled plan, every batch that comes in is compiled and a fresh plan is inserted into the cache. This will lead to performance degradation due to large number of compiles and plan cache bloating. Therefore it is important to make sure all statements in the batch are parameterized as follows:


 


declare @param_value int, @sqlstring nvarchar(500),


@param_definition nvarchar(500), @col2 int;


 


set @sqlstring = N‘select @col2_out = col2 from t1 where col1 = @param1; update t1 set col1 = col1 + 1 where col2 = @param2’;


set @param_definition = N‘@param1 int, @param2 int, @col2_out int output’;


 


set @param_value = 5;


exec sp_executesql @sqlstring, @param_definition, @param1 = @param_value, @param2 = @param_value, @col2_out = @col2 output;


 


set @param_value = 6;


exec sp_executesql @sqlstring, @param_definition, @param1 = @param_value, @param2 = @param_value, @col2_out = @col2 output;


go


 


The compiled plan generated for both executions as:


 


(@param1 int, @param2 int, @col2_out int output)select @col2_out = col2 from t1 where col1 = @param1; update t1 set col1 = col1 + 1 where col2 = @param2


 


This is the recommended approach to parameterizing batches with more than one query since it benefits from plan re-use and avoids plan cache bloating problems.


 


A word of caution on potential over use of prepared queries: If a query is going to be executed only once, then it is probably more efficient to directly execute the query on the server. This avoids the extra network round trip to prepare the query.


 


6.2 Use Fully Qualified Names


 


It is advisable to use fully qualified names in queries. There are some significant performance gains in doing so because it avoids the need to look up the default schema for the current user. It also provides us with a plan that can be executed by multiple users (irrespective of the default schemas).  Let us demonstrate with an example: table t1 is part of user u1’s default schema s1. Executing a select query as user u1, without a fully qualified object name, results in the cache key user_id to be equal to the schema id to which this table belongs.


 


select * from t1


go


 


Use the query below to view the cache key attributes for the compiled plan for the query above:


 


select st.text, cp.plan_handle, cp.cacheobjtype, cp.objtype, pa.attribute, pa.value, pa.is_cache_key


from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(plan_handle) st


outer apply sys.dm_exec_plan_attributes(cp.plan_handle) pa


where cp.cacheobjtype = ‘Compiled Plan’


and st.text not like ‘%select st.text%’


and pa.is_cache_key = 1


order by pa.attribute;


go


 




















Text


Plan_


handle


Cache


objtype


Obj


type


Attribute


Value


Is_


cache_


key


select * from t1


 


0x0600010


0F299431E


B8614C040


000000000


000000000


00000


Compiled Plan


Adhoc


user_id


7


1


 


However executing a select query with a fully qualified table name results in a plan who’s cache key user_id is -2 indicating that this plan can be shared across multiple users (irrespective of what their default schema is).


 


select * from s1.t1


go


 


Use the query above to view the user_id cache key attribute for the compiled plan:


 




















Text


Plan_


handle


Cache


objtype


Obj


type


Attribute


Value


Is_


cache_


key


select * from


master.


u1.t1


0x060001


00609A91


2EB86175


04000000


00000000


0000000000


Compiled Plan


Adhoc


user_id


-2


1


 


6.3 Use RPC Events over Language Events


 


RPC execution gives better performance than using language events. For example consider stored procedure executions with different parameters passed to the stored procedures on each execution. If we executed adhoc queries as below:


 


exec master.dbo.test_proc 1, 1000;


.


.


.


exec master.dbo.test_proc 99, 99000;


exec master.dbo.test_proc 100, 100000;


go


 


Then depending on the number of stored procedure executions we may cache the batch. Now if subsequent executions of the stored procedure have different parameter values, then this means we will land up caching all of these unique batches that have small probability of re-execution. This can cause the cache to bloat quickly and come under memory pressure. This situation can be entirely avoided by replacing these adhoc queries with RPC executions of the stored procedures as below:


 


{call master.dbo.test_proc (1, 1000)}


.


.


.


{call master.dbo.test_proc (99, 99000)}


{call master.dbo.test_proc (100, 100000)}


 


Now we have eliminated the problem of additional caching of the adhoc batches and have also got performance gains from using RPC.


 


6.4 Choose Suitable Object Names


 


It is also important to name tables, functions and procedures suitably. Procedures prefixed with “sp_”, functions names starting with “Fn_”, tables prefixed with “sys” might result in resource database look ups in the absence of fully qualified names in queries using these objects.


 


6.5 Exact Match of High Re-use Adhoc Query Text


 


For adhoc queries, the query text needs to be identical. Small differences like extra white space characters or case difference of the query text will result in different plans, and no re-use of cached plans. Comments are also considered part of the query text. Therefore the queries below result in 2 different cached plans:


 


—this is an example of an adhoc query


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


go


 


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


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


go


 


Use the query below to view the sql_handle and plan_handles for both plans and notice that the sql_handles are different:


 


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%’


go


 
















Text


Sql_handle


Plan_handle


—this is an example of an adhoc query with different comment  if exists(select col1 from t1 where col2 > 5) declare @x int;             


0x0200000


0EC59E433


8B492CC1D


F65C99E5D


352A4E641


06FA0


0x060001


00EC59E43


3B8417204


000000000


000000000


000000


—this is an example of an adhoc query  if exists(select col1 from t1 where col2 > 5) declare @x int; 


0x0200000


04C1C833A


B811F38FB


C18913249


C73C4DFD2


A74DC


0x0600010


04C1C833A


B80167040


000000000


000000000


00000


 


As demonstrated previously different drivers have different default settings for set options. Difference in set options will not result in plan re-use for identical queries. For example, OSQL sets quoted_identifier on while SQL Server Management Studio sets quoted_identifier off. Identical queries issued from these different clients will result in two different plans.

Comments (6)

  1. D@TPBS says:

    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.

  2. sangeethashekar says:

    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?

  3. D@TPBS says:

    @@version is:

    Microsoft SQL Server 2005 – 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    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;

    go

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

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

    go

    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%’

    go

    …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;

    go

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

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

    go

    —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;

    go

    — 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

    go

    —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

    go

    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%’

    go

    …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%’   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

       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.

  4. sangeethashekar says:

    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;

    go

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

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

    go

    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

    go

    —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

    go

    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%’

    go

    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.

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

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