Forced Parameterization Extended Events That Don’t Work

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!



Leave a Reply

Your email address will not be published. Required fields are marked *