SQL Server 2012: Trace Flags

This is something that I have been planning to release for some time but just have not been able to get to mainly due to procrastination! So this new year, I decided to get down to it! A list of all the publicly documented SQL Server 2012 Trace Flags.

The list below shows the trace flags available in SQL Server 2012 along with whether the trace flag can be enabled as a startup parameter or using DBCC TRACEON command or both.

SQL Server exposes multiple trace flags which are required to set specific server characteristics or to switch off a particular behavior. Some trace flags are required to enable the fix post the installation of the update.

This is currently a work-in-progress. The trace flags mentioned in the table below have the associated public article links mentioned which provide more information about the trace flag.

If you want a trace flag to be mentioned in the list below, then feel free to leave a comment. Note that only publicly documented trace flags will appear in this list.

The list of trace flags available for SQL Server 2005 are documented here.

The list of trace flags available for SQL Server 2008 are documented here.

Disclaimer
1. Data mentioned below is as of 14th January, 2014.
2. Trace flags should be used under the guidance of Microsoft SQL Server support.  They are used in this post for discussion purposes only and may not be supported in future versions.
3. Trace flags for hotfixes should be only enabled if the fix is applicable to the SQL Server instance that you are enabling the trace flag on.

Legend
Startup: If Y,Trace flag can be enabled by adding the trace flag number as a startup parameter using –T<trace flag number>
DBCC: If Y, Trace flag can be enabled using DBCC TRACEON(<trace flag number>,-1)

Trace Flag Description KB Startup DBCC
205 Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog. 195565 Y Y
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). BOL Link Y Y
610 Minimally logged inserts for indexed tables for fast load scenarios. Read article for limitations. Article Y Y
652 Disable pre-fetch scans 920093 Y Y
661 Disable ghost removal process 920093 Y Y
806 Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag 841776 Y Y
815 Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled Article Y Y
818 Detect stale reads. Very helpful in analyzing recurring data corruption issues. Article Y Y
830 Suppress stuck-stalled I/O warnings Article Y Y
834 Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances. 920093 Y N
836 Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server 920093 Y N
840 On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts 912322 Y Y
1106 Enable tempdb allocation trace. Not suggested for production use. 947204 Y Y
1117 Even growth of all files in a file group Whitepaper Y Y
1118 Prevents allocation of pages from mixed pages for all databases 328551 Y Y
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected. Useful for SQL Server 2000. BOL Link Y Y
1205 Prints information about each deadlock search 832524 Y Y
1211 Disabled lock escalation BOL Link Y Y
1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. To be used for SQL Server 2005 and above. BOL Link Y Y
1224 Disables lock escalation based on the number of locks BOL Link Y Y
1260 Disabled mini-dump for non-yield conditions Whitepaper Y Y
1262 Dump everytime a non-yielding scheduler condition is detected. Article Y Y
1439 Trace database restart and failover messages to SQL Errorlog for mirrored databases 983500 Y Y
1448 Prevent replication latency with database mirroring enabled. Apply KB 983480 to prevent issues after enabling -T1448. 937041 Y Y
2301 Enable advanced decision support optimizations 920093 Y Y
2340 Disable batch sort to reduce CPU consumption Blog Y Y
2371 Use a modified threshold for auto update statistics 2754171 Y Y
2389 Automatically generated quick statistics for known ascending keys 922063 N Y
2390 Automatically generated quick statistics for all columns that include known ascending keys or unknown ascending keys 922063 N Y
2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. Article Y Y
2544 Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2546 Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2551 Generate a filtered dump of the SQL Server process. Reference Y Y
3004 Write extended information about backup/restore to Errorlog Blog N Y
3014 Write extended information about backup/restore to Errorlog Blog N Y
3023 Automatically enable CHECKSUM for BACKUP command 2656988 N Y
3042 Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size Article Y Y
3205 Disable hardware compression for tape drives. Article N Y
3213 Display backup/restore configuration parameters Blog N Y
3226 Suppress successful backup messages in the logs Article N Y
3502 Displays start and end of checkpoint. Can be very verbose and increase your transaction log size very quickly 815436 Y Y
3504 Print Checkpoint summary Blog Y Y
3505 Disable Checkpoint 815436 Y Y
3604 Send trace output to client Y Y
3605 Send trace output to SQL Errorlog Y Y
3608 Prevents SQL Server from automatically starting and recovering any database except the master database Article Y N
3625 Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages. Article Y N
3688 Needed to enable fix for issue mentioned in KB Article. 922578 Y N
4010 Allows only shared memory connections to the SQL Server Blog Y Y
4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0. Article Y Y
4029 Push more verbose errors to the errorlog when read+write errors occur in the network layer on the server side. Blog Y Y
4199 Controls multiple query optimizer changes previously made under multiple trace flags. Refer article for more details. 974006 Y Y
4606 Disables password policy check during server startup Blog Y Y
4610 Increases the size of the hash table that stores the cache entries by a factor of 8. 959823 Y Y
4616 Makes server-level metadata visible to application roles Article Y Y
4618 Limit the number of entries in the TokenAndPermUserStore store 959823 Y Y
4621 Control TokenAndPermUserStore store size using registry value (Applies only to SQL Server 2005. For SQL Server 2008 and above, please refer 959823) 959823 Y Y
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration Article Y Y
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express Article Y N
8002 Treat affinity mask like processor affinity 818765 Y Y
8004 Create memory dump for the first occurrence of out-of-memory condition Article Y Y
8011 Disable the ring buffer for Resource Monitor 920093 Y Y
8012  Disable the ring buffer for schedulers 920093 Y Y
8018 Disable the exception ring buffer 920093 Y N
8019 Disable stack collection for the exception ring buffer 920093 Y N
8020 Disable working set monitoring 920093 Y N
8024 Performs additional checks before generating non-yielding condition dump Whitepaper Y Y
8032 Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger Article Y N
8048 Forces CPU based partitioning Blog Y N
8602 Ignore all index hints Whitepaper Y Y
8721 Will dump information into the error log when AutoStat has been run. 195565 Y Y
8744 Disable pre-fetching for ranges 920093 Y Y

PowerView and System Health Session–Scheduler Health

This is actually turning out to be a series. I had demonstrated the use of System Health session to retrieve CPU usage details earlier. In this post, I will demonstrate how to use Power Pivot and Power View with System Health Session data to fetch SQL OS scheduler health information.

Using the Transact-SQL query below, I shall fetch the SQL OS scheduler statistics from the System Health Session data in a PowerPivot table name “QueryProcessingComponent”.

SET NOCOUNT ON

-- Fetch data for only SQL Server 2012 instances
IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)
BEGIN
-- Get UTC time difference for reporting event times local to server time
DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());

-- Store XML data retrieved in temp table
SELECT TOP 1 CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'   AND xet.target_name = 'ring_buffer';

;WITH CTE_HealthSession (EventXML) AS   (
SELECT C.query('.') as EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C) )
SELECT
DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
EventXML.value('(/event/data/value/queryProcessing/@maxWorkers)[1]','bigint') as [Max Workers],
EventXML.value('(/event/data/value/queryProcessing/@workersCreated)[1]','bigint') as [Workers Created],
EventXML.value('(/event/data/value/queryProcessing/@workersIdle)[1]','bigint') as [Idle Workers],
EventXML.value('(/event/data/value/queryProcessing/@pendingTasks)[1]','bigint') as [Pending Tasks],
EventXML.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as [Unresolvable Deadlock],
EventXML.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as [Deadlocked Schedulers]
FROM CTE_HealthSession
WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'
AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'
ORDER BY [Event Time];

DROP TABLE #SystemHealthSessionData

END  

Now I shall create three new columns in the Power Pivot sheet as follows:

1. Day using Day() function on the Event Time column

2. Hour using Hour() function on the Event Time column

3. Minute using the Minute() function the Event Time column

After this is done, I created two Power View sheets with the following layouts:

1. One sheet named Threads Timeline containing scatter graph with the hour column on the X-axis and the day column on the Y-axis. The play axis uses the event time data.

2. Another sheet named Worker Thread Stats with a line graph for workers created, pending tasks and idle workers using event time as the x-axis. Also there is a tabular output showing the same data with two additional columns showing the unresolvable deadlocks and deadlocked schedulers respectively.

A screenshot of one of the Power View sheets is shown below. The screenshot of the scatter graph would not do justice which is why you need to download the Excel sheet and see for yourself! Smile

Excel file download location: http://sdrv.ms/10O0udO

image

Stay tuned to TroubleshootingSQL for new visualizations that I will be discussing soon.

Previous post in this series

PowerView and System Health Session–CPU health 

A year that was

Happy New Year to all of you out there!!

The year that was…. 2012… was an action packed year in all things SQL. Microsoft launched SQL Server 2012 which introduced a large number of enhancements and new features in the database engine, integration services, analysis services and reporting services. If I would have to summarize my learning time year-on-year, then I have spent the maximum time in 2012 studying and enhancing my knowledge in SQL Server due to all the new features introduced.

TroubleshootingSQL also had a good year… The Facebook page now has over 300 likes… The Twitter avatar of TroubleshootingSQL has over 200 followers and my blog showed a significant YoY growth in visitor count!! So thank you for all the patronage and I am glad the SQL folks find my posts useful! The TOP 5 posts of the year in my blog were:

The new Wiki series that I started also received significant amount of coverage as well and the posts #2 and #3 mentioned above are part of my Wiki series.

I did speak at various community events throughout the year, the most significant of them being TechEd India.

Another highlight was the book launch at SQL PASS 2012 which I co-author: Professional SQL Server 2012: Internals and Troubleshooting, which is now available both as e-books as well as paperback across many regions.

What can you look forward to in 2013. Since the world did not end, I will write about all the posts that I have been holding onto (partly due to procrastination) due to the fact that world was going to end in December!!

You will see new additions to the SQL Wiki series, more posts about Power View, useful features in SQL Server 2012 that are not commonly used and much more! So stay tuned….

SQL PASS and Book Launch

Day 1 done at the SQL PASS Summit 2012!! …. It was a packed day with some interesting events starting with the Keynote session. I spent most of my day at the SQL Server Clinic meeting customers and discussing their work with SQL Server and helping them with suggestions and answers to the questions they had regarding the product. Most of all, I enjoyed meeting all the friends that I made on Twitter in person. It is always good to connect a face to a virtual name that you interact with.

Day 2 will be much more interesting as I will be part of the official launch of the book, Professional SQL Server 2012 Internals and Troubleshooting, that I have co-authored with Christian Bolton, James Rowland Jones, Glenn Berry, Justin Langford and Gavin Payne.

You will still find me at the SQL Server Clinic or have me loitering about near Exhibition Hall 4. In case you still can’t locate me, send a tweet across my way (@banerjeeamit). In partnership with SQL Sentry at the exhibitors stand from 12:30 on Thursday 8th November the authors will be available for a book signing event which will give you the chance to meet and greet the authors and contributors. This book is truly a great example of collaboration and community power where the authors have interacted over email for months but have met each other only once. And for some, this summit is their first face-to-face meeting!

SQL Sentry also have lots and lots of copies to give away for free so be sure to drop by their stand and ask about it! The book is also available at the PASS bookstore for sale. So if you want one, head out there and grab a copy.

You can also order it from your favorite online retailer:

Amazon.com: http://amzn.to/U9IlPV

Barnesandnoble.com: http://bitly.com/Ux1gog

Flipkart

SQL PASS Summit 2012

If you follow SQL Server related hashtags on Twitterverse, then you are already aware of the tweets that have been helping in the building of the SQL PASS Summit being held at Seattle from November 6-9. Rest assured that there will be a flurry of tweets originating from the Washington State Convention & Trade Center starting from Tuesday. And the reason for the excitement is coupled by the fact that I shall be present in person rather than following the event via Twitter or a streaming video session!

You might have already read the various blog posts doing the rounds on the net on why you should attend and what you can look forward to. I shall give you my 2 cents on why I think this is really worth a SQL Server professional’s time.

First and foremost, you will very rarely find the majority of the SQL Server brains in the world gathered under a single roof for a period of three days discussing and exchanging ideas about what you deal with everyday i.e. SQL Server. The session’s line-up will re-emphasize what I just said! Some of the best speakers in the industry will talk about various topics on SQL Server and you stand to gain a wealth of knowledge by just attending these sessions.

I have interacted with a lot of folks on Twitter regarding SQL Server related queries and it would be a great opportunity to connect faces to the Twitter handles that I am familiar with. Networking is one of the key components in SQL PASS and whether you want to or not, you will end up making acquaintances and even friends during the course of the three days (four if you are attending the Pre-Cons). Networking and interacting with the SQL Server community is the biggest motivation for me to attend PASS!

I always love to discuss SQL Server at any given opportunity and I will be available at the SQL Server Clinic on all three days. So feel free to walk up with your queries! Remember no question is a bad question! You don’t know till you don’t ask! You will get the opportunity to discuss your SQL Server related queries with some of the most experienced SQL Server engineers and members of the SQL CAT team)…. So feel free to walk by the clinic. The SQL Server Clinic timings are as follows:

•Wednesday

◦7:00am-8:00am (Early Birds)

◦10:00am – 6:15pm

•Thursday

◦7:00am-8:00am (Early Birds)

◦10:00am – 6:30pm

•Friday

◦7:00am – 2:00pm

We will be in 4C-3 which will be across from the Expo hall and on your way to the meal hall.

The Microsoft CSS team will also be delivering sessions at PASS. So don’t miss out on those. Bob Ward [B|T] will be delivering another session titled Inside SQL Server 2012 on November 7th: 1:30 pm – 4:30pm. Don’t miss that! For more details on what the CSS team will be upto at PASS, read the post by Adam Saxton [B|T] (yes, he is also delivering sessions).

Hope to see you there!

Psssstt… We shall try to get Bob’s Twitter followers to cross Adam’s… So start following Bob if you are not already doing so!!