3 SQL Server Trace Flags to Know Before Troubleshooting

By Pinal Dave on October 30, 2015

In SQL Server, there are various options available to override the default behavior of the product, including use of trace flags. Trace flags almost every DBA is likely to have used include 1204 or 1222. These are used for printing deadlock graphs in ERRORLOG file for troubleshooting. While these don’t have anything to do with troubleshooting performance, they are a good example of how and when trace flags can be used.

In the case of troubleshooting performance issues with SQL Server, there are few specific trace flags a DBA should keep in mind. Before considering use of these flags, I must note that it’s important to have done a basic level of troubleshooting first, in particular making sure that:

The trace flags discussed shouldn’t be used for basic troubleshooting but rather are for an advanced level of troubleshooting in identifying the source of a performance issue.

Trace Flag 4199 (Enable Optimizer Fixes)

Trace flag 4199 enables many other query optimizer fixes. This means that optimizer fixes are off-by-default. Article 974006 from the Microsoft Knowledge Base contains list of other trace flags that are hidden behind 4199. In the past, Microsoft would often release a fix for a particular optimizer issue via a fix and a trace flag. More recently, it has become difficult to keep track of such fixes, and Microsoft has instead “activated” all the fixes through use of just one trace flag.

Trace Flag 9481 (Disable New Cardinality Estimation)

Whenever there is any upgrade done to SQL Server 2014 and slow performance issue is reported, this is a trace flag to consider. The new Cardinality Estimator (CE) in SQL Server 2014 may slow some queries down. This trace flag disables the new CE at a server level, if added as startup parameter. If there is a specific database which is showing problems after upgrading to SQL Server 2014, you can also change the compatibility level of the database to anything lower than 120.

Keep in mind that by enabling this trace flag, we are asking ALL queries to use the old CE–even queries that might actually benefit from the new CE. In this case, you should first can identify the queries which are slow, then selectively force the trace flag using the hint “OPTION (QUERYTRACEON 9481)” at the end of query.

Trace flag 2312 has the reverse behavior, forcing the query optimizer to use the new CE, even if the database compatibility is set to value lower than 120 (SQL Server 2014)

Trace Flag 4136 (Disable Parameter Sniffing)

Have you ever seen or heard about parameter sniffing problems in SQL Server? Parameter sniffing is a phenomenon where the value of a parameter is sniffed by the query optimizer during the compilation phase of a stored procedure and a query plan is generated based on that value. If the data in the table is unevenly distributed, that is skewed, then the query plan might be sensitive to values. If a different parameter value is passed, then same plan is used and it might be a bad plan for the new value. Let’s have a look at quick example.

USE tempdb
GO

IF OBJECT_ID('Employee') IS NOT NULL
	DROP TABLE Employee
GO

CREATE TABLE Employee (
	id INT identity
	,gender CHAR(1) DEFAULT 'F'
	,DUMMY CHAR(7888) DEFAULT 'A'
	)
GO

SET NOCOUNT ON
GO

INSERT INTO Employee DEFAULT
VALUES 
GO 995

INSERT INTO Employee (gender)
VALUES ('M') 
GO 5

CREATE INDEX idx ON Employee (gender)
GO

Now, if we run a query having predicate as gender and search for M and F, we would get different plan. In next step, we would create stored procedure.

CREATE PROCEDURE CountEmp @gender CHAR(1)

AS

BEGIN

      SELECT *

      FROM employee

      WHERE gender = @gender

END

If we run the stored procedure then based on first value passed, the plan would be selected.

SET STATISTICS PROFILE ON

GO

DBCC FREEPROCCACHE

GO

EXEC CountEmp 'M'

GO

EXEC CountEmp 'F'

GO

DBCC FREEPROCCACHE

GO

EXEC CountEmp 'F'

GO

EXEC CountEmp 'M'

GO

SET STATISTICS PROFILE OFF

GO

If you run above queries, there would be four plans. First two and last two would be same. When plan for M is cached, the value F is also using same plan.