Post reply

Query tuning

  • April 6, 2009 at 9:05 am

    #208218

    Hi,

    1) Assume i have a stored procedure. It is taking more time, then what are key steps we look into about that query. I mean how do we do query tuning. Please tell me all steps. (or)

    2) As a admin we will get a mail from users saying that 'Query taking more time than before'. What is reason ?

    For above doubts, please tell me reasons

    Thanks in advance

    vijay

  • April 6, 2009 at 9:20 am

    #972623

    prvreddy (4/6/2009)

    Hi,

    1) Assume i have a stored procedure. It is taking more time, then what are key steps we look into about that query. I mean how do we do query tuning. Please tell me all steps. (or)

    That's a big question. Really big. I just finished a 20 page article on a subset of how to do query tuning. (over at Simple Talk)

    Basically, at a very high level, look through the query for sections that are badly written, T-SQL constructs that don't perform well or unnecessary statements. Look at the execution plan. Is the index usage optimal?

    If you need help with any of that, post the query here and someone will take a look at it.

    2) As a admin we will get a mail from users saying that 'Query taking more time than before'. What is reason ?

    Seriously, it could be anything.

    Blocking

    Other queries running that are slowing that one down

    Increase in the amount of data

    Fragmented indexes

    Bad statistics

    Someone deployed a change that broke things.

    Some other app on the server slowing all of SQL down.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2009 at 9:29 am

    #972643

    Gail,

    Would you mind posting the URL please?

    Many thanks,

    Rich

    Hope this helps,
    Rich

    [p]
    [/p]

  • April 6, 2009 at 9:36 am

    #972651

    Please note that the fixing portion of this article is solely looking at indexes. Nothing whatsoever on badly-written queries. That's another article and one that Tony will likely be nagging me for sooner or later (probably sooner)

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2009 at 9:37 am

    #972654

    Hi Gail,

    Thanks for your reply. Can you please paste that link here?

    Thanks a lot

    vijay

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

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