SQL Server – Playing With Trace Flags

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. They are usually scoped to a global level or a session only level. For this blog post I thought it would be handy to show you how to enable (and disable) global based trace flags and then check the status on your system.

The difference between global and session is important to understand, global trace flags are visible to every connection whereas session scoped means that the trace flag is visible to only that connection.

Global Scope

For this example I will be using a global trace flag that overrides the use of index hints (This is not something I do or encourage; it is just a use case for this post).


SELECT AddressID,city FROM [Person].[Address]
WHERE City = 'Albany'

I wrote a basic query because as you will see it gives me a Non-Clustered Index Seek.

index

I am now going to write a query where I will force the above SELECT statement to use an index where it really shouldn’t – the outcome is a scan with a key lookup – yep not what we want

SELECT AddressID,city FROM [Person].[Address]  WITH (INDEX (AK_Address_rowguid))
WHERE City = 'Albany'

index1

So let’s set a global based trace flag to say for any connection ignore ALL index hints. I would expect it to go back to an index seek. The -1 in the TRACEON is very important – that is what makes it global.

 DBCC TRACEON (8602,-1)

SELECT AddressID,city FROM [Person].[Address]  WITH (INDEX (AK_Address_rowguid))
WHERE City = 'Albany'

index3.JPG

So it’s clear to see that even with the index hint it is now ignored.

Because it’s global I then connected to the server as someone else and issued the query with the same results.

Check status

To check what trace flags are enabled on the system run the below.

DBCC TRACESTATUS (-1);
GO

index4

Turn off

DBCC TRACEOFF (8602,-1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC TRACESTATUS(-1);
GO

Re-issuing the above DBCC TRACESTATUS will now show nothing.

What about after a restart?

I switched the trace flag back on then restarted SQL Service and issued the status command:

index6

DBCC TRACESTATUS(-1);

GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The flag has been lost.

SQL Server Configuration Manager

Personally I prefer using configuration manager if I am using trace flags. Under properties of your MSSQLSERVER you will see a start-up parameters tab.

index0

Click ADD.

index101

After you have restarted logon to the SQL Server and check the status

DBCC TRACESTATUS(-1);

GO

index45

I restarted the SQL service again just to prove that settings will be preserved – which they are and that is the part that I like about configuration manager.

One thought on “SQL Server – Playing With Trace Flags

  1. Pingback: SQL Server – Playing With Trace Flags - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s