Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NUMA and PLE on SQL Server 2012


NUMA and PLE on SQL Server 2012

Author
Message
Don Halloran
Don Halloran
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1392
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 Tongue

Blog on sqlservercentral
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
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

Follow @sqlscribe
sql_handle
sql_handle
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 114
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
Don Halloran
Don Halloran
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1392
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.

Blog on sqlservercentral
ewebb 23926
ewebb 23926
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 160
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
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
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
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

Follow @sqlscribe
Don Halloran
Don Halloran
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1392
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)

Blog on sqlservercentral
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
allmhuran (4/11/2013)
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)


Same build - 3351 ;-)

Tommy

Follow @sqlscribe
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
Tommy Bollhofer (4/11/2013)
allmhuran (4/11/2013)
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)


Same build - 3351 ;-)


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

Follow @sqlscribe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search