Locking Problems (Need more locking info from SQL)
Dan Gold
I have a problem that I can't seem to resolve. I have tried everything
I can think of to debug and/or isolate the problem, but have hit a brick
wall.
I am using SQL Server 7 with IIS 4.0 to power a busy website. There are
some stored procedures that are used extremely often to return a very
small amount of data. The stored procedures call a group of tables and
perform INNER JOINS on them. These often used procedures are optimized
with indexes and should execute in under 500 ms on my system with the
current database size.
The problem is that each stored procedure performs 2 or 3 queries on
various tables using Views. Because of this design I am unable to
isolate the actual cause of the timeout. Past experience tells me that
it is a locking issue on one or more of the underlying tables, but I
cannot be sure. It could be a connections issue, however when checking
the performance monitor for SQL Server, the number of connections is
under 100. When using the profiler I cannot see any other Lock event
choices than Deadlock or Deadlock chain despite the longer list that is
covered in the Profiler Help file.
In order to pinpoint the problem I've analyzed my IIS log files in SQL
to see which procedures are generating the error. The ones that are
called frequently have more Timeout errors than the rest, but that is
easily explained by the frequency with which they're called.
These procedures are very READ oriented so I would not have a problem
with reading 'dirty' data. I've implemented
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the beginning of a number of stored procedures and, then
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
at the end to minimize the disruption to the application.
However the above solution seems like a band aid. The level of timeouts
seems to have decreased, but they still happen. What I really want to
do is understand why this is happening. Some sort of auditing of locks
or timeouts would be helpful so I can see the locks that are present at
the exact point in time when the procedures are called and then
subsequently timeout.
I've tried using the profiler but have found that the locking data that
I can profile differs from what is documented in the help file. I can
only follow deadlocks and I haven't had much success doing this.
Can any SQL gurus point me in the right direction to solve this problem?
I don't want to resort to increasing timeout settings because this would
also be a bandaid and not fix the underlying problem.
Thanks in advance!
Dan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
BP Margolin
I have SQL Server 2000 installed, and with SQL Server 2000 all the client
utilities from SQL Server 7.0 are replaced with the client utilities from
SQL Server 2000 ... including Profiler, so I'm unable to give you specific
instructions, however ...
If I remember correctly, there is an option somewhere in SQL Server 7.0
Profiler to show ALL the events listed in the documentation. I believe that
SQL Server 7.0 Profiler, by default, only showed a subset of the complete
set of events, but you can request the full set.
Again, sorry that I can't be of more specific assistance.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Dan Gold" <smu...@yahoo.com> wrote in message
news:3c740231$0$68191$7586...@news.frii.com...