Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query slower on 2008 than 2000 Expand / Collapse
Author
Message
Posted Monday, March 10, 2014 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
Hi all,

I've restored a SS2K DB to SS2K8, new box with superior (virtual) hardware, however one of our views is running much more slowly.

On the old box it runs in 2 minutes, yet takes in excess of 20 on the new environment.

Rebuilt the indices and updated the stats on both DB's referenced in the view. Compatibility Level for both = 100.

Execution plan, Costliest Items (Old box)

61% Sort (Order By)
21% Table Scan (Where date greater than...Or 'Period' greater than...)
3% (x3) (Clustered Index scans)

Execution plan Costliest Items (New box)

53% Table Scan (Select from 2 DB's with implicit conversions not occurring on old box)
11% Table spool (Lazy)
7% (x3) Clustered Index scans


Thanks in advance for your help,

Jake
Post #1549412
Posted Monday, March 10, 2014 12:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 21,209, Visits: 14,899
With the improvements in the QO, the implicit conversions are likely just being revealed to you now while they 2000 didn't care so much about it. I'd find a way to resolve the implicit conversions.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1549437
Posted Monday, March 10, 2014 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
SQLRNNR (3/10/2014)
With the improvements in the QO, the implicit conversions are likely just being revealed to you now while they 2000 didn't care so much about it. I'd find a way to resolve the implicit conversions.


QO??

Any good resources to help circumvent those conversions? I smell a major code rewrite...
Post #1549514
Posted Monday, March 10, 2014 4:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 21,209, Visits: 14,899
QO = query optimizer.

As for how to circumvent those implicit conversions - nothing comes to mind at the moment (without the code rewrite).




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1549515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse