Over the many years that I’ve been dealing with SQL Server, I’ve always been told and read about how using dynamic SQL statements can’t be reused by the query optimizer. So, this morning, I thought about testing it out and finding out if dynamic SQL statements get stored in cache. Below I have a query that sets the statistics IO and Time on so that we can see more details regarding our execution. Before running the query, I also wanted to restart the instance of SQL Server that I was developing on. If you need to know how to restart your SQL Server instance using the GUI, see my blog here. If you want to see how to restart SQL Server via a command line using a batch file, see my blog here.
NOTE: PLEASE DO NOT RUN THIS ON YOUR PRODUCTION SYSTEM… Now that I’ve stated that here we go.
After restarting my instance of SQL Server and connecting to AdventureWorksDW, I ran the DBCC PROCCACHE statement to see what was being stored in cache. As you can see in figure 1, there is nothing in cache for the procedures. As an alternative to restarting the SQL instance, I could have just freed up the procedure cache, but I’ll show you that in another blog.
Figure 1: DBCC PROCCACHE
I then ran the below Dynamic SQL statement to see what my results were.
SET NOCOUNT ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @SQL NVARCHAR(1000)
--Load the variable to execute
SELECT @SQL =
JOIN dbo.DimCurrency dc ON fs.CurrencyKey = dc.CurrencyKey
JOIN dbo.DimTime od ON fs.OrderDateKey = od.TimeKey
od.FullDateAlternateKey > ''2004-03-01''
--Now Run It!
EXEC sp_executesql @SQL
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
After running the statement, click on the Messages tab. You may see something similar to what I am showing in figure 1 below.
Figure 2: Results
As you can see, the 1140 logical reads are good and no physical or read-ahead reads. The key to checking if it’s going to be stored is in my next statement. So, if I run the DBCC PROCCACHE again, I get the below results.
Figure 3: PROCCACHE After Query Execution
As can be seen in the above example, even dynamic SQL statements are stored in cache.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting