Query tunning

Question

Query tunning

sntiwary

Hall of Fame

Points: 3475
More actions
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

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

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.

Lowell SSC Guru Points: 323504 More actions · Answer 2

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!

sntiwary Hall of Fame Points: 3475 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

SHATCHARD Valued Member Points: 67 More actions · Answer 5

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