August 8, 2012 at 1:53 am
I am using SQL Server 2008 in on of our project, I am facing an issue, sometime daily or after 1-2days at morning(08:00 to 09:00) and at evening(18:00 to 19:00) database stuck. The query which we runs directly from database it works fine but the same query we execute from ASP.NET application then it stuck, mean when we click on that page it shows in progress. I also tried to debug from live database server but on Database stuck time when we called any stored procedure EXECUTE command then it not responds. I also observe that it stuck for 30-35mints, so after that it works fine automatically.
Current Solution : We are managing by Restarting SQL Server, so as we re-start SQL server it works fine.
August 8, 2012 at 3:40 am
Using the term "stuck" is is a bit vague.
From what I can gather, running a query from an ASP.NET page causes issues on the SQL Server?
If that is the case, I'd check the Windows Application Logs for the Web Server hosting the application to start with.
Have you set connection and query timeouts in Web.config?
Hope this gives you something to start with.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
August 8, 2012 at 4:06 am
This was removed by the editor as SPAM
August 8, 2012 at 4:38 am
Shot in the dark...
Could be a stale statistics issue. If the query runs fine for a couple of days and then start misbehaving, you statistics might be outdated due to updates to the underlying tables.
I would compare the execution plan of the ad-hoc query in SSMS and the execution plan of the statement executed by the app while it's running. You can capture it from sys.dm_exec_query_plan(plan_handle) passing the plan_handle found in sys.dm_exec_requests.
If the plans are different (they likely are), check if the two statements are executed in the exact same way (literal values/parameters, direct execution/sp_executesql). I would check for outdated statistics and parameter sniffing.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply