sql.sasquatch

Tuesday, April 9, 2013

AIX enhanced_memory_affinity

Oracle Database on AIX; memory_affinity

What is memory affinity? Memory affinity is a performance strategy to optimize memory latency based on locality of threads and the physical memory they use.

Does it help? For some workloads, its awesome.

Optimizing IBM DB2 pureScale transaction throughput in virtualized IBM Power Systems
http://www.ibm.com/developerworks/aix/library/au-aix-optimize-ibm-db2/au-aix-optimize-ibm-db2-pdf.pdf
This document shows the benefit of optimized memory latency for in-memory database transaction throughout. Also detailed is the attention needed to allocate LPAR resources for optimized memory latency.

How hard is it to get the benefits?
It can be tough. As I mentioned above, attention must be given to resource allocation for LPARs.
In fact, consider the somewhat counter-intuitive recommendations on page 40 of the following document.
When's the last time you heard someone - other than me :) - warn against too many CPUs or too much RAM for the workload? But its right there...

Oracle Architecture and Tuning on AIX v2.30
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883

=====
~~~~~
Don’t over-allocate CPUs
- If a given workload (LPAR) requires <= 16 processors (single CEC), don’t allocate more than 16 processors (2 or more CECs)
- If all the LPARs in a given shared pool require (in aggregate) <= 32 processors (2 CECs), don’t allocate more than 32 processors (3 or more CECs) to the shared pool
- For Shared Processor LPARs, don’t overallocate vCPUs relative to Entitled Capacity
Don’t over-allocate memory
- May cause processors/memory to be allocated on additional CECs because there wasn’t sufficient free memory available on the optimal CEC
~~~~~
=====

OK. So it's not easy to get the benefits of memory affinity. Does it always help? Nope.
Here's the general considerations for a developer.
AIX 7.1 information > Performance management and tuning > Performance management > Memory performance > AIX memory affinity support > Performance impact of local MCM memory allocation
http://pic.dhe.ibm.com/infocenter/aix/v7r1/index.jsp?topic=%2Fcom.ibm.aix.prftungd%2Fdoc%2Fprftungd%2Fperf_impact_mcm_mem_alloc.htm

=====
~~~~~
Applications with threads that individually allocate and reference unique data areas may see improved performance. Applications that share memory among all the threads are more likely to get a degradation from memory affinity.
~~~~~
=====

That is echoed by the following IBM Data Warehouse documentation.
'Infrastructure Solutions: Design, Manage, and Optimize a 60 TB SAP NetWeaver Business Intelligence Data Warehouse'
http://www.redbooks.ibm.com/redbooks/pdfs/sg247385.pdf

=====
~~~~~
Memory affinity only benefits an application if the application’s threads can stay on the same processor that the memory was allocated from. Since most of the memory accessed in a database environment is shared, there is no point in using memory affinity. Also, there is even less need for memory affinity in shared processor environment.'
~~~~~
Often, when there is memory on the freelist but the system is still paging out, a likely cause is that one or more memory pools have fewer memory frames than the other pools. This causes the freelist of these smaller pools to become depleted more quickly. If LRU cannot find enough pages to steal in those pools, then paging could occur.
An easy solution to fix this problem is to let the system create evenly balanced memory pools. The reason why they are not balanced is because when memory affinity is enabled, at least one pool is created from each MCM or DCM that has contributed a processor to that partition. Since the order of boots by different partitions can affect which processor they get, the number of memory pools may differ over subsequent reboots. Also, since the amount of memory left to allocate to the LPAR from that MCM or DCM may be small, one or more memory pools may end up with a lot less memory than the other pools.
~~~~~
=====

And finally, there is a small reference in this IBM/Oracle document, which I keep forgetting about.
Oracle Performance Monitoring on IBM Power Systems
http://www.ibm.com/developerworks/wikis/download/attachments/104533522/Oracle+Performance+Monitoring+on+IBM+Power+Systems.pdf

From page 44:
=====
~~~~~
Memory Affinity
- Not generally a benefit unless processes are bound to a particular processor
- It can exacerbate any page replacement algorithm issues (e.g. system paging or excessive lrud scanning activity) if memory pool sizes are unbalanced
- If there are paging or lrud related issues, try basic vmo parameter or Oracle SGA/PGA tuning first
- If issues remain, use 'kdb' to check if memory pool sizes are unbalanced:
- If the pool sizes are not balanced, consider disabling Memory Affinity:
# vmo –r –o memory_affinity=0 (requires a reboot)
~~~~~
=====

An imbalance in pool sizes makes it more likely that paging will result from a pool running low on free memory (each pool has its own lrud daemon for page stealing and minfree/maxfree apply at the pool level).

So if your pools are imbalanced, and there is a low likelihood of balancing the pools based on LPAR profile, consider disabling memory_affinity.

If your pools are imbalanced, and there is paging in the face of free memory - especially of Oracle SGA contents - please resolve the paging in a manner that doesn't require shrinking the PGA to an unreasonably small size (which will increase pressure on the Oracle disks).

Memory management should be predictable, and a primary intent or Oracle system design should be to keep database cache in physical memory.

Oracle Architecture and Tuning on AIX v2.30
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883
Page 28
2.2.3. AIX file system cache size
=====
~~~~~
With Oracle database workloads, we want to make sure that the computational pages used for Oracle executable code, the Oracle SGA and Oracle PGA, etc. always stay resident in memory.
~~~~~
=====

IBM AIX 7.1, memory_affinity, memplace_shm_anonymous and Oracle SGA

I'm not just an MSSQL.Sasquatch :)

Below is one of my favorite series on Oracle memory behavior on IBM Power systems running AIX.
http://intermediatesql.com/aix/how-oracle-uses-memory-on-aix-part-1-processes/
http://intermediatesql.com/aix/how-oracle-uses-memory-on-aix-part-2-sga/
http://intermediatesql.com/aix/how-oracle-uses-memory-on-aix-part-3-locking-sga/

In these posts, as well as other sources, shmget() is documented as the shared memory allocation function for creating the Oracle SGA.

There's a whole world of fun memory management information waiting at:
vmo -Fa

(The capital F is necessary to see the restricted parameters.)

I believe vmo parameter memplace_shm_anonymous will influence memory allocation for the Oracle SGA.
http://pic.dhe.ibm.com/infocenter/aix/v7r1/index.jsp?topic=%2Fcom.ibm.aix.prftungd%2Fdoc%2Fprftungd%2Fmem_place_vmo.htm

The documentation indicates that, like the other memplace parameters, memplace_shm_anonymous "can either have a value of 1, signifying the first touch scheduling policy, or 2, signifying the round-robin scheduling policy."

Further, the documentation indicates that the default for memplace_shm_anonymous is 2 for round-robin.

But its never really that easy, is it? :)

All of the Power 7 servers with AIX 7.1 that I've seen have had this parameter set to 0.

This document explains that the value of zero means auto-affinitized, "where the system decides the best placement for the memory."
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ74067

I wish there was more information about how the system decides. I'm working with and near a LOT of IBM Oracle where "the system has decided" that a significant portion of the Oracle SGA should be in paging space, even though there are quite a few free memory pages.

Disabling 64k pages helps a bit. On one of the systems memory_affinity was disabled, and that stopped the paging.

I've read a lot about memory_affinity. Too much maybe. But I still haven't been able to figure out the most predictable method of memory management on these servers. Based on the documentation round-up I'll share in a future post, and experiences across several systems, I think I'll be recommending the following for the analytics systems I work with: disable 64k pages, disable memory_affinity, disable enhanced_memory_affinity. 4k only pages while optimizing SGA size and other memory config. If 16mb pages are desired, only after optimal sizes have been determined with 4k-page-only system.

A lot of sysadmins have just acclimated to Oracle SGA in paging space while there is free server memory. That's too bad.

But, like me, they may just have run out of solid answers for memory management.

Friday, April 5, 2013

MSSQL - @@microsoftversion and EngineEdition

Sometimes, its the little things that make me happy. Like learning something that is just a little bit more efficient than the way I accomplished something just last week.

I end up writing more than a few stored procedures to catch logs of various system activity. I used to grab the MSSQL major version and edition as below, in order to decide if I should compress me log table. That's one way to verify that the version is past MSSQL 2005 and that the edition allows for compression. But, I'm not happy with it. Today I learned about @@microsoftversion and and server property EngineEdition. So I give an example of their use below.

~~~~

CREATE PROCEDURE QSP_SUPER_DUPER_DIAGNOSTIC
AS

DECLARE @version NVARCHAR(MAX)
DECLARE @major   INT
DECLARE @edition NVARCHAR(MAX)
DECLARE @sqltext NVARCHAR(MAX)

SET @version = CAST(SERVERPROPERTY('productversion') as NVARCHAR(MAX))
SET @major   = CAST(LEFT(@version,CHARINDEX('.',@version)-1) AS INT)
SET @edition = CAST(SERVERPROPERTY('edition') AS NVARCHAR(MAX))

IF OBJECT_ID(N'TB_SUPER_DUPER_DIAGNOSTIC') IS NULL
BEGIN
    SET @sqltext=
      N'CREATE TB_SUPER_DUPER_DIAGNOSTIC
          (sample_dt DATETIME NOT NULL,
           session_id INT,
           sql_handle VARBINARY(64),
           is_next_candidate BIT
           PRIMARY KEY CLUSTERED (sample_dt,session_id)
           )'
    IF @major > 9 AND @edition LIKE '%ENTERPRISE%'
      SET @sqltext = @sqltext + N' WITH (DATA_COMPRESSION = PAGE)'
    EXEC sp_executesql @sqltext
END

~~~~

Now I can determine eligibility for compression much more cleanly:

CREATE PROCEDURE QSP_SUPER_DUPER_DIAGNOSTIC
AS

DECLARE @major        INT
DECLARE @engEdition INT
DECLARE @sqltext       NVARCHAR(MAX)

SET @major = @@microsoftversion / 0x01000000
SET @engEdition = CAST(SERVERPROPERTY('EngineEdition') AS INT)

IF OBJECT_ID(N'TB_SUPER_DUPER_DIAGNOSTIC') IS NULL
BEGIN
    SET @sqltext=
      N'CREATE TB_SUPER_DUPER_DIAGNOSTIC
          (sample_dt DATETIME NOT NULL,
           session_id INT,
           sql_handle VARBINARY(64),
           is_next_candidate BIT
           PRIMARY KEY CLUSTERED (sample_dt,session_id)
           )'
    IF @major > 9 AND @engEdition = 3 --after 2005, Enterprise, Evaluation, or Developer
      SET @sqltext = @sqltext + N' WITH (DATA_COMPRESSION = PAGE)'
    EXEC sp_executesql @sqltext
END

~~~~

Wednesday, April 3, 2013

Windows Port Exhaustion and Connection Failures

Port exhaustion is far from unique to Windows. I first became familiar with port exhaustion and idle connection behavior by recommending intervention and eventually writing up some best practices for a tiered database architecture on AIX and HP-UX systems. But, here I'll just describe the Windows details.

Although I've seen sources discuss Windows idle connection termination behavior, and other sources discuss TIME_WAIT delay status, and still other sources discuss port exhaustion, I can't think of a single place that I saw all of those concepts tied together in a neat little bundle. That might still be true once I publish this blog post :) At least it'll be here for me to refer to in the future :)

If you want to evaluate a particular delayed/rejected connection situation for port exhaustion, the Powershell script at the following location is a great place to start:
http://blogs.msdn.com/b/debuggingtoolbox/archive/2010/10/11/powershell-script-troubleshooting-for-port-exhaustion-using-netstat.aspx

I recommend evaluating the idle connection and TIME_WAIT behavior regardless, from a best practice standpoint.

Client/server communication typically relies on dynamic ports. Dynamic ports are also known as ephemeral or anonymous ports.
Client communication will often use an available dynamic port on the client system to begin communication with a specified port on the server. The maximum number of connections from that client is then limited to the number of dynamic ports.
Some server activities use dynamic ports as well: a communication request initially arrives on a specific server port listener, and the client connection is handed off to a dynamic server port. FTP is an example of server activity which uses dynamic ports.
Port exhaustion occurs when there are no available dynamic ports for new connections. If dynamic ports are used on the client and server side of the connections, port exhaustion could occur on the client or server system.

What can lead to port exhaustion? I'll only give a few examples here. But the main concepts involved are the limited number of dynamic ports on the client and/or server, the amount of time to terminate idle connections, and the amount of time before a "closed" dynamic port can be reused.
If a web service goes crazy and starts gobbling up dynamic ports, all available dynamic ports can get chewed up real fast. If it keeps those connections open, as long as they are open no more connections for that protocol will succeed.
If it closes the connections, there could still be a problem. That's because of the amount of time that a port will wait in the TIME_WAIT state after closed, before it can be reused. By default this is 4 minutes. A crazy web service could keep the connection inflow rate equal to the port available rate for a long time, leading to lots of connection failures.
Here's another thing to add to the mix: if a connection isn't properly closed, whether due to the connection being severed between client code and server code (power outage, switch crash or even intrusion prevention), the connection may need to time out as an idle connection before its terminated. By default, that will take more than 2 hours.

So, here's where I've typically seen this come into play in an OLTP setting:
A power outage drops large number of Citrix or web servers from a client server farm. All of the connections on the 'server' server are now idle. They aren't terminated immediately, they need to go through TCP timeout protocol. Assume the number of connections at the time of power loss was 80% of the available dynamic ports. Assume that many connections will be attempted again when those client servers come back on line 20 minutes after the power loss.

That's 160% of the available dynamic ports. Of the new connections, only 1/4 will succeed until hitting the dynamic port limit. Since power was restored 20 minutes after power was lost, that means that 3 out of 4 new connections will be waiting - either for one of the other new connections to terminate and the port becomes available, or for the remaining default 1 hour and 40 minutes until idle connections from the power loss are terminated.

So, what can be done about this?

*Step 1
Verify dynamic port range is sufficient.

For Windows version up to and including Windows Server 2003, the default dynamic port range was 1025 to 5000. In those versions, HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort registry key could set a nondefault max dynamic port number. Windows Server 2003 security bulletin MS08-037 hotfix introduced a new default dynamic port range from 49152 to 65535. That default range is still current through Windows Server 2012 6.2.9200.
http://support.microsoft.com/kb/953230
http://support.microsoft.com/kb/956188

Starting at least with Windows Server 2008 (and maybe with Windows Server 2003 after the security bulletin, I'm not sure), the dynamic port range is specified per protocol.
Check it for each protocol with these commands:
netsh int ipv4 show dynamicport tcp
netsh int ipv4 show dynamicport udp
netsh int ipv6 show dynamicport tcp
netsh int ipv6 show dynamicport udp

A typical recommendation for Microsoft Exchange 2007 systems was a dynamic port range of 1025 to 65535. Seems reasonable for most Windows server systems to me, unless there is a particular reason against it (such as the possibility of consuming too much connection-related memory).

*Step 2
Verify idle connection behavior is appropriate.
For an idle TCP connection, the default Windows behavior is to wait for 2 hours before sending a TCP keepalive packet.
The registry value KeepAliveTime (milliseconds) can specify an alternative elapsed time.
If the keepalive packet is not acknowledged within 1 second by default, another keepalive packet will be sent. Registry entry KeepAliveInterval (milliseconds) specifies this interval.
The number of keepalive packet retries is determined on some Windows versions by registry vlaue MaxDataRetries. Various documents indicate this is hardcoded at 10 in Windows Server 2008 R2.

I recommend setting KeepAliveTime to 300000 milliseconds (5 minutes), dropping expected idle time to 5 minutes and 10 seconds.

I do not recommend changing KeepAliveInterval from the 1 second default. There isn't much to gain there, and I haven't seen recommendations to change this value in any of the sources I've consulted.

While "Additional Registry Entries" below recommends changing MaxDataRetries from default 5 to 3 retries, there just isn't much to gain in terms of idle time expected before termination.

TCP/IP Registry Values for Microsoft Windows Vista and Windows Server 2008
http://www.microsoft.com/en-us/download/details.aspx?id=9152

Windows Server 2008 R2 and Windows Server 2008
>Secure Windows Server
>>Threats and Vulnerabilities Mitigation
>>>Threats and Countermeasures Guide: Security Settings in Windows Server 2008 and Windows Vista
>>>>Additional Registry Entries
http://technet.microsoft.com/en-us/library/dd349797%28v=ws.10%29.aspx

*Step 3
Verify appropriate TIME_WAIT behavior.
The registry value TCPTimedWaitDelay in HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters determines how long a port will remain in TIME_WAIT status after the port has been closed. By default, the value of 240 seconds or 4 minutes is used. So, a dynamic port typically cannot be reused for 4 minutes after communication is complete. The intent for this delay was to allow for efficient handling of a new communication request from the same client. However, the combination of a small dynamic port range and a long wait time for reuse can easily lead to port exhaustion.
This kb article describes how the size of the dynamic port range and the time period specified for TCPTimedWaitDelay can cause port exhaustion for SQL Server client connections.
http://support.microsoft.com/kb/328476
I recommend a TCPTimedWaitDelay of 30 seconds. This aligns with the typical Exchange 2007 recommendation.

*UPDATE 4/29/2013 sql.sasquatch
First time I've noticed this. An 'application layer' TCP keepalive was implemented in SQL Server 2005, with a default timeout of 30 seconds and default interval of 1 second. This is separate from the 'looks alive' and 'isalive' checks used with clustering.
END UPDATE*
http://msdn.microsoft.com/en-us/library/ms190771(v=sql.105).aspx
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx

Tuesday, April 2, 2013

(Startup) Trace Flags I Love

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.

Detecting merry-go-round scans? Rambling about trace flags 8048 and 8015

OK... anyone know of a way to detect/identify merry-go-round scans with SQL Server extended events or another clever way? Its got to be on a loaded system with a performance test workflow that simulates various DSS/analytics batch report loads. So firing multiple queries and counting physical IO in comparison to the size of the scan objects won't work :) I actually want to run the workflow and quantify the merry-go-round scans somehow... then make a change (startup flags 8048 + 8015), repeat the workflow and compare results.

Miscellaneous rambling to follow...

I've spent a lot of time researching, thinking, and arguing about SQL Server NUMA behavior with respect to the ETL and batch report workflows I work with. And arguing about how or why the combination of trace flag 8048 and 8015 would help. (Please, please don't use trace flag 8015 without trace flag 8048 on a busy system. Maybe you'll never hear that warning from anyone other than me. And I'm not gonna give you test results to support my warning. Don't have time. When I draw some pictures for this, maybe they'll be good enough that its clear why 8015 should always be partnered with 8048.)

More than a few folks thought I was tilting windmills: this stuff has been around for 8 years, ol' sasquatch, you really think you found something so simple that NOBODY else seems to care about? After reducing a 20 hour elapsed time to a 4 hour elapsed time with trace flag 8048 and no other changes, I sat in on the phone call where "an expert" wanted to talk about the root cause of poorly tuned queries. Excuse me? EXCUSE ME?!? OK... if we took 16 hours out of the elapsed time with one configuration change, how you gonna wiggle query tuning (which can ONLY reduce the remaining 4 hours of elapsed time) into a "root cause" and wiggle system behavior surrounding NUMA configuration out of that spot?

But I think I'm over that now. Maybe. Those hours of discussion, and the hours I spent researching outside of the discussions, are finally starting to pay off. Some structured in-house performance testing showed repeatable gains with trace flag 8048 + 8015. I'll post more details, and some gangly diagrams of what I think is happening, soon. But in a nutshell what we saw was a ~12% reduction in batch report window elapsed time, and a 25% reduction in total disk read IO during the batch (so there was also a peak and average disk throughput reduction during the report batch window). This was on a 4 socket, 4 NUMA node server. The disk IO reduction was smaller in similar tests run on a 2 socket, 2 NUMA node server - that was exactly what I expected. There are four socket, 8 NUMA node AMD servers out there (due to the glued architecture of at least some AMD processors) - these changes should make their DBAs dance! (Well... if those DBAs work with workflows similar to the ones I work with most of the time, anyway.)

The reduction in IO was expected. The reduction in elapsed time was a little surprising. I've been tracking a particular performance degradation that seems to cohabit with persistent large numbers of "foreign pages" showing up in perfmon for 2 or more NUMA nodes and decreasing database efficiency per CPU percent utilized... but our test systems didn't bring this nasty condition about. So I was pretty much expecting reduced read IO and the same elapsed time with maybe a bit lower CPU utilized, due to less disk IO being managed, and a small amount less MSSQL buffer management. I was pleasantly surprised with the reduction in elapsed time.

But I can't let it rest at "pleasantly surprised". Its part of who I am. Performance degradation AND improvement should both be predictable in my book. The level of benefit may not be predictable due to interrelated factors in the system... but if I make a change and it helps, I can't be completely comfortable until I at least think I understand why.

Enter the merry-go-round scan, also known as advanced scans. Its a feature that is only in editions similar to Enterprise Edition (for example, Developer, Evaluation, Data Center) and absent from Express and Standard. Here's the idea: for a large enough full tablescan, why not let another query that needs to perform the full tablescan glom right on to the current scan wherever its at? One the scan is complete, the johnny-come-lately can complete its work by re-scanning the stuff it missed.

It'll reduce IO if the table is a "buffer-buster". And I think this may be the mechanism by which the elapsed time of the batch report workflow was reduced. But I'd kinda like to know :)

~~~~~

A few notes about these tests. The test database was created from scratch via massive ETLs. After the ETLs, indexes were enabled and built, then defragmented.

After that, there were no modifications to the persistent tables between test runs of the report batches. So, the index rebuilds eliminated fragmentation to the extent they could, and nothing introduced additional fragmentation between the comparative performance runs of the batch report workflows.

Also, none of the indexes rebuilt were "no recompute", so all of the statistics were based on fullscan. As good as they'd get... and again there would have been no statistics changes between the test runs.

Wanted to make those points to prevent the questions (which would be valuable questions) about whether changed statistics could have lead to more efficient plans, resulting in the reduced elapsed time...

Tuesday, April 9, 2013

Friday, April 5, 2013

Wednesday, April 3, 2013

Tuesday, April 2, 2013

sql.sasquatch Archive