A SQL Server configuration option used to temporarily enable or disable specific server behaviours.
Questions tagged [trace-flags]
35 questions
40
votes
3 answers
Methods for finding new Trace Flags in SQL Server
There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, Microsoft employees, etc., what are ways to find new…
Erik Reasonable Rates Darling
- 45,549
- 14
- 145
- 532
13
votes
1 answer
Prevent THREADPOOL waits due to idle worker thread trimming
After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:
The idea is that once SQL Server has created enough threads to service the peak workload, it…
Paul White
- 94,921
- 30
- 437
- 687
7
votes
2 answers
Enabling trace flag 8048
My database is running on SQL Server 2012 with 32 core processors. As per sp_blitz (by Brent Ozar) we need to enable trace flag 8048.
Should we enable this flag on 2012, or it is recommended only for 2014 onwards?
Debajit Chandra
- 125
- 2
- 6
5
votes
1 answer
Should we really turn on trace flag 1118 for every SQL instance?
It has been recommended that every SQL instance have TF 1118 turned on
What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having…
James Jenkins
- 6,318
- 6
- 49
- 88
4
votes
1 answer
Is Trace Flag 6534 Relevant Past SQL Server 2016?
Based on this link, I see that trace flag 6534 is relevant for SQL Server 2012 through 2016, but no mention of SQL Server 2017 or 2019. Is it still relevant for those editions or has it been folded into the database engine or compatibility level…
Lee M
- 396
- 2
- 11
4
votes
2 answers
ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES
Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database where killing active sessions and rolling back…
Richard Herling
- 61
- 7
4
votes
1 answer
Suppress specific SQL Server log warnings and messages
I want to minimize the output of certain warnings and messages in the SQL Server 2012 logs, such as when an spid is killed. I am unable to find information on suppressing/expanding which messages are actually sent to the error logs. Is this done…
PicoDeGallo
- 1,554
- 1
- 20
- 30
3
votes
1 answer
SQL Server Processor affinity with trace flag 8002 ON
I have been going through an article that explains the unwanted side effects of Setting a Processor Affinity in SQL Server.
What I have come to know is that in most of the cases the default setting of SQL Server works best as when we are setting…
Vivek Kumar Singh
- 233
- 4
- 11
3
votes
0 answers
how to tell which statistics SQL server references in query
Is there a way to check in SQL Server 2016 which statistics the optimizer referenced when compiling a particular query?
My question is similar to Check which statistics were used in query optimization,
except that I am using SQL Server 2016, which…
user1430949
- 265
- 1
- 5
3
votes
1 answer
Trace flag 2861 and what a 'zero-cost' plan actually means
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of…
YaHozna
- 357
- 1
- 11
2
votes
1 answer
When does a subquery choose a join instead as a logical operator?
so the simplification in the algebrizer is supposed to convert some of the subqueries to inner or outer joins.
for example, i tried this
DBCC TRACEON(3604)
USE ContosoRetailDW
BEGIN TRAN
SELECT ProductKey
FROM DimProduct
WHERE ProductKey IN…
Suleyman Essa
- 167
- 1
- 8
2
votes
1 answer
Why does SQL Server Trace Flag 715 behave differently from the TABLOCK query hint?
It's my understanding, based on numerous sources (including this one: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster/ba-p/384910) that SQL Server Trace Flag 715 should be the equivalent…
Mike S
- 177
- 2
- 8
2
votes
1 answer
Does Traceflag 1800 require a restart?
We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB.
As a result of this we…
SE1986
- 2,142
- 4
- 30
- 61
2
votes
1 answer
Do I need to enable Trace Flag 1117 for equally sized data files?
I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states:
When a file in the filegroup meets the autogrow threshold, all files
in the filegroup grow. This trace flag affects all databases and is
…
Rauf Asadov
- 1,313
- 14
- 36
2
votes
1 answer
Azure SQL Database vs. error 8152 enhancement
How can I take advantage of the enhancement for error 8152 (https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/) in Azure SQL Database? Even when logged in as the Server admin,…
Mark Freeman
- 2,293
- 5
- 32
- 54