no parallelism in the execution plan - why?

Question

no parallelism in the execution plan - why?

tim densmore

SSC Enthusiast

Points: 109
More actions
June 26, 2008 at 12:51 pm

#193253

I am trying to put a new SQL Server machine into production. In this process, I restored SQL 2000 database backup to then new machine, a 64bit windows 2003 server with the 64bit edition of SQL 2005 installed. The new machine has double the processors and 4x as much ram – so you would expect a performance boost. Initial tests showed slightly faster query execution in the new box when compared to the old, but nothing spectacular - maybe 2-3% faster.
When I put the machine into production all 4 CPUs pegged out at 100%. I happened to discover a query that was taking 20-30 minutes to run on the new server – but only takes 7 SECONDS on the old! I set up another test SQL 2005 server, same problem with that query.
The execution plans for the offending query under 2005 are very different than the execution plan on my production 2000 server. Noticeably, there is no parallelism in the 2005 execution plans.
I’m not sure if this is the cause of performance issues, but I though it was a good place to start.
Any thoughts?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The 2005 optimiser is quite different to the 2000 one and often produces different plans.

One very quick thing to check first. Did you update statistics after restoring the 2000 database to 2005? If not, go and update the statistics on all tables.

The statistics kept in SQL 2005 are more details than the ones in SQL 2000 were. The 2005 optimiser can use the 2000 style statistics, but not very well.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

I dunno... I'm thinking that's about the 100th 64 bit related performance problem that I've seen in the last month or so... I'm thinking the 64 bit version is like the early 75 Mhz Pentiums... sounded real good... couldn't add a thing correctly...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tim densmore SSC Enthusiast Points: 109 More actions · Answer 3

Thanks for your help, I tried to update all stats a few times now, also rebuilt indexes - I hoped that would be all it was. To clarify what I said above, it is a view in the db - not just ad-hoc query that was (extremely) slow. I dropped and recreated the view on the new server, still same issue.

As for the 64bit thing, I thought maybe there was something there myself. Then I ran the same view on another server, which was SQL 2005 but 32bit and have the same issue. I am experiencing a general performance issue when I restore my SQL 2000 db to a 2005 machine... but I am focusing in on this view because the performance gap was so huge. I figure whatever is causing that is the culprit overall.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Please post the query and the query plans.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Also, are your Service Packs up-to-date?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Toby White SSCertifiable Points: 6187 More actions · Answer 6

What version of SQL Server 2005 are you using Std/Ent? If it's STD do you see any memory trimming in the sql server logs or anything unusual there?

tim densmore SSC Enthusiast Points: 109 More actions · Answer 7

Service packs up to date, nothing in the log that looks abnormal.

I did discover some a useless bit of sql: a nested query in a Where clause that was responsible for most of the run time. I deleted this useless bit (not sure what the orginal intent was but it would have always returned true in our db) - Now the view runs in 2 seconds on both machines. Still, why did it run in 7 seconds on the 2000 machine but take 30 minutes on 2005 machines? ..hmmm

Attached are the execution plans and the Query (The part that was bogging down anyway)

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 8

Hi there - i had similar thing this week - where it took over 10 minutest to run a process and cpu went to 100%....and stayed there...I turned off the auto stats which are set to true on db and it took 2 minutes 35% cpu............

Do you have auto stats set to true on db. ....

I have turned mine off because we process hugh amounts of data at a time.

Be interesting to see if you have same settings.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

TRACEY (6/29/2008)
Hi there - i had similar thing this week - where it took over 10 minutest to run a process and cpu went to 100%....and stayed there...I turned off the auto stats which are set to true on db and it took 2 minutes 35% cpu............
Do you have auto stats set to true on db. ....
I have turned mine off because we process hugh amounts of data at a time.
Be interesting to see if you have same settings.

Ir would be interesting to see the code... any chance of you posting the offending code, the CREATE statements for the tables, and the CREATE statements for the indexes?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Feeg SSCrazy Points: 2766 More actions · Answer 10

We had an issue with a New production server, 64bit MS SQL 2005 on 4 AMD chips ... 2 CPU's were always MAXED out (100%), with DB activity or no DB activity ...

All of this happened straight after installing Service Pack 2 for MS SQL 2005. We searched the interWEB and found a Fix which fixed the MAXED out CPU's, don't know if the fix is what you missing in the case above ...

:ermm:

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

tim densmore SSC Enthusiast Points: 109 More actions · Answer 11

tim densmore

SSC Enthusiast

Points: 109

June 30, 2008 at 6:52 am

#836069

What was the fix in your case?

Feeg SSCrazy Points: 2766 More actions · Answer 12

it was a SP2 hot fix, check this out ... http://support.microsoft.com/kb/921896

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 13

How did you check for the there is no parallelism i have open your sqlplan...Can you explain how you know there is no parallelism - sorry not really study one of these plans before..

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

1) In general the optimizer is much better in 2005 than 2000. However, there were a BUNCH of regressions - queries that perform slower on 2005 than 2000. They have been addressing these since RTM (another one was just fixed in the recently released CU7 to SP2). I would set up a test box and put CU7 on it to see if the query is faster.

2) When you ran update stats, did you do EVERY SINGLE object (including manually created and automatically created statistics) using a FULL SCAN?

3) Looking at the query plan, it is bad statistics or bad estimation by the optimizer that is causing the problem. Note that the ACADEMICCALENDAR CI scan estimates 36 rows but gets almost 4 orders of magnitude more rows actual (339K). It goes down hill QUICKLY from there. Nested loop joins all over the place, expecting a few rows here and there, but actually doing hundreds of MILLIONS of loops in some cases (key lookup into TRANSCRIPTDETAILS for example). The IO required for this must be staggering!

4) You need to start using the correct datatypes. This is a VERY common error by database developers. For example, (CONVERT(varchar, GETDATE(), 101) <= dbo.ACADEMICCALENDAR.END_DATE) requires an implicit conversion, which causes numerous problems both CPU use and optimizer estimation issues.

5) AND

((SELECT SUM(TRANSCRIPTDETAIL_1.CEU) AS SCEU

FROM dbo.TRANSCRIPTDETAIL AS TRANSCRIPTDETAIL_1 INNER JOIN

dbo.vwCT3CurrentYearTermByDate ON

TRANSCRIPTDETAIL_1.ACADEMIC_YEAR = dbo.vwCT3CurrentYearTermByDate.ACADEMIC_YEAR AND

TRANSCRIPTDETAIL_1.ACADEMIC_TERM = dbo.vwCT3CurrentYearTermByDate.ACADEMIC_TERM

WHERE (TRANSCRIPTDETAIL_1.ADD_DROP_WAIT = 'A')) > 0

I seem to recall there were issues with the 2005 optimizer with this form of subquery in the where clause. I could be misremembering however. Try refactoring the code to see if it perks up without that in the where clause.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service