I Started a Fight
I had no idea, but evidently I started a bit of a donny-brook. Cool!
Procedure Cache and Dynamic Management Views
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.
nHibernate Recompiles and Execution Plans
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.