March 10, 2014 at 11:48 am
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
March 10, 2014 at 12:41 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2014 at 4:26 pm
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...
March 10, 2014 at 4:29 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply