Home > SQL server DBA administration > Some Important Trace in Microsoft SQL server

Some Important Trace in Microsoft SQL server


A trace flag is a directive used to “set specific server characteristics or to switch off a particular behavior”. Here I have listed some Important Trace  which can be useful as per the environment.

 

Trace Flag 834

• Trace flag 834 allows SQL Server 2005 to use large-page allocations for the memory that is allocated for the buffer pool. 

• May prevent the server from starting if memory is fragmented and if large pages cannot be allocated

• Best suited for servers that are dedicated to SQL Server 2005

• Page size varies depending on the hardware platform

• Page size varies from 2 MB to 16 MB. 

• Improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU

• Only applies to 64-bit architecture

• Startup

• Documented: KB920093

• Now automatic:

• Enterprise / Developer Edition

• “Lock Pages in Memory” privilege

• >= 8GB RAM

 

Trace Flag 835

• Trace flag 835 enables “Lock Pages in Memory”  support for SQL Server Standard Edition

• Enables SQL Server  to use AWE APIs for buffer pool allocation

• Avoids potential performance issues due to trimming working set

http://msdn.microsoft.com/en-us/library/ms187499.aspx

• Introduced in:

• SQL Server 2005 Service pack 3 Cumulative Update 4

• SQL Server 2008 Service Pack 1 Cumulative Update 2

• Only applies to 64-bit architecture

• Startup

• Documented: KB970070

 

Trace Flag 3226

• Trace flag 3226 prevents successful back operations from being logged

• By default SQL Server logs every successful backup operation to the ERRORLOG and

the System event log

• Frequent backup operations can cause log files to grow and make finding other

messages harder

 

Documented: BOL

 

Trace Flag 806

• Trace Flag 806 enables DBCC audit checks to be performed on pages to test for logical consistency problems. 

• These checks try to detect when a read operation from a disk does not experience any errors but the read operation returns data that is not valid. 

• Pages will be audited every time that they are read from disk

• Page auditing can affect performance and should only be used in systems where data Stability is in question.

 

Documented: KB841776

 

“SQL Server I/O Basics, Chapter 2” white paper

http://technet.microsoft.com/en-au/library/cc917726.aspx

 

Trace Flag 818

 

• “Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048

successful write operations that are performed by the computer running SQL Server, not

including sort and workfile I/Os”

• Use to further diagnose operating system, driver, or hardware problems causing lost write

conditions or stale read conditions

• May see data integrity-related error messages such as errors 605, 823, 3448.

• Documented: KB826433

 

 

Trace Flag 3422

• Trace Flag 3422 enables log record auditing

• “Troubleshooting a system that is experiencing problems with log file corruption may be easier using the additional log record audits this trace flag provides”

• “Use this trace flag with caution as it introduces overhead to each transaction log record”

• Similarly to trace flag 806, you would only use

this to troubleshoot corruption problems

 

Documented: 

 

“SQL Server I/O Basics, Chapter 2” white paper

http://technet.microsoft.com/en-au/library/cc917726.aspx

 

Trace Flag 2528

 

• Trace flag 2528 disables parallel checking of

objects during DBCC CHECKDB, DBCC

CHECKFILEGROUP and DBCC CHECKTABLE.

• Scope: Global | Local

• Documented: BOL

• Typically leave parallel DBCC checks enabled

• DBCC operations can dynamically change their

degree of parallelism

• Alternatives:

 

• MAXDOP option

• Resource Governor

 

 

Trace Flag 1224

 

• Trace flag 1224 disables lock escalation based on the number of locks

• Memory pressure can still trigger lock escalation

• Database engine will escalate row or page locks to table locks

• 40% of memory available for locking

• sp_configure ‘locks’

• Non-AWE memory

• Scope: Global | Session

• Documented: BOL

 

Trace Flag 1211

 

• Trace flag 1211 disables lock escalation based on memory pressure or number of locks

• Database engine will not escalate row or page locks to table locks

• Scope: Global | Session

• Documented: BOL

• Trace flag 1211 takes precedence over 1224

• Microsoft recommends using 1224

 • Trace flag 1211 prevents escalation in every case, even under

memory pressure

• Helps avoid “out-of-locks” errors when many locks are being used.

• Can generate excessive number of locks

• Can slow performance

• Cause 1204 errors

 

Trace Flag 1118

• Trace flag 1118 directs SQL Server to allocate full extents to each tempdb objects (instead of mixed

extents)

• Less contention on internal structures such as SGAM pages

• Story has improved in subsequent releases of SQL

Server

• So represents a “edge case”

Scope: Global

Documented: KB328551, KB936185

Working with tempdb in SQL Server 2005 white paper

http://www.microsoft.com/technet/prodtechnol/sql/2005/wor

kingwithtempdb.mspx.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: