Post reply

Query CPU Performance

  • October 31, 2012 at 9:40 am

    #1554674

    1\ Please try in set uncluster index on the fields you order by

    2\ Try to invertigate the execution plan to figure out what is the most consume time.

  • October 31, 2012 at 10:39 am

    #1554699

    TheSQLGuru (10/31/2012)

    1) Funny that you call this fairly simple. That is a beast of a query, with MANY joins, MANY CPU-burning operations (CASES, hash joins, aggregates,etc).

    2) There is a CONVERT_IMPLICIT in there that will FUBAR the optimizer and burn CPU ticks

    3) I don't have time to dig into the query plans, but they seem different. Less hashing in OLD server one? Maybe try forcing LOOP JOINS judiciously (or brute for all of them) with hints?

    4) When you updated stats, did you do so with FULL SCAN? That is MANDATORY when upgrading a database on new version.

    5) If you really want to get this spiffied up, hire a professional performance tuning consultant for an hour or two. I have seen people hunt and peck for days/weeks with something this complex when getting someone into their system for even a few minutes could pay huge dividends. You might pick up some tricks from him/her also!

    Thanks for this...I think we may possibly have a work around although your insight is very useful. The query is indeed quite large - I probably should've rephrased what I wrote....I just meant it wasn't trying to do anything out of the ordinary! Saying that, even with the volume of data and number of joins, aggregates etc. it still runs in seconds on our old SQL 2005 box. I think I did slip up regarding #4 with the stats update, as I just ran a basic maintenance plan, so that probably didn't help. I would love to have the luxury of working alongside a performance tuning expert because it certainly isn't my strongest area! 😉

  • October 31, 2012 at 11:13 am

    #1554717

    I would love to have the luxury of working alongside a performance tuning expert because it certainly isn't my strongest area!

    I happen to know one of the best! Several actually, but one stands out in particular. 😎

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

Viewing 3 posts - 31 through 32 (of 32 total)

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