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

SQL Server, T-SQL
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, T-SQL
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)',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…
Read More