Targeted Plan Cache Removal

Azure, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
A lot of times you'll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they'll fix it by running the following code: DBCC FREEPROCCACHE(); BOOM! Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing... Your fault for going nuclear. Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here's a little piece of code to help out: DECLARE @PlanHandle VARBINARY(64); SELECT @PlanHandle = deps.plan_handle FROM…
Read More

Generating Estimated Plan and the Plan Cache

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
Does generating an Estimated Plan cause that plan to be loaded into the plan cache? No.   What? Still here? You want more? Proof? Fine. Let's first run this bit of code (but please, not on your production server): DBCC FREEPROCCACHE(); That will remove all plans from cache. Now, let's take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the "Display Estimated Execution Plan" button on the toolbar): SELECT * FROM Production.ProductModel AS pm; This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let's run another query: SELECT deqs.plan_handle FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM Production.ProductModel AS pm;'; That's just an easy way to see if a plan_handle exists.…
Read More

Simple Parameterization and Data Types

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But... Let's take this example. Here's a very simple query: SELECT ct.* FROM Person.ContactType AS ct WHERE ct.ContactTypeID = 7; This query results in simple parameterization and we can see it in the SELECT operator of the execution plan: We can also see the parameter that was defined in use in the predicate of the seek operation: Hang on. Who the heck put the wrong data type in there that's causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I'll get two more plans and I…
Read More

Saving Execution Plans on Azure SQL Database

Azure
In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it's pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can't think of at the moment? To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most…
Read More

How to Drop One Plan from Cache

SQL Server 2008, SQL Server 2012, TSQL
While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is: DBCC FREEPROCCACHE(0x05000700618F532C40E190CE000000000000000000000000) ; Not much to it is there? The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don't need to drop the entire procedure cache as people so frequently…
Read More

Changing DB_CHAIN Can Clear the Plan Cache

SQL Server 2008, TSQL
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. ALTER DATABASE Testing SET DB_CHAINING OFF; GO CREATE PROCEDURE x AS SELECT * FROM test.dbo.A AS a2; GO CREATE PROCEDURE y AS SELECT * FROM dbo.Table_1 AS t; GO EXEC dbo.x; EXEC dbo.y; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'CREATE PROCEDURE x%' OR dest.text LIKE 'CREATE PROCEDURE y%'; ALTER DATABASE Testing SET DB_CHAINING ON; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs…
Read More

Ad Hoc Queries Don't Reuse Execution Plans: Myth or Fact

SQL Server 2005, SQL Server 2008, TSQL
Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: DECLARE @sql NVARCHAR(MAX), @value int; SET @value = 43668; SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh '; SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod '; SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID '; SET @sql = @sql + 'WHERE soh.SalesOrderID = ' + CAST(@value AS NVARCHAR); EXEC (@sql); And as ad…
Read More

Procedure Cache and Dynamic Management Views

SQL Server 2005, SQL Server 2008, TSQL
I'm just going through the chapter on the procedure cache in the new book and I'm having a blast playing with the dynamic management views and functions that let you access the procedure cache. It's just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints.…
Read More

nHibernate Recompiles and Execution Plans

nHibernate
One little potential for performance problems that we've seen comes out of how nHibernate generates it's parameratized queries. It limits the length of any parameter to the length of the column, but if the length of that parameter is less than the column, it uses tha smaller length when declaring the variable. This results in a query that looks like this: exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',[email protected] nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8)',@p0=N'Ted Cool',@p1=N'abc123',@[email protected]',@p3='2008-04-29 14:10:44:000',@p4=N'ted_cool' Note the parameter @p4 which is the parameter mapping to the primary key for the little sample table. In this query it's declared as nvarchar( 8 ) because 'ted_cool' is composed of eight characters. But if we changed it to 'ted_coolish': exec sp_executesql N'INSERT INTO dbo.users…
Read More