Here's my disclaimer: trace flags are not toys. Don't deploy them in production until you are familiar with their expected effects (so they can be measured) and their possible side effects (so you can look out for signs of danger). Startup trace flags are not necessarily more dangerous than global trace flags... but since they require a restart of SQL Server to enable/disable I advise even more consideration for them.
I have a natural bias against trace flags and startup trace flags. Seems just too easy for them to get lost, or for the motivation behind them to get forgotten.
But I can be won over.
Here's the best explanation I've seen on the -E startup flag. This can provide enhanced sequentiality to the extents for a given database object. The enhanced data contiguity for larger readahead IOs and fewer IO operations for a given query. The larger readahead requests will not necessarily improve performance (from the perspective of query elapsed time). But with fewer IO operations for the same data, the SQL Server instance can be a better neighbor on the SAN.
http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/04/25/focus-on-fast-track-understanding-the-e-startup-parameter.aspx
The -E startup option alters the proportional fill algorithm so that 4 mb (64 extents of 64k) is written before changing the write destination to another file in the filegroup for the database. This increases the possibility of uneven file growth for databases with mutliple database files. Trace flag 1117 causes all database files in the filegroup to trigger for expansion at the same time, instead of only expanding the particular file in need of expansion. (I'll just mention that each file in the filegroup for the database should start at the same size and have the same expansion configuration if you want to keep database files equally sized and weighted over time this way.) Most often people think of the benefits for tempdb files with this trace flag. But, if you've enabled the -E startup option and don't also have trace flag 1117 in place, consider it.
TF1117
http://blogs.msdn.com/b/ialonso/archive/2011/12/01/attempt-to-grow-all-files-in-one-filegroup-and-not-just-the-one-next-in-the-autogrowth-chain-using-trace-flag-1117.aspx
If you want to max out the sequential readahead possiblity for tables with the -E startup option, also consider using trace flag 1118. This flag converts the allocation algorithm so that all extents are uniform extents and no mixed extents are used.
TF1118
http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
Trace flag 8048 taught me the term "spinlock convoy". :) After the optimizer has prepared the query plan, a query memory grant request is made. If there is room in the "granted workspace" when the request makes it to the next position in the FIFO queue for grant requests, the request will be granted. But, here's a secret: the grant is not "ready to eat." Its kind of like a line of credit - the query can ask for that much memory as its processing threads progress. There might be cached database blocks in "reserved query memory" for that grant for quite some time. As the query progresses, it can request memory from the remaining reserve of its grant, and when its allocated its ready to use.
Time for secret number two: by default, query memory allocations go through a chokepoint. All query allocations from schedulers with the same os_node_id (typically a NUMA node) just might compete with each other at the chokepoint for a spinlock resource. So if you have a batch report workload with three or four times as many concurrently executing queries as physical cores (either on a given NUMA node or server-wide) there are lots of opportunities for spinlock contention. Get enough spinlock contention and there will be more CPU burnt up spinning than doing database work.
Trace flag 8048 removes that chokepoint by promoting the "per-NUMA node serialized" memory allocations to "per core serialized". CMEMTHREAD waits and there associated spins virtually disappeared on the systems I worked with to diagnose this issue and put the startup trace flag in.
TF8048
http://blogs.msdn.com/b/psssql/archive/2011/09/01/sql-server-2008-2008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048.aspx
http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx
TF8015
This trace flag tells Sql Server to ignore the NUMA setup of the server, and manage a single group of schedulers for task and connection distribution, as well as manage a single bpool instead of one scheduler group and one bpool per NUMA node. For some workflows, there can be considerable efficiency gained by telling SQL Server to ignore the NUMA boundaries. I'll have to spend more time later explaining why.
But... don't put trace flag 8015 in without trace flag 8048. Otherwise all of your schedulers will compete at a SINGLE chokepoint for query memory allocation. If there was query memory allocation spinlock contention before, for example, on a 4 NUMA node server and you put trace flag 8015 in place without trace flag 8048 - there's a good chance that spinlock contention will skyrocket.
****UPDATED on 2013-04-23 by sql.sasquatch ****
I think that a recent SQL Server 2012 CU3 hotfix, and some other factors, may increase interest in the combination of startup trace flags 8015 + 8048.
Here's a rundown of the documentation from which I gleaned the benefits and liabilities of disabling SQL Server NUMA support with trace flag 8015. Enjoy!
The following KB hotfix doc and related forum discussion hint at issues with SQL Server NUMA support. One option to be evaluated is disabling database level NUMA support with trace flag 8015. Do so only in combination with trace flag 8048, and only after understanding the potential benefits (no away or foreign buffers tracked by SQL Server, different IO profile with a single buffer pool, different pattern of task assignment especially for parallel queries) and liabilities (single lazy writer, single IO completion port, potential loss of memory affinity.)
KB 2819662 SQL Server 2012 performance issues in NUMA environments
http://support.microsoft.com/kb/2819662
NUMA and PLE on SQL Server 2012 - private build 11.0.3351.0
http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx
SQL Server NUMA support effects task assignment as well as management of the buffer pool. In addition, SQL Server NUMA support allows one lazy writer and IO completion port per NUMA node; disable NUMA support and there will be only one lazy writer and one IO completion port for the SQL Server instance.
How It Works: SQL Server 2008 NUMA and Foreign Pages
http://blogs.msdn.com/b/psssql/archive/2010/02/23/how-it-works-sql-server-2008-numa-and-foreign-pages.aspx
How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)
http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx
How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes
http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx
How It Works: SQL Server 2005 Connection and Task Assignments
http://blogs.msdn.com/b/psssql/archive/2008/02/12/how-it-works-sql-server-2005-connection-and-task-assignments.aspx
NUMA Connection Affinity and Parallel Queries
http://blogs.msdn.com/b/psssql/archive/2007/06/28/numa-connection-affinity-and-parallel-queries.aspx
Growing and Shrinking the Buffer Pool Under NUMA
http://msdn.microsoft.com/en-us/library/ms345403%28v=sql.105%29.aspx
****END 2013-04-23 UPDATE by sql.sasquatch ****
TF2389 and 2390
http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx
http://www.sqlmag.com/article/tsql3/making-the-most-of-automatic-statistics-updating--96767
Dealing with statistics for ascending keys - fun stuff. With trace flag 2389 enabled, the nature of statistics updates will be tracked and ascending key stats will qualify for quick stats updates when a query cares about them. Trace flag 2390 will do the same for statistics that are unknown - which they all will be until after 3 updates. On the systems I work with, the potential expense of stats updates for the unknowns for three updates is greater than the cost of waiting for the benefits until after three updates. So we'll likely just recommend trace flag 2389 without 2390. Trace flag 2388 can be used within a session to see how a given structure has been branded.
TF2371
If you've got honkin' big tables and don't regularly update stats (whether as part of an index rebuild or a specific stats update job), its quite possible that the stats could be way out of date in those honkers, due to the change threshold of 20%+512 for an auto stats update. Enter trace flag 2371, which introduces a sliding scale which can result in more frequent auto stats updates for the largest tables.
http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
Who didn't make the cut? Trace flag 2301 was one that I had very high hopes for - the reports in the batch report workload I work with have very complex predicates. Since the optimizer rules enabled by trace flag 2301 are DSS/analytics focused, I had high hopes. But our in-house testing showed no discernible benefit. So, for now, this one gets the cold shoulder from me :) But it very well could help out another DSS/analytics/datawarehouse workflow. It'll make query compile more expensive, so any benefit to plan choice has to be weighed against the added expense in query compile. For many DSS/analytics/datawarehouse workflows the optimization of plan selection might outweigh the overhead in compilation, since the count of compiles is generally small compared to the execution time of the queries.
http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582219.aspx
Here's my disclaimer again: trace flags are not toys. Don't deploy
them in production until you are familiar with their expected effects
(so they can be measured) and their possible side effects (so you can
look out for signs of danger). Startup trace flags are not necessarily
more dangerous than global trace flags... but since they require a
restart of SQL Server to enable/disable I advise even more consideration
for them.
No comments:
Post a Comment