I’ve been looking for this for a while now I found it. All features in Denali in a single View, Enjoy it
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
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
•Data Loading Performance Guide white paper
- 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
- 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
•SQL Server 2005 Service pack 3 Cumulative Update 4
•SQL Server 2008 Service Pack 1 Cumulative Update 2
•Only applies to 64-bit architecture
- 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
•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
- Trace Flag 4199 /* IMPORTANT */
•Trace flag 4199 enables all the fixes that were previously made for the query processor under many trace flags
•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
•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
Lets Build a Scenario,
Imagine your need to Standardize your Stored procedure output for dynamic batching. as a matter of fact if the output of your Stored procedure is different from what you expect your query will fail.
So you need to standardize your output from stored procedure. to Do that lets welcome the new SQL Server 2012 T-SQL Feature Result Set.
Result Set will let the format the stored procedure output to map your requirements no matter how the data is formatted internally. Lets take a look using the following code sample
EXECUTE <batch_or_proc> WITH <result_sets_option>;
Sample Code for that is
EXECUTE GetEmployees WITH RESULT SETS(
Some notes to consider when using Result Set
- Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
- The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
- The number of columns being returned as part of result set cannot be changed