InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:13:22 GMT20http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]allmhuran (4/18/2013)[/b][hr]I'm not 100% convinced. We only had one weekday with the hotfix installed, but this graph of timeouts captured on the application servers seems to indicate pretty clearly that RTM is better than SP1 with and without the 3351 fix installed:[img]http://i.imgur.com/pX8x1FP.jpg[/img][/quote]After several days of testing, I'm not convinced either. I stood up an RTM instance after you posted this for good measure and I'm seeing the same behavior. Still some work to do with the 3351 build. MS is supposedly going to produce a new build in the coming weeks for us to test.Wed, 24 Apr 2013 08:25:54 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]allmhuran (4/24/2013)[/b][hr]Having said that, in my opinion 2008R2 running on half the hardware was performing than 2012 RTM.[/quote]WOW! That's quite a statement!! :crying:Wed, 24 Apr 2013 07:12:35 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx2012 RTM is definitely better than SP1. No CU's applied, although if you go to 7 it should be OK, apparently this specific problem appeared with SP1.Having said that, in my opinion 2008R2 running on half the hardware was performing better than 2012 RTM is performing. Unfortunately infrastructure can't provide me with detailed stats from that far back for a proper comparison.Wed, 24 Apr 2013 07:10:31 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxI also have a very similar issue.Are you saying that you fixed your PLE-problem by rolling back to the RTM version ?Are you using plain RTM, or have you applied any CU?Wed, 24 Apr 2013 06:38:58 GMTStefan_Ghttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxI'm not 100% convinced. We only had one weekday with the hotfix installed, but this graph of timeouts captured on the application servers seems to indicate pretty clearly that RTM is better than SP1 with and without the 3351 fix installed:[img]http://i.imgur.com/pX8x1FP.jpg[/img]Thu, 18 Apr 2013 23:01:36 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]allmhuran (4/16/2013)[/b][hr]Haha, likewise. The problem is a version incompatibility between sqlservr.exe and sqlaccess.dll. We've just been sent a link to an updated one, but we had already decided to roll back to RTM, couldn't wait any longer.RTM seems much better. I'm going to hold out for SP2.[/quote]We disabled strong name validation to work around the CLR issue; i.e. [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\StrongName\Verification\*,*]Testing thus far confirms build 3351 resolves the issue. I imagine we will see this make its way into CU4 and SP2.Thu, 18 Apr 2013 14:37:04 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxHaha, likewise. The problem is a version incompatibility between sqlservr.exe and sqlaccess.dll. We've just been sent a link to an updated one, but we had already decided to roll back to RTM, couldn't wait any longer.RTM seems much better. I'm going to hold out for SP2.Tue, 16 Apr 2013 17:05:18 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]Tommy Bollhofer (4/11/2013)[/b][hr][quote][b]allmhuran (4/11/2013)[/b][hr]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)[/quote]Same build - 3351 ;-)[/quote]FYI, we ran into a bunch of issues with CLR using this build (3351)[i]Failed to initialize the Common Language Runtime (CLR) v4.0.30319 with HRESULT 0x8013141a.[/i]Apparently the product team is looking into it. I'll let you know if we receive a different build to address it.Tue, 16 Apr 2013 07:18:15 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]allmhuran (4/11/2013)[/b][hr]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)[/quote]Same build - 3351 ;-)Thu, 11 Apr 2013 14:50:19 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxFor 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)Thu, 11 Apr 2013 08:48:20 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]TheSQLGuru (4/11/2013)[/b][hr]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.[/quote]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.Thu, 11 Apr 2013 07:23:53 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxThanks 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.Thu, 11 Apr 2013 07:08:02 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxWow, 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,ErikThu, 11 Apr 2013 06:30:50 GMTewebb 23926http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxWe have been looking at a similar problem, you can find my thread on it here http://www.sqlservercentral.com/Forums/Topic1424826-2799-1.aspxAfter 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.Thu, 11 Apr 2013 03:36:28 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxHere'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 [i]together with[/i] 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 [i]work better[/i] 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.aspxThu, 11 Apr 2013 03:24:22 GMTsql_handlehttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxFYI, CU3 for SQL Server 2012 SP1 has been released which includes the following hot fix.SQL Server 2012 experiences performance issues in NUMA environments[url=http://support.microsoft.com/kb/2819662]http://support.microsoft.com/kb/2819662[/url]CU3 for SQL Server 2012 SP1 is available via:[url=http://support.microsoft.com/kb/2812412]http://support.microsoft.com/kb/2812412[/url]Thu, 21 Mar 2013 11:18:37 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxThanks 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 :PFri, 15 Mar 2013 17:09:48 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]allmhuran (3/14/2013)[/b][hr]Ah, well that's interesting. I am seeing performance degradation with the PLE drops as well as the movement of other related counters (eg disk % jumping to 100,000%... yes... one hundred thousand!) when the collapses occur[/quote]Interesting; perhaps the particular issue you are experiencing is unrelated to ours. Nevertheless, there are a host of collections we ran to rule out other issues, if you would like, please feel free to contact email me offline and I will be more than happy to provide the scripts, collections, etc. I would start by dumping the ring buffer resource monitor (script below) before, during, and after the event. [code="sql"]SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time], cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type], cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %], cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint')/1024 AS [SQL_ReservedMemory_MB], cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint')/1024 AS [SQL_CommittedMemory_MB], cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint')/1024 AS [SQL_AWEMemory_MB], cast(record as xml).value('(//Record/MemoryNode/PagesMemory)[1]', 'bigint') AS [PagesMemory_MB], cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')/1024 AS [TotalPhysicalMemory_MB], cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024 AS [AvailablePhysicalMemory_MB], cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint')/1024 AS [TotalPageFile_MB], cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint')/1024 AS [AvailablePageFile_MB], cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint')/1024 AS [TotalVirtualAddressSpace_MB], cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint')/1024 AS [AvailableVirtualAddressSpace_MB], cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],tme.ms_ticks as [Current Time],cast(record as xml) as [Record]from sys.dm_os_ring_buffers rbfcross join sys.dm_os_sys_info tmewhere rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' order by rbf.timestamp ASC[/code]Thu, 14 Mar 2013 19:49:02 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxAh, well that's interesting. I am seeing performance degradation with the PLE drops as well as the movement of other related counters (eg disk % jumping to 100,000%... yes... one hundred thousand!) when the collapses occurThu, 14 Mar 2013 17:00:45 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx[quote][b]TheSQLGuru (3/14/2013)[/b][hr]One thing I don't see mentioned is if PERFORMANCE or actual IO or other metrics change when PLE falls off a cliff. If it doesn't, I would chalk it up as an internal bug that is "meaningless" (and which thus might get a lower priority by MS to fix). If it does, then the issue becomes finding out WHY stuff actually gets flushed out of the buffer pool and address that.[/quote]Hey Kevin, you are correct. For this particular issue, we are not experiencing any performance degradation whatsoever before or after the PLE drop and neither MS nor our team has been able to correlate it with any other metrics, IO or otherwise. So in a sense it is a "meaningless" internal bug that in all likelihood will be a much lower priority for MS to fix.Thu, 14 Mar 2013 07:38:59 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxOne thing I don't see mentioned is if PERFORMANCE or actual IO or other metrics change when PLE falls off a cliff. If it doesn't, I would chalk it up as an internal bug that is "meaningless" (and which thus might get a lower priority by MS to fix). If it does, then the issue becomes finding out WHY stuff actually gets flushed out of the buffer pool and address that.Thu, 14 Mar 2013 07:30:45 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxThanks for the info, I will be sure to check out the thread. We are seeing the same exact behavior. Physical memory is always reported high during these large drops which seem to occur at random and oddly enough during periods of low activity with the largest read at 32K. I opened a connect item for this prior to the Sev-B case (which has been open for over a month now) however the product team closed it rather quickly opting to work offline with us on it instead...Wed, 13 Mar 2013 06:52:33 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxI've been watching PLE closely on our new 2012 box (see thread: PLE drops to sub 10 on 128gb server). Grabbing two nodes and graphing them over several days shows that the lines match almost perfectly a lot of the time, but after large drops (eg, from 30,000+ to low hundreds) they tend to deviate from each other, wiggle around a little bit, and then resynch as PLE regrows back into the several hundreds / thousands.Wed, 13 Mar 2013 00:23:04 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxFYI, we opened an SR for this with MS about a few weeks ago and it is currently escalated to the DEV team. The consensus thus far is that there does appear to be a defect calculating the PLE value in 2012; I will update when more information from MS is available in the event anyone else is experiencing this. Some additional info:SQL Server 2012 Core Edition4 Socket 8 Core Xeon w/ HT enabled, 128GB, 112GB max memoryWe have been collecting the ring buffer outputs for RING_BUFFER_RESOURCE_MONITOR and they do not show any new entries around the time when PLE dropped. Moreover during this timeframe, Page Reads/sec value do not fluctuate much indicating that we did not have to read a bunch of new pages into memory. The query with max reads around this time frame was 32KTue, 12 Mar 2013 07:27:25 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxThank you Grant -Tue, 05 Feb 2013 09:46:32 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxI'm pretty sure that it just comes down to the fact that while processes are distributed, the work isn't and one node can be done most/all of the work while others are effectively asleep. In general, but absolutely not always, I've seen the distribution between the nodes to be reasonably consistent over time, but weird spikes are fairly normal.Tue, 05 Feb 2013 08:27:34 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspxI've read both Paul and Jonathan's blogs regarding this issue (http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/) and started looking at the PLE counters for each individual NUMA node. I can't seem to wrap my head around why there is such a widespread discrepancy between the NUMA nodes. We are running SQL Server 2012 Enterprise Core. Any insight would be greatly appreciated.Thanks,Tommy[url=https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/url][img]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/img]Tue, 05 Feb 2013 07:26:15 GMTTommy Bollhofer