August 19, 2008 at 9:27 am
I'm running SQL Server 2005 on dual-xeon quad core processors with 8 gb of ram. Every once in a while, a developer performing simple table insertions complains of "out of memory" exceptions. Additionally, the server's CPU utilization goes up to 100% around the same time. Assuming no "hunches" (i.e., no knowledge of database user tendencies), what's the best way to troubleshoot this problem? I don't have a ton of experience doing general database troubleshooting, so any help would be appreciated.
August 19, 2008 at 9:31 am
set up some perfmon counters and profile the developers code. Once you have the trace and permon results you can tie them together and check for problems that way
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2008 at 9:40 am
The first thing to consider is if the INSERT is just inserting one row by INSERT INTO... VALUES... or is it doing a query INSERT INTO... SELECT...
Also, does the table they are doing an INSERT on have a trigger on it?
Once you get a better grasp of the situation, SQL Server includes some tools to help you figure out why the problem is occuring. You can do a trace using Profiler to see the database activity, I/O, and CPU time for a particular session, or across the entire database even. After seeing what statement or statements seem to be causing the problem, you can use the Estimated Execution Plan and/or the Actual Execution Plan in Management Studio on the statement in question (you can copy and paste it from Profiler into Management Studio) to see how SQL Server is executing this statement.
August 19, 2008 at 9:41 am
What perfmon counters would you use?
My only concern is that the developer's code might not be the problem. If that's the case, what steps should I use to rule out the developer as the cause of the problem?
August 20, 2008 at 6:15 am
Perry,
What perfmon counters would you use?
My only concern is that the developer's code might not be the problem. If that's the case, what steps should I use to rule out the developer as the cause of the problem?
August 20, 2008 at 8:49 am
If every time the developer runs his code there's a problem, I'd statrt by looking at that developer's code. If there's nothing wring there, run profiler for a while and look for queries with high CPU around the time the problems occur.
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
August 20, 2008 at 10:03 am
jlp3630 (8/19/2008)
I'm running SQL Server 2005 on dual-xeon quad core processors with 8 gb of ram. Every once in a while, a developer performing simple table insertions complains of "out of memory" exceptions. Additionally, the server's CPU utilization goes up to 100% around the same time. Assuming no "hunches" (i.e., no knowledge of database user tendencies), what's the best way to troubleshoot this problem? I don't have a ton of experience doing general database troubleshooting, so any help would be appreciated.
I'll leave the methodical to the other responders, and instead give you my "hunch"...
In my experience (several decades as a programmer and DB professional) the combined phenomena of "long pause" plus "high CPU spike" plus "Out of Memory error" are likely to be caused by one thing: an Infinite Error-Handling Loop/Recursion, either on the client or the server.
For SQL Server the other likely potential cause is an Infinite Trigger Recursion.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply