Nothing, Nothing, Nothing
Aside from the obvious perils of SQL Injection, parameterizing queries can be helpful in other ways.
Not to downplay SQL Injection at all, it’s just that fixing code is only one part of the equation. Vendor applications often need to do some pretty administrative things, and require elevated permissions.
Just last week I was looking at an application that had a stored procedure which took a parameter that was the name of an executable. There were no checks on the name. You could put in quite not-figuratively anything you wanted, and xp_cmdshell would run it.
All the parameterization in the world won’t help that.
Well, you get what you get.
Of Parameters And Plans
This can happen in three common ways:
- You have an interface that accepts user-entered values
- You have an ORM where you haven’t explicitly defined parameter types and precisions
- You have dynamic SQL where values are concatenated into strings
Since I spend all my time in the database, I’m going to show you the third one. I wouldn’t normally format a query like this, but I’m trying to keep it web-friendly.
DECLARE @StartDate DATETIME = '20130101'; WHILE @StartDate < '20140101' BEGIN DECLARE @NoParams4u NVARCHAR(MAX) = N' SELECT SUM(c.Score) AS TotalScore FROM dbo.Comments AS c WHERE c.CreationDate BETWEEN CONVERT(DATETIME, ''' + RTRIM(@StartDate) + ''') AND CONVERT(DATETIME, ''' + RTRIM(DATEADD(DAY, 11, @StartDate)) + ''') AND 1 = (SELECT 1); '; EXEC sys.sp_executesql @NoParams4u; RAISERROR('%s', 0, 1, @NoParams4u); SET @StartDate = DATEADD(DAY, 11, @StartDate); END GO
To make things interesting, I’ve created a non-covering index on the Comments table:
CREATE INDEX c ON dbo.Comments(CreationDate);
I’ve also had to use an oddly specific number of day increment in order to get some good plan variety because of that index. If you ever wonder why some blog posts take three hours to write, the most likely cause is finding the right number.
The first thing you should notice is that even using sp_executesql doesn’t help when our code isn’t parameterized. The second thing you’ll probably notice is 1 = (SELECT 1) at the end of the query. Past me has answers to all those questions, if you click on the links.
If I run that loop, I get back 34 results. Each query gets an individually compiled query plan, though there are only three “different” plans used.
Notice that each plan has different literal date values passed in to it, and different estimates. If we created a covering index, we’d get plan stability across executions, but then we’d still have to compile it when these literal values get passed in. I did it like this to reinforce my point.
We can validate that by looking in the plan cache and surrounding DMVs, using sp_BlitzCache.
If you have code like this, one good way to find culprits is by running it like this:
EXEC sp_BlitzCache @SortOrder = 'query hash';
A zoomed-in snapshot of the part of the results that prove my point looks about like this:
This is just the top 10 results. You can see a warning about multiple plans, and that each query has a single execution.
I mentioned before that if we add a covering index, we’ll get plan stability. That’s true, but specific to this demo on SQL Server 2019, there’s an alternate plan available for executions that qualify for Batch Mode On Rowstore (BMOR):
In prior versions of SQL Server in row mode only plans, we could only have a stream aggregate. But even getting the same plan most of the time, we still need to compile it every time. SQL Server still thinks each of these queries is “new” and needs to get a plan compiled.
There are two options for fixing this. If you need to do it quickly, at scale, the database level option FORCED PARAMETERIZATION can take care of most of these problems. Just make sure you read up on the limitations. To fix it for just a couple problem queries, you need to fix the dynamic SQL.
DECLARE @NoParams4u NVARCHAR(MAX) = N' SELECT SUM(c.Score) AS TotalScore FROM dbo.Comments AS c WHERE c.CreationDate BETWEEN @StartDate AND DATEADD(DAY, 11, @StartDate) AND 1 = (SELECT 1); '; EXEC sys.sp_executesql @NoParams4u, N'@StartDate DATETIME', @StartDate;
I’m not concatenating values into the string anymore, and I’m passing the @StartDate value in when I execute the dynamic SQL.
One point I want to make is that it’s generally safe to do date math on the parameter. I’m not doing date math on the column, which would generally be a bad idea.
But anyway, now our plan gets used 34 times.
In this case, plan reuse works out well. Every query is looking at a sufficiently narrow range of data to have it not matter, and the longest running execution is around 31ms.
But what about when that doesn’t work out? When can parameterization backfire? We’ll find out tomorrow!
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Starting SQL: How Parameters Can Change Which Indexes SQL Server Chooses
- Starting SQL: What’s The Difference Between Parameters And Local Variables In SQL Server?
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- SQL Server 2017 CU 30 Doesn’t Actually Fix The Problem With Views And Parameters