December 18, 2003 at 9:31 am
Hello-
Everthing was fine till yesterday but I walk in this morning and my server is behaving strange. We have a app and db on the same server (I know this is bad but I could never convince my management to buy another server). This morning when I walked in, I see that SQL Server is taking all the CPU and memory. I have no clue where to start troubleshooting the problem. Can you please tell me where to start and what all to check. Some possible scenarios when things like this could happen? Any help is greatly appreciated. (I did fix the amount of memory SQL Server could use, earlier it was dynamic). I rbooted the server and it did not change anything.
- sbaru
December 18, 2003 at 9:59 am
Have you tried running profiler to see what queries are running? Are any of those queries running over and over(infinite loop). You may also get some clues from sp_who2 along with dbcc inputbuffer(spid). Did anyone promote new versions of code recently? How did you determine SQL was taking all the CPU and memory?
Brian
December 18, 2003 at 10:05 am
CPU is a tough one. Profiler is a good start. Also, you might try checking to see if the app changed.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 18, 2003 at 10:33 am
There is no change in the code. It is exactly the same as it was yesterday. It is possible that there could some queries that are executed today. I will check that with the profiler. Are there any performance counters I should be looking out for in this case? I just looked at the task manager to assume that the sqlservr.exe is taking up the maximum CPU and memory.
December 18, 2003 at 10:43 am
I would first run SP_Who2 and see if a specific spid is using all of the CPU and memory. If one spid is clogging the system, then you can do DBCC INPUTBUFFER(spid) and see what query it is running. This may point you in a direction. If this doesn't turn up anything, then you will want ot go to profiler. Within profiler you will probably want to start out by tracking RPB starting and completed as well as SP starting and completed. This will hopefully tell you if a procedure is running over and over. If that doesn't show you anything, you may then want to add in SQL StmtStarting and SQL StmtCompleted. Has the code changed within the last week?
Brian
December 18, 2003 at 1:41 pm
Thanks a lot for your responses. I ran the profiler and saw that there is a SP that is being called today (not ever before) because the client started using some new functionality today. This SP has 2 evils. One there is a cursor declared and then this SP calls itself !!!! I am right now trying to re-write it avoiding cursors and then my next step would be to avoid recursion.
Again, thanks a lot for your time and thoughts
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply