Posted by Erland Sommarskog on 07/23/05 00:54

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Our database server has started acting weird and at this point I'm
> either too sleep deprived or close to the problem to adequately diagnose
> the issue.
>
> Basically to put it simply... when I look at the read disk queue length,
> the disks queues are astronomical.
>
> normally we're seeing a disk queue length of 0-1 on the disks that contain
> the DB data and index. (i.e non clustered indexes are on a disk of their
> own).
>
> Writes are just fine.
>
> Problem is, all our databases are on the same drive, and I can't seem to
> nail down which DB, let alone which table is the source of all our reads.
>
> Now, to really make things weirder.. during the busier times of the day
> today (say 1:00 PM to 4:00 PM) things were fine.
>
> At 4:20 PM or so it was like someone hit a switch and read disk queue
> length jumped from 0-1 up to 100-200+... with spikes up to 1500 for a
> split second or so.
>
> What's the best way folks know to nail down this?

I would use Profiler. I'm also fond of my own aba_lockinfo, which
gives a snapshot of all active processes, their locks and their
statements. You find it on
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]