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 do. The only trick to using this is that you need to get the plan handle, that long, meaningless string inside the parentheses above. You can do that using this query (or several others):

SELECT  decp.plan_handle
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE   dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%';

I’m joining the plans from cache that are displayed through sys.dm_exec_cached_plans to the query text through sys.dm_exec_sql_text and, in this case, searching for the CREATE PROCEDURE statement to find the one I’m interested in. That’s a quick & dirty way to get the job done. Simple stuff, but hopefully helpful.

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

The script is almost self-explanatory. I want to point out that I put in one cross-database query to imply the possibility of cross-database ownership or access, but also to show that regardless of what’s referenced, all queries from this database are flushed from cache.

The first of the simple DMO queries returns two rows, the second returns no rows because everything is out of the cache because of the change to the database. It’s a little thing, but since it wasn’t explicitly stated in the Microsoft documentation, I thought I’d toss this out there.

I had no idea, but evidently I started a bit of a donny-brook. Cool!

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 hoc TSQL goes, that one is actually some what clean. They get a lot worse. But, in this case, each and every time the @value variable is changed, you’re going to get a different execution plan. That’s because the full text of the query is used to determine if the existing plan will work for the new query and changing the @value variable will result in a change to that text.

However, there is another way to build and execute ad hoc TSQL. Done correctly, it will work exactly the same way as stored procedures, including plan reuse. This method is known as a parameterized query. There ways to do this through OLEDB & ODBC and there’s a way to do it through TSQL, sp_executesql. Instead of the query above, let’s examine this query:

DECLARE @sql NVARCHAR(MAX), @value INT, @parm NVARCHAR(MAX);
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 = @internalvalue'
SET @parm = '@internalvalue int'
EXEC sp_executesql @sql, @parm, @internalvalue = @value;

Using sp_executesql you can define parameters and put them into the query. Now, when this query is called again, you’ll see that the execution plan gets reused. As part of the comparison, I’ll also create this procedure:

CREATE PROCEDURE dbo.AdHocTest
(@value INT)
AS
SELECT * 
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @value;

To test these queries, we’ll need to capture some trace events. Here are the ones I’ll capture:

• SQL:BatchCompleted
• SP:CacheMiss
• SP:CacheHit
• SP:CacheInsert

When all three queries are run, changing the parameter passed to @value, here is the resulting trace event output:

From the top, I ran each query in order as listed here in the article, changing the value passed once each time I called query. You first see a cache miss for the pure ad hoc query. It inserts to the cache and finishes the batch. Then, the next ad hoc query has a cache miss, a cache insert, and completes it’s batch. Proving that ad hoc queries with only a small change will restult in multiple execution plans.

Then we move on to the sp_executesql queries. They start out the same way, with a cache miss and an insert before the batch is completed. Note, the cache insert is for a different set of code than is immediately visible based on what we’ve provided. The TextData column starts with “(@internalvalue int)…” and procedes to show the rest of the query. This is the use of sp_executesql at work. It’s creating a parameterized query, effectively the same thing as a stored procedure. Then, with the second call to sp_executesql, we see a cache miss followed by a cache hit. This is the classic pattern for a successful call to the plan cache.

If you don’t trust that, the last two batches are the stored procedure. You can see that the first call to the procedure results in the same cache miss followed by a cache insert that every other first call had. The second call to the stored procedure had a cache miss followed by a cache hit. The same as the second, ad hoc sql, call to sp_executesql.

That, my friends, is plan reuse in action from an ad hoc query. Myth 1/2 Busted. Or maybe: Myth “It Depends” Busted.

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. I’ll get into those things another day, but they’re pretty exciting all by themselves. Finally, you can also get information about currently executing queries, which may or may not be in the cache, through sys.dm_exec_requests. These are all views, so you can just query them directly. Unfortunately, the first two don’t offer any chance to filter the information returned by database or spid, etc., but sys.dm_exec_requests does.

So, getting basic information about the size of a plan or how long it’s been cache or the accumulated time that it’s run is all interesting and everything, but what about getting a peek at the execution plan itself. Enter the dynamic management function sys.dm_exec_query_plan( plan_handle ). Just join it with one of the other views and suddenly you’ve got some real information:

SELECT *
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) p

You’ll get an XML plan, which, when you click on it in 2008 opens up as a graphical plan since, to Management Studio in 2008, they’re the same thing. Easy-peasy.

But what about that query that’s hanging up and blocking all the othe query’s? What the heck did the developers do this time? Let’s try out the DMF sys.dm_exec_sql_text ( plan_handle ).

SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) t

You’ll see the query, as it was passed, in the text field. Great stuff. Really handy.

There is a ton to explore here and it’s all very useful stuff.  These are available in SQL Server 2005 in addition to SQL Server 2008.

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)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@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 (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(11)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@p3='2008-04-29 14:13:30:000',@p4=N'ted_coolish'

Now that same parameter is declared as nvarchar(11). So if we look at the procedure cache to see what’s inside for this query:
 
SELECTobjtype
,p.
size_in_bytes
,[sql].
[text]
FROM
sys.dm_exec_cached_plans
p
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle)
sql
WHERE
[text] LIKE
‘%INSERT INTO dbo.users%’

 We get the following results (I ran the test a few times, so I have more than two rows):

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(11))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(10))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(12))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

Prepared 40960 (@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8))INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)

For what should have been a single plan, I have four (or more) clogging up the cache and causing unecessary compiles, etc. I’m not completely enamored with what this tool is going to do tofor me as a dba.