sp_prepare For Mediocre
You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)!
Great posts, Kendra!
Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector rather than the histogram for cardinality estimation.
Puddin’
Let’s create an index to make our query’s work easy!
1 |
CREATE INDEX ix_whatever ON dbo.Users (Reputation) INCLUDE (DisplayName); |
Now, to mimic the behavior of a JDBC query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @out INT; EXEC sys.sp_prepare @out OUTPUT, N'@r INT', N'SELECT COUNT(DisplayName) as records FROM dbo.Users AS u WHERE u.Reputation = @r;', 1; EXEC sys.sp_execute @out, 1; EXEC sys.sp_execute @out, 2; EXEC sys.sp_execute @out, 6; EXEC sys.sp_execute @out, 10; EXEC sys.sp_unprepare @out; GO |
The query plans for all of these have something in common. They have the exact same estimate!

You might be saying to yourself that the first parameter is sniffed, and you’d be wrong.
That estimate exactly matches the density vector estimate that I’d get with a local variable or optimize for unknown: SELECT (7250739 * 5.280389E-05)

You can validate things a bit by adding a recompile hint to the demo code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @out INT; EXEC sys.sp_prepare @out OUTPUT, N'@r INT', N'SELECT COUNT(DisplayName) as records FROM dbo.Users AS u WHERE u.Reputation = @r OPTION(RECOMPILE);', 1; EXEC sys.sp_execute @out, 1; EXEC sys.sp_execute @out, 2; EXEC sys.sp_execute @out, 6; EXEC sys.sp_execute @out, 10; EXEC sys.sp_unprepare @out; GO |
The plans for all of the recompiled queries get different estimates, and no estimate matches the 382 estimate we saw from the first round.

Am I saying you should recompile all of your queries to get around this?
No, of course not. Query compilation isn’t what you should be spending your SQL Server licensing money on.
You may want to not use JDBC anymore, but…
How Is sp_executesql Different?
Well, sp_executesql “sniffs” parameters.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @sql NVARCHAR(MAX ) = N'' SET @sql += N'SELECT COUNT(DisplayName) as records FROM dbo.Users AS u WHERE u.Reputation = @r;' EXEC sys.sp_executesql @sql, N'@r INT', 1; EXEC sys.sp_executesql @sql, N'@r INT', 2; EXEC sys.sp_executesql @sql, N'@r INT', 6; EXEC sys.sp_executesql @sql, N'@r INT', 10; GO |
If I run my demo queries in this order, the plan for Reputation = 1 gets cached and reused by all the other calls.

If I change the order so Reputation = 2 runs first, the plans change (after clearing the plan out of the cache, of course).
Now they all reuse that plan:

Why Is One better?
I put together this handy chart!

I’m not smart enough to get a formatted table like this into a web page.
I’m a bad DBA.
Thanks for reading!
UPDATE:
The admirable and honorable Joseph Gooch notes in the comments that you can configure this with the jTDS JDBC driver:
prepareSQL
(default –3
for SQL Server,1
for Sybase)This parameter specifies the mechanism used for Prepared Statements.
Value Description 0
SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower) 1
Temporary stored procedures are created for each unique SQL statement and parameter combination (faster) 2
sp_executesql is used (fast) 3
sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)
Though I’m not too thrilled that sp_prepare is called “faster”.
And! That similar options are available in the 6.1.6 preview of the Microsoft JDBC drivers.
10 Comments. Leave new
You can change this behavior in the JDBC drivers….
See #1 here , for MS Driver per 6.1.6-preview
https://github.com/Microsoft/mssql-jdbc/wiki/PreparedStatement-metadata-caching
For JTDS, you can set the prepareSQL= option in the connection string to 2, or something else like 1 or 0.
http://jtds.sourceforge.net/faq.html
Joseph — that’s great to know! I’ll add that to the post.
Hi Erik,
great post as always! I am excited as this seems to be exactly an issue I spotted some days ago and didn’t really understand with a query plan having a ridiculous low cardinality estimate. Definitely something I am going to test soon. Just one question: Is setting 1 for the JDBC Driver (temproary stored procedures) the same thing as Tara suggested for Troubleshooting Parameter Sniffing issues on https://www.brentozar.com/archive/2018/03/troubleshooting-parameter-sniffing-issues-the-right-way-part-3/?
Since I got Brent into this… 🙂 I’ve read through the jTDS source. (TdsCore.java and JtdsConnection.java)
From his code above:
SET @sql += N’SELECT COUNT(DisplayName) as records
FROM dbo.Users AS u
WHERE u.Reputation = @r;’
jTDS would build:
create proc #jtds_0000xx @P0 int as
SELECT COUNT(DisplayName) as records
FROM dbo.Users AS u
WHERE u.Reputation =@P0
(where 0000xx is generated from a sequence number)
And replace your executions with:
EXEC #jtds_0000xx @P0=1
Based on that it does look like what Tara suggested, without the recompile hint.
This used to be the bane of my existence some weeks. An application would work and for no reason one day running thousands of these would suddenly be slow and a process would crawl to a halt.
In your JPG table, did you mean “Can call victim to parameter sniffing” or “Can fall victim to parameter sniffing”?
Yeah, that’s a typo. Sorry about that!
Is anyone else having heart palpitations over the description for option 0 (SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower))?? This sounds like a direct and definitely obscured invitation to SQL Injection.
Steve – yeah, that doesn’t sound good. I don’t really have a way to test it though.
I’ve been looking at this for PDO_SQLSRV PHP MS SQL driver. We switched on direct statement (does sp_execute) but MS advises otherwise saying sp_prepare is better see link?
https://docs.microsoft.com/en-us/sql/connect/php/direct-statement-execution-prepared-statement-execution-pdo-sqlsrv-driver?view=sql-server-ver15