Month: February 2019

Last week I talked about single use plans. One way to increase execution plan re-use is to parameterize queries by wrapping them in sp_executesql.

I’ve started using this stored procedure enough that I wanted to write a short guide on converting a query into sp_executesql.

Our query before sp_executesql

SELECT CreationDate
FROM Users
WHERE DisplayName = N'Community'
AND Location = N'on the server farm'

So there’s two values in the WHERE clause that could change between queries, if we want to look for a different DisplayName or Location. Let’s parameterize those in sp_executesql.

EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @LocationParam'

That’s a good start, but now we need to tell the server what our parameters are. Add a comma, and add those after the first string like this:

EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @Location',
N'@DisplayNameParam NVARCHAR(40), @LocationParam NVARCHAR(100)'

Completing the example of sp_executesql

And finally, we need another two commas. This is the part where we add the values for the parameters, in the same order that we declared them. That means DisplayNameParam first, then LocationParam second.

EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @Location',
N'@DisplayNameParam NVARCHAR(40), @Location NVARCHAR(100)',
N'Community', N'on the server farm'

Perfect! Here’s the execution plan and the parameter list from the SELECT operator.

Changing the values to prove the execution plan will be re-used

Let’s look for a user that doesn’t exist. Here’s some parameters that won’t match any rows in StackOverflow2010.

EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @LocationParam',
N'@DisplayNameParam NVARCHAR(40), @LocationParam NVARCHAR(100)',
N'Not a real user', N'No location'

Here’s the same execution plan and parameter list. The compiled values are Community and on the server farm, showing that the first plan was re-used.

That’s it for an introduction to sp_executesql. You can add more parameters, I just chose to use two for this demo.

Stay tuned!

To add onto yesterday’s post about which cardinality estimator (CE) your query will use, there’s an additional complexity. This specifically applies to cross database queries.

The database context in which you run the query will determine which CE is used. Let’s look at an example.

First, check on the master and StackOverflow2010 compatibility levels

Querying a 3-part name: StackOverflow2010.dbo.Users

USE [StackOverflow2010]
GO

SELECT TOP 100 * 
FROM StackOverflow2010.dbo.Users AS u
WHERE u.Reputation = 1001

This is our baseline query from yesterday. As expected, when we look in the properties of the SELECT operator in the execution plan, we’ll see CE version 70.

Changing database context to master

In this example, I’ll use the master database but it could be any other database.

USE [master]
GO

SELECT TOP 100 * 
FROM StackOverflow2010.dbo.Users AS u
WHERE u.Reputation = 1001

So, now that we’re running in master, we used CE version 140. Now in this execution plan, it didn’t make a difference which CE was used. I apologize for not having a good demo of that yet.

I hope this helps when looking at upgrading compatibility levels. The CE version will change based on what database context was used.

Stay tuned!

Happy Friday! We made it. Here’s something I came across while testing optimize for ad hoc for this week’s blog posts.

First, the (better) way to use sp_executesql

The rest of this post will be using ad hoc queries with optimize for ad hoc ON at the several level. But first, take a look at using a parameter inside sp_executesql and what it shows inside the plan cache.

I want to say that this is not the best way to use sp_executesql. Here’s my query and how I could properly wrap it in sp_executesql.

--Here's the ad hoc version
SELECT Score FROM Posts AS p
JOIN Users as u ON u.Id = p.OwnerUserId  
WHERE p.Id = 13
GO
--And here's it wrapped it in sp_executesql
--with a variable, @PostId instead of the literal, 13
EXEC sp_executesql N'SELECT Score FROM Posts AS p  
JOIN Users as u ON u.Id = p.OwnerUserId
WHERE p.Id = @PostId', N'@PostId int', '13'
GO

And here’s the plan cache after running those two queries, with optimize for ad hoc on.

SELECT TOP 10 
cache.size_in_bytes,
cache.objtype,
stat.execution_count,
stext.text,
splan.query_plan
FROM sys.dm_exec_query_stats as stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as stext
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) as splan
JOIN sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle

Okay, so this is as expected. The Adhoc query didn’t save the execution plan into cache, and the size is a lot smaller. But what if we didn’t replace 13 with the variable @PostId?

Leaving things as they are

Sometimes, we don’t have time to change the code. Sometimes the code is still using literals inside sp_executesql, essentially running ad hoc statements. Let’s take a look at that scenario.

--ad hoc version
SELECT Score FROM Posts AS p
JOIN Users as u ON u.Id = p.OwnerUserId  
WHERE p.Id = 13
GO


EXEC sp_executesql N'SELECT Score FROM Posts AS p  
JOIN Users as u ON u.Id = p.OwnerUserId
WHERE p.Id = 13'
GO

Here’s the results of our plan cache query.

Changing the order of statement – sp_executesql first

