Question about Execution plan and...hardware??

Question

Question about Execution plan and...hardware??

Rich Hansell

SSCrazy

Points: 2346
More actions
February 2, 2007 at 2:56 pm

#116193

Hey guys..
I have a view that we use here that takes about 4-6 minutes to return all rows in three of our environments, however in our dev environment it runs forever..insanely long.
Now the both boxes are 2-way boxes, Dev has half the RAM of the other environments, and Dev is direct attached as opposed to a SAN. I have compared settings, and restored all the pertinent DBs from PRD down to the DEV box..and still..Never finishes..
So, I look at the execution plan for the View create on both PRD and DEV and they are very different..with the main difference being Hardware.. I am lost, I can't imagine SQL would care about the hardware when creating the execution plan.. Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I would say run the code in Profiler in both boxes and compare executing time and do the same thing with Management Studio/Query Analyzer and click on show execution cost I think you will see the difference. That said Views are Query rewrites but persisted so I don't think you can compare its execution plan retention to a stored proc which are just compiled. The SQL Server team created a blog to help with query performance tuning. Hope this helps.

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/09/26/771411.aspx

Kind regards,
Gift Peddie

John Rowan SSC Guru Points: 56440 More actions · Answer 2

It may also help if you post the execution plans or at least tell us what the differences are.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Another couple o' things to check...

Are the boxes running the same version/service pack level of Windows Server?
Are the boxes running the same EDITION/service pack level of SQL Server (SQL Server Standard Edition isn't quite as robust)?
Do the boxes have the same amount of "read ahead memory" on the drive controller?
Are you positive the tables involved have the same indexes?
Are the boxes running the same number of CPU's?
Are the settings for both boxes, both the operating system and the SQL Server setting identical?
Is TempDB setup to be the same size on both?

And, yes... memory makes a huge difference. If your Dev box only has half the memory, your view could be using it all and not leaving much room for anything else.

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

Rich Hansell SSCrazy Points: 2346 More actions · Answer 4

Thanks all.. I should have taken a closer look at the versions (both Enterprise) however the dev box was missing a hotfix. After I installed it, the plans matched up (the main difference was the last step prehotfix was a compute scalar step(100%) and no parallelism in previous steps... now its on par with the other environments.. and returns in under 3 minutes! Sorry for wasting everyone's time!!

Thanks... Rich