Post reply

Performance Problem

  • February 25, 2009 at 1:44 am

    #126665

    Hi,

    in one of our servers, when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.

    there's much free space on the server and tempdb size is big enough besides, it has 3GB RAM.

    What may cause such low performance?

  • February 25, 2009 at 2:55 am

    #949850

    - size

    - distribution

    - index organization

    - memory usage

    - paging activity

    - other processes on the server

    - locking

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 25, 2009 at 3:06 am

    #949852

    peace2007 (2/25/2009)

    What may cause such low performance?

    Is this the only poor-performing query or you are seeing poor performance all across the board?

    Check execution plan, be sure query takes advantage of available indexes.

    Trace the query and check for wait events.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 25, 2009 at 3:31 am

    #949855

    peace2007 (2/25/2009)

    Hi,

    in one of our servers, when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.

    post the query

    there's much free space on the server and tempdb size is big enough besides, it has 3GB RAM.

    What may cause such low performance?

    Before looking into this first lets tune the query, to check whether you are returning unwanted rows, to check whether the query is using indexes or out of date, to check whether the statistics are up to date. Fine tune your query as much as possible before worrying about the hardware resources.

  • February 25, 2009 at 4:13 am

    #949861

    Hi,

    when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.

    Hi,

    The table has only 5700 rows.so Its not very big.

    Don't always take the Query running time only. Because (Some heavy Traffic,Locking,Blocking....)something happen in ur server the query running slow.

    Before u running the select statement Set statistics io on and see the Scan count , logical reads , physical reads , read-ahead reads.

    (As per MS Small Values are better)

    Muthu,

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 25, 2009 at 4:31 am

    #949866

    Check the performance in below sequence.

    > Is joins are correct in the query

    > check for subquery or corelated query in your query

    >what are the other process running at that point . use sp_who or sysprocesses in master

    >other processes or application in the server

  • February 25, 2009 at 5:21 am

    #949884

    Is it actually a table... or is it actually a view?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 25, 2009 at 6:05 am

    #949913

    Also, are there any user defined functions in the query?

Viewing 8 posts - 1 through 7 (of 7 total)

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