To know about a topic we usually ask questions. Here I used the same method to explain about trace flags of SQL Server.
Below are some basic questions and its answers on this.
What is the use of Trace Flag?
Trace Flags are used to enable or disable certain functionality of SQL server temporarily. It is used to diagnose performance issues or to debug stored procedures
Where will I get all the trace flags available for SQL Server?
There are many resources you can find in internet. Below are some reliable sources on trace flag.
What are the types of Trace Flags?
SQL Server provides below two types of trace flag.
Session Level: Active and visible only to a specific connection where it is set. A session level trace flag never effect any other session.
Global Level: Active and visible at the server level (for all connections). Some trace flag can only be set at global level. If a global trace flag enable at session level there will be no effect of the flag on the server.
Note: Some of the trace flags can be enabled either at session level or at global level.
How to enable or disable a Trace Flag?
Using method DBCC TRACEON, we can enable a trace flag.
Ex: DBCC TRACEON (1224)
To set a trace flag at Global level use argument -1.
Ex: DBCC TRACEON (1224, -1)
Global level trace flag can also be set using –T start-up option. Using –T start up option we can’t enable any session level trace flag.
How to check a trace flag is enabled or disabled?
Using method DBCC TRACESTATUS, we can check the status of a trace flag.
Ex: DBCC TRACESTATUS (-1) will display list of trace flags enabled at global level
DBCC TRACESTATUS () will display list of trace flags enabled for current session