Now, you might wonder what happens if we change the order of the statements. Since I ran the sp_executesql as the second statement, maybe it cached that plan. Let’s run the sp_executefirst.

EXEC sp_executesql N'SELECT Score FROM Posts AS p  
JOIN Users as u ON u.Id = p.OwnerUserId
WHERE p.Id = 13'
GO

--ad hoc version
SELECT Score FROM Posts AS p
JOIN Users as u ON u.Id = p.OwnerUserId  
WHERE p.Id = 13
GO

So, why does sp_executesql cause ad hoc plans to be stored as full plans in cache?

I don’t know. I came across this while blogging and thought it was worth its own post. If you know why or know more, feel free to send me a message or leave a comment!

Stay tuned!

Yesterday’s post talked about single use plans. The statements I’m using are also called ad hoc queries. The alternative to ad hoc would be to create an object like a stored procedure or preparing the statement with a command like sp_prepare or sp_executesql.

The object type will show as “ad hoc” if you’re looking inside sys.dm_exec_cached_plans , which I’ll do later in this post.

Let’s add that DMV to our DMV queries. As a reminder, here’s how I generated my queries:

SELECT TOP 200 
N'EXEC(''SELECT Score FROM Posts AS p 
JOIN Users as u ON u.Id = p.OwnerUserId 
WHERE p.Id = ' + CAST(Id as nvarchar(200))  + ''')'
FROM Posts
ORDER BY Id

Okay, now looking in the plan cache:

SELECT TOP 10 
cache.size_in_bytes,
cache.objtype,
stext.text,
splan.query_plan
FROM sys.dm_exec_query_stats as stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as stext
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) as splan
JOIN sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
WHERE stat.query_hash = 0x5F56B6B5EC1A6A6F

The setting, optimize for ad hoc

I can change the server level setting to On for optimize for ad hoc workloads. Let’s turn that on and try again.

/*
Don't run this in Prod. Always test settings thoroughly before applying
*/
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE 
GO

Okay, and now I’ll re-run the test workload from above, and then the plan cache query. Here’s the results:

The size in bytes per entry is massively reduced, and these were small execution plans to begin with. However, we lost the entire query plan. That’s not the only cost, but that can have consequences.

Moral of the post

In my opinion, there’s two big downsides to optimize for ad hoc.

First, we lose the query plan information for single use plans. That means that any queries that parse the plan cache (for example, sp_BlitzCache) will not be nearly as useful if your workload is heavily ad hoc. The query plan will be stored if the query runs twice, so this isn’t a total loss.

Second, compiling a plan isn’t free. Optimize for ad hoc stores a stub, but the plan was still compiled for the query when it runs.

Overall, my opinion is that if you’re worried about the memory space used by single use plans, you’ll benefit from the reduced size from optimize for ad hoc workloads.

There’s better solutions, like increasing execution plan re-use. That’s the topic of a future post! Stay tuned.

A single use plan is an entry in the SQL Server plan cache that was only used once. When a query generates many similar entries in the plan cache, that query is using extra CPU to generate plans and wasting memory space to store them.

I’m going to focus on finding all single use plans in my plan cache. Then, in a later post, I’ll discuss how to reduce the number of plans.

We can find these plans by counting the number of entries in sys dm_exec_query_stats per query hash, where the plan has an execution count of 1.

Here’s a sample script:

SELECT query_hash,
COUNT(*) as number_of_entries
FROM sys.dm_exec_query_stats
WHERE execution_count = 1
GROUP BY query_hash
HAVING COUNT(*) > 1

Setting up single use plans

So I want a lot of single use plans. I wrote this SQL in StackOverflow2010 to generate a bunch of queries that will each take up one spot in the plan cache.

SELECT TOP 200 
N'EXEC(''SELECT Score FROM Posts AS p 
JOIN Users as u ON u.Id = p.OwnerUserId 
WHERE p.Id = ' + CAST(Id as nvarchar(200))  + ''')'
FROM Posts
ORDER BY Id

Running those queries on my test server, then running the script above it gives me this result:

So there’s 200 entries in the plan cache for this query. The plan cache can have up to 160,036 entries on 64-bit systems (source), so my test plan cache isn’t in any danger of running out of space.

Looking at the single use plans by query hash

Now let’s take a look at one of those queries. Take the query_hash from above, or one in your environment, and take a look:

SELECT TOP 10 stext.text,
splan.query_plan
FROM sys.dm_exec_query_stats as stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as stext
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) as splan
WHERE stat.query_hash = 0x5F56B6B5EC1A6A6F

That’s just 10 of the 200 entries in my plan cache which are identical except for the value used in the WHERE clause.

In another post, I’ll tackle ways to handle these duplicates and reduce the number of single plans in cache. Stay tuned!