martes, 5 de julio de 2016

SQL Server Performance-Related Trace Flags



When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://speedysql.com/2015/09/20/performance-related-trace-flags/



SQL Server Performance-Related Trace Flags

Here are some trace flags that you may find useful when query tuning or to improve server performance.
A quick reminder of the syntax to enable them:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--Enable trace flag at session level.
DBCC TRACEON (3604)
--Enable trace flag at global level.
DBCC TRACEON (3604, -1)
--Check active trace flags.
DBCC TRACESTATUS
--Disable trace flag at session level.
DBCC TRACEOFF (3604)
--Disable trace flag at global level.
DBCC TRACEOFF (3604, -1)
--Enable multiple trace flags at global level.
DBCC TRACEON (3604, 3605, -1)
--Disable multiple trace flags at global level.
DBCC TRACEOFF (3604, 3605, -1)
The QUERYTRACEON query hint can also be used to activate a trace flag at the query level. Not all trace flags can be activated in this way. See the following KB article for the syntax and for details of which trace flags can be used with QUERYTRACEON.
To survive a server restart, a trace flag must be enabled using SQL Server Configuration Manager as shown in the image below.
Adding trace flags as startup parameters
WARNING: Some of these trace flags are undocumented and behaviour is neither guaranteed nor supported by Microsoft. The documented trace flags should only be enabled to fix a specific problem and only after thorough testing. All are to be used at your own risk.

Performance Trace Flags




No hay comentarios.:

Publicar un comentario