July 26, 2011 at 2:22 pm
The server is running OK. No hiccups yet. 🙂 Touch wood. More details regarding the system.
Cache Hit ratios
Buffer 100%, Plan(SQL) 55%, Plan (Objects) 100%.
No IO Waits (Very less).
The Stats are updated daily. The Indexes are maintained regularly. We do everything we can but there is always room for improvement. Even if it is tiny, I will take that. From the projection I have seen for 2012 and 2013, we will be tripling the traffic to our web site. That means more DB load.
We are planning to upgrade our HW next year.
-Roy
July 26, 2011 at 2:24 pm
Ninja's_RGR'us (7/26/2011)
That bugs me that you don't see anything for the DegreeOfParallelism either. Looks like there's something I don't understand in there (either the caching or the XML query). :unsure:
That attribute is only present in 'actual' plans. The plan cache stores plans that do not have specific runtime information - they are used (hopefully) for many executions.
July 26, 2011 at 2:28 pm
Roy Ernest (7/26/2011)
The server is running OK. No hiccups yet. 🙂 Touch wood. More details regarding the system.
Sounds like it's not broken = may not need fixing 😉
From the projection I have seen for 2012 and 2013, we will be tripling the traffic to our web site. That means more DB load. We are planning to upgrade our HW next year.
The peak load statistics you provided earlier indicated that you could handle roughly double the load right now (40% peak CPU) - all things being equal. I might feel happier keeping the relatively low utilization now, in anticipation of future needs (hardware does not always arrive on time). Also, think of all you are doing to help the environment by not stressing the A/C units in the server room so much :w00t:
July 26, 2011 at 2:30 pm
Ninja's_RGR'us (7/26/2011)
ALZDBA (7/26/2011)
See what happens when you rise the cost threshold for parallisme and be surprised, like I was.Good or bad surprised? What and how did you trace the changes?
I just sampled the waits before and after. (cleared the stats after modification to have a fresh start)
cxpacket waits dropped well and stabelized to far less as before.
OLTP aps didn't complain they got served better.
So to me it seems this modification was a good guess and worth the try.
Parallelism had been restricted to 4 ( 16 way ) + treshold raised to 45.
So : less queries apply for parallelism and parallelism itself doesn't starve my cpus.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 26, 2011 at 2:31 pm
Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)
-Roy
July 26, 2011 at 2:41 pm
SQLkiwi (7/26/2011)
Ninja's_RGR'us (7/26/2011)
That bugs me that you don't see anything for the DegreeOfParallelism either. Looks like there's something I don't understand in there (either the caching or the XML query). :unsure:That attribute is only present in 'actual' plans. The plan cache stores plans that do not have specific runtime information - they are used (hopefully) for many executions.
Figured as much. Thanks for saving me the research.
July 26, 2011 at 2:44 pm
Roy Ernest (7/26/2011)
Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)
I never say always when it comes to general settings like this. It sounds like a bad idea but I'm sure there are "some" cases where it could be a good idea.
July 26, 2011 at 3:00 pm
Roy Ernest (7/26/2011)
Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)
Shades of grey. True, for a 100% OLTP load, where the number of concurrently runnable batches are sufficient to keep all available cores nicely humming along, and parallelism confers no elapsed-time benefits, server DOP 1 can make sense. Thing is, relatively few systems are 100% OLTP. Those with just a handful of things that would genuinely benefit from parallelism, can use the query-hint option. As soon as the workload becomes more mixed, if the serial load is not enough to keep the cores busy, and if there are queries that make effective use of parallelism (this is most systems now), server DOP 1 makes much less sense. So, guess what? It depends.
July 26, 2011 at 3:12 pm
Thx for all the input. I appreciate it very much. I have two action points from this.
I will have to look at the missing indexes and I also know that I have to be a bit more careful when doing reviews of the DB objects.
-Roy
July 26, 2011 at 6:00 pm
SQLkiwi (7/26/2011)
I do want to respond to the comments about CXPACKET though. Very few people understand what CXPACKET truly means (and I do not number storage engine experts among them). For almost all intents and purposes, CXPACKET should be ignored. It is a consequence of running a parallel query, nothing more. People do tend to see CXPACKET 'waits' and assume they mean something similar to PAGEIO_LATCH* or whatever - but they really really don't. Yes, if you run fewer parallel queries, and/or fewer parallel regions, you will see CXPACKET numbers decline. No surprises there. It does not mean your server has become more efficient, or that response time has improved, or much of anything else.
I'd love to read more about this. Anyone got a good article link handy?
July 27, 2011 at 11:14 am
I previously had to change the DOP settings. For OLTP MS recommends DOP to be 1. I think you should monitor the setting usng DMV's for more than one value and then decide the best.
July 27, 2011 at 7:29 pm
Ninja's_RGR'us (7/26/2011)
I'd love to read more about this. Anyone got a good article link handy?
I humbly suggest this:
http://www.simple-talk.com/content/article.aspx?article=1250
A detailed discussion of CXPACKET will be in a future instalment. There's also some good information here:
July 28, 2011 at 6:50 am
SQLkiwi (7/27/2011)
Ninja's_RGR'us (7/26/2011)
I'd love to read more about this. Anyone got a good article link handy?I humbly suggest this:
http://www.simple-talk.com/content/article.aspx?article=1250
A detailed discussion of CXPACKET will be in a future instalment. There's also some good information here:
Hmm I don't trust that first guy :hehe:.
Thanks, the article looks awesome, I'll setup 1-2 days to read and digest it this WE. 😉
July 29, 2011 at 12:22 am
Ninja's_RGR'us (7/28/2011)
Thanks, the article looks awesome, I'll setup 1-2 days to read and digest it this WE. 😉
More reading for you, directly on CXPACKET, and why it doesn't matter:
July 29, 2011 at 10:45 am
SQLkiwi (7/29/2011)
More reading for you, directly on CXPACKET, and why it doesn't matter:
Of which only the third one really matters??
Tom
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply