Forced Parameterization Extended Events That Don’t Work In SQL Server

Posted on by Erik Darling

Would I Could I

There are a couple Extended Events that I’ve tried to get working at various times:

  • forced_param_clause_skipped_reason: Fired for every clause that was skipped during forced parameterization.
  • forced_param_statement_ignored_reason: Fired when forced parameterization was not applied for the whole statement.

Mostly because I wanted to see if the list of limitations on this ancient documentation page held up, or if there were new ones.

Somewhat curious was that there’s stuff in dm_xe_map_values for them.

SELECT
    map_value
FROM sys.dm_xe_map_values
WHERE
    name LIKE '%forced_param_clause_skipped_reason%';

SELECT
    map_value
FROM sys.dm_xe_map_values
WHERE
    name LIKE '%forced_param_statement_ignored_reason%';

forced_param_clause_skipped_reason:

+---------------------------+
|         map_value         |
+---------------------------+
| CheckSum                  |
| ConstFoldableExpr         |
| EcDMLOutput               |
| EcDoubleColonFunctionCall |
| EcGroupBy                 |
| EcHaving                  |
| EcLike                    |
| EcOpenRowset              |
| EcOrderBy                 |
| EcSelectList              |
| EcTableSample             |
| EcTopOrPaging             |
| EcTSEqualCall             |
| None                      |
| StyleConvert              |
| XvtDate                   |
| XvtGuid                   |
| XvtNull                   |
+---------------------------+

forced_param_statement_ignored_reason:

+------------------------------------+
|             map_value              |
+------------------------------------+
| AnsiNullsOff                       |
| AnsiPaddingOff                     |
| BucketEndStatementNoVarsBucketized |
| BucketEndStatementUnreplacedVar    |
| BucketVarCursor                    |
| BucketVarNotParameter              |
| BucketVarOutput                    |
| BucketXvtEmpty                     |
| BucketXvtXml                       |
| Cursor                             |
| Error                              |
| HintCompileVarValue                |
| HintRecompile                      |
| HintSimpleParam                    |
| InsideFunction                     |
| InsideReplProc                     |
| InsideStoredProc                   |
| InsideTrigger                      |
| InsideView                         |
| Max                                |
| MaxVars                            |
| None                               |
| QueryStoreHintSimpleParam          |
| RegularPlanGuide                   |
| ReplacedTooMuchConstants           |
| TableVariable                      |
| Variable                           |
| VariableAssignment                 |
| WhereCurrentOf                     |
+------------------------------------+

Ain’t No Love

Unfortunately — and I’ve confirmed recently with support — these events don’t actually fire for anything.

They’re just empty shells, but at least there’s some interesting details in the DMVs about what might work someday, and the reasons that just might fire.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts