My Wish List for SQL Server Performance Features
There are a lot of shopping days left before Christmas, and even more before the next version of SQL Server ships, so might as well get my wish list over to Santa now so the elves can start working on ignoring them.
My work focuses on performance tuning, so that’s what my wish list focuses on, too. They really are wishes, like I-want-a-pony, because I know I’m discussing some stuff that’s easy to describe, but really challenging to implement.
Forced Parameterization v2: The original implementation of this feature helped mitigate the multiple-plans-for-one-query issue, but it has a ton of gotchas, like not fully parameterizing any partially parameterized queries, or the select list of select statements. I still hit a lot of clients with those problems in their queries, making monitoring tools, Query Store, and the plan cache way less useful, and I’d love to see forced parameterization updated to fix these additional issues. If you search the the SQL feedback site for forced parameterization, there are a few dozen issues that reference it in various ways, including plan guide challenges.
Forced Parameterization v3, Handling IN Lists: When you use Contains() in a LINQ query, it gets translated into a T-SQL IN clause. This list can have different numbers of values depending on how many things you’re looking for – maybe you’re looking for just 1 value, or 10, or 100. Unfortunately, even when forced parameterization catches these, it still builds different plans for different quantities of values – like 1 value, 10 values, and 100 values all produce different plans in the cache – again, making monitoring tools, Query Store, and the plan cache way less useful. I would love the ability to just build one plan for an IN list, regardless of the number of values.
Better Approach to Query Hash: The whole reason I’m focusing on Forced Parameterization here is that SQL Server compiles different execution plans for each submitted query string. Even differences in spacing and casing cause different plans to be cached in memory – again, as discussed in this multiple-plans-one-query post. What if the optimizer was just smarter about recognizing that these two queries are really the same thing, and only caching one plan instead of building two separate plans?
|
1 2 3 4 |
SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Brent Ozar'; GO SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Lady Gaga'; GO |
Cost Threshold for Recompile: If a query plan’s cost is higher than X, recompile it every time rather than caching the plan. If the query cost is 5000 Query Bucks, it’s worth the extra 1-10 seconds to compile the dang thing again before we run a giant data warehouse report with the wrong parameter plan. It’s basically like adding a Query Store hint for OPTION (RECOMPILE), but doing it automatically on expensive queries rather than trying to play whack-a-mole. Here’s my feedback request for it.
Execution Threshold for Recompile: If a query has run 100 times, asynchronously compile a new plan for it, but this time around, take a lot more time to build the plan. Don’t do an early termination of statement optimization to save a few milliseconds – we’re serious about running this query. Use the execution metrics that have piled up over those 100 executions to think about whether this is really small data or big data. This is challenging to do, I know, because it means SQL Server would need a task list (like plans to be recompiled), plus an asynchronous way of processing those tasks, plus a way to know when it’s safe to run those tasks without impacting end user activity. It would also require a lot of new monitoring to know if we’re falling behind on that task list, and ways to identify which plans were re-thought, and persistence of those “better” plans in places like Query Store. Here’s my feedback request for it.
Configurable Statistics Size: SQL Server’s stats histogram size has been frozen at a max of 201 buckets since the dawn of time. In the age of big data, that’s nowhere near enough for accurate estimates, as explained in this Query Exercise challenge post and the subsequent answer and discussion posts. I wish SQL Server would switch to a larger statistics object by default, perhaps jumping to an extent per stat for large objects rather than a single page, or a configurable stats object size. This would be painful to develop, for sure – not only would it affect building the stats, but it would also impact everything that reads those stats, like PSPO which needs to detect stats outliers. Here’s the feedback request for it.
Update Statistics Faster: In SQL Server 2016, Microsoft added parallelism during stats updates, and then promptly ripped it back out in 2017, as the documentation explains in a note in the sampling section. Forget parallelism on a single stat: I want SQL Server to do a single parallel pass on a table, updating all of the statistics on that table at the same time, instead of doing separate passes of the table for each and every statistic on it. Think merry-go-round scans meets stats updates. Here’s the feedback request for it.
DDL Change Logging: When any object is modified – table, index, stored proc, database setting, server-level setting, login – have an API hook or call of some kind. Send notifications to an API to log that action: who did it, what object was changed, and what the change was. This would make true source control integration and alerting easier, not just for the database, but for the server itself, getting us closer to the point of being able to rebuild existing servers from some kind of source of truth. Yes, I know the “right” thing to do is make people check their own changes into some kind of source control system first, and then use that source control to build the server and the database, but in reality, that poses both organizational AND technical problems that most organizations can’t fix. We need a change logging system so we can at least be reactive. There have been multiple feedback requests for this over time and they’ve all gotten archived, but ever the optimist, here’s my new feedback request for it.
That last one isn’t technically a performance feature, but… if people can make changes in a production system without easy alerting, logging, and source control, then it’s a performance issue in one way or another, because people gonna break stuff. Users gonna use.
18 Comments. Leave new
Great suggestions. Now if Microsoft will renew their cert on the feedback site (“The certificate for feedback.azure.com expired on 3/21/2026.”), we can (securely) add our vote for them (yeah, not something that _needs_ to be secured behind SSL, but if the rest of us have to keep an eye on such things, then so should MS, y’know?).
I really like the idea of “Cost Threshold for Recompile” , “Execution Threshold for Recompile” and “Configurable Statistics Size”. Can’t vote as Daryl said, the SSL certificate expired
YES to DDL change logging. Especially for smaller shops where a code control system is still a nice-to-have.
Give me a query option or whatever to indicate a match quote when joining two table.
Example:
I create a #tmp table with a subset of my large dbo.order table. Of course every single order_id in the #tmp has a match in the dbo.order.
But when I join #tmp and dbo.order it assumes, that only a small subset of my tmp-rows will have a match in the order table, so it works with completely wrong estimates.
Of course theoretical I could create a foreign key on the #tmp, so that SQL Servers knows, that they are a 100% match, but nobody would do this.
—
Another nice feature – I’d like to indicate, how many rows of a table I expect. For example I may want to process an imported file with a specific file_id. And since the file had a trailer line (or I counted the rows during the import) I know, that it contains 1.25 mio rows, while another file may have only 5k rows and the next one 3 mio.
Splitting my workload into batchgroups with e.g. ~1 mio lines would be another scenario for this hint.
Ooo, interesting!
Hi Thomas
What if you create an index on order_id column in#tmp table? It should give close estimates , am I missing something?
See it from SQL Server side.
All it knows is that there is a #tmp with a column order_id that contains e.g. 50k row within a range of x to y (after index creation it has also an idea about the distribution).
But it didn’t know that every single one of those numbers has it equivalent in the dbo.orders table, so it assumes, that only a more or less low percentage of those 50k rows fits an existing order, so it guesses that there are just maybe 10k matches after the join.
A real world example:
Assume that you get a list of 100 names. And now you have to match it with the people who works in a plant.
Without any other information, you would assume that maybe 5 or 10 of those people works in that plant. It would not help you in any way, regarding this estimate if the list is ordered or not. And it wouldn’t help you, if you have valid statistics on that list, e.g. that there are 5 people whose name starts with A and just one with its name beginning with an Y.
But when someone tells you, that this list of names was from the HR department and contains only existing workers at that plant, you would estimate, that you’ll find 100 matches (or maybe just 95 because of sickness, vacation or whatever).
And with the correct estimate you would book a much larger room (= reserving more RAM) and maybe get some coworkers to help you (parallelism) etc.
At one point I really wanted multiple tempDBs but either I have moved on to places where tempdb isn’t abused as much in reports or tempdb has been more optimized, I haven’t had tempdb issues recently other than a place that ran SQL Server on sata disk a couple years ago.
Out of curiosity – how would it have helped, when you had multiple TempDBs? Besides having multiple files for the TempDB that is a best practise since ages.
BTW: you can have some sort of multiple TempDBs, when you stop creating #tmp tables but create a real table e.g. in the WORKER database to store temporary results. But of course you have to be way more aware of naming conflicts, so your table may not just be #files but must be more specific as dbo.files_MyDB_dbo_usp_import_files
Hey Brent,
I got tired of the problems with SqlCommand in C# so I wrote my own Microsoft.Data.SqlClient that overrides the Microsoft .dll but fixes the issue with hashes. I use SqlParser to properly case the SQL, remove the excess white space and parameterized the query. I also use Roslyn to determine where that query came from so that my version of SqlCommand reuses the new query without having to go through the process of reparsing the query. Not entirely perfect but it does handle a huge number of your queries that uses SqlCommand.
I also have a Roslyn app that finds these SqlCommand issues in a C# solution and lists those where those issues are so they can be resolved. My SqlCommand is a nice replacement that can be plugged into a project without modifying anything than a Global Using but needs to get done is to fix the C# code. Plenty of developers are lazy and just build the query string and don’t parameterize. These issues need to be pointed out to the development manager so they can be fixed. These types of fixes are very good as “Low Hanging Fruit” that can be done if a developers finishes their task on their sprint early.
Cool! Can you drop a link to the GitHub repo for it?
Brent says that Cost Threshold For Recompile is “basically like adding a Query Store hint for OPTION (RECOMPILE), but doing it automatically on expensive queries”. Has anyone actually tried doing that? Like, make an Agent Job that runs every minute and hints every query over a certain cost with OPTION (RECOMPILE)? It should be pretty easy to implement with the SQL Server 2022 Query Store hints.
Great List Brent…Thank you!
As for DDL Change Logging: I think there is a workaround using Event Notifications and Service Broker. Of course that would mean a bit of legwork for then calling the APIs and stuff but if people are really motivated to do this it should be doable…especially with now everyone doing things with AI right? Or am I missing something?
That is quite a bit of legwork if we’re honest.
Hi Brent, until I zoomed in, I thought that you had stuck a giant furry pom pom on the nose of your car. Maybe an idea for next year? 😀
HA! A lit-up Rudolph nose would be great!
[…] My Wish List for SQL Server Performance Features (Brent Ozar) […]
Forced Parameterization v3, Handling IN Lists: Maybe instead of using the IN List to form one plan, just explicitly parameterize the query, but its LINQ right 🙂