Batch compilation, recompile, and schedule caching issues in SQL Server 2005 (4)

Next, consider the following T-SQL code snippet: -- dbo.someTable will be used to populate a temp table
-- subsequently.
create table dbo.someTable (a int not null, b int not null)
go
declare @i int
set @i = 1
while (@i <= 2000)
begin
　　insert into dbo.someTable values (@i, @i+5)
　　set @i = @i + 1
end
go
　
-- This is the stored procedure of main interest.
create procedure dbo.AlwaysRecompile
as　
set nocount on
　
-- create a temp table
create table #temp1(c int not null, d int not null)
　
select count(*) from #temp1
　
-- now populate #temp1 with 2000 rows
insert into #temp1
select * from dbo.someTable
　
-- create a clustered index on #temp1
create clustered index cl_idx_temp1 on #temp1(c)
　
select count(*) from #temp1
go

In SQL Server 2000, the first Sp:recompile event is generated for the first "select" statement when the stored procedure is executed for the first time. This is a deferred compilation, not a true recompilation. The second Sp:recompile event is for the second "select". When the first recompilation occurs, the second "select" is also compiled, because in SQL Server 2000, compilation is done at the batch level. Then, at execution time, the #temp1 schema changes as a result of a new clustered index. Therefore, the reason for the second sp:recompile is the schema change.

Recompilation caused by row modification times

Consider the underlying stored procedure and its execution.

use AdventureWorks　 -- or say "use pubs" on SQL Server 2000
go
create procedure RowCountDemo
as
begin
　　create table #t1 (a int, b int)
　　declare @i int
　　set @i = 0　　while (@i < 20)
　　begin
　　　 insert into #t1 values (@i, 2*@i - 50)
　　　 select a
　　　 from #t1　
　　　 where a < 10 or ((b > 20 or a >=100) and (a < 10000))
　　　 group by a
　
　　　 set @i = @i + 1
　　end
end
go
exec RowCountDemo
go

Recall that a temporary table has a recompile threshold of 6 when the table is empty when the threshold is computed. When the Rowcountdemo is executed, the recompilation associated with "statistics changed" (statistics changed) can be observed after the #t1 contains the entire 6 lines. You can observe more recompilation by changing the upper limit of the "while" loop.

recompilation caused by SET option changes

Consider the following stored procedures.

use AdventureWorks
go
create procedure SetOptionsDemo as
begin
　　set ansi_nulls off
　　select p.Size, sum(p.ListPrice)
　　from Production.Product p　
　　　　 inner join Production.ProductCategory pc
　　　　　on p.ProductSubcategoryID = pc.ProductCategoryID
　　where p.Color = 'Black'
　　group by p.Size
end
go
exec SetOptionsDemo　　-- causes a recompilation
go
exec SetOptionsDemo　　-- does not cause a recompilation
go

When the Setoptionsdemo is executed, the "select" Query is compiled with "Ansi_nulls" on. When Setoptionsdemo begins execution, the value of the SET option will change because of "set ANSI_NULLS off", so that the compiled query plan will no longer be "valid." Therefore, the recompile will be done with "ansi_nulls" off. The second execution does not cause recompilation because the cached plan is compiled with "Ansi_nulls" off.

Another example of the recompilation required for SQL Server 2005 that is more than SQL Server 2000

Consider the following stored procedures.

use AdventureWorks--say ' use pubs ' on SQL Server tab
Go
CREATE procedure createthenreference as
begin< br>--Create two temp tables
CREATE TABLE #t1 (a int, b int)
CREATE table #t2 (c int, d int)
--Populate th EM with some data
Insert to #t1 values (1, 1)
INSERT into #t1 values (2, 2)
INSERT into #t2 values (3, 2)
INSERT INTO #t2 values (4, 3)
--issue two queries on them
Select X.a, x.b, sum (y.c)
from #t1 x in NER join #t2 y on x.b = Y.d
Group by x.b, X.A
Order by x.b

Select *
from #t1 z cross join #t2 w< br> where w.c!= 5 or w.c!= 2
End
Go
exec createthenreference
Go

In SQL Server 2005, the first execution of Createthenreference resulted in six statement-level recompilation: Four for the INSERT statement and two for the select query. When the stored procedure starts executing, the original query plan does not contain a plan for the INSERT or SELECT statement because the object it references (temporary tables #t1 and #t2) does not yet exist. After the #t1 and #t2 are created, the query plans for insert and select are compiled and are considered recompiled. In SQL Server 2000, because the entire stored procedure is recompiled immediately, it occurs only once (stored procedure level) recompilation-the recompilation that is thrown when the first "insert" begins execution. At this point, the entire stored procedure is recompiled, and because the #t1 and #t2 already exist, the subsequent insert and select can be compiled at once. Obviously, the number of statement-level recompilation in SQL Server 2005 can increase infinitely by adding more statements referencing objects such as #t1 and #t2.

Xi. Tools and Orders

This section describes the various tools and commands for observing and debugging recompilation.

Sys.syscacheobjects Virtual Tables

Although queries can be made from any database, the virtual table exists theoretically only in the master database. The Cacheobjtype column for this virtual table is particularly interesting. When cacheobjtype = "Compiled plan", the corresponding row references a query schedule. When cacheobjtype = "Executable Plan", the corresponding row references an execution context. As we explained earlier, each execution context must have its own associated query plan, and vice versa. Another column involved is the ObjType column: the type of object that indicates that it is scheduled to be cached (for example, "Adhoc", "Prepared", and "Proc"). The setopts column encodes a bitmap indicating the SET options that are in effect when the plan is compiled. Sometimes, multiple copies of the same compiled plan (which differ only setopts columns) are cached in a plan cache. This means that different sets of SET options are being used by various connections-usually in situations that should not occur. The usecounts column holds the number of times the cached object has been reused since the object was cached.

Please refer to BOL For more information about this virtual table.

DBCC Freeproccache

This command deletes all cached query plans and execution contexts in the schedule cache. This command should not be run on a production server because it in turn affects the performance of the running application. This command is useful for controlling the contents of a plan cache when troubleshooting a recompile issue.

DBCC flushprocindb (db_id)

This command deletes all cached schedules from the scheduled cache for a particular database. This command should not be run on a production server because it in turn affects the performance of the running application.

Profiler trace Events

The following profiler trace events involve observing and debugging plan caching, compiling, and recompiling behavior.

•	' Cursors:cursorrecompile ' (New in SQL Server 2005), which is used to observe the recompilation caused by the batch processing associated with the cursor.
•	' Objects:auto Stats ', which is used to observe statistical updates caused by the automatic statistics feature of SQL Server.
•	' Performance:show plan all to Query Compile ' (New in SQL Server 2005) is useful for tracking batch compilation. Compilation and recompilation are not differentiated. Generates SHOWPLAN data in text format (similar to the Showplan data generated using the "SET SHOWPLAN_ALL on" option).
•	' Performance:show plan XML for Query Compile ' (New in SQL Server 2005) is useful for tracking batch compilation. Compilation and recompilation are not differentiated. Generates SHOWPLAN data in XML format (similar to the Showplan data generated using the "Set showplan_xml on" option).
•	' Stored Procedures:SP:Recompile ' excitation (when recompilation occurs). Other events in the "Stored procedures" category are also useful-for example: Sp:cacheinsert, sp:stmtstarting, Sp:cachehit, sp:starting, and so on.

Performance counters

The following performance counter values are involved when debugging a performance problem that can result from excessive compilation and recompilation.

Performance objects	Counter
SQL Server: Buffer Manager	Cache hit ratio, lazy write/sec, process cache pages, total pages
SQL Server: Cache Manager	Cache hit rate, cache object count, cache number of pages, Cache usage Count/sec
SQL Server: Memory Manager	SQL Cache Memory (KB)
Sqlserver:sql Statistics	Automatic parameterization attempt/sec, Batch request/sec, automatic parameterization failure/sec, secure automatic parameterization/sec, SQL Compile/sec, SQL Recompile/sec, unsafe automatic parameterization/sec

　　
Summarize

SQL Server 2005 can cache query plans that are submitted to the various types of statements that they execute. Query plan caching can result in query plan reuse, avoid compiling penalty points, and better utilize plan caching. Some coding methods prevent query plan caching and reuse, and should therefore be avoided. SQL Server can discover the opportunity for query plan reuse. In particular, the query plan cannot be reused for the following two reasons: (a) The object schema that appears in the query plan changes, causing the plan to become invalid, and (b) the data in the tables referenced by the query plan changes enough to make the plan not optimal. SQL Server discovers both types of situations when the query is executed and compiles the entire or partial batch as needed. Poor T-SQL coding methods increase the frequency of recompilation, which in turn affects SQL Server performance. In many cases, this type of situation can be debugged and corrected.

Appendix A:sql Server 2005 when the query is not automatically parameterized?

Automatic parameterization is a process by which SQL Server replaces text constants that appear in SQL statements with parameters such as @p1 and @p2. The compiled plan for the SQL statement is then cached in a parameterized form in the plan cache so that subsequent statements (which differ only in the value of the text constant) can reuse the cached plan. As mentioned in part four, only the SQL statements that have parameter values that do not affect the selection of the query plan are automatically parameterized.

SQL Server's LPE (Language processing and execution) component can parameterize SQL statements. When the value of a text constant is found not to affect the query plan selection, the QP (query processor) component declares the LPE automatic parameterization attempt to be "safe" and continues to perform automatic parameterization; otherwise, it declares that automatic parameterization is "unsafe" and aborts it. The values of some performance counters mentioned in section 11.5 (the ' Sqlserver:sql Statistics ' category) Report statistical information about automatic parameterization.

The list below lists the type of statements that SQL Server 2005 does not automatically parameterize.

•	Queries with in clauses are not automatically parameterized. For example:
•	WHERE ProductID in (707, 799, 905)
•	BULK INSERT statement.
•	An UPDATE statement with a SET clause containing a variable. For example: UPDATE Sales.Customer SET CustomerType = N'S' WHERE CustomerType = @a
•	A SELECT statement with UNION.
•	A SELECT statement with an into clause.
•	A SELECT or UPDATE statement with a for BROWSE clause.
•	A statement with a query hint specified with the OPTION clause
•	Its select list contains a DISTINCT SELECT statement.
•	A statement with a TOP clause.
•	WAITFOR statement.
•	The DELETE or UPDATE statement with the FROM clause.
•	When the FROM clause contains the following: • Multiple tables • TABLESAMPLE clause • Table-valued function or table-valued variable • Full-text table • OPENROWSET • Xmlunnest • OPENXML • OPENQUERY • IRowset • OpenDataSource • Table hints or index hints
•	When a SELECT query contains a subquery
•	When a SELECT statement contains a GROUP by, having, or COMPUTE by
•	An expression added with OR in the WHERE clause.
•	The comparison predicate in expr <> non-null-constant form.
•	Full-text predicate.
•	When the target table in INSERT, UPDATE, or DELETE is a table-valued function.
•	Statement submitted through the EXEC string.
•	Statements submitted through sp_executesql, Sp_prepare, and sp_prepexec do not have parameters that are automatically parameterized under TF 447.
•	When a query is requested.
•	When a query contains a list of common table expressions.
•	When the query contains a for UPDATE clause.
•	When UPDATE contains an ORDER BY clause.
•	When the query contains the GROUPING clause.
•	Insert: INSERT INTO T DEFAULT VALUES.
•	INSERT ... EXEC statement.
•	When the query contains a comparison of two constants. For example: WHERE 20 > 5
•	With automatic parameterization, you can create more than 1000 parameters.