Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Page life expectancy diving to sub-10 on a 128 GB server Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 7:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:34 PM
Points: 1,157, Visits: 3,255
TheSQLGuru (3/14/2013)
You have been going round and round with this for over 2 weeks now. May I suggest that you get a performance tuning professional on board to really help you diagnose and correct the problem(s) you are encountering? Seems somewhat silly to hunt-and-peck on a forum for production problems that a good tuner could pinpoint and resolve in a matter of hours (or at worst a day or two)!!


Kevin, for what it is worth the MS product team has since confirmed to us that this is in fact a defect. While I am unable to confirm what Don is experiencing is related to the same defect, the behavior is strikingly similar. To your point however, I would escalate the case with MS, perhaps use the SR we already have open as a starting/reference point.


Tommy

Post #1430951
Posted Thursday, March 14, 2013 4:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:41 PM
Points: 316, Visits: 1,135
TheSQLGuru (3/14/2013)
You have been going round and round with this for over 2 weeks now. May I suggest that you get a performance tuning professional on board to really help you diagnose and correct the problem(s) you are encountering? Seems somewhat silly to hunt-and-peck on a forum for production problems that a good tuner could pinpoint and resolve in a matter of hours (or at worst a day or two)!!


As I said earlier:

I've opened a premier support case on it,


And and as I also said earlier

I have been working with SQL Server for a bit over 10 years


So while I've acknowledged that there are defintely a few people around this place that know more than I do, I'm no accidental DBA.

You can also see that another forum user is reporting similar problems, so I'm keeping this thread updated with new information mostly for his benefit, clearly the normal "can answer just about everything" users of the forum were unable to provide much assistance in this particular case.

I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral
Post #1431292
Posted Thursday, March 14, 2013 6:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 36,793, Visits: 31,250
allmhuran (3/14/2013)I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


Nah... don't let a couple of answers that you consider to be rude ruin it for you. There's a ton of good folks on this forum including some of the ones that you thought were rude. They've (actually, we've) got your best interest at heart even if they (we) come across a little heavy handed or miss a read on a post now and then.

You should see me when someone posts something like a recursive CTE or While loop that counts sequentially and insists that is HAS to be done that way. There's also a lot lost in the written word.

If you really want to do a litmus test on rudeness, post a question about a table design that uses an IDENTITY column named "ID" and wait for a certain famous author to show up.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1431315
Posted Thursday, March 14, 2013 7:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 23,081, Visits: 31,618
Jeff Moden (3/14/2013)
allmhuran (3/14/2013)I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


Nah... don't let a couple of answers that you consider to be rude ruin it for you. There's a ton of good folks on this forum including some of the ones that you thought were rude. They've (actually, we've) got your best interest at heart even if they (we) come across a little heavy handed or miss a read on a post now and then.

You should see me when someone posts something like a recursive CTE or While loop that counts sequentially and insists that is HAS to be done that way. There's also a lot lost in the written word.

If you really want to do a litmus test on rudeness, post a question about a table design that uses an IDENTITY column named "ID" and wait for a certain famous author to show up.



No kidding. He'll ride in on his high horse, spout you don't know nothin' and databases and that you need to use ISO Standards for everything, drop some potentially unworkable code and then ride off again.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431327
Posted Thursday, March 14, 2013 8:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 21,340, Visits: 15,012
Lynn Pettis (3/14/2013)
Jeff Moden (3/14/2013)
allmhuran (3/14/2013)I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


Nah... don't let a couple of answers that you consider to be rude ruin it for you. There's a ton of good folks on this forum including some of the ones that you thought were rude. They've (actually, we've) got your best interest at heart even if they (we) come across a little heavy handed or miss a read on a post now and then.

You should see me when someone posts something like a recursive CTE or While loop that counts sequentially and insists that is HAS to be done that way. There's also a lot lost in the written word.

If you really want to do a litmus test on rudeness, post a question about a table design that uses an IDENTITY column named "ID" and wait for a certain famous author to show up.



No kidding. He'll ride in on his high horse, spout you don't know nothin' and databases and that you need to use ISO Standards for everything, drop some potentially unworkable code and then ride off again.


Is it an African or European high horse?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1431339
Posted Thursday, March 14, 2013 9:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:41 PM
Points: 316, Visits: 1,135
Jeff Moden (3/14/2013)
allmhuran (3/14/2013)I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


Nah... don't let a couple of answers that you consider to be rude ruin it for you. There's a ton of good folks on this forum including some of the ones that you thought were rude. They've (actually, we've) got your best interest at heart even if they (we) come across a little heavy handed or miss a read on a post now and then.

You should see me when someone posts something like a recursive CTE or While loop that counts sequentially and insists that is HAS to be done that way. There's also a lot lost in the written word.

If you really want to do a litmus test on rudeness, post a question about a table design that uses an IDENTITY column named "ID" and wait for a certain famous author to show up.



Haha, yeah, no doubt.

I remember having some good debates with Mr Celko a few times back in the days when microsoft.public.sqlserver and microsoft.public.sqlserver.programming were the primary sources of good information. But I also remember a lot of bad debates there, and I've definitely seen them here (edit: and understand why people get short after a while. I remember one regarding whether updates of the form set col.x=col.x actually cause changes on the page... that one was hilarious as an observer). I'm quite familiar with most of the names here even though I'm a bit of a lurker most of the time. I figured Gail was just having a rough day, so no harm no foul. I think I migrated from usenet to here in around 2007. IIRC one of the first things I saw that made me think "yep, here is good" was something to do with your formatting preferences Jeff :)

PS: I agree with Joe on the "ID" thing. ISO11179 suggests that leaving out the class term when there is no qualification term is a bad idea. I tend to go along with the high level principles of ISO11179... which I first saw referenced in a post from Mr C on usenet many years ago! But I less strictly opposed to the idea of a surrogate key, as long as the dev knows why they need it ;)


allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral
Post #1431341
Posted Friday, March 15, 2013 3:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
I'm not sure what happened to this forum, the level of courtesy and respect seems to have dropped off. Perhaps it has become too cliquey.


1) I was (as I always attempt on this forum) honestly giving you the best advice I could give for the situation. Some things really cannot be solved using forums, for a variety of reasons. Some things aren't appropriate for a forum, given that they are "staffed" by volunteers and thus are best for simple, direct issues. Some things could be solved by a forum post or two but the OP may be incapable of carrying out the change or following along. Some things are so critical to the OP that it cannot wait (and yet you wouldn't believe how many think everyone on the forum should drop what they are doing and get this person's production system fixed up). So I do recommend professional help a fair bit for those and other reasons.

2) Sorry you were offended that "I" think it is silly to spend weeks going round and round for a solution to a production database issue.

3) As for 10 years with SQL Server, sorry again but "time on the box" doesn't carry much weight with me. I have come across far too many "old hats" at clients that I wouldn't trust to manage the Access database I use to track my time! For some posters I have collected enough information to be able to know more about their knowledge and skill level. Until such time, everyone pretty much gets lowest-common-denominator guidance. I would rather someone feel I am talking down to them than miss helping someone because I went over their head - or worse lead to someone doing something unfortunate because they didn't really understand something I put out there.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431800
Posted Thursday, March 21, 2013 12:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:34 PM
Points: 1,157, Visits: 3,255
FYI, just as a follow-up to thread I had going (NUMA and PLE on SQL Server 2012).

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

There are a number of other fixes in this particular CU which address a host of performance issues. Most notably:

FIX: CPU spike when there is no load on a server after you install SQL Server 2012 on the server
http://support.microsoft.com/kb/2813214

FIX: A query that runs against a DMV enters an infinite loop in SQL Server 2008 R2 or in SQL Server 2012
http://support.microsoft.com/kb/2819662

FIX: Poor performance in SQL Server 2012 when you run a SQL Server trace
http://support.microsoft.com/kb/2803529

FIX: Memory leak when you run queries against a temporary table in a nested stored procedure in SQL Server 2012
http://support.microsoft.com/kb/2803065


Tommy

Post #1433962
Posted Friday, March 22, 2013 7:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:41 PM
Points: 316, Visits: 1,135
Yep, we've been working with MS via premier support for the past week, sending gigs worth of text logs. The issue has been escalated to development, but I put CU3 on last night. I'm still seeing the occasional PLE crash but usually it's down to a still-reasonable value, it only floored to zero three times today, and only on one NUMA node. Yet to determine whether that was related to query activity (I have an EE capture running to find that out, but it's late here, and it's Friday... )

allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral
Post #1434249
Posted Thursday, April 11, 2013 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:42 PM
Points: 9, Visits: 87
Depending on your workload, even with MSSQL 2012 SP1 CU3 in place, there may be significant gains in running with startup trace flags 8015 and 8048 in place. 8015 tells SQL Server to ignore NUMA: one bpool, one scheduler group. The cost: a single lazy writer and a single IO completion port instead of one of each per NUMA node. And, lower level of memory affinity.

Trace flag 8048 removes spinlock contention within a scheduler group during query memory allocation. By default, all members of the scheduler group (usually a NUMA node) go through one serialization point for query memory allocation. With trace flag 8015 in place, instead of all schedulers within a NUMA node at risk for contention, it becomes ALL schedulers. Trace flag 8048 promotes serialization at the NUMA node/scheduler group level to serialization to the core level, removing this bottleneck.

Spent lots of time trying to replicate huge numbers of foreign pages on multiple NUMA nodes, something I saw in the field across numerous 2, 4, and 8 node systems.

Couldn't reproduce the foreign pages. But, comparing test runs of a batch report workflow (thousands of reports, 120 concurrent reports) with no trace flags (and no foreign pages after max server memory) to TF 8015 + TF 8048 showed a reduction of approximately 25% in disk IO and approximately 10% elapsed time. Some workflows just work better with a single bpool and a single scheduler group.
Post #1441176
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse