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. 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 performance problems quickly.

Related Posts