One option to modify MSSQL Server behavior is to use traceflags. You can compare a trace flag with the old DIP switches that you sometimes come across in equipment. At the moment there are already more than 100 traceflags and this number is only increasing.
There are traceflags that are described (“documented”) and thus supported by Microsoft, but there are also known “undocumented” traceflags. The use of undocumented traceflags carries a risk, as this can lead to a situation where support is no longer provided. Conversely, undocumented traceflags may only be activated on the advice of Microsoft. Always study the purpose and effects of a traceflag before trying it out in a test environment. DBA.nl can provide support with questions about the traceflags application.
With the T-SQL hint QUERYTRACEON a traceflag can only be activated for a specific query (in this example traceflag 4199):
SELECT … FROM … OPTION (QUERYTRACEON 4199)
You can use a traceflag for a specific query by using the DBC TRACEON() command. An example to test traceflag 4199:
DBCC TRACEON(4199) SELECT …
When the session ends, the trace flag is turned off again. You can specify multiple trace flags, separated by commas.
The same command can be used to enable a traceflag globally. The flag then applies to all sessions. Then only -1 has to be added to the last trace number:
DBCC TRACEON(4199, -1)
This keeps the specified traceflag active until the instance is restarted. After the reboot, the traceflag is gone.
This means that the traceflag is added as a start parameter to the MSSQL service. The method differs per MSSQL version used, from MSSQL 2012 this has become a lot easier.
Changing Boot Parameters for MSSQL 2005 / 2008 (R2)
Changing boot parameters from MSSQL 2012
The procedure is broadly the same as previous versions, but a separate tab has been created for startup parameters. Add the parameter here, without a semicolon.
With the command DBCC TRACESTATUS() you can request which traceflags are active and whether they are active for a session or globally. If you enter -1, you will see the global traceflags, otherwise you will only see your session traceflags. You can also provide a trace number to verify that it is active:
Because traceflags can be enabled globally, this is a handy method (in the example below we activate traceflag 4199)
With the command DBCC TRACEOFF() a traceflag can be turned off for a session or globally. Add -1 to globally disable the traceflag:
DBCC TRACEOFF(4199, -1)
To remove a traceflag as a startup parameter, see the description how to add a traceflag. With MSSQL 2012 and higher you can go to the –T option and then click the button[remove] click. Be careful not to accidentally delete other startup parameters as well.
DBA.nl is the all-round database expert specialized in setting up, maintaining and monitoring database environments. In addition, we provide advice and remove performance problems.
Since last June, we have switched to a working method based on customer teams within DBA.nl....
DBA.nl has launched a new service management system. The choice fell on Topdesk....
DBA.nl, the database experts, may continue to manage the database environment of RID de Liemers....