SQL Server Trace Flags


Trace flags are required to modify Session and Server state very helpful when dealing with system Diagnostics and system maintenance. Trace flags are also dangerous so always think what do you need to enable or disable a specific trace flag.

Enabling and Disabling Trace flags can be done using multiple ways, SQL Server Configuration Manager, DBCC or Command line.

to enable trace flag using DBCC (my Favourite) use the following command

DBCC TRACEON (3604)

To Disable trace flag:

DBCC TRACEOFF (3604)

To Check for trace flag Status

DBCC TRACESTATUS(3604)

There are 2 Different types of Trace Flags Documented , and None Documented

Below are a list of the most important ones

    • Trace Flag 610

      •Trace flag 610 controls minimally logged inserts into indexed tables

      •Allows for high volume data loading

      •Less information is written to the transaction log

      •Transaction log file size can be greatly reduced

      •Introduced in SQL Server 2008

      •“Very fussy”

      •Documented:

      •Data Loading Performance Guide white paper

      http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

      DBA-

    • 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

    • 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

    • 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 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 4199 /* IMPORTANT */

      •Trace flag 4199 enables all the fixes that were previously made for the query processor under many trace flags

      •Policy:

      •Any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag

      •Except for fixes to bugs that can cause incorrect results or corruption

      •Helps avoid unexpected changes to the execution plan

      •Which means that virtually everyone is not necessarily running SQL Server with all the latest query processor fixes enabled

      •Scope: Session | Global

      •Documented: KB974006

      •Consider enabling for “virgin” SQL Server deployments?

      •Microsoft are strongly advising not to enable this trace flag unless you are affected

There is more you can find on MSDN site for more info but for me these the the most Important DOCUMENTED Trace flags

for undocumented trace flags I didn’t try any my self but here you can find some of what you are looking for

Trace Flag 3004

Most Database Administrators are aware of instant file initialization. In a nutshell, when instant file initialization is enabled the data files do not need to be zeroed out during creation. This can save an incredible amount of time during the restoration of VLDBs. As you can imagine, the zeroing out of a 1 TB data file can take a very long time.

Trace flag 3004 turns on information regarding instant file initialization. Enabling this trace flag will not make this information available to view. You will still need to turn on trace flag 3605 to send this information to the error log.

Trace Flag 3014

Trace flag 3014 provides detailed information regarding the steps performed during the backup and restore process. Normally, SQL Server only provides a limited amount of information in the error log regarding these processes. By enabling this trace flag you’ll be able to see some very detailed and interesting information.

Trace Flag 3604

Trace flag 3604 can be used under a variety of circumstances. If you’ve ever used DBCC IND or DBCC PAGE then you’ve probably already used trace flag 3604. It simply informs SQL Server to send some DBCC output information to the screen instead of the error log. In many cases, you have to use this trace flag to see any output at all.

Trace Flag 3605

Trace flag 3605 will send some DBCC output to the error log. This trace flag needs to be enabled to see the instant file initialization information made available by trace flag 3004

What’s News In T-SQL Server 2012–Series


With the release of SQL 2012 a lot of modifications has been added to T-SQL to help Developers and DBA improve the performance of their queries. But Before we start we need to state that the News SSMS (SQL Server Management Studio) is now based on the engine of Visual Studio, Same look and feel and the powerful features of Visual Studio now will help you write and manage  your queries.

The Intellisense improved a lot in this version of SQL Server which will save you a lot of time.

Offset and Fetch

In Earlier versions of SQL Server in order to create paging in your application you need to handle this through your Datagrids or though a complex – performance impacting Stored Procedures.

In 2012 a New TSQL functions has been introduced to improve your paging methodology.

for example to get the next 10 rows in Employee table you can use the following command.

–Leave first 10 rows and Fetch next 5 rows

SELECT ProductID, Name

FROM AdventureWorks.Production.Product

ORDER BY NAME

OFFSET 10 ROWS

FETCH NEXT 5 ROWS ONLY

Know your SQL Hardware Info


When It comes to database engine it’s not recommended by all means to allow admins and users to connect using Remote desktop to the database Server to do anything so By Default SQL Server includes a large set of DMV (Data Management Views) to help you with your required admin configurations

Below is for me 1 of the most important Queries that will give you an overview of your Server Hardware status

   1: Select 

   2: (cpu_ticks/1024)/1024 as CPU_Ticks_MB,

   3: (ms_ticks/1000) as Ticks_in_Sec,

   4: cpu_count,

   5: hyperthread_ratio,

   6: (physical_memory_in_bytes/1024)/1024 as RAM,

   7: (virtual_memory_in_bytes/1024)/1024 as Virtual_RAM,

   8: max_workers_count,

   9: os_priority_class,

  10: bpool_committed as MemoryCommitInBufferPool

  11:  

  12:  From

  13: sys.dm_os_sys_info