Post reply

Query tunning

  • June 16, 2010 at 10:50 am

    #222204

    I have a report with fixed set of Queries which some time runs in 20-50 secs and some other time 3-4 mins so it's run duration varies. What would be the approach to find the exact cause of queries execution time variation. Pls suggest

    Thanks

    -SNT

  • June 16, 2010 at 12:32 pm

    #1181227

    I think that would largely depend on the parameters the report may use. Just to state the obvious if the report sometimes runs for 20 sec and is being run for yesterday for example and the runs 4 min and is being run for the last year that may not be surprising at all. If there are no parameters then I would look at what else is going on during that time. are other large reports being ran against the same database for example. I had one such situation where a depertment was complaining that a report was suddenly taking longer than normal. I discovered after much investigation that unlike normal they decided to speed up so report generation and 10 people were kicking off the same report with different parameters at the same time. you can certainly look at the query but I suspect external variables are at work here.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 16, 2010 at 12:51 pm

    #1181232

    it might also be parameter sniffing; if you have parameters with default values on them, a query plan is probably built assuming the default value (ie myproc(@The date datetime = null));

    when the proc is called with a real value, the old query plan is not valid to get the data, and becomes super inefficient as the engine switches to a table scan and tries to apply that bad plan to each row in the scan;

    parameter sniffing can occur even without default values, but it's a very common issue if there are defaults on the parameters used.

    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!

  • June 17, 2010 at 10:13 am

    #1181688

    thanks a lot Dan and Lowell for replying on this.

    Dan, I am trying to find if there are any other things running against the same DB.

    Lowell, Report is running with fixed parameter always

    -Snt

  • June 17, 2010 at 11:15 am

    #1181732

    Blocking?

    Please post query, table definitions, index definitions and execution plan (both for fast and slow), as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

  • June 17, 2010 at 1:43 pm

    #1181824

    Do you have covering indexes for the tables that form the query/queries?

    Also could you accept dirty reads from the results because a query WITH (NOLOCK) would help reduce locking and blocking issues

    Steve Hatchard

    Director

    Mattched IT Ltd

    http://www.mattchedit.com

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

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