March 14, 2013 at 7:14 am
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
Follow @sqlscribeMarch 14, 2013 at 4:58 pm
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.
March 14, 2013 at 6:45 pm
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
Change is inevitable... Change for the better is not.
March 14, 2013 at 7:53 pm
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.
March 14, 2013 at 8:45 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2013 at 9:08 pm
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 😉
March 15, 2013 at 3:30 pm
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! :w00t: 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 on googles mail service
March 21, 2013 at 12:22 pm
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
Follow @sqlscribeMarch 22, 2013 at 7:14 am
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... )
April 11, 2013 at 4:25 am
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.
April 24, 2013 at 7:29 am
We haven't seen much of a problem with foreign memory requests, but it's an interesting idea. We did set up a lab a couple of weeks ago with a premiere support engineer on site to do some testing and send updates back to the product team, so we could give it a go there.
At the moment we are relatively stable on 2012 RTM with no CU's applied, though, so the priority of the issue has been dropped a notch or two. I still think 2012 is underperforming compared to 2008R2, particularly given it's running on almost double the hardware our old 2008R2 box was.
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
We had a Microsoft premiere support engineer on site working with myself and the bloody product team in the states during that time, which eventually resulted in a private hotfix being sent out. You might want to pull your head in.
April 24, 2013 at 8:17 am
Head pulled, and I have you on my list of posters who really know their SQL engine! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 24, 2013 at 9:00 am
TheSQLGuru (4/24/2013)
Head pulled, and I have you on my list of posters who really know their SQL engine! 🙂
My apologies, I have been off the forum for a while and missed a few updates, it looks like you have been in the same boat, but added a new post to Tommy's thread which, on reading it, was obviously more sympathetic to, and understanding of, the situation at hand.
Unfortunately I came to this thread first and got my feathers ruffled. 😛
April 24, 2013 at 9:10 am
allmhuran (4/24/2013)
TheSQLGuru (4/24/2013)
Head pulled, and I have you on my list of posters who really know their SQL engine! 🙂My apologies, I have been off the forum for a while and missed a few updates, it looks like you have been in the same boat, but added a new post to Tommy's thread which, on reading it, was obviously more sympathetic to, and understanding of, the situation at hand.
Unfortunately I came to this thread first and got my feathers ruffled. 😛
No apology needed. Disconnected comms like a forum are rife with opportunites for misunderstandings/misinterpretations/etc.
Sure hope the SQL Server team gets the scalability kinks worked out of SQL 2012, and quickly!! I am not liking what I am seeing and hearing about on a number of fronts honestly...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 24, 2013 at 9:11 am
"I still think 2012 is underperforming compared to 2008R2, particularly given it's running on almost double the hardware our old 2008R2 box was."
This is a big clue. Have you tested the workflows on SQL Server 2008 R2 on the new hardware, or was it SQL Server 2008 R2 on old hardware, SQL Server 2012 on new hardware only? SQL Server 2008 R2 might struggle just as hard as SQL Server 2012 on "twice the hardware", depending on what that means.
This is what I learned based on internal testing and production system observation across numerous 2, 4, and 8 NUMA node systems. The workloads within scope of our tests:
1) high concurrency ETL (typically twice as many client connections than logical CPUs)
2) higher concurrency batch report workload (driven by reports scheduled on report app servers, concurrency typically at least 4 times the logical CPU count).
1) assuming same core count, increase core count per NUMA node (or drive up query memory allocation concurrency per NUMA node in any manner) = poke the bear of per-NUMA-node query memory allocation spinlock contention. Indicated by CMEMTHREAD waits, and high CPU during low page lookups/logical reads per CPU % utilized. Can lead to high CPU utilized per core, aligned along NUMA node boundaries (if not across all cores). Resolved by trace flag 8048.
2) assuming same core count, increase NUMA node count = decrease the database cache efficiency for workloads whose queries/connections don't benefit from NUMA node affinity. Basically, imbalanced cache churn rates cause more disk IO than necessary*. Seems to vary with the square of NUMA node count according to my tests. This case is especially relevant when transitioning between Intel servers at one NUMA node per socket and the two-NUMA node per socket AMD servers. Resolved by trace flag 8015. (Don't use without trace flag 8048 or you'll poke the #1 bear even harder.)
3) for a given amount of RAM, increase NUMA node count = increased exposure to persistent high foreign memory page count on multiple SQL Server buffer nodes. I haven't seen confirmation of this anywhere, but especially after the SQL Server 2012 SP1 CU3 hotfix for NUMA behavior, I'm convinced that persistent high foreign page counts triggers a reaction within SQLOS, trying to sort away the high foreign page count. If the count can't be sorted away (perhaps because the amount of free memory on each NUMA node doesn't allow it, even if the amount of physical memory on each physical NUMA node is balanced) a significant amount of overhead is added to some SQL Server memory operations. That overhead seems to be hard to pinpoint with typical wait and spinlock measures. (It may be related to a spinlck category that isn't documented anywhere available to me yet.) This is also resolved with trace flag 8015.
4) for a given core count, increase NUMA node count = increased likelihood of imbalanced CPU utilization, aligned with NUMA node boundaries, due to the tendency of parallel workers for a given query to be assigned within a NUMA node. Closely related to number 2. Increases the CXPACKET and SOS_SUSPEND_QUEUE waits for a given workload, as well as signal wait time across all wait types. This is also resolved by trace flag 8015.
So, during your targeted workloads, you may want to monitor per CPU CPU busy, per buffer node PLE, stolen pages, database cache pages, and free pages, and overall stolen pages, and reserved memory, and granted workspace memory.
Use a stored procedre to capture and diff waits and spinlocks in small increments. At live production sites I was using 5 minute intervals, in-house testing we decreased the intervals.
Rohit Nayak's post on debugging CMEMTHREAD waits can really help, too - especially if you are skilled with extended events (I'm no good at them yet).
http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx
You may be in a situation where trace flag 8048 helps but 8015 provides no extra benefit. If 8015 helps, I'd warn against using it without 8048. Of course, monitoring CMEMTHREAD waits, spinlocks, per-node PLE and free memory, stolen memory, reserved memory, etc, might just show that neither of these will help at all.
Or, you may be in a situation where 8015 + 8048 could help, but going to a single lazy writer or a single IO completion port makes it a no-go.
I posted this at length here not to bore or attempt to impress you 🙂 but because as far as I know, I'm the only person that's talking much about this potential optimization for "SMP type" batch workloads that don't require NUMA node connection affinity. Many data warehouses fall into this description. And, attempting to explain it here allows me to practice for an upcoming blog post where I'll include perfmon stats and maybe a graph or two. 🙂
*I often see an assumption that the main considerations in widely swinging PLE (when the PLE calculation is legit) are database block physical read rate and/or first database block write rate. When stolen memory (this includes the "active/used" portion of query memory grants - the not-yet-used query memory which may still cache database blocks is in "reserved memory") goes up, the database cache typically gets smaller - then PLE goes down even if physical read rate and first write rate remain the same. Same pace of blocks marching down the line, but its a shorter line til they age out 🙂
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply