Post reply

Query Suddenly Starts Taking Huge Time

  • December 9, 2013 at 12:09 pm

    #281008

    Hi All,

    We have a SQL QUERY which Extracts Data from 10 Tables,

    But the Query All of a Sudden bumps the TIme of Execution Hugely.

    Can you please help me to Understand the Causes of the same... or Direction I need to take to check the Issues.

    Note: No New Code changes Occurred

  • December 9, 2013 at 12:29 pm

    #1672095

    showing us the actual query would help, and whether the query is performed in a procedure or ad hoc;

    the actual execution plas, posted as .sqlplan, would let us identify the exact problem.

    my knee jerk reaction would be to assume statistics are out of date, and would immediately update statistics on those ten tables featured int eh query.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 9, 2013 at 12:31 pm

    #1672096

    This is usually caused from one of two sources. You've had data changes recently that have resulted in the distribution of your data to change, updating the statistics, and the query plan which used to work well is no longer being produced because of those changes. You have a bad parameter sniffing situation occurring. The initial work on both is the same. First, capture the execution plan as it exists in cache and store it as a *.sqplan file. Then, update your statistics, preferably with a FULL SCAN. Check the query again, preferably with a different parameter value to see if the plan changes. If so, there's a chance you're seeing bad parameter sniffing. If not, you've eliminated bad parameter sniffing. Then, you'll need to look at the plan to understand what's wrong, why it's running slow, etc.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 10, 2013 at 4:31 am

    #1672254

    as mentioned by Grant, parameter sniffing does make you query slow. I personally experienced it.

    go through below article.

    http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx


    Praveen D'sa
    MCITP - Database Administrator 2008
    http://sqlerrors.wordpress.com

  • December 10, 2013 at 5:47 am

    #1672271

    PravB4u (12/10/2013)

    as mentioned by Grant, parameter sniffing does make you query slow. I personally experienced it.

    go through below article.

    http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

    Be careful with statements like that. Parameter sniffing is an automatic process occurring all the time with stored procedure and parameterized queries parameters. Most of the time it's helpful. But sometimes parameter sniffing can go bad. That's why I'm always careful to say "bad parameter sniffing" to differentiate when it's a problem from when it's not.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 10, 2013 at 5:52 am

    #1672273

    Thanks Grant for updating..


    Praveen D'sa
    MCITP - Database Administrator 2008
    http://sqlerrors.wordpress.com

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

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