SQL Server ASPState Blocking under high load
-
We are running SQL 2008 R2 Enterprise server with ASP Persistent sessions and our main data DB on the same 2008 R2 Enterprise server. We're currently experiencing random freezes where all websites and applications accessing the DB become un-responsive. Has anyone same problem and how you solved it. Any suggestion? Thx:confused:
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
-
We are running SQL 2008 R2 Enterprise server with ASP Persistent sessions and our main data DB on the same 2008 R2 Enterprise server. We're currently experiencing random freezes where all websites and applications accessing the DB become un-responsive. Has anyone same problem and how you solved it. Any suggestion? Thx:confused:
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
The first thing os to start monitoring, what happens in the database. Open Management Studio and check the Activity Monitor. Based on your symptoms search for processes that have a value in "Blocked By" -column. Since this sounds like it could be a locking problem see if the processes are waiting for lock releases. If you find those, you can search for the process having a value in "Head Block" column, this would be the one (or several) process causing the situation. If you find such process, have a look at the details, what is it doing. This helps to find the problem source in the calling code. Also check memory and CPU usage,are they extremely high.
The need to optimize rises from a bad design.My articles[^]
-
The first thing os to start monitoring, what happens in the database. Open Management Studio and check the Activity Monitor. Based on your symptoms search for processes that have a value in "Blocked By" -column. Since this sounds like it could be a locking problem see if the processes are waiting for lock releases. If you find those, you can search for the process having a value in "Head Block" column, this would be the one (or several) process causing the situation. If you find such process, have a look at the details, what is it doing. This helps to find the problem source in the calling code. Also check memory and CPU usage,are they extremely high.
The need to optimize rises from a bad design.My articles[^]
At that particular time yes, CPU 100% (2.27GHz), memory 99% (8GB) and no release. Yes, procedure is identified : UPDATE [ASPState].dbo.ASPStateTempSessions procedure trying to update ASPState table... :mad: and after: life is crashed.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
-
At that particular time yes, CPU 100% (2.27GHz), memory 99% (8GB) and no release. Yes, procedure is identified : UPDATE [ASPState].dbo.ASPStateTempSessions procedure trying to update ASPState table... :mad: and after: life is crashed.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
Ok, several possible reasons for this but I guess you should go through the procedure and optimize the statements, possibly create indexes etc. Also feels like the server could be paging. You could check that with Performance Monitor (Windows) and if the server is paging, reduce maximum amount of memory allocated to SQL Server.
The need to optimize rises from a bad design.My articles[^]
-
Ok, several possible reasons for this but I guess you should go through the procedure and optimize the statements, possibly create indexes etc. Also feels like the server could be paging. You could check that with Performance Monitor (Windows) and if the server is paging, reduce maximum amount of memory allocated to SQL Server.
The need to optimize rises from a bad design.My articles[^]
Yes, I've already done it. In my case Expired sessions were not cleaned. When I release Memory with T-SQL, I allocated these jobs to my Agent, and memory release comes after 30-40 min, (I run job every 30 min) I understand that the problem is session. I am going to try CURSOR-BASED ROUTINE. Hope it will prevent my Server from troubles what cause us all week. If there any suggestions, I would be glad to hear them. P.S.: Found solution. I replaces ASPState expiry session procedure with Alternative CURSOR-BASED ROUTINE. Performance came back to normal state. No crashes also.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
modified on Friday, April 15, 2011 6:25 AM
-
Yes, I've already done it. In my case Expired sessions were not cleaned. When I release Memory with T-SQL, I allocated these jobs to my Agent, and memory release comes after 30-40 min, (I run job every 30 min) I understand that the problem is session. I am going to try CURSOR-BASED ROUTINE. Hope it will prevent my Server from troubles what cause us all week. If there any suggestions, I would be glad to hear them. P.S.: Found solution. I replaces ASPState expiry session procedure with Alternative CURSOR-BASED ROUTINE. Performance came back to normal state. No crashes also.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
modified on Friday, April 15, 2011 6:25 AM