Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add trace flag 8759 #146

Closed
BlitzErik opened this issue Sep 11, 2018 · 10 comments

Comments

@BlitzErik
Copy link

commented Sep 11, 2018

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

@ktaranov

This comment has been minimized.

Copy link
Owner

commented Sep 11, 2018

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

This comment has been minimized.

Copy link
Author

commented Sep 11, 2018

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

This comment has been minimized.

Copy link

commented Sep 13, 2018

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

This comment has been minimized.

Copy link

commented Sep 13, 2018

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

@ktaranov

This comment has been minimized.

Copy link
Owner

commented Sep 16, 2018

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

@jzabroski

This comment has been minimized.

Copy link

commented Sep 17, 2018

@ktaranov

This comment has been minimized.

Copy link
Owner

commented Sep 19, 2018

@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

This comment has been minimized.

Copy link
Author

commented Sep 19, 2018

Yeah, using TOP prevents simple parameterization.

@ktaranov

This comment has been minimized.

Copy link
Owner

commented Sep 29, 2018

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

@ktaranov ktaranov closed this Sep 29, 2018

@jzabroski

This comment has been minimized.

Copy link

commented Mar 19, 2019

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.