All Inserts/Updates halted for a 1 minute time period. Looking for a reason why.

We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
a 1 minute time period - there were SQL statements that all finished within
about 10 milliseconds of each other. I immediately suspected blocking as
the culprit but I am 100% sure that blocking was not the cause (I had a
"blocker script" running continuously looking for blocking and nothing
showed up).
When I looked a little closer - I noticed that ALL of these 8 SQL statements
were either Inserting or Updating. All 8 SQL statements were each touching
different tables and were in very small units of work. In looking at the
READS, WRITES and CPU for each of these statements they were all very low.
Again - I have to emphasize there was no database blocking involved.
Furthermore - during this time period I was able to determine that of all
the ~ 6200 SQL statements that were started, these 8 SQL statements were the
only ones updating. All other SL statements were reading only.
This means that for a one-minute time period all Update/Insert activity was
"halted" (without database blocking occurring). Does anyone have an
explanation as to why this could have occurred? Could it be a problem with
cache or transaction log backup?
Also - wanted to indicate that the transaction log backup was not
interfering with this time period. I checked msdb.backupset and I can see
that this questionable time period did not coincide with any t-log backup.
Looking for some advice... Maybe there are some pertainant Perfmon
counters that I could enable?
Thanks in advance!Hi
If you were using sp_blocker_pss80 you may want to decrease the time in your
delay between calls.
You may also want to trace lock escalations, timeouts and deadlocks in
profiler along with events in the audit category such as backup/restore and
DBCC events. Also check out any file expansions in performance monitor.
John
"TJT" wrote:
> We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
> a 1 minute time period - there were SQL statements that all finished within
> about 10 milliseconds of each other. I immediately suspected blocking as
> the culprit but I am 100% sure that blocking was not the cause (I had a
> "blocker script" running continuously looking for blocking and nothing
> showed up).
> When I looked a little closer - I noticed that ALL of these 8 SQL statements
> were either Inserting or Updating. All 8 SQL statements were each touching
> different tables and were in very small units of work. In looking at the
> READS, WRITES and CPU for each of these statements they were all very low.
> Again - I have to emphasize there was no database blocking involved.
> Furthermore - during this time period I was able to determine that of all
> the ~ 6200 SQL statements that were started, these 8 SQL statements were the
> only ones updating. All other SL statements were reading only.
> This means that for a one-minute time period all Update/Insert activity was
> "halted" (without database blocking occurring). Does anyone have an
> explanation as to why this could have occurred? Could it be a problem with
> cache or transaction log backup?
> Also - wanted to indicate that the transaction log backup was not
> interfering with this time period. I checked msdb.backupset and I can see
> that this questionable time period did not coincide with any t-log backup.
> Looking for some advice... Maybe there are some pertainant Perfmon
> counters that I could enable?
> Thanks in advance!
>
>