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.

Script 1:

SET NOCOUNT ON

GO

SET STATISTICS IO ON

GO

SET STATISTICS TIME ON

GO

DECLARE @SQL NVARCHAR(1000)

--Load the variable to execute

SELECT @SQL =

      '

      SELECT

            fs.*

      FROM

            dbo.FactInternetSales fs

            JOIN dbo.DimCurrency dc ON fs.CurrencyKey = dc.CurrencyKey

            JOIN dbo.DimTime od ON fs.OrderDateKey = od.TimeKey

      WHERE

            od.FullDateAlternateKey > ''2004-03-01''

      '

--Now Run It!

EXEC sp_executesql @SQL

--Clean Up!

SET STATISTICS IO OFF

GO

SET STATISTICS TIME OFF

GO 

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

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter