Understanding the mysteries of performance-slow in applications, SSMs fast (6) How--sql server compiles dynamic SQL

This article belongs to theUnderstand the mysteries of performance-slow in applications, fast in SSMS "series

Next: Understanding the Mysteries of performance-application slow, SSMs fast (5)--case: How to deal with parameter sniffing

We put aside the argument sniffing topic and went back to the original point of concern in this series: Why is the statement slow in the application, but fast in ssms? So far, it's all about the stored procedure. The problem with stored procedures is usually due to the different set-arithabort of sets. If your app does not use stored procedures, but instead submits client queries through the middle tier, there are several reasons why your query might have different cache entries to run in SSMs and the application.

What is dynamic SQL?
Dynamic SQL is any SQL code that is not part of a stored procedure (or other type of module). Contains:

• SQL statements executed using EXEC () and sp_executesql.
• The SQL statement that is sent directly from the client to the server.
• The SQL statement that was submitted in SQLCLR.

Dynamic SQL comes in two ways: non-parametric and parameterized. In non-parameterized SQL, the developer stitching the parameters into the SQL string. Like what:

Select @sql = ' Select MyCol from tbl where keycol = ' + convert (varchar, @value) EXEC (@sql)

or in C #:

Cmd.commandtext = "Select MyCol from tbl WHERE keycol =" + value. ToString ();

non-parametricSQL is very inefficient in some scenarios. can refer to my article: T-SQL Dynamic Query (1)--Introduction,T-SQL Dynamic Query (2)--keyword query,T-SQL Dynamic Query (3)--Static SQL,T-SQL Dynamic Query (4)--Dynamic SQLIn parameterized SQL, you can pass in parameters like stored procedures, such as:

EXEC sp_executesql n ' SELECT mycol from dbo.tbl WHERE keycol = @value ',                   n ' @value int ', @value = @value

or in C #:

Cmd.commandtext = "Select MyCol from dbo.tbl WHERE keycol = @value"; cmd. Parameters.Add ("@value", SqlDbType.Int); cmd. parameters["@value"]. Value = value;

Scheduled generation of dynamic SQL:SQL Server compiles dynamic SQL in a similar way as stored procedures. That is, if the batch statement contains multiple queries, the whole batch is not compiled as a whole, and SQL Server is not aware of the local variable values in the batch. In stored procedures, SQL Server sniffs the parameter values and then uses these values to generate the query plan.In contrast, for dynamic SQL with only one statement, you can use parameterization to implement similar functionality, and SQL Server replaces the arguments in the statement, such as:

SELECT * from Orders WHERE OrderID = 11000

If you submit the following method, then compile it to get the above statement:

EXEC sp_executesql n ' SELECT * from Orders WHERE OrderID = @p1 ', n ' @p1 int ', @p1 = 11000

There are also two modes of parameterization: simple and mandatory. For simple parameterization, SQL Server simply parameterized a fairly small portion of a simple query. For forced parameterization, SQL Server replaces all parameters with constants, except where this article mentions: Forced parameterization (Microsoft Books Online) The default mode is simple parameterization, and you can modify each library using the ALTER DATABASE command.Forcing parameterization can save a lot of performance for applications that do not use parameterization, but there are still a few cases that are valid in even the best-coded applications.
Dynamic SQL and Plan caching:The query plan for dynamic SQL is similar to a stored procedure and is put into the plan cache. As with stored procedures, the scheduled cache for dynamic SQL is flushed out for some reason, and standalone statements in dynamic SQL may be recompiled. Also, there may be multiple schedules for the same query text because of different set configurations.However, compared to stored procedures, there are two special cases where dynamic SQL is available and stored procedures do not:
Query text as a hash key:When SQL Server looks up a query plan for a stored procedure from the cache, it uses the stored procedure name. However, for dynamic SQL, because there is no name, SQL Server calculates the query text as a hash value (including the parameter list) and then finds it in the cache as a hash value. However, the comments are filtered out, but the spaces are not truncated or shrunk. WhileCase Sensitive, even if the database is case-sensitive. Hash values are computed by text, and all the slightest text is different (as many as a single space), resulting in different cache entries.Open the actual execution plan and run the following statement:

Use northwindgoexec sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 2000010 1 ' EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 19980101 ' EXEC sp_exe Cutesql n ' select * from Orders where OrderDate > @orderdate ', n ' @orderdate datetime ', ' 19980101 '

Execution plan For example, you will find that the first two execution statements use the same query plan:

The third query uses a clustered index scan. That is, the second call reuses the query plan for the first call, but the third call, the SQL keyword is lowercase and the plan cache does not exist, so a new query plan is created. If you want to enforce this policy, you can use the following statement:

Use NORTHWINDGODBCC freeproccachegoexec sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdat e datetime ', ' 20000101 ' EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 19980101 ' EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 19980101 '

This time, three query plans are the same.
The importance of the default schema:
The other one is not noticeable in the stored procedure, you can open the actual execution plan and run the following statement:

Use NORTHWINDGODBCC freeproccachegocreate SCHEMA schema2gocreate USER User1 without loginwith Default_schema = DboCREATE U  SER User2 without Loginwith default_schema = schema2grant Selecton ordersto user1,user2grant showplanto User1,User2GOEXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 20000101 ' Goexecute as USER = ' User1 ' EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', ' 19980101 ' REVE Rtgoexecute as USER = ' User2 ' EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ', n ' @orderdate datet IME ', ' 19980101 ' revertgodrop user user1drop user User2drop SCHEMA Schema2go

The execution plan, as shown earlier, is the same as the first two execution plans but the third one is different. This script creates two database users and authorizes them to run queries. The statement was then executed three times. The first time is the default schema (that is, dbo) and then runs with two new accounts.When SQL Server looks for an object, it finds its default schema, and if it does not, it looks from the dbo schema. For dbo and User1, the query is not confusing, because dbo is the default schema for the Orders table. But it's different for User2. Because only the dbo. Orders table, what happens if you add schema2.orders later? By rule, User2 gets the data on the Schema2.orders table instead of the dboorders data. But if User2 also uses the cache entries produced by User1 and dbo, the situation is different. Therefore, User2 needs its own cache entry. If Schema2.orders is added, the cache entry is independent and does not affect other users. You can use a script similar to those previously used to view:

SELECT Qs.plan_handle, A.attrlistfrom   sys.dm_exec_query_stats qscross  APPLY sys.dm_exec_sql_text (qs.sql_ HANDLE) Estcross  APPLY (SELECT epa.attribute + ' = ' + convert (nvarchar (127), epa.value) + '   from   sys.dm _exec_plan_attributes (qs.plan_handle) EPA              WHERE  epa.is_cache_key = 1              ORDER by  Epa.attribute              For    XML PATH (")) as a (attrlist) WHERE  est.text like '%where OrderDate > @orderdate% ' and  Est.text not like '%sys.dm_exec_plan_attributes% '

Here are three different points:

1. There is no qualification for db_id () because this column is only common to stored procedures.
2. Because there are no stored procedure names to match, you must use the statement text to search.
3. Additional conditions are required to filter the sys.dm_exec_plan_attributes.

After executing this statement, you can see that the contents of attributes are as follows:

date_first=7   date_format=1   dbid=6   objectid=158662399   set_options=251   user_id=5date_first=7   date_format=1   dbid=6   objectid=158662399   set_options=251   user_id=1  

First look at the Objectid, which is used to identify two separate cache entries. Then look at the user_id, in fact, it should be planned to cache the corresponding default schema name will be more appropriate. The DBO schema is always 1. In the Northwind Library, SCHEMA2 is 5 (the exact value is not very important).Then run this statement:

EXEC sp_executesql N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ',                   N ' @orderdate datetime ', ' 20000101 '

Then the query can find the third row of data:

date_first=7   date_format=1   dbid=6   objectid=549443125   set_options=251   user_id=-2

Objectid has changed because the query text is not the same. What does user_id mean now-2? By examining the statement carefully, you can see that the schema name has been explicitly added, meaning that the statement is now accurate, and that all users can use the cache entry. And the meaning of-2 is: There is no confusing object referencing this query. This is one of the reasons why it is recommended to explicitly define a schema name in the programming specification. It should be a best practice both on the application side and in the stored procedure.For stored procedures, the naming interpretation is always performed from the owning party of the stored procedure, not the current user. So a stored procedure owned by the DBO, orders can use only dbo.orders instead of using other schemas. (except for dynamic SQL called inside a stored procedure, this is only appropriate for executing SQL statements directly in the stored procedure.) ）

Run the application query in SSMS:When you understand the previous content, there are some pitfalls to keep in mind when dealing with ssms fast and slow to apply. For stored procedures, you need to remember issues with ARITHABORT and other set options. However, it is also necessary to ensure that the query text is the same as the default schema for the account where the terminal runs the statement.In most cases, it can be handled in the following way:

EXECUTE AS USER = ' appuser ' go--the SQL statement that needs to be executed Gorevert

However, if the resource accessed by this account is not in the current library, an error will be present. At this point, you can use EXECUTE as login instead, but this scenario requires server-level permissions.Because it is often not easy to get SQL text, the best way is to use tracing to get SQL statements, either using Profiler or server-side tracing. If the SQL statement is non-parametric, you need to be careful with the full text you copied, and then execute it in SSMs. In other words, do not clear or add some prefixes or spaces. Do not change lines, delete comments, and so on. Make sure that the statements executed by the application are identical. Can be checked by sys.dm_exec_plan_attributes.Another scenario is obtained from Sys.dm_exec_query_stats and Sys.dm_exec_sql_text, which executes the following statement:

SELECT ' < ' + Est.text + ' > ' from   sys.dm_exec_query_stats qscross  APPLY sys.dm_exec_sql_text (qs.sql_ HANDLE) Estwhere  est.text like '% can identify the statement characteristics of the SQL code% '

Note that this is going to run in text mode, which defaults to Grid mode, and SSMs uses spaces to replace line breaks. Where the angle brackets are just for the purpose of separating.It's much easier to parameterize SQL. Because the SQL statement is wrapped in parentheses. In other words, you may see in the Profiler:

EXEC sp_executesql n ' SELECT * from Orders WHERE OrderDate > @orderdate ',                   n ' @orderdate datetime ', ' 20000101 '

Even if you do it in this format, it doesn't matter:

EXEC sp_executesqln ' SELECT * from Orders WHERE OrderDate > @orderdate ', N ' @orderdate datetime ', ' 20000101 '

note, however, that you do not change the code inside the parentheses, which affects the calculation of the hash value.
Handling parameter sniffing problems in dynamic SQL:
The previous scenario of parameter sniffing in stored procedures is also applicable in dynamic SQL, but there are some caveats:
Effects of automatic parameterization:Check the verbs in the operator properties to see this part of the phenomenon, such as executing this statement:

SELECT * from Orders WHERE OrderID = 11000

The SEEK predicate is as follows:

Seek keys[1]: Prefix: [Northwind]. [dbo]. [Orders]. OrderID =scalar Operator (convert_implicit (int,[@1],0))

[@1] reveals the automatic parameterization of statements (auto-parameterised)
Sometimes this situation is initiated by the user, such as:

SELECT ... FROM dbo. Orders WHERE Status = ' Delayed '

This table in Northwind does not exist in the Stauts column, and there is no delayed this value, just the demo needs, when SQL Server parameterized this query, because the requirements of generating a query plan must be able to overwrite all parameters, the optimizer does not use the column on status.There is no absolute way to turn off any of the parametric features of the pattern, but there are some tricks you can use. If the database is a simple parametric pattern, parameterization occurs only in very simple queries, such as single-table queries. One technique is to use and 1=1 in statements to disable the occurrence of simple parameterization.If the database is forced parameterization, then there are two alternatives. You can see in Books Online that forced parameterization of which scenarios are not appropriate for parameterization. One is to use option (RECOMPILE) and the other is to add a variable:

DECLARE @x intSELECT ... FROM dbo. Orders WHERE Status = ' Delayed ' and @x is NULL

Schedule Wizard and schedule freeze:Sometimes you want to fix the problem by adding some kind of hint to modify the statement. For stored procedures, you can modify and deploy new stored procedures to resolve the problem immediately, but this is much less likely for statements generated inside the application. Because it causes the entire code to recompile the generator, it may also be deployed to all user machines. It may also involve the entire software lifecycle (such as testing), which is almost impossible to implement if the application is a third-party software.However, SQL Server provides a solution for these approaches, the Plan Wizard, which is guides. There are two ways to create a Schedule wizard, a general approach, and a shortcut (schedule freeze). The general approach is introduced from SQL 2005, and the plan freezes are introduced from SQL 2008.
The following is an example of a planning wizard, but this example is only suitable for SQL 2008 and later versions:

Use NORTHWINDGODBCC freeproccachegoexec sp_executesql N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ', N ' @orderdate datetime ', @orderdate = ' 19960101 ' goexec sp_create_plan_guide @name = N ' myguide ', @stmt = N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ', @type = N ' SQL ', @module_or_batch = NULL, @params = n ' @orderdate datetime ', @hints = n ' OPTION (TABLE HINT (dbo. Orders, INDEX (OrderDate))) ' Goexec sp_executesql N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ', n ' @orderdate datetime ', @orderdate = ' 19980101 ' goexec sp_control_plan_guide N ' DROP ', N ' myguide '

In this example, a plan is created to ensure that the query is bound to use the index on the OrderDate and is an index lookup. The name of the wizard is then specified. The statement that uses this wizard is then specified. When you execute in SSMs, make sure you don't add or lose any spaces or other changes. Where the @type parameter defines the wizard for dynamic SQL instead of stored procedures. If the SELECT statement is part of a bulk process, you need to specify the part of the code that the application submits in @module_or_batch. If @module_or_batch is null, then @stmt is assumed to be the entire batch. @params is a list of parameters for the batch and must exactly match the characters submitted by the application.
Finally, @hints is the point. In this example, the query is specified to always use the index on OrderDate, regardless of the @orderdate's sniffer value. There is also a query hint option (TABLE HINT) that is not available in SQL 2005, which is why this script can only be used in SQL 2008+.In this script, DBCC FREEPROCCACHE is used only to empty the cache. In addition, to demonstrate, I used a parameter that leads to a bad query plan, making it a clustered index scan. Once the statement uses this wizard, it can be directly effective. That is, all current entries for the statement are cleared out of the cache.In SQL 2008, as long as you know the name, you can use sp_create_plan_guide to specify any order of the arguments, and omit the n before the string. However, there are limitations in SQL 2005, the order of the parameters is required, and n cannot be ignored.
In this example, I use the Schedule Wizard to force the index, but you can also use other hints that include the USE plan hint to specify a specific complete query plan. But the risk is a bit big.That is, when there are two query plans, a good one, a bad one, because of the reason for the parameter sniffing, and there is no proper way to get rid of bad query plans, it is better to use a plan freeze. Rather than using sp_create_plan_guide to handle complex parameters, it is better to extract the plan handle from the cache and then populate it directly to Sp_create_plan_guide_from_handle. As in the following example:

Use NORTHWINDGODBCC freeproccacheset ARITHABORT ongoexec sp_executesql N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ', N ' @orderdate datetime ', @orderdate = ' 19990101 ' godeclare @plan_handle VARBINARY ( ), @rowc intselect @plan_handle = plan_handlefrom sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text (qs.sql_ HANDLE) Estwhere est. TEXT like '%orders WHERE orderdate% ' and est. TEXT not like '%dm_exec_query_stats% ' SELECT @rowc = @ @rowcountIF @rowc = 1EXEC sp_create_plan_guide_from_handle ' Myfrozen Plan ', @plan_handleELSERAISERROR ('%d plans found in plan cache. Canno Create plan guide ', 16,1, @rowc) go--Test it out! SET ARITHABORT offgoexec sp_executesql N ' SELECT * FROM dbo. Orders WHERE OrderDate > @orderdate ', N ' @orderdate datetime ', @orderdate = ' 19960101 ' goset ARITHABORT onexec Sp_control _plan_guide ' DROP ', ' Myfrozenplan '

example, first empty the cache and then turn the ARITHABORT on. Then execute a parameter that enables the query to use the appropriate query plan. Then the second batch demonstrates how to use Sp_create_plan_guide_from_handle. First, query sys.dm_exec_query_stats and sys.dm_exec_sql_text to find the entries for the batch. The @ @rowcount value is then stored in the local variable. Doing so prevents multiple matches or mismatches from being obtained from the cache. If you get an exact match, you can call Sp_create_plan_guide_from_handle and pass in two parameters: the name of the Schedule wizard and plan handle.The next section is to check the wizard, to make sure that the same cache entry is not used, changing the ARITHABORT settings. If you open the execution plan to run this script, you can see the look, the second execution is the same as the first execution plan, although the specified parameters cause a clustered index scan, so we can know that the Plan Wizard is now independent of the SET option.
When you really use this approach, you may want to use only if you expect the plan to not exist in the cache. For this, you need to use some tricks on the query so that you can hit it accurately.The Plan Wizard is stored in sys.plan_guides. So once you have the right wizards, you can use sp_create_plan_guide to create the right content for your production environment.If you have a multi-statement batch or stored procedure and you just want to apply a wizard to a statement instead of the whole batch, you can use Sp_create_plan_guide_from_handle's third parameter, @statement_start_offset, This parameter value can be obtained from the sys.dm_exec_query_stats. About the Plan Wizard and plan freezes are not the focus of this series, and readers can read more about it from Books Online: Click to open the link summary:The end of this series is complete, and the article explains why a query statement has significant performance differences in SSMS and in the application, and also knows some ways to solve the problem with parameter sniffing. But in fact, this series does not completely cover all scenarios (of course, it is not possible), such as some access to the remote server statements, and directly in the server local ssms execution of queries, but also due to network reasons for performance differences. But I think the series should already cover common problems.
Extended reading and references:Here are some information about statistics, statement compilation, the text of a lot of content from the following information:

1. Statistics used by the Query Optimizer in Microsoft SQL Server 2008
   
2. Plan Caching in SQL Server 2008
3. Troubleshooting performance Problems in SQL Server 2008
   
4. Forcing Query plans
   

Understanding the mysteries of performance-slow in applications, SSMs fast (6) How--sql server compiles dynamic SQL