Celexsa Developer Knowledgebase

How to Reduce the CPU Overhead of Dynamic SQL

Written by

Celexsa Team

in

,

Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string.

For frequently executed queries, “EXEC sp_executesql @sql” is a good choice but at the risk of encountering parameter sniffing issues.

For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated plan cache with lots of single-use execution plans.

Let’s compare the performance difference between “EXEC (@sql)” and “EXEC sp_executesql @sql” for a frequently executed query.

SELECT COUNT(*) 
FROM Posts
WHERE 
  PostTypeId NOT IN (2, 3, 4)
  AND CreationDate BETWEEN @d AND DATEADD(dd, 5, @d)
  AND CommentCount IN (0, 1)
  AND OwnerUserId = 0;

The query is very fast: takes less than a millisecond to compile and execute.

I usually use SQLQueryStress to load test a query, but it couldn’t deliver what I needed. It maxes out at 200 threads for a single instance. You can run multiple instances of it to get past 200 threads, but each was using a lot of CPU for this load test. Erik pointed me to Microsoft’s ostress utility instead.

EXEC (@sql)

Let’s look at the most common way to execute dynamic queries first: EXEC (@sql). The fact that it’s the most common doesn’t mean it’s the right way or the wrong way.

CREATE PROC Test1
AS
SET NOCOUNT ON;

DECLARE @sql nvarchar(1000), @d datetime;

--get a random date, 2017-12-03 is the newest date in this version of the database
SET @d = DATEADD(mi, -CAST(RAND() * 1440 AS INT), DATEADD(dd, -CAST(RAND() * 500 AS INT), '2017-12-03 05:01:10.747'));
--PRINT @d;

SET @sql = '
SELECT COUNT(*) 
FROM Posts
WHERE 
  PostTypeId NOT IN (2, 3, 4)
  AND CreationDate BETWEEN ''' + CAST(@d AS varchar(20)) + ''' AND ''' + CAST(DATEADD(dd, 5, @d) AS varchar(20)) + '''
  AND CommentCount IN (0, 1)
  AND OwnerUserId = 0;
'

--PRINT @sql;
EXEC (@sql);

You may wonder why I’m calculating @d inside the proc and not just sending in a value to an input parameter. It was just to make the calls to ostress easy. I could have used a script file via the -i parameter rather than executing a single batch via the -Q parameter. Besides, this is my blog post. I’ll do what I want.

Before starting the test, I cleared out the plan cache so that when I show you the plan cache bloat you won’t wonder what was already in there.

DBCC FREEPROCCACHE;

Time to run the load test. I decided on 50 threads with 15000 iterations after running a bunch of tests. I wanted enough threads to cause high CPU utilization and enough iterations to run sp_BlitzFirst with a 30-second sample.

ostress.exe -SServer1\SQL2017 -dStackOverflow -Utest -Ppassword1 -n50 -r15000 
-q -Q"EXEC Test1" -oC:\temp\ostress

After a minute, I ran sp_BlitzFirst to see what was going on in SQL Server.

EXEC sp_BlitzFirst @ExpertMode = 1, @Seconds = 30;

Though not horrible waiting time, SOS_SCHEDULER_YIELD is the primary wait here.

CPU was pegged at 100% during this load test.

After the test completed, I examined the plan cache using Kimberly‘s handy plan cache query.

The plan cache totaled 2.3GB, which isn’t that big. But it’s big when you consider that each of the adhoc plans is tiny. Most of the 36,000 adhoc plans were only used once. Why bother storing them in memory if they aren’t going to be reused?

EXEC sp_executesql @sql

Now let’s look at the other way to execute dynamic queries: EXEC sp_executesql @sql.

CREATE PROC Test2
AS
SET NOCOUNT ON;

DECLARE @sql nvarchar(1000), @d datetime, @paramdef nvarchar(100);

--get a random date, 2017-12-03 is the newest date in this version of the database
SET @d = DATEADD(mi, -CAST(RAND() * 1440 AS INT), DATEADD(dd, -CAST(RAND() * 500 AS INT), '2017-12-03 05:01:10.747'));
--PRINT @d;

SET @sql = N'
SELECT COUNT(*) 
FROM Posts
WHERE 
  PostTypeId NOT IN (2, 3, 4)
  AND CreationDate BETWEEN @d AND DATEADD(dd, 5, @d)
  AND CommentCount IN (0, 1)
  AND OwnerUserId = 0;
'

SET @paramdef = N'@d datetime';  
--PRINT @sql;
EXEC sp_executesql @sql, @paramdef, @d = @d;

I again cleared out the plan cache, but this time so that we can see the difference between the two load tests.

DBCC FREEPROCCACHE;

I then ran the load test again but calling Test2 this time.

ostress.exe -SServer1\SQL2017 -dStackOverflow -Utest -Ppassword1 -n50 -r15000 
-q -Q"EXEC Test2" -oC:\temp\ostress
EXEC sp_BlitzFirst @ExpertMode = 1, @Seconds = 30;

SQL Server was pretty bored with this workload even though it was running more queries per second than the previous test – not much waiting time for anything really.

CPU averaged around 45% during this load test.

Look at that tiny plan cache!

The roundup

ostress settings: threads=50, iterations=15000

Should you just use sp_executesql always?
If you have high CPU utilization and run frequently-executed dynamic queries with “EXEC (@sql)”, consider using “EXEC sp_executesql @sql” instead. You’ll need to be aware of parameter sniffing issues, but you should see a drop in CPU utilization and plan cache size.

Parameter sniffing issues are no joke. I have lost hundreds of hours troubleshooting parameter sniffing issues over the past 15 years. Know how to identify if parameter sniffing is the problem and how to troubleshoot it.

Don’t default to using sp_executesql though. If a query is not executed frequently, “EXEC (@sql)” can work just fine. Just be sure to monitor the ad-hoc plans in the plan cache.

Original Source: https://www.brentozar.com/archive/2018/05/using-dynamic-sql-frequently-executed-queries/

 

More posts