September 24, 2007 at 10:14 am
Hello,
We have Tomcat and Apache running a site with SQL Server 2000 as the back end. I'm a newbie to performance troubleshooting.
I have begun collecting and analyzing information for a problem we have where Tomcat hangs and, also, sometimes we see blocking on the database. I also collected System Monitor data from the database server at the same time and so can correlate them using the instructions I found at this helpful SQL Server Performance.Com article.
The cases where there is no database blocking are not directly of interest to me at this point - my guess is that somehow Tomcat is failing independently of the database blocking, and we have a group that is looking into that issue.
My concern is the cases where Tomcat hangs and there is database blocking around the same time. In those cases, I don't yet know how to analyze the data to see whether the Tomcat hanging is causing the database blocking or whether the database blocking is causing the Tomcat hanging.
And even though I have collected information on some long-running queries, it is hard for me to create a line of cause and effect that connects the start of a specific query with the onset of the Tomcat problem, or the other way around. Something like:
time 1: query 1 starts
time 2: query 2 tries to start, but is blocked by query 1
time 3: long duration of block causes Tomcat to hang waiting for results of query 1 (or query 2 - I don't know yet)
time 4: web site hangs
time 5: web site pages go out
Does anyone have any tips for looking at this data (or other data counters that need to be looked at) in order to isolate the cause? (I haven't yet looked at the Tomcat log data but I could ask for some help in doing so if someone can suggest what to look for.)
Thanks in advance for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 24, 2007 at 11:07 am
If you think that you've already narrowed it down to possible blocking from long running queries, you can stop running the Windows Performance Monitor and focus more on SQL Profiler. If you can catch instances where your web servers are timing out, run SQLDiag.exe. This will give you detailed information on processes, locking, and blocking. If you don't always know when it is happening, I suggest setting up a SQL Profiler trace to run periodically throughout the workday. You can then find out at which times your web servers start timing out and use the Profiler trace to tell you exactly what was going on at the time of the problem. If you already suspect long running queries and blocking, do you know which queries are long running? If so, get them fixed ASAP.:)
September 24, 2007 at 11:22 am
I was going to suggest what John wrote. Since locks are very transient, I'd use Profiler to capture SQL and when Tomcat hangs, go back at that time in the trace and figure out what's active, what's happening.
You can also run sp_who2 at the time it's hanging to try and see which blocks are being held.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply