SQL Server 2022: FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION

Mouthful

---

SQL Server 2022 is kind of a boring release. I had high hopes that it would be a rich environment for new blog material, like other releases have been (Except SQL Server 2014. We don’t talk about SQL Server 2014.), but for performance tuners, it’s largely just some more layers of crap tacked on top of of an already complex set of adaptations and automations to sift through when tracking down performance issues.

One thing that apparently hasn’t caught anyone’s eye is the FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION database scoped configuration, which could come in handy when troubleshooting parameter sniffing problems that… SQL Server 2022 claims to solve.

Well, okay then. It also comes with this horrifying, terrifying, sky-is-falling note:

The FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION database scoped configuration option isn’t meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. Using this database scoped configuration option will introduce additional and possibly significant CPU and memory overhead as we will create a Showplan XML fragment with runtime parameter information[…]

So, I guess don’t flip this on if you’re already having CPU and memory problems potentially caused by parameter sniffing scenarios and you need to troubleshoot long running queries?

Hm. I guess I can see why this isn’t lighting the blogopshere on fire.

Enablement

---

If you’re running SQL Server 2022, and you’re feeling brave, you can flip this thing on like so:

ALTER DATABASE SCOPED CONFIGURATION 
    SET FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = ON;

Now, the big question becomes: how do you see all this super helpful information at the cost of additional and possibly significant CPU and memory overhead?

You may have some meme scripts that you found on the internet that do things way worse than sp_WhoIsActive, but if you want to see these goodies you’ll need to hit the dm_exec_query_statistics_xml DMF, which… your meme scripts probably don’t do.

Sorry about that.

But you can do this, which is relatively simple and easy even for the most steadfast meme script users:

EXEC sp_WhoIsActive 
    @get_plans = 1;

Now, when you look at the properties of the root plan operator, you should see a parameter list like this:

Which has both the compile and run time values for any parameters your query was supplied. Keep in mind this won’t work with local variables, because they’re not parameters ;^}

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.