August 9, 2015 at 11:24 pm
Hi All,
I'm testing out SQL 2014 migrated from our current Prod backup of SQL 2008 R2 database.
I'm aware of the MDOP setting, however when troubleshooting a poor performing query, I'm at a loss for reason.
Our Sql 2014 server (latest cu) has 4 cores with MDOP set to 2. Stats and compatibility updated.
When investigating a query, said query by default runs "really slow /abnormal". (17 minutes)
I checked the query plan and it's cost is about 500, and runs in parallel as expected.
I tested the set OPTION (QUERYTRACEON 9481) to prevent SQL 2014 from using the new Cardinality Estimator(CE).
The query ran as fast / "normal like SQL 2008". It's cost was about 500.
In checking the plan, it does use MaxDOP 2. (53 seconds)
Then then I tested query with OPTION MAXDOP 1 not using the Trace 9481, it runs fastest with cost about 600 (43 seconds)
Query is running a large Data Warehouse report, so I expect a lot of reads.
Any clues as to why this behaviour changes so dramatically with MDOP of 1.
(I'm ruling out reverting to the old CE when we eventually upgrade to SQL 2014 to Production.
Thank you in advance.
-RR
August 10, 2015 at 12:53 am
Hi Jayanth,
I actually read that whitepaper and got that trace flag from there.
I understand some queries might degrade, but how can the query perform 15x better with no parallelism?
Understand this is a test server with no additional work load.
I'm drilling down into these massive stprocs and finding these rogue queries that now all of a sudden work better single threaded?
It would be ok if a CTOP modification would benefit, but the query plan costs are too high.
Any other thoughts?
Regards
-RR
August 10, 2015 at 2:39 am
You're going to have to look at the query, look at the plans under the old CE and the new, with parallelism and without, identify the cause of the problems and fix whatever it is. The CE changes do cause some queries to degrade in performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 4:55 am
UserRR (8/10/2015)
Hi Jayanth,I actually read that whitepaper and got that trace flag from there.
I understand some queries might degrade, but how can the query perform 15x better with no parallelism?
Understand this is a test server with no additional work load.
I'm drilling down into these massive stprocs and finding these rogue queries that now all of a sudden work better single threaded?
It would be ok if a CTOP modification would benefit, but the query plan costs are too high.
Any other thoughts?
Regards
-RR
Parallel query execution isn't a guaranteed speed enhancer. In some cases the cost of making the operations parallel offset the benefits that using multiple processors gives. Sounds like you might be in that situation. You might want to check that your stats are up to date (new cost estimates or not). As Gail said, get the execution plans and compare them to understand what's going on.
On a side note, execution plan cost isn't really a good measure of performance or for comparing one plan to another.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2015 at 10:23 am
here is a possible and fairly common scenario. The original execution plan is flawed and overestimates the number of suitable rows and therefore decides it needs more CPU power to get the job done. Change in cardinality estmate causes a more realistic number of rows to be identified and therefore the optimizer switched back to single threaded queryies.
The cost is a realtive and inaccurate number and more of a best guess from the optimizer on how much CPU and RAM it needs. Don't use it to claim improvments in performance since its not accurate.
August 10, 2015 at 5:01 pm
The guessing game that is the plan cost I agree is not accurate. I'm more concerned with cpu,ram,io and execution times.
That being said, the query will probably have to be redesigned to fit the new CE, but we'll have to find a suitable way to do that.
I was concerned that sql 2014 changed something under hood unexpectedly for parallel queries that wasn't documented
and that others encountered similar situations.
Thank you everyone for your comments.
-RR
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply