|
Home > Archive > MS SQL Server > July 2005 > random timeout expired messages - locking?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
random timeout expired messages - locking?
|
|
| Victor Ambruso 2005-07-22, 9:23 am |
| My company is experiencing period "timeout expired" messages throughout the
day.
We have 200 users who access our SQL Database via a combination of Windows
and Web applications.
Our server is a Dual Xeon with 2 gig of RAM and a RAID array for the drives.
Our Database is 14 gig.
The server is basically always under medium to heavy load. It's primarily
used for OLTP.
Stored procedures that normally execute in a few milliseconds will
periodically take minutes to execute.
Because the procedures work some of the time but not all of time, I'm
working on a few theories as to why things are slow. I'm monitoring disk and
processor activity on the server but they don't seem to spike when these
items are occurring.
I'm trying to eliminate locking as the source of the problem. Specifically,
I've been doing some reading that states when the server is running out
locks, it escalates all of it's lower level locks to be table locks. That
would explain a lot of the behavior I'm seeing.
My questions are: How do I determine when this is occuring? How do I
calculate the # of locks the server can handle, and the number that is has? I
can probably answer the second with sp_lock, but I'm not sure where to go
with the first.
Thanks in advance.
Victor Ambruso
| |
| Alejandro Mesa 2005-07-22, 9:23 am |
| See if this helps:
How to monitor SQL Server 2000 blocking
[url]http://support.microsoft.com/default.aspx?scid=kb;en- us;271509#appliesto[
/url]
AMB
"Victor Ambruso" wrote:
> My company is experiencing period "timeout expired" messages throughout the
> day.
> We have 200 users who access our SQL Database via a combination of Windows
> and Web applications.
> Our server is a Dual Xeon with 2 gig of RAM and a RAID array for the drives.
> Our Database is 14 gig.
>
> The server is basically always under medium to heavy load. It's primarily
> used for OLTP.
>
> Stored procedures that normally execute in a few milliseconds will
> periodically take minutes to execute.
>
> Because the procedures work some of the time but not all of time, I'm
> working on a few theories as to why things are slow. I'm monitoring disk and
> processor activity on the server but they don't seem to spike when these
> items are occurring.
>
> I'm trying to eliminate locking as the source of the problem. Specifically,
> I've been doing some reading that states when the server is running out
> locks, it escalates all of it's lower level locks to be table locks. That
> would explain a lot of the behavior I'm seeing.
> My questions are: How do I determine when this is occuring? How do I
> calculate the # of locks the server can handle, and the number that is has? I
> can probably answer the second with sp_lock, but I'm not sure where to go
> with the first.
> Thanks in advance.
> Victor Ambruso
>
|
|
|
|
|