August 15, 2012 at 3:05 pm
Hi,
Recently we migrated one of our database instances to a new server. The new server has significantly higher amount of RAM (256 GB) and this has 4 physical CPUs with 10 Cores each. Before this we had a 2 CPU with 2 cores each.
The assumption was that the performance won't go south. But I have observed that some the queries that were running fine before have started to run extremely slow.
I checked the execution plans and I see that the plan hasn't changed much. They all use parallelism in the plan.
Apart from this I have verified that the stats are updated and the indexes are not fragmented.
From what I here, I should be increasing Cost threshold for parallelism to at least 25-30 to prevent simple queries from using parallelism.
Apart from that I also read that I should be setting the MAXDOP server settings to something like 8 instead of the default value 0.
To fix this I recommended that these queries use the MAXDOP=1 option and they are running just like they did before. With this option the queries run within 4-5 seconds. But if I don't use it then the queries run longer than 5-6 minutes.
Has anyone else experienced such a situation?
Blog
http://saveadba.blogspot.com/
August 15, 2012 at 6:39 pm
I have and I can't account for why it happens, just that it does:
http://www.sqlservercentral.com/Forums/Topic1338934-3122-3.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 16, 2012 at 12:45 pm
Sadly, yes, I've seen similar items. I've had it explained to me twice by people who get it and I still end up with a glazed look by the time their done explaining it. Each particular case ends up being unique, but the end result is the same... don't give a query the opportunity to grab 40 threads.
I personally would recommend you set those particular queries to MAXDOP 4 or 2 instead of 1 first, and test them that way. It will allow them a bit of flexibility once you've incremented the base cost.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply