ktaranov/sqlserver-kit

Add trace flag 8759

BlitzErik opened this issue · 10 comments

BlitzErik commented

Trace flag 8759 will detect and write part of the query to the error log when it has been autoparameterized.

ktaranov commented

Hi, @BlitzErik . Great thanks for issue, added via 3450944
Do you have any additional information about this flag: Microsoft KB, blog post or demo script how to use it?

BlitzErik commented

Not yet. I've only found it referenced in one book. Wanted to log it here so I didn't forget. Gimme a couple days.

jzabroski commented

It appears to be from The Guru's Guide to SQL Server Architecture and Internals, ISBN:0201700476 - Page 466:

The syscacheobjects table is also a good place to check for auto-parameterization. WHen SQL Server has auto-parameterized an ad hoc query, the automatically generated parameter placeholders and data types will be included at the start of the sql column in the query's rows in syscacheobjects. The query text listed in the sql column will also use these placeholders to filter the data it returns, like so:
(@1 smallint)SELECT * FROM [Orders] WHERE [OrderId]=@1
When trace flag 8759 is enabled, the first part of an auto-parameterized query is written to the SQL Server error log, as shown below.
SAFE auto-paramd query: (@1 smallint) SELECT * FROM [Orders] WHERE [Orderid]=@1

jzabroski commented

@BlitzErik What are you doing reading a 14 year old book on SQL Server? :)

ktaranov commented

@jzabroski how you are google it? Could you describe this (it will be goog help to find more information for some undocumented flags)?

jzabroski commented
ktaranov commented

@BlitzErik , @jzabroski

Trying reproduce behaviour of this trace flag on Microsoft SQL Server 2014 (SP2-CU13) (KB4456287) - 12.0.5590.1 (X64) Aug 1 2018 01:23:36 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) , but without luck:

USE master;
GO

/*Enable Trace flag*/
DBCC TRACEON(8759, -1);
GO

DBCC TRACESTATUS;
GO

IF OBJECT_ID(N'TF8759', 'U') IS NOT NULL DROP TABLE TF8759;

SELECT TOP(1000) *
  INTO TF8759
  FROM sys.all_objects WHERE object_id <= 0
  ORDER BY object_id DESC;
GO

ALTER TABLE TF8759 ADD CONSTRAINT PK_object_id PRIMARY KEY CLUSTERED (object_id DESC);

SELECT * FROM TF8759 WHERE object_id = -101;

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%-101%';

EXEC sp_readerrorlog 0, 1, 'SELECT';

/*Disable Trace flag*/
DBCC TRACEOFF(8759,-1);
GO

Could you try this demo script on SQL Server 2012 or 2008?

Also can't get auto param queries for SQL Server 2014 like in this articles

BlitzErik commented

Yeah, using TOP prevents simple parameterization.

ktaranov commented

Cant reproduce behaviour this trace flag on SQL Server 2014 and 2017. Flag was added in list via 3450944
Closed today.

jzabroski commented

@ktaranov @BlitzErik Sorry for the late reply. I subscribe to so many GitHub projects that sometimes I get backlogged and have to work through it.

I just re-ran your repro on SQL Server 2017 and confirm your results. I think the modern equivalent for this would be to use Extended Events. If I had to guess, this trace flag was removed in SQL Server 2014 in alignment with Hekaton features https://en.wikipedia.org/wiki/Hekaton_(database)

In particular, Hekaton added natively compiled procedures, and the natively_compiled_proc_slow_parameter_passing extended event. If you think about the problem of forced parameterization broadly enough, you'll consider that this Trace Flag doesn't really help you in all situations. In addition, you can use other extended events:

  1. forced_param_clause_skipped_reason
  2. forced_param_statement_ignored_reason
  3. query_parameterization_data

In addition, the Query Store feature has the sys.query_store_query DMV which will tell you the type of parameterization applied to a given query.

In this sense, there is probably no real need for this.

  • 👍1