upc cablecom internet only Plan Caching and Recompilation in VB.NET

Generation UPC-A Supplement 5 in VB.NET Plan Caching and Recompilation

9
UPC-A Supplement 5 Creator In Visual Basic .NET
Using Barcode drawer for Visual Studio .NET Control to generate, create GS1 - 12 image in VS .NET applications.
www.OnBarcode.com
Scanning GTIN - 12 In VB.NET
Using Barcode scanner for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Plan Caching and Recompilation
Painting Barcode In Visual Basic .NET
Using Barcode creator for VS .NET Control to generate, create bar code image in .NET framework applications.
www.OnBarcode.com
Decode Barcode In VB.NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Tip It is, of course, recommended that you don t use these commands on your production servers because it could affect the performance of your running applications. Usually, you want to keep plans in cache.
UPC A Maker In C#.NET
Using Barcode creator for VS .NET Control to generate, create UPC-A image in .NET framework applications.
www.OnBarcode.com
UPC Code Maker In .NET
Using Barcode generation for ASP.NET Control to generate, create UPC-A Supplement 2 image in ASP.NET applications.
www.OnBarcode.com
Caching Mechanisms
UPC Symbol Creation In .NET Framework
Using Barcode maker for .NET framework Control to generate, create UPC Code image in Visual Studio .NET applications.
www.OnBarcode.com
2D Barcode Generator In Visual Basic .NET
Using Barcode generation for .NET framework Control to generate, create Matrix 2D Barcode image in .NET applications.
www.OnBarcode.com
SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations:
Encoding Bar Code In Visual Basic .NET
Using Barcode maker for .NET framework Control to generate, create bar code image in .NET applications.
www.OnBarcode.com
USS-128 Creator In VB.NET
Using Barcode encoder for Visual Studio .NET Control to generate, create EAN128 image in .NET framework applications.
www.OnBarcode.com
Adhoc query caching Autoparameterization Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API Stored procedures or other compiled objects (triggers, TVFs, etc.)
Print Linear 1D Barcode In VB.NET
Using Barcode generator for .NET Control to generate, create Linear 1D Barcode image in .NET applications.
www.OnBarcode.com
RM4SCC Encoder In Visual Basic .NET
Using Barcode generation for Visual Studio .NET Control to generate, create British Royal Mail 4-State Customer Code image in VS .NET applications.
www.OnBarcode.com
To determine which mechanism is being used for each plan in cache, we need to look at the values in the cacheobjtype and objtype columns in the sys.dm_exec_cached_plans view. The cacheobjtype column can have one of six possible values:
Recognizing EAN / UCC - 13 In VS .NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Painting DataMatrix In Java
Using Barcode creation for Java Control to generate, create Data Matrix 2d barcode image in Java applications.
www.OnBarcode.com
Compiled Plan Compiled Plan Stub Parse Tree Extended Proc CLR Compiled Func CLR Compiled Proc
Drawing GTIN - 12 In Visual Studio .NET
Using Barcode creation for Visual Studio .NET Control to generate, create UPC-A Supplement 5 image in .NET applications.
www.OnBarcode.com
Code 128 Code Set B Scanner In None
Using Barcode decoder for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
In this section, the only values we are looking at are Compiled Plan and Compiled Plan Stub. Notice that I lter the usecount query to limit the results to rows with one of these values. There are 11 different possible values for the objtype column:
Print PDF417 In None
Using Barcode printer for Office Word Control to generate, create PDF-417 2d barcode image in Office Word applications.
www.OnBarcode.com
Bar Code Generation In None
Using Barcode encoder for Software Control to generate, create barcode image in Software applications.
www.OnBarcode.com
Proc (Stored procedure) Prepared (Prepared statement) Adhoc (Adhoc query) ReplProc (Replication- lter-procedure) Trigger View
Barcode Scanner In Java
Using Barcode Control SDK for Java Control to generate, create, read, scan barcode image in Java applications.
www.OnBarcode.com
UCC - 12 Generator In .NET Framework
Using Barcode creation for Visual Studio .NET Control to generate, create UCC - 12 image in Visual Studio .NET applications.
www.OnBarcode.com
Microsoft SQL Server 2008 Internals
Default (Default constraint or default object) UsrTab (User table) SysTab (System table) Check (CHECK constraint) Rule (Rule object)
We are mainly examining the rst three values, but many caching details that apply to stored procedures also apply to replication lter procedures and triggers.
Adhoc Query Caching
If the caching metadata indicates a cacheobjtype value of Compiled Plan and an objtype value of Adhoc, the plan is considered to be an adhoc plan. Prior to SQL Server 2005, adhoc plans were cached occasionally, but it was not something on which you could depend. However, even when SQL Server caches your adhoc queries, you might not be able to depend on their reuse. When SQL Server caches the plan from an adhoc query, the cached plan is reused only if a subsequent batch matches exactly. This feature requires no extra work to use, but it is limited to exact textual matches. For example, if the following three queries are executed in the Northwind2 database (which can be found on the companion Web site, http://www.SQLServerInternals.com/companion), the rst and third queries use the same plan, but the second one needs to generate a new plan:
SELECT * FROM Orders WHERE CustomerID = 'HANAR'; SELECT * FROM Orders WHERE CustomerID = 'CHOPS'; SELECT * FROM Orders WHERE CustomerID = 'HANAR';
You can verify this by rst clearing out the plan cache and then running the three queries in separate batches. Then run the usecount query referred to previously:
USE Northwind2; DBCC FREEPROCCACHE; GO SELECT * FROM Orders WHERE CustomerID = 'HANAR'; GO SELECT * FROM Orders WHERE CustomerID = 'CHOPS'; GO SELECT * FROM Orders WHERE CustomerID = 'HANAR'; GO SELECT usecounts, cacheobjtype, objtype, [text] FROM sys.dm_exec_cached_plans P CROSS APPLY sys.dm_exec_sql_text (plan_handle) WHERE cacheobjtype = 'Compiled Plan' AND [text] NOT LIKE '%dm_exec_cached_plans%';
9
Plan Caching and Recompilation
You should get two rows back because the NOT LIKE condition lters out the row for the usecount query itself. The two rows are shown here and indicate that one plan was used only once, and the other was used twice:
usecounts
cacheobjtype
Compiled Plan Compiled Plan
objtype
Adhoc Adhoc
text
SELECT * FROM Orders WHERE CustomerID = 'CHOPS' SELECT * FROM Orders WHERE CustomerID = 'HANAR'
Note The results shown in this section are obtained with the Optimize for Ad Hoc Workloads con guration option set to 0, which is the default value when you install SQL Server. I discuss this new SQL Server 2008 option later in this chapter. The results show that with a change of the CustomerID value, the same plan cannot be reused. However, to take advantage of reuse of adhoc query plans, you need to make sure that not only are the same CustomerID values used in the queries, but also that the queries are identical, character for character. If one query has a new line or an extra space that another one doesn t have, they are not treated the same. If one query contains a comment that the other doesn t have, they are not identical. In addition, if one query uses a different case for either identi ers or keywords, even in a database with a case-insensitive collation, the queries are not the same. If you run the code here, you see that none of the queries can reuse the same plan:
USE Northwind2; DBCC FREEPROCCACHE; GO SELECT * FROM orders WHERE customerID = 'HANAR'; GO -- Try it again SELECT * FROM orders WHERE customerID = 'HANAR'; GO SELECT * FROM orders WHERE customerID = 'HANAR'; GO SELECT * FROM Orders WHERE CustomerID = 'HANAR'; GO select * from orders where customerid = 'HANAR'; GO SELECT usecounts, cacheobjtype, objtype, [text] FROM sys.dm_exec_cached_plans P CROSS APPLY sys.dm_exec_sql_text (plan_handle) WHERE cacheobjtype = 'Compiled Plan' AND [text] NOT LIKE '%dm_exec_cached_plans%';
Copyright © OnBarcode.com . All rights reserved.