NUMA and PLE on SQL Server 2012

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL 2012 - General » NUMA and PLE on SQL Server 2012

27 posts, Page 2 of 3

««1 2 3 »»

NUMA and PLE on SQL Server 2012

Rate Topic

Display Mode

Topic Options

Author

Message

Don Halloran

Posted Friday, March 15, 2013 5:09 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 312, Visits: 1,037

Thanks Tommy. I've already got collectors running and premier support provided me with some additional tools to help diagnose the issue... I sent about 750 meg worth of log data to them yesterday. I hope that's enough :P

allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral

Post #1431842

Tommy Bollhofer

Posted Thursday, March 21, 2013 11:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148

FYI, CU3 for SQL Server 2012 SP1 has been released which includes the following hot fix.

SQL Server 2012 experiences performance issues in NUMA environments
http://support.microsoft.com/kb/2819662

CU3 for SQL Server 2012 SP1 is available via:
http://support.microsoft.com/kb/2812412

Tommy

Post #1433928

sql_handle

Posted Thursday, April 11, 2013 3:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 12:49 PM
Points: 8, Visits: 36

Here's the thing: the SQL Server 2012 SP1 CU3 fix is probably a good thing - I'm waiting on more details on it. But, the behavior it addresses goes all the way back to SQL Server 2005. No fixes yet for versions SQL Server 2005, 2008, 2008 R2. So for others experiencing similar issues, trace flag 8015 which the kb article http://support.microsoft.com/kb/2819662 may be a solution. But, please consider using startup trace flag 8015 together with startup trace flag 8048 to avoid spinlock contention (and the possibility of a spinlock convoy).
If the MSSQL 2012 SP1 CU3 fix is targeted to the handling of NUMA node foreign and away buffers, there still could be a considerable benefit to TF 8015 + 8048 on that version or later. That's because some workloads just work better with a single large bpool and a single large scheduler group.
Worked for quite a while to reproduce in a test lab the large numbers of persistent foreign pages I was seeing in the field on two, four, and eight NUMA node servers. I wasn't able to*. But, on a four NUMA node server with no foreign pages after achieving max server memory the test workload (thousands of batch queries submitted at concurrency of 120 queries) required ~25% more disk IO and ~10% more elapsed time to complete than the comparative test runs with trace flag 8048 and trace flag 8015 in place.
So, dealing with foreign/away buffers is an important piece of the puzzle, but its far from the only piece of the puzzle. For many systems SQL Server 2008 R2 and before, TF 8048 + 8015 will be the only available resolution until a future CU/SP. But, even then, depending on the scope of the fix therein, TF 8048 + 8015 may offer more performance benefits.
The tradeoff is that systems with TF 8015 in place will have only one lazywriter and one IO completion port, instead of one lazy writer and one IO completion port per NUMA node. But... that's how most SQL Server on VMWare or Hyper-V will also operate. And many systems will not be adversely effected by a single lazy writer or IO completion port (keep watch on checkpoint performance to verify).
Sooner or later I'll post at sql-sasquatch.blogspot.com more details of the test system and perfmon/spin/wait stats from the test workloads. Also diagrams of the mechanics as I understand them, and some deidentified stats from 4 and 8 NUMA node servers in the field. It takes me forever to prepare that stuff, sorry.

*I now believe that the high persistent foreign pages was due to interaction of complex ETL and/or SSIS packages with "right-sized" systems (relative to SQL Server max server memory and other expected SQL Server memory needs). Without much free memory on these systems, SQL Server couldn't continue to perform memory sorting to eliminate the foreign pages. On the other hand, the test lab systems always had super-sized memory, to allow for multiple iterations of tests at various memory configurations. With more free memory available at various max server memory levels, SQL Server was able to sort memory as it wanted and keep the number of foreign memory pages to a minimum.
http://msdn.microsoft.com/en-us/library/ms345403%28v=sql.105%29.aspx

Post #1441146

Don Halloran

Posted Thursday, April 11, 2013 3:36 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 312, Visits: 1,037

We have been looking at a similar problem, you can find my thread on it here http://www.sqlservercentral.com/Forums/Topic1424826-2799-1.aspx

After working with MS on this for a couple of weeks we have received a hotfix.

The bug was apparently introduced by SP1. All we know so far is that it has something to do with SQL not flipping off the low memory flag, although we haven't heard why it would have been flipped on in our environment yet. For those who haven't seen the other thread, my PLE graph looked like shark's teeth, with gradual climbs into reasonable values and precipitous crashes back to zero happening half a dozen or more times a day.

We've tested the fix in a distributed replay environment and the graph now looks as it should... slow and steady PLE growth throughout the execution of the replay, and we'll be pushing it into prod tonight. I'd expect this to be integrated into CU4.

allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral

Post #1441152

ewebb 23926

Posted Thursday, April 11, 2013 6:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 1, Visits: 58

Wow, we have been fighting this same issue and it is really driving us nuts. Is there any reference info on the hotfix you can provide so we can get it? We have the same issue where it appears that sql believes there is a low memory situation and begins dropping the buffer cache with a corresponding drop in PLE. All the memory is released to free memory and not used. The buffer will then try to fill back up then drop to zero again. This will eventually clear itself or if I run DBCC FREESYSTEMCACHE ('ALL') it will return to normal right away.
Again, any help would be appreciated.
Thanks,
Erik

Post #1441211

TheSQLGuru

Posted Thursday, April 11, 2013 7:08 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 3,575, Visits: 5,115

Thanks for the feedback on this. Did they post a public hotfix yet or is it still a private build? If the latter, please post the link here.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #1441227

Tommy Bollhofer

Posted Thursday, April 11, 2013 7:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148

TheSQLGuru (4/11/2013)

Thanks for the feedback on this. Did they post a public hotfix yet or is it still a private build? If the latter, please post the link here.

It is still a private build. Below is the email we received from the MS Development team.

"We are done with the data analysis and have confirmed that this behavior is a bug. We have a private fix for this issue available now. Do you have a test environment on which we can validate this fix? Assuming the issue is reproducible in your test environment."

If anyone else is experiencing this issue, feel free to PM me offline and I can provide our SR number. Might save some time when contacting MS.

Tommy

Post #1441236

Don Halloran

Posted Thursday, April 11, 2013 8:48 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 312, Visits: 1,037

For mine, we received a private build, basically a direct copy over the sqlservr.exe and a few dll's. It took us to the following @@version. Tommy, I'd be curious as to whether you end up with the same version if you take the fix.

Microsoft SQL Server 2012 (SP1) - 11.0.3351.0 (X64)
Apr 5 2013 10:52:23
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral

Post #1441295

Tommy Bollhofer

Posted Thursday, April 11, 2013 2:50 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148

allmhuran (4/11/2013)

Same build - 3351 Wink

Tommy

Post #1441485

Tommy Bollhofer

Posted Tuesday, April 16, 2013 7:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148

Tommy Bollhofer (4/11/2013)

allmhuran (4/11/2013)

Same build - 3351 Wink

FYI, we ran into a bunch of issues with CLR using this build (3351)

Failed to initialize the Common Language Runtime (CLR) v4.0.30319 with HRESULT 0x8013141a.

Apparently the product team is looking into it. I'll let you know if we receive a different build to address it.

Tommy

Post #1442730

« Prev Topic | Next Topic »

27 posts, Page 2 of 3

««1 2 3 »»

Permissions