March 4, 2009 at 10:13 am
Hey Guys. Our 2005 Database will intermitently lock up. I understand the profiler can help me figure out what is causing it. Can you guys point me in the right direction to set it up to do this? I also understand it can create some huge logs if it isint setup selectively. Any help would be much appreciated.
March 4, 2009 at 10:23 am
I would start by looking at activity monitor when the system is locked up.
Check for blocking transactions and see what host / application / statement is causing the issue.
This is usually caused by an open transaction not being closed or a very long running transaction.
March 4, 2009 at 10:26 am
you can also use activity monitor to see blocked transactions, or Performance dashboard to get live reports of locks and blocks 🙂
March 4, 2009 at 10:34 am
Do you know if it is blocking or locking. First thing I would do is to set up the start up poarameters with -t 1204. This will write the Dead lock chain if any into the error log. Second would be to check what is blocking. Blocking should not matter since it is usually for a very period of time and should clear up pretty soon.
My 2 cents
-Roy
March 4, 2009 at 12:10 pm
Good old fashioned sp_who2 could help here too.
You don't want to run Profiler on your production system. What you want to do is set up a server side trace. It's basically Profiler without the GUI, but there is actually a slight internal difference between the two that makes Profiler less efficient. Anyway, go to Books Online and look up sp_trace_create. That'll get you started. There are several articles on here that show to do it too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 2:02 pm
Thanks for the tips guys. I am looking at those tools now.
ANother question. Can someone explain the difference between a Lock and a Block? Are you guys suggesting it is likely one of the two?
March 4, 2009 at 3:08 pm
kellyrmartin (3/4/2009)
Thanks for the tips guys. I am looking at those tools now.ANother question. Can someone explain the difference between a Lock and a Block? Are you guys suggesting it is likely one of the two?
A lock, and there are several kinds, is basically a session holding on to a part of the datase, a page on an index or a row on a table. A block is when one session is holding a lock and another session wants it. The second session is considered to be blocked. It's a normal part of processing, nothing to worry about unless it gets excessive.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 4:07 pm
Ok. So if I am understanding correctly is that a process is likely holding a Lock on some resources that alot of people access. SO I could probably use activity monitor to isolate which exact process is causing the lock, and then from that process ID narrow down which app/piece of code is causing the problem? Once I have that information would it be ok to kill the stalled process instead of asking everyone to logout and log back in? At which point I would begin to research the issue further.
Thanks for all your help.
March 4, 2009 at 4:28 pm
Yeah basically. There are other tools you could use to help out. When you're really trying to figure out what's causing the server to slow down (or stop), this is a great way to go about it:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply