Category: Transact-SQL

You have probably seen the recommendation to turn on the “optimize for ad-hoc workloads” setting. You might even have seen a more recent recommendation to set the database setting parameterization to forced (instead of the default which is simple). The aim of this post is to briefly describe each and then do some test with various settings.

I will not dive into each sub-area of this topic (plan caching, parameter sniffing, each setting, etc). There is loads of information out there, use your favourite search engine if there are areas mentioned here that you aren’t familiar with.

If you are unlucky you have application that submits queries as text to SQL Server and that text doesn’t use stored procedures, nor is it parameterized by the application. In short, the end result is that SQL server will recognize the query by producing a hash of the full text including your search arguments etc. The end result is typically lots and lots of execution plans for the same query. You have very little plan re-use (unless your users search for the very same things), and you litter the plan cache wasting precious memory.

Optimize for ad hoc workloads

Above setting exists at the instance level and since a few versions back also at the database level. SQL Server will not cache a plan for the first execution. It does save a hash of the text representation, though, so it can recognize if you execute the exact same text again and for second execution a plan will be saved. This can save memory. It will not save CPU.

Parameterization forced

This is a database setting that will make SQL Server parameterize most queries. This means that what you search for (etc) will be broken out as parameters and the same plan will be used even if you don’t search for the same values. This can save CPU and possibly also memory.

But you can end up with getting a plan which is good for some values but bad for some other values. This is what we refer to as a “parameter sniffing” problem. There are no free lunches. There has been some work done in this area in SQL Server 2022, but it is not as simple as “solving the problem”. See for instance this post by Brent Ozar.

How do these settings relate and putting some numbers on it

I want to actually tests things and see for myself instead of just reading some theoretical reasoning. This is risky since my very simple test isn’t representative for your query load. Keep that in mind. Having said that, here’s what I did:

I executed a query 30000 times. The query uses two search condition on the same table, and returns 0 rows. I’m interested in the plan aspect, not the query execution aspect.

Ideally I wanted to not return a resultset, but assigning the returned value to a variable caused the query to not be parameterized with forced parameterization. We don’t want to return 30,000 resultsets in SSMS, it takes ages, so the execution part was done using SQLCMD and some suitable command-line switches to minimize the output. Feel free to contact me if you have improvements to the test-bench I was using and I will possibly amend this blog post.

The plan generation is pretty cheap: full optimization with early termination (good enough plan found).

Here are my findings

Optimize for ad-hoc workloads	Parameterization forced	Time (seconds)	Memory used for plans (MB)
Off	Off	42	1640
On	Off	39	13
Off	On	3.2	470
On	On	3.3	3.4

Some things are less surprising:

• Less surprising
  • The huge savings in memory with optimize for ad-hoc on
  • The reduced execution time with forced parametrization on.

• More surprising
  • The large memory usage with optimize for ad-hoc off and forced parametrization on. Possibly some text representation of the query or some shell-plan causing this?
  • How well optimize for ad-hoc and forced parametrization play together

Anyhow, there you have it. Remember that this was a very simplified test. Feel free to grab the SQL below and tweak it. Let me know if you find something interesting.

/*
Create a proc that generates lots of execution plans
Each execution is in itself very cheap
Takes about 1.2 second per 1000 executions, i.e. 30000 executions takes about 40 seconds or less (for me)

We will be using this table: 
SELECT * FROM Person.PersonPhone

And this query something like either of below two:
DBCC FREEPROCCACHE
SELECT TOP(1) BusinessEntityId FROM Person.PersonPhone WHERE PhoneNumberTypeID = 1 AND ModifiedDate = '20120523'
DECLARE @b int SET @b = (SELECT TOP(1) BusinessEntityId FROM Person.PersonPhone WHERE PhoneNumberTypeID = 1 AND ModifiedDate = '20120523')

The first we use if we want to test forced parameterization, which I couldn't get to work without actually returning a resultset.
Run this from SQLCMD, not SSMS since SSMS will have a huge overhead in printing header for all resultsets returned (or configure SSMS to discard the result).

The second query can be used from SSMS, but it won't parameterize even when the database setting is forced.

The plan is simple enough and very cheap.
*/



/**********************************************************************************************
Setup
**********************************************************************************************/

USE Adventureworks
GO

DROP INDEX IF EXISTS PhoneNumberTypeID ON Person.PersonPhone
DROP INDEX IF EXISTS ModifiedDate ON Person.PersonPhone
GO

CREATE INDEX PhoneNumberTypeID ON Person.PersonPhone(PhoneNumberTypeID)
CREATE INDEX ModifiedDate ON Person.PersonPhone(ModifiedDate)
GO

/**********************************************************************************************
A version thet doesn't return a resultset, can be used from SSMS. 
it will NOT be parameterized with the database setting "Forced Parameterization"
**********************************************************************************************/
CREATE OR ALTER PROC CreateAdHocPlansNoResultset
 @noOfPlans int = 20000
AS
DECLARE 
 @i int = 1
,@sql nvarchar(1000)
,@dt datetime2(1) = SYSDATETIME()
,@ModifiedDate char(8) = CONVERT(char(8), DATEADD(YEAR, -10, GETDATE()), 112)

SET NOCOUNT ON

WHILE @i <= @noOfPlans
BEGIN
 SET @sql = 'DECLARE @b int SET @b = (SELECT TOP(1) BusinessEntityId FROM Person.PersonPhone WHERE PhoneNumberTypeID = ' + CAST(@i AS varchar(5)) + ' AND ModifiedDate = ''' + @ModifiedDate + ''')'
-- PRINT @sql
 EXEC(@sql)
 SET @i +=1
END

SELECT DATEDIFF(ms, @dt, SYSDATETIME())
GO

/**********************************************************************************************
--A version that DOES return an (empty) resultset, and that will be parameterized.
**********************************************************************************************/
CREATE OR ALTER PROC CreateAdHocPlansWithResultset
 @noOfPlans int = 20000
AS
DECLARE 
 @i int = 1
,@sql nvarchar(1000)
,@dt datetime2(1) = SYSDATETIME()
,@ModifiedDate char(8) = CONVERT(char(8), DATEADD(YEAR, -10, GETDATE()), 112)

SET NOCOUNT ON

WHILE @i <= @noOfPlans
BEGIN
 SET @sql = 'SELECT TOP(1) BusinessEntityId FROM Person.PersonPhone WHERE PhoneNumberTypeID = ' + CAST(@i AS varchar(5)) + ' AND ModifiedDate = ''' + @ModifiedDate + ''''
-- PRINT @sql
 EXEC(@sql)
 SET @i +=1
END

SELECT DATEDIFF(ms, @dt, SYSDATETIME())
GO


/**********************************************************************************************
Test

Run with below combinations:
	Optimize	Force
a	Off			Off
b	On			Off
c	Off			On
d	On			On

Time each execution, and check memory usage for plans
**********************************************************************************************/

--a
EXEC sp_configure 'optimize for', 0
RECONFIGURE
ALTER DATABASE Adventureworks SET PARAMETERIZATION SIMPLE
GO
--42 seconds, 1640 MB memory usage

--b
EXEC sp_configure 'optimize for', 1
RECONFIGURE
ALTER DATABASE Adventureworks SET PARAMETERIZATION SIMPLE
GO
--39 seconds,   13 MB memory usage

--c
EXEC sp_configure 'optimize for', 0
RECONFIGURE
ALTER DATABASE Adventureworks SET PARAMETERIZATION FORCED
GO
-- 3.2 seconds, 470 MB memory usage (the actual text respresentation of each query, I believe)

--d
EXEC sp_configure 'optimize for', 1
RECONFIGURE
ALTER DATABASE Adventureworks SET PARAMETERIZATION FORCED
GO
-- 3.3 seconds, 3.4 MB memory usage


/*
Run the tests and check the execution time, with above combination settings. 
Use either SQLCMD (adjust servername) or straight TSQL from SSMS, but the query wont be parameterized.
SQLCMD /S <servername> -d Adventureworks /Q"DBCC FREEPROCCACHE EXEC CreateAdHocPlansWithResultset @NoOfPlans = 30000" -h-1
*/
DBCC FREEPROCCACHE EXEC CreateAdHocPlansNoResultset @NoOfPlans = 30000


--Investigate plan cache

/*
I have my own below proc:
EXEC sp_showplans
*/

--Break-down per how many time the plans has been executed
SELECT 
 c.usecounts
,COUNT(*) AS antal
,SUM(c.size_in_bytes / (1024 * 1024.0)) AS size_in_MB
FROM sys.dm_exec_cached_plans AS c
GROUP BY c.usecounts
ORDER BY usecounts ASC

--Break-down per database
SELECT 
 c.usecounts
,DB_NAME(CAST(a.value AS sysname))
,COUNT(*) AS antal
,SUM(c.size_in_bytes / (1024 * 1024.0)) AS size_in_MB
FROM sys.dm_exec_cached_plans AS c
 CROSS APPLY sys.dm_exec_plan_attributes(c.plan_handle) AS a
 WHERE a.attribute = 'dbid'
GROUP BY c.usecounts, DB_NAME(CAST(a.value AS sysname))
ORDER BY usecounts ASC

Have you ever experienced a sudden jump an identity column in SQL Server? Yeah, SQL Server caches the identity values and if you have a hard shutdown, you have consumed a number of values from that cache.

Should you care about the gap? In most cases: no. The identity value should be meaningless. In many cases I think that it is just an aesthetic issue to not have these gaps. (I’ve seen cases where you do run into problems because of the gap, I should add – but not frequently.)

For the SEQUENCE object, we have the CACHE option to specify how many values to cache. I.e., max values we can jump if we have a hard shutdown.

For identity, we have the IDENTITY CACHE database scoped configuration, introduced in SQL Server 2017. Caching on or off. On is default. We also have trace flag 272, at the instance level.

However, disabling the caching isn’t free. I’ve seen numerous posts on disabling the cache and not getting gaps. But I didn’t find any elaboration on performance. Hence this article. If you’ve done performance testing for this or know about such article, please leave a comment!

The problem is as always how to set up a realistic test case. What would the typical table structure and insert pattern look like? How long is a piece of string? So I decided to skip that part and go for the most simplified case I could:

A table with an identity columns and one more int column. (The reason for that second column is to hopefully avoid any special handling for identity-only tables in SQL Server.) The insert is an insert with a subselect. I.e., all rows in one transaction.

You know this already: your mileage will vary!

Anyhow, for my test, I consistently had about 5 times better performance with identity caching turned on. IMO, this is at least enough to show that caching identity values *can* be have performance aspect to; not turn it off “just because”.

--Test performance penalty for lowering identity cache
SET NOCOUNT ON
USE master

DROP DATABASE IF EXISTS Identity_test
GO

CREATE DATABASE Identity_test
 ON  PRIMARY 
( NAME = N'Identity_test', FILENAME = N'C:\DemoDatabases\DbFiles\a\Identity_test.mdf' , SIZE = 500MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB )
 LOG ON 
( NAME = N'Identity_test_log', FILENAME = N'C:\DemoDatabases\DbFiles\a\Identity_test_log.ldf' , SIZE = 500MB , MAXSIZE = 2048GB , FILEGROWTH = 64MB )
GO

USE Identity_test
DROP TABLE IF EXISTS t

--Our identity table
CREATE TABLE t(c1 int IDENTITY(1,1), c2 int)

--Where we will log execution times
CREATE TABLE timing(run int, descr varchar(50), ms int)


ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(1, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO

WAITFOR DELAY '00:00:01'
GO

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(2, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO


ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(3, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO

WAITFOR DELAY '00:00:01'
GO

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(4, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO


ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(5, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO

WAITFOR DELAY '00:00:01'
GO

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms) 
VALUES(6, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
--TRUNCATE TABLE t
GO


--Result?
SELECT * FROM timing ORDER BY run

SELECT 
 AVG(CASE descr WHEN 'cache on' THEN ms ELSE NULL END) AS cache_on
,AVG(CASE descr WHEN 'cache off' THEN ms ELSE NULL END) AS cache_off
,CAST(
   CAST(AVG(CASE descr WHEN 'cache off' THEN ms ELSE NULL END) AS decimal(9,2)) / 
   AVG(CASE descr WHEN 'cache on' THEN ms ELSE NULL END) 
  AS decimal(9,2)) AS ratio
FROM timing

Now that SQL server 2019 is released I want to discuss one of the more interesting improvements, at least from a performance perspective. I believe that this alone can result in greatly improved performance and reduced resource usage. For some installations, I should add.

Just a couple of quick notes:

• This post is about scalar UDFs that does SELECT. A forthcoming post will be about scalar functions that doesn’t do SELECT.
• Jump towards the end to see a table with the performance differences. That is why you are here, after all! Then you can come back and read the introduction paragraphs.

For a couple of decades, “we all” have known that scalar functions are bad. Really bad. To the extent that they are verboten in many installations. Why are they so bad? For two reasons:

1. The function is called once per row. Imagine if you have a SELECT in there, and the function is called 100,000 times. You have now executed that SELECT 100,000 times.
2. But also the overhead of calling the function is significant. So, even if it does something very simple like adding two numbers the overhead of calling it many times can be huge.

Don’t believe me? Read on.

I don’t want to get too theoretical regarding how this work, digging into execution plans etc. But the big thing here in SQL server 2019 is that SQL server can in-line those functions for you! I won’t go into the pre-requisites for this, just check out the product documentation for those things. You will also see that this requires database compatibility level 150 (2019) and that the database option for this isn’t turned off.

I’m using the AdventureworksDW database, and two tables:

• DimProduct, 606 rows, 6 MB.
• FactResellerSalesXL, approx 11,000,000 rows. I have three versions of this.
  • Row-based clustered index. 2.48 GB.
  • Same as above but with page compression: 0.68 GB. I don’t use this table for my tests here.
  • Same as the first with a clustered columnstore index: 0,48 GB

UDF with I/O

Let’s start with a UDF that performs I/O. It is dead-simple, it will just summarize the sales amount from the fact table for a certain product. Here’s the first version:

CREATE OR ALTER FUNCTION ProdSalesAmt(@ProductKey int)
  RETURNS int
 AS
 BEGIN
     DECLARE @ret int
     SET @ret = 
         (
         SELECT SUM(d.UnitPrice * d.OrderQuantity) 
         FROM dbo.FactResellerSalesXL AS d
         WHERE d.ProductKey = @ProductKey
         )
     IF @ret IS NULL
         SET @ret = 0
     RETURN @ret
 END
GO

You probably say that the function can be simplified. So here is such a simplified version. The idea is to get rid of all procedural code constructs.:

CREATE OR ALTER FUNCTION ProdSalesAmt_Simple(@ProductKey int)
  RETURNS int
 AS
 BEGIN
     RETURN (
         SELECT ISNULL(SUM(d.UnitPrice * d.OrderQuantity), 0)
         FROM dbo.FactResellerSalesXL AS d
         WHERE d.ProductKey = @ProductKey
         )
 END
GO

Some of you know the trick to not use a scalar UDF but an Inline Table function instead. The calling query would have to use a CROSS APPLY, so the optimization isn’t transparent to the client code. Anyhow, here is such a version:

CREATE OR ALTER FUNCTION ProdSalesAmt_TVF(@ProductKey int)
  RETURNS table
 AS
     RETURN (
         SELECT ISNULL(SUM(d.UnitPrice * d.OrderQuantity), 0) AS SumSales
         FROM dbo.FactResellerSalesXL AS d
         WHERE d.ProductKey = @ProductKey
         )
GO

I’m going to test with different compatibility levels for the database:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150

And I’m also going to test with and without in-lining turned on.

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING  = ON 
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING  = OFF 

Automatic inlining requires compatibility level 150 and also that inining is turned on. I did not see any difference between turning off iniling one or the other way, so I will just report it as inlining turned on or off.

But what if we didn’t use a function in the first place? Sure, functions are a neat programming construct. But neat programming constructs and performance doesn’t necessarily go hand-in-hand – we learned the hard way. So, I will also use a simple join as well as a correlated subquery.

So here are the queries I will use:

-- Q1 Basic scalar function
 SELECT p.ProductKey, p.EnglishProductName, dbo.ProdSalesAmt(p.ProductKey) AS SumSales
 FROM DimProduct AS p
 ORDER BY SumSales

-- Q2 Simplified scalar function
 SELECT p.ProductKey, p.EnglishProductName, dbo.ProdSalesAmt_Simple(p.ProductKey) AS SumSales
 FROM DimProduct AS p
 ORDER BY SumSales

-- Q3 Inline TVF instead of scalar function
 SELECT p.ProductKey, p.EnglishProductName, s.SumSales AS SumSales
 FROM DimProduct AS p
  OUTER APPLY dbo.ProdSalesAmt_TVF(p.ProductKey) AS s
 ORDER BY SumSales

-- Q4 Join instead of calling a function
 SELECT p.ProductKey, p.EnglishProductName, SUM(f.UnitPrice* f.OrderQuantity) AS SumSales
 FROM DimProduct AS p
  LEFT OUTER JOIN FactResellerSalesXL AS f ON f.ProductKey = p.ProductKey
 GROUP BY p.ProductKey, p.EnglishProductName
 ORDER BY SumSales

-- Q5 Correlated subquery
 SELECT p.ProductKey, p.EnglishProductName, 
 ( SELECT SUM(d.UnitPrice * d.OrderQuantity) 
   FROM dbo.FactResellerSalesXL AS d
   WHERE d.ProductKey = p.ProductKey ) AS SumSales
 FROM DimProduct AS p
 ORDER BY SumSales

-- Q6 Join using a table with a columnstore index
 SELECT p.ProductKey, p.EnglishProductName, SUM(f.UnitPrice* f.OrderQuantity) AS SumSales
 FROM DimProduct AS p
  LEFT OUTER JOIN FactResellerSalesXL_CCI AS f ON f.ProductKey = p.ProductKey
 GROUP BY p.ProductKey, p.EnglishProductName
 ORDER BY SumSales

I measured three things. The execution time as shown in SSMS. I also used an Extended Event trace to capture duration and logical reads. I will limit significant figures for duration and logical reads to 3.

Query	Description	Inlining	Time	Duration	Logical reads
Q1	Basic UDF	N	3:23	204,000	200,000,000
Q1	Basic UDF	Y	0:32	32,100	35,600,000
Q2	Simplified UDF	Y	0:32	32,100	35,600,000
Q3	Inline TVF		0:32	33,200	35,600,00
Q4	Join		0:00	692	331,000
Q5	Join corr sub		0:00	708	331,000
Q6	Join col-store		0:00	153	4,960

Conclusions

Let us focus on the time it takes, the “Time” column above. This is the end-user perspective, and of is what matters in the very end.

Without inlining, the end-user would have time to grab a cup of coffee and chat with a friend while waiting. This is the pre-2019 behavior, assuming you didn’t do any optimization.

With inlining, the user can swap to the email program and check for new mails while waiting. We had to do manual work prior to 2019 to get from three and a half minute to half a minute. This meant re-writing the function and the SQL that called the function. SQL server 2019 basically does that for us! Note that I didn’t see any difference with simplifying the scalar function to only contain one SELECT.

But we could be smarter than that. For this simple example we could either do a join or a correlated subquery. It might not be that simple in all situations, but there can still be room for query optimizations, evidently. Going from half a minute to half a second is a big thing. Now the end-user will barely notice the execution time.

And imagine if the dev/dba created a columnstore index to support the query. Now we basically have instant result.

Also consider the resource consumption. I’m thinking about the logical reads column here. I didn’t measure CPU time for this test, but I would expect that it to some extent reflect the execution time and logical reads.

I will discuss a scalar UDF that doesn’t do SELECT (I/O) in a later blog post.

Disclaimers

You can do many more combinations than what I did. For instance scalar inlining with columnstore index. Etc. My goal here was not to test all possible permutations. Please give the others a try if you want!

This was only one example. I’m sure that there are examples where inlining doesn’t work that well. And perhaps also where the benefits are even grater. My aim here was to give it a try, with an example with was simple enough and something that I have seen in the wild (similar cases, that is).

I recently read a discussion whether RPC events add overhead compared to just submitting text. I got curious and did some testing, which I want to share.

Background, SQL events
Using most APIs, if you just submit a query to SQL Server, you will get what we call an SQL event (for instance in Profiler an SQL:BatchCompleted). The client app submits some text, SQL Server parses the text, and either creates an execution pland or re-uses a cached plan (if such exists, based on checksum of the query text submitted). Now, this is very straight forward. But there’s also problems, mainly risk for SQL Injection and bad query plan re-use. Your query probably has a WHERE clause, and if you just concatenate the values you will have in the WHERE clause, then you will submit different text each time. Different text means different execution plans – bad plan re-use. Also, if you have some malicious user or are under hacker-attack, then somebody can inject some query or part of query inside what you think would be only the search value (this is a classic).

Stored procedures as RPC events
Now, consider if we are using stored procedures. We might want to pass the proc name, and the values for each parameter, where we pass the parameter values as binary data. Do this right (for instance in ADO, you configure your command object’s CommandType property as being CommandType.StoredProcedure (instead of CommandType.Text which is default). This causes the batch be shown in Profiler as an RPC event, for instance RPC:Completed. So, consider executing the same procedure either by constructing a string such as “EXEC myProc @p1 = ‘20080223’, @p2 = ‘Stockholm'” as text (SQL event), or passing it as a stored procedure using parameter objects (RPC event). We expect RPC event to be more efficient, right? We’ll get to that in a moment.

Queries as RPC events
Now, we can also submit queries, without using stored procedures, so they are passed as RPC events. We do this by adding at least one parameter to the command object. This causes ADO.NET to use an RPC event and execute sp_executesql, parameterizing accoring to the parameter object for the command object. This causes your code to be safe from SQL injection and will allow for better plan re-use. I.e., in most cases a very good thing – second best to use stored procedures!

Non-parameterized queries as RPC events
Now, consider if we don’t want to parameterize our queries (we don’t have a WHERE clause, we just don’t want to, we use some programming layer which doesn’t allow for it, or we are just plan curious – like now). Can we still get RPC events? Yes, by adding a dummy parameter to the command object. We don’t have to use this command object in the query – just the existence causes an RPC event instead of SQL event. This allow us to compare SQL events to RPC event and look at only the overhead for the sp_executesql part.

Overhead for using sp_executesql
Below is some VB.NET code which does just that. For the first two, we use exactly the same query multiple executions and compare timing for doing them as SQL events or RPC events. For the second two, we change what produceID we search for (but not parameterizing the search condition) which causes different execution plans for each execution. I verified by counting execution plans that the first two uses the same execution pland and the second two generates a new plan for each iteration in the loop. Here’s the VB.NET code:

Imports System.Data
Imports System.Data.SqlClient
Module Module1

Sub Main()

Dim i As Int32, iterations As Int32 = 20000
Dim tickcount As Int64
Dim ProductID As Int32 = 43664
Dim ProdStatus As Int16
Dim sql As String

sql = "SELECT status FROM Sales.SalesOrderHeader WHERE SalesOrderID = "
Dim cn As New SqlConnection("SERVER=localhost\a;DATABASE=AdventureWorks2008;INTEGRATED SECURITY=TRUE")
cn.Open()

Dim utilCmd As New SqlCommand("DBCC FREEPROCCACHE", cn)
utilCmd.ExecuteNonQuery()

'Definitions
'**********************************************************************************************
'Same query every execution, SQL:BatchCompleted
Dim cmdAllText As New SqlCommand(sql + "43664", cn)
cmdAllText.CommandType = CommandType.Text

'Same query every execution, RPC:Completed
Dim cmdParmText As New SqlCommand(sql + "43664", cn)
cmdParmText.CommandType = CommandType.Text
cmdParmText.Parameters.Add("@dummy", SqlDbType.Int)
cmdParmText.Parameters("@dummy").Value = -9999

'Different query every execution, SQL:BatchCompleted
Dim cmdAllText2 As New SqlCommand()
cmdAllText2.Connection = cn
cmdAllText2.CommandType = CommandType.Text

'Different query every execution, RPC:Completed
Dim cmdParmText2 As New SqlCommand()
cmdParmText2.Connection = cn
cmdParmText2.CommandType = CommandType.Text
cmdParmText2.Parameters.Add("@dummy", SqlDbType.Int)
cmdParmText2.Parameters("@dummy").Value = -9999

'Execution
'**********************************************************************************************
Console.WriteLine("Same query every execution, SQL:BatchCompleted")
tickcount = Environment.TickCount
For i = 1 To iterations
ProdStatus = cmdAllText.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)

Console.WriteLine("Same query every execution, RPC:Completed")
tickcount = Environment.TickCount
For i = 1 To iterations
ProdStatus = cmdParmText.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)

Console.WriteLine("Different query every execution, SQL:BatchCompleted")
tickcount = Environment.TickCount
For i = 1 To iterations
cmdAllText2.CommandText = sql + i.ToString()
ProdStatus = cmdAllText2.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)

Console.WriteLine("Different query every execution, RPC:Completed")
tickcount = Environment.TickCount
For i = 1 To iterations
cmdParmText2.CommandText = sql + i.ToString()
ProdStatus = cmdParmText2.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)

Console.ReadLine()

End Sub

Sub PrintOuput(ByVal iterations As Int32, ByVal tickcount As Int64)
Console.WriteLine("Number of executions: " & iterations.ToString)
Console.WriteLine("Total exec time (ms): " & tickcount)
Console.WriteLine("Exec time per query (ms): " & tickcount / iterations)
Console.WriteLine("")
End Sub

End Module

And here’s a typical execution result from above code:

Same query every execution, SQL:BatchCompleted
Number of executions: 20000
Total exec time (ms): 1357
Exec time per query (ms): 0,06785

Same query every execution, RPC:Completed
Number of executions: 20000
Total exec time (ms): 1513
Exec time per query (ms): 0,07565

Different query every execution, SQL:BatchCompleted
Number of executions: 20000
Total exec time (ms): 2402
Exec time per query (ms): 0,1201

Different query every execution, RPC:Completed
Number of executions: 20000
Total exec time (ms): 14446
Exec time per query (ms): 0,7223

As you can see, there is an overhead to use RPC events and sp_executesql. I.e., if we don’t parameterize our command object, then we only pay with no gain. The overhead is definitely noticeable were we generate a new plan for each execution. An interesting aspect is that if I remove the call to DBCC FREEPROCCACHE, (so we get new plan for each iteration in the loop, but plans can be re-used from prior execution of the VB code), then this is much cheaper and closer to the others (about 5.7 seconds instead of 14 seconds). So, we do pay extra for sp_executesql especially when we generate a new plan.

Is above a weird example?
Yes, it is, and I want to emphasize that. Typically, you will use sp_executesql when you actually parameterize your queries. That will cause better plan re-use (and protect from SQL injection). Above is only to show whether sp_executesql has a cost – without considering the benefits.

How about using stored procedures?
Say you are using stored procedures, and wonder how different ways to execute a procedure compares? Let’s have a look. We can do it three ways (probably more, but below are the ones I’m interested in):

1. Build the proc command and pass it as a string. No parameter objects, no sp_executesql. You are still open to SQL injection. Don’t do this, we only want to show it for comparsion reasons. Profiler will show an SQL:BatchCompleted event with TextData something like “EXEC dbo.GetOrderStatusRes @SalesOrderID = 43664”.
2. Use parameters for your command objects, but you forget to define the command object of type CommandType.StoredProcedure. Profiler will show an RPC:Completed event with TextData something like “exec sp_executesql N’EXEC dbo.GetOrderStatusRes @SalesOrderID = @theID’,N’@theID int’,@theID=43664”.
3. The right way. Define the command object as of type CommandType.StoredProcedure, and of course add parameter object to the command object. Profiler will show an RPC:Completed event with TextData something like:
   “declare @p2 tinyint
   set @p2=5
   exec dbo.GetOrderStatusOut @SalesOrderID=43664,@Status=@p2 output
   select @p2”
   Note that the Profiler tool adds the declare, set and select parts, it is not part of what is submitted from the client app. The client app actually submit the paramaters as binary values. Profiler add this to be nice to us, so we can copy that text and execute it, read it, etc.

Anyhow, here’s the VB code:

Imports System.Data
Imports System.Data.SqlClient
Module Module1

Sub Main()

Dim i As Int32, iterations As Int32 = 20000
Dim tickcount As Int64
Dim ProductID As Int32 = 43664
Dim ProdStatus As Int16
Dim sql As String

Dim cn As New SqlConnection("SERVER=localhost\a;DATABASE=AdventureWorks2008;INTEGRATED SECURITY=TRUE")
cn.Open()

'Create the procedures we will execute
Dim utilCmd As New SqlCommand("", cn)
utilCmd.CommandText = "IF OBJECT_ID('dbo.GetOrderStatusRes') IS NOT NULL DROP PROC dbo.GetOrderStatusRes" & vbCrLf
utilCmd.CommandText += "IF OBJECT_ID('dbo.GetOrderStatusOut') IS NOT NULL DROP PROC dbo.GetOrderStatusOut"
utilCmd.ExecuteNonQuery()
utilCmd.CommandText = "CREATE PROC dbo.GetOrderStatusRes @SalesOrderID int AS SELECT status FROM Sales.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID"
utilCmd.ExecuteNonQuery()
utilCmd.CommandText = "CREATE PROC dbo.GetOrderStatusOut @SalesOrderID int, @status tinyint OUT AS SET @status = (SELECT status FROM Sales.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)"
utilCmd.ExecuteNonQuery()

'Run each once so it is in cache
utilCmd.CommandText = "EXEC dbo.GetOrderStatusRes @SalesOrderID = 43664"
utilCmd.ExecuteScalar()
utilCmd.CommandText = "DECLARE @s tinyint EXEC dbo.GetOrderStatusOut @SalesOrderID = 43664, @status = @s OUT"
utilCmd.ExecuteNonQuery()

utilCmd.CommandText = "DBCC FREEPROCCACHE"

'Definitions
'**********************************************************************************************
'Exec proc as string, SQL:BatchCompleted
Dim cmdAllText As New SqlCommand("EXEC dbo.GetOrderStatusRes @SalesOrderID = " & ProductID.ToString(), cn)
cmdAllText.CommandType = CommandType.Text

'Parameterize, but still as text, RPC:Completed with sp_executesql
Dim cmdParmText As New SqlCommand("EXEC dbo.GetOrderStatusRes @SalesOrderID = @theID", cn)
cmdParmText.CommandType = CommandType.Text
cmdParmText.Parameters.Add("@theID", SqlDbType.Int)
cmdParmText.Parameters("@theID").Value = ProductID

'Parameterize as stored procedure, RPC:Completed
Dim cmdRpc As New SqlCommand("dbo.GetOrderStatusOut", cn)
cmdRpc.CommandType = CommandType.StoredProcedure
Dim prmSalesOrderID As SqlParameter = New SqlParameter("@SalesOrderID", SqlDbType.Int)
prmSalesOrderID.Value = ProductID
cmdRpc.Parameters.Add(prmSalesOrderID)
Dim prmStatus As SqlParameter = New SqlParameter("@Status", SqlDbType.TinyInt)
prmStatus.Direction = ParameterDirection.Output
cmdRpc.Parameters.Add(prmStatus)

'Execution
'**********************************************************************************************
utilCmd.ExecuteNonQuery()
Console.ReadLine()

Console.WriteLine("Exec proc as string, SQL:BatchCompleted")
tickcount = Environment.TickCount
For i = 1 To iterations
ProdStatus = cmdAllText.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)
Console.ReadLine()

utilCmd.ExecuteNonQuery()
Console.WriteLine("Parameterize, but still as text, RPC:Completed with sp_executesql")
tickcount = Environment.TickCount
For i = 1 To iterations
ProdStatus = cmdParmText.ExecuteScalar()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)
Console.ReadLine()

utilCmd.ExecuteNonQuery()
Console.WriteLine("Parameterize as stored procedure, RPC:Completed")
tickcount = Environment.TickCount
For i = 1 To iterations
cmdRpc.ExecutteNonQuery()
Next
tickcount = Environment.TickCount - tickcount
Call PrintOuput(iterations, tickcount)

Console.ReadLine()

End Sub
Sub PrintOuput(ByVal iterations As Int32, ByVal tickcount As Int64)
Console.WriteLine("Number of executions: " & iterations.ToString)
Console.WriteLine("Total exec time (ms): " & tickcount)
Console.WriteLine("Exec time per query (ms): " & tickcount / iterations)
Console.WriteLine("")
End Sub

End Module

And here’s the result from a typical execution:

Exec proc as string, SQL:BatchCompleted
Number of executions: 20000
Total exec time (ms): 1810
Exec time per query (ms): 0,0905

Parameterize, but still as text, RPC:Completed with sp_executesql
Number of executions: 20000
Total exec time (ms): 1700
Exec time per query (ms): 0,085

Parameterize as stored procedure, RPC:Completed
Number of executions: 20000
Total exec time (ms): 1388
Exec time per query (ms): 0,0694

We can see that doing it the “proper” way is cheapest, but there’s not a big difference between the three. The first alternative is not good, though, since we aren’t protected from SQL injection. And since you then will be using parameter object anyhow, just go ahead and define the CommandType as stored procedure while you’re at it.