Current disk queue length

Question

Current disk queue length

Juanita

SSCertifiable

Points: 5300
More actions
June 2, 2005 at 2:27 pm

#93070

HI. I've noticed my sql server performance counter, "current disk queue length", is running very high. How can I tell what application or users could be causing this.
Juanita

Viewing 4 posts - 1 through 3 (of 3 total)

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

Glauco Costa Mr or Mrs. 500 Points: 547 More actions · Answer 1

With Perfmon, under process, select IO* counters ,but well-taken care of therefore these accountants also collect IO of network, try to stop your anti-virus solution and verify if extreme IO continues.

Joe Old Hand Points: 319 More actions · Answer 2

if you suspect SQL Server process try

SELECT spid 'PID', rtrim(status) 'Status', SUSER_SNAME(sid) 'User', rtrim(hostname) 'Host', rtrim(program_name) 'Program', memusage 'Mem Usage', cpu 'CPU Time', physical_io 'I/O', blocked 'Blocked', 'Database'= CASE WHEN dbid=0 THEN '[NULL]' ELSE DB_NAME(dbid) END, cmd 'Command', last_batch 'Last Batch', login_time 'Login Time', rtrim(nt_domain) 'NT Domain', rtrim(nt_username) 'NT User', net_address 'Net Address', net_library 'Net Library' FROM master.dbo.sysprocesses (nolock) ORDER BY 8 desc

or

this

SELECT @handle = sql_handle

FROM master..sysprocesses

WHERE physical_io in (select max(physical_io)FROM master.dbo.sysprocesses)

print @handle

SELECT db_name(dbid),object_name(objectid),[text]

FROM ::fn_get_sql(@handle)

William Carroll Newbie Points: 7 More actions · Answer 3

If you want to know what internal queries are doing large reads\writes over a period of time you can run a profiler trace and capture various SP and TSQL events. They will tell you the amount of read and write activity occuring for each statement. The reads is not necessarily I/O but might be from the buffer pool. But this give you a starting point.

After you capture a profiler trace download and run read80trace.exe on the profiler trace. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;887057

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

Read80trace takes a profiler trace as input and will create an xml\html docuement that summarizes batches and statements by duration, cpu, memory, reads, writes.

Once you have identified these problem queries you can start to tune them.

As a general guideline current disk queue length > 2 is a warning. Avg sec. disk read\write\transfer I use < 10ms excellent, 10-20ms very good, 20-30ms good, 30-40ms average, > 40ms poor.

If after tuning the queries and analyzing the datbase file layouts are optimal you can start to add more spindes to the drives until the disk counter numbers come into line with the guideline or you see performance improve.

HTH,

Bill