July 3, 2014 at 7:50 am
Hi Experts,
One of our application is getting database timeout when they try to generate report. I have checked server when they ran the same and found CPU & IO to be normal,no blocking exists.
The same report is getting generated in another server without any issue and both servers have query time our set to 3600. Please help.
TIA
July 3, 2014 at 8:01 am
Have you put on a server-side trace or extended events session that captures the query and metrics like reads, cpu, duration for it?
Have you verified that statistics are up to date?
It sounds a bit like "bad" parameter sniffing where on the poor performing server the cached plan is for a set of parameters that returns a small result set and the query timing out is passing parameters that return a larger result set so the query plan is not optimal. Often times when this happens there aren't any obvious metrics that jump out in monitoring, CPU is fine, the query isn't waiting, but it is doing millions more reads than it really should to return the results.
I recently dealt with a query that had this happen. A table with about 100,000 rows requiring over 100 million reads to return a subset of the table because of "bad" parameter sniffing causing a sub-optimal query plan for that set of parameters. Actually adding an index fixed it in this case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 3, 2014 at 8:03 am
Identify the query being run, run it in a representative test environment, identify the slow parts. Tune the query to be faster.
Could be any number of reasons why it times out on one server and not the other. Data volumes, blocking, waits for resources, different indexes, stats out of date...
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply