Excessive disk usage

Question

Excessive disk usage

David Lester

Hall of Fame

Points: 3647
More actions
January 15, 2008 at 9:18 am

#119614

Hello all.
So first of all, I am not a DBA, more of a developer, however, its a small company so I become the defacto "tech" problem guy.
I have learned a great deal of SQL over several years now, but today I am faced with something I am at a loss to explain or fix. Our data warehouse SQL 2000 server has suddenly spiked disk usage to a crippling level, such as 100% usage non-stop, nearly any query has a pageiolatch on it.
Nothing has been altered on the server between last week when it performed acceptably, and today where run times have doubled or tripled on standard processes.
I have checked on all processes running, they are not unusual, many are daily or weekly processes. The machine is using very little processor or memory. At the moment, even with only two queries executing the disk usage is 100%.
Any suggestions as to what I can look at to find the issue that is behind this?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

It might be a hardware issue. Check to see if you have a failed disk in a RAID array. Ask your hardware vendor to perform diagnostics on the disks and controllers. If you ever ran disk IO benchmarks on server before, run them again to compare the performance.

David Lester Hall of Fame Points: 3647 More actions · Answer 2

I am still poking around, I just noticed one of the DB's had autoshrink on, and it is running at the moment. The DB is an archive DB so not actively used, would this be a likely thing to cause an unending 100% disk usage?

From what I am reading, it should be a background process, and only cause serious problems if it is locking a table or db that is being used. Am I off base on this?

Thanks!

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

Try to run the stored procedure sp_who2 to find out which connections are actually running/sleeping/locked.

DBCC INPUTBUFFER (SPID) gives you the command text.

In addition you could start a sql trace with sql server profiler (statement completed,statement started, rpc starting, SP completed) so see the actual commands executed on your server to find the culprit.

I don't have experience with Autoshrink, but why is that needed on an archive db since the data doesn't change anymore?

io hiccups can be caused by large table scans (perhaps some outdated statistics?). Take a peek at the (estimated) query plan of the troublesome queries in query analyzer.

Also a failed drive may cause the raid-array to run at a much slower speed.

David Lester Hall of Fame Points: 3647 More actions · Answer 4

Thanks Jo, I will try those things.

Actually, autoshrink is not needed, I have since turned it off, though that does not stop the one which has started already.

Unfortunately, there are only three users with connections to the server, myself being one of those, with no active processes running, and disk is still pegged at 100% read time.

I may yet have to get someone to check out the drives on the server, it "feels" to me like its a drive issue.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 5

Also check available memory and other applications running on the server.

Anything special in the logs? (sql, windows)

Ian Yates SSCoach Points: 19738 More actions · Answer 6

Use the SysInternals utilities (Process Explorer or Process Monitor) to have a squiz at what's going on. You can see what files are in use by which applications. ProcMon should let you see in real time what's being accessed (read or write) and by which application.