October 27, 2009 at 8:21 am
Folks,
We have a .NET web application that on occasion (about once every 10 days) generates a timeout on the web side (an Attention event in SQL profiler) trying to execute a SQL query. The query itself contains a couple of sub-queries, a UNION call, and a PIVOT. I am not sure how to categorize the complexity of the query, as I am not a DBA. Furthermore, when the query is executed within query analyzer it returns the results in under 5 seconds. The timeout only occurs when called from .NET. The resolution to date has been to bring the DB offline.
From timeout perspective, I am thinking QA works better, because the SQL is processed differently vs. an external call. Can that be confirmed? The fact that the issue is resolved by taking the db offline makes me thing it has something to do with tempdb, but I cannot be sure. They are not using temp tables in the SQL query. I cannot be sure what the timeout value is but I know several values were tried and all failed. Can anyone provide guidance as to what may be culprit or ways to troubleshoot the issue to identify the problem. Again, it happens about once every 10 days or so.
Many thanks in advance.
Steve
October 27, 2009 at 8:38 am
Is hard to say why your query would time out at regular intervals. But if the intervals are as regular as you say, 10 days, it would be worth monitoring server performance on the days in question with SQL Profiler and PerfMon etc.
Also, i'd suggest turing your query into a stored procedure and altering you web app to call the stored procedure instead of firing off sql statements. This means you'll be reusing a stored query plan which you can optimise.
October 27, 2009 at 8:44 am
PS
This book on Execution Plans is a must, and may provide you with some answers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply