Post reply

Table valued Function Performance

  • May 6, 2011 at 10:57 am

    #1321168

    Alright i need to do that, and thanks for the link to the Hidden RBAR. 🙂

    But one thing i don't understand, why there's such difference between 2000 and 2005, if i optimize the query, i won't find out what's the real problem, and i want to find out why. Or i'm missing something in the 2005 and this performance difference it's normal between the versions?

    Thanks

  • May 9, 2011 at 6:04 am

    #1321660

    rootfixxxer (5/6/2011)

    Alright i need to do that, and thanks for the link to the Hidden RBAR. 🙂

    But one thing i don't understand, why there's such difference between 2000 and 2005, if i optimize the query, i won't find out what's the real problem, and i want to find out why. Or i'm missing something in the 2005 and this performance difference it's normal between the versions?

    Thanks

    There's so much going on here that is problematic, but you might be hitting one of the edge cases where a query that ran well enough under 2000 degrades under 2005. When this happens it's almost always with a query, like this one, that has a number of tuning opportunities, even in 2000.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 9, 2011 at 10:13 am

    #1321803

    So my only hope it's redesigning the query and remove the RBARs...

    I'll see what i can do to solve this.

    Thanks

  • May 10, 2011 at 8:10 am

    #1322328

    Sounds like a possible regression to me, although I will add the following first:

    1) update stats must be done on EVERYTHING and with FULL_SCAN. no exceptions, and to do anything else on this server before you do that is a complete waste of your time.

    2) i would recheck your configuration on the new server, and run a file IO and wait stats analysis while this beast is running. If it used to run in 11 seconds, there could be another reason other than 2005 regression for the slowness.

    3) good luck refactoring that monster! That really does look like fun stuff - I love that kind of challenge personally! 😎

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

  • May 10, 2011 at 8:21 am

    #1322343

    Thanks for the reply.

    I'll do what you suggest and come back with the results, but how do i test the file IO in the second point?

    I'll let the refactoring to do later, right now i don't have the time neither the patience... 🙂

  • May 10, 2011 at 11:23 am

    #1322527

    rootfixxxer (5/10/2011)

    Thanks for the reply.

    I'll do what you suggest and come back with the results, but how do i test the file IO in the second point?

    I'll let the refactoring to do later, right now i don't have the time neither the patience... 🙂

    1) take a snapshot of sys.dm_io_virtual_file_stats, waitfor delay XX, take another snapshot and do a diff

    or

    2) Hire a performance tuning pro for an hour 😎

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

  • May 10, 2011 at 11:30 am

    #1322548

    Thanks

    I'll go with the first one, i'm just the programmer, the server administration it's someone else's work...

    I'm just checking if there is something wrong with m database.

Viewing 7 posts - 16 through 21 (of 21 total)

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