SAP Support for Microsoft KBA 2964518

{NOTE – The SAP Note hyperlinks are only available if you have an S-User account for access.}

Microsoft published KBA 2964518 that recommends a few different Trace Flags (TFs) in Table 1.   SAP describes a list of TFs that it recommends to best support the SQL Server environment.  For SQL Server 2012 that list is described in SAP Notes 1702408 and 1725220.   And then, that list is expanded for SQL Server 2014 in SAP Notes 1961467 and 1986775.   Additionally, Table 2 of KBA 2964518 lists the recommended Support Packages and Cumulative Updates to get over various challenges that might be encountered.   This contrasts with the SAP note where a Minimum Supported version of SQL Server SP and CU is recommended (See SAP Note 62988 for details).  This post is centered on resolving the differences where they exist.

There is consistency from both perspectives that TF 1117 and 1118 are recommended.  They will not be further discussed here.  

TF 617 is recommended on SAP systems because of the unique way that SAP leverages uncommitted read sessions.   TF 2371 has also been repeatedly demonstrated to deliver positive results on SAP systems.  Similarly for SQL Server 2014 TF 9481 is needed, as is described in the SAP Note 1961467.  Even though none of these trace flags are recommended in the Microsoft KBA, it is necessary that you enable them as recommended in the SAP notes.

That leaves 4 TFs that the Microsoft KBA recommends that SAP does not mention: 1236, 8032 (Table 3), 8048, and 9024.   SAP Application Servers do not perform thousands of connection resets (leveraging connection pooling), so as a result TF1236 is not valuable in the SAP operational world.    That leaves the 8048,  9024, and the 8032 (from Table 3) TFs.   We have seen some benefit from these for customers that have run into those symptoms, but in general they haven’t been advocated broadly for all customers.

In Summary, here are the recommendations:

Trace Flag RECOMMENDATION                                                                        

617, 1117, 1118, 2371                      Set them for SQL Server 2012 and 2014

9481                                                  Set it on all SQL Server 2014 systems

1236                                                  No need to set it on SAP Systems

8032, 8048, 9024                              Set if relevant to your environment.   But don’t set without justification

Support Package (SP) and Cumulative Update (CU)

2012 SP1 CU7 (11.0.3393) or Later             Should any problems be encountered, move to the latest Service Pack (SP) and to the latest provided Cumulative Update (CU) that is available.

2014 CU6 (12.0.2480) or later                      As noted HERE, SQL Server 2014 is now officially supported.

Details

Regarding the 3 trace flags: 3032, 8048, and 9024, inspect the linked KBAs in KBA 2964518 for more information on them.   TF 8048 is specific only to NUMA machines having 8 or more cores per socket.  If you are trying to maximize performance on NUMA before leveraging this TF you should be using a recent Windows version of the OS; Windows 2012 or preferably 2012 R2.   TF 9024 is valuable only to Always On Availability Groups.  TF 8032 has not been seen to bring any value to SAP systems yet.   These trace flags are supported in your operational environments, if they are warranted.   But generally, avoid setting other SQL Server TFs unless you have justification for their usage.   As an example, KBA 974006 offers a query optimizer TF that can be used sometimes for troubleshooting, it should only be used under advice from a support professional since its impact to normal SAP software operation is unknown.

Regarding the appropriate SQL Server SP and CU that you should use: Make sure to use a SQL Server build that is at the same or later SP and CU as described in SAP note 62988.  The versions listed in that note are intentionally referenced as the “Minimum version”.   The minimum version is increased as we encounter problems that either most SAP customers will likely hit or have sufficiently severe potential risk to warrant making every effort to avoid the problem.   So again, strive to stay on a SQL Server build that is at least the minimum version or later of the product.   But should you encounter any problems that are fixed in any SP or CU after the build that you are currently running, then move directly to the latest SP and CU that is available.   This position is supported by the statement in note 62988, “We recommend that you update your SQL Server installation regularly. Always import the most recent Support Package, the latest Security Update, and the most current Cumulative Update as soon as they are available”.  We understand that many customers have their own operational guidelines that advocate a more conservative approach than this, but when faced with symptoms that may already be addressed it can help resolve problems faster by moving to the most recent build as soon as convenient.   This will also allow a quicker response by support engineers to be able to capture information involving the most recent bits, should you be hitting a new problem never encountered before.

Remember too, that SQL Server SPs and CUs are cumulative, outside of the SP roll-up window, as is described here.   The SP “roll-up window” refers to those hotfixes and CUs that get delivered at the tail end of one SP code line while the next SP is getting evolved.   Those late arriving hotfixes and CUs are then rolled into the early CUs after shipping the next SP.   Because of this “SP roll-up window” it can sometimes take 1 to 3 CUs until all of the late arriving hotfixes of the previous SP get delivered in the most recent SP.   And because of this, a greater numbered build might not have all of the fixes of a lesser numbered build.   And this explains why many KBA fixes list differing CUs for the SPs where they get shipped.  From reading KBA 2995352 it can be seen: The fix was first shipped in 2012 sp1cu13 (i.e. 11.0.3482) and then was also shipped in sp2cu4 (i.e. 11.0.5569), but if you were on any other SP2 build before cu4 (RTM, sp2cu1, sp2cu2, or sp2cu3) you would not have that fix.   So, you could have had the fix in sp1cu13; applied sp2 (which was a later build than sp1cu13) and lost the fix until you moved to the sp2cu4 build that had the fix again.  This is the “SP roll-up window”.

So returning finally to the SP and CU situation, say you are on SQL Server 2012 SP1 CU7 and your alert DBA notices some surprisingly slow tempdb I/O and is aware of KBA 2958012 from looking at the KBA at the top.  They could recommend moving to SP1 CU10 or to SP2 and CU1.  But why?  Since you are aware that SPs and CUs are cumulative {outside of the SP rollup window} you could simply move to the latest SP and CU that is available.   Which for 2012 SP2 can be found in KBA 2983249.   And you would also gain all of the additional fixes that were released in that CU and might potentially avoid additional maintenance downtime.