Why the DMVs are not a replacement for SQL Trace

With the introduction of the DMVs in SQL 2005, there’s now a wealth of information available on every aspect of SQL’s behaviour. It’s possible now to interrogate the SQL procedure cache to find out what queries have been running and how well or badly they are performing. So does all this mean that SQL Trace is now obsolete?

It does not. The execution stats available through sys.dm_exec_query_stats are only retained while the plan for the query is in cache. As soon as the plan is removed from cache (for whatever reason), the query stats for that query will be discarded. There are also a couple of reasons for a query’s plan not going into the cache at all.

Let’s try a quick example. I’m going to use the AdventureWorks database (SQL 2008), because it’s convenient. I’m going to create 4 procedures, run them several times, along with a couple other commands and then compare what a trace shows and what a query of the plan cache shows.

Create Procedure GetLatestPriorWeeksTransactions (@WeekEnds DATETIME)
AS

SELECT p.Name, TransactionDate, TransactionType, Quantity, ActualCost
FROM Production.TransactionHistory th inner join Production.Product p on th.ProductID = p.ProductID
WHERE TransactionDate BETWEEN @WeekEnds AND DATEADD(ww,-1,@WeekEnds)

GO

CREATE Procedure ArchiveTransactionHistory (@ArchiveEndDate DATETIME)
AS
Insert Into Production.TransactionHistoryArchive (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)
SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate
  FROM Production.TransactionHistory
  WHERE TransactionDate < @ArchiveEndDate

DELETE FROM Production.TransactionHistory WHERE TransactionDate < @ArchiveEndDate
GO

CREATE PROCEDURE SalesToday (@ProductID int)
AS
SELECT p.name, sum(sod.lineTotal) AS TotalSales, SUM(OrderQty) AS TotalSold
  FROM Sales.SalesOrderHeader soh
    INNER JOIN Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
    INNER JOIN Production.Product p on sod.ProductID = p.ProductID
  WHERE DATEADD(dd,dateDiff(dd,0,soh.OrderDate),0) = DATEADD(dd,dateDiff(dd,0,GETDATE()),0)
GO

CREATE PROCEDURE PersonSearch (
  @Surname Varchar(50) = NULL,
  @FirstName Varchar(50) = NULL
)
WITH RECOMPILE
AS

SELECT Firstname, middlename, LastName
  FROM Person.Person
  WHERE (LastName like @surname + '%' OR @Surname IS NULL)
    AND (FirstName like @FirstName + '%' OR @FirstName IS NULL)
GO

I’m going to use the same trace definition that I detailed in the article that I wrote for Simple-Talk, and use the same technique as is in the article to load the trace up and get aggregated totals.

When I query the plan cache, this is what I get back from it.

And this is what I got from the trace (with the queries relating to the trace removed)

So why the discrepancy?

Let’s start with the entries returned in the query of the trace, not the procedure cache.

The ALTER PROCEDURE wouldn’t be visible in a query of the procedure cache, because DDL statements don’t have execution plans. There’s only one way to run an ALTER PROCEDURE, so there’s no need for a plan, hence there’s nothing to cache and nothing for the query stats to hook off.

According to the procedure cache, the PersonSearch procedure was never run, but the trace records 35 executions. There’s no entry for this procedure in the cache, because it’s marked WITH RECOMPILE. Since a new plan has to be compiled on each execution, the plan is never cached and hence it will never be visible in a query of the plan cache and there will never be saved execution statistics for it.

Now, how about the two with very different execution counts? In each of those cases, something happened to throw the procedure’s existing plan out of cache part way through the test. When the plan gets thrown from cache the query stats get discarded as well.

In the case of the SalesToday procedure, the procedure was altered part way through the test. Altering a procedure or any object that the procedure depends upon will invalidate the cached plan and cause it to be discarded

The other procedure, GetLatestPriorWeeksTransactions, had it’s plan invalidated due to a statistics update. The archiving of TransactionHistory changed enough rows in the table that the next time a query ran it triggered a statistics update. That statistics update would have invalidated any plans that used those statistics. In general, this doesn’t have to be stats update on a permanent table. If a procedure uses a temp table and adds rows to that temp table, there’s a good chance of getting statistics-based recompiles of statements within that procedure, thereby skewing the results of queries against the plan cache.

In conclusion, while the DMVs exposing the plan cache are very useful in seeing how queries are running, they cannot be seen as a complete replacement for SQL Trace because of the possibility of certain types of commands not getting cached at all, and the possibility of plans getting invalidated and dropped from cache for various reasons.