Post reply

Query Performance is very slow

  • July 30, 2009 at 1:50 pm

    #385092

    The query is running is taking forever. This query was running fine before and was taking 30 minutes to compelete but all of a sudden it is just running for six hours.

    Checked following things.

    1. Missing indexes. All are fine.

    2. Fragmentation. There is no fragmentation

    3. checked execution plan. Allt he tables are taking index seek.

    4. Blocking. No blocking is going on.

    5. Query is not hanged because checked the IO and CPU which is increasing.

    I don't know what else should I check to make this query fast. It was running fine before but now taking forever.

    Appreciate if anyone would help in this matter.

  • July 30, 2009 at 2:03 pm

    #1032661

    Have you tried updating statistics?

    UPDATE STATISTICS <Table Name>

    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

  • July 30, 2009 at 2:05 pm

    #1032662

    balbirsinghsodhi (7/30/2009)

    3. checked execution plan. All the tables are taking index seek.

    :pinch: All tables? Are you sure? something about it doesn't sounds good.

    Look at what changed in the environment since last well performing run. Data volume? Statistics? New application? Concurrence?

    I would trace the offending query and check wait events to see where is time being wasted.

    _____________________________________
    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.

  • July 30, 2009 at 2:39 pm

    #1032687

    Thanks guys,

    Update statistics is the part of my database plan so it's uptodate. Data volume is same because it's running before, Nothing has changed.

    The wierd thing is that when I run the Profiler on this server, it does not show the SPID which is running on this server. When I use SP_WHO2 command and it shows Logical reads and CPU is increasing so it means that query is doing something.

    Actually, SSIS package which is on Machine A calling this stored procedure which is on Machine B. I checked the remote link and it's fine.

    Really wieird.

  • July 30, 2009 at 5:04 pm

    #1032738

    Solved the issue. One of the table was highly fragmented and did the rebuild and query is working fine.

  • July 30, 2009 at 6:06 pm

    #1032754

    balbirsinghsodhi (7/30/2009)

    2. Fragmentation. There is no fragmentation

    Heh... "Must Look Eye". 😛

    --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)

  • July 31, 2009 at 11:34 am

    #1033092

    Wish, I had three eyes, looks like two eyes are not enough.

    Thanks guys.

  • July 31, 2009 at 6:23 pm

    #1033223

    balbirsinghsodhi (7/31/2009)

    Wish, I had three eyes, looks like two eyes are not enough.

    Thanks guys.

    Heh... that's why I wear glasses... it sometimes takes "four eyes". 😛

    --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)

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

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