1

Topic: How to find that loads a disk on the server?

Is MSSQL 11.0.5058 on WINSRV 2008R2.
Files of databases lie on a system disk, log files on other disk.
From time to time, the system disk queue length to become> 100 units, all dies, the monitor of resources shows nothing (in sense starts to show, when queue falls also who so sinkers not clearly). On broad gulls it became clear that it is Process sqlservr.
As to me to adjust a broad gull in the server that it became clear who so loads that...?

2

Re: How to find that loads a disk on the server?

It can be a consequence of very many moments
Beginning from badly written requests to a wrong choice of iron
The ideal decision it to employ  on a sequel that he looked at your system
And so look here at it

select
*
from
sys.dm_os_wait_stats
where
wait_type like ' pageiolatch %'
order by
wait_type

As counter Page Life
And here it

SELECT dm_ws.wait_duration_ms;
dm_ws.wait_type;
dm_es.status;
dm_t. TEXT;
dm_qp.query_plan;
dm_ws.session_ID;
dm_es.cpu_time;
dm_es.memory_usage;
dm_es.logical_reads;
dm_es.total_elapsed_time;
dm_es.program_name;
DB_NAME (dm_r.database_id) DatabaseName;
-- Optional columns
dm_ws.blocking_session_id;
dm_r.wait_resource;
dm_es.login_name;
dm_r.command;
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO

3

Re: How to find that loads a disk on the server?

Thanks I will track. Yes I approximately know that, but that was specific I do not know smile

4

Re: How to find that loads a disk on the server?

select top 100
proc_name = object_schema_name (st.objectid, st.dbid) + '. ' + object_name (st.objectid, st.dbid) +case when st.number> 1 then isnull ('; ' +convert (varchar, st.number), ") else" end;
sql = replace (replace (replace (substring (st.text;
qs.statement_start_offset/2 + 1,
(case qs.statement_end_offset when-1 then datalength (st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2 + 1), char (13), "), char (10),"), char (9), ");
disk_reads = qs.total_physical_reads;
query_plan = try_convert (xml, pt.query_plan)
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) st
cross apply sys.dm_exec_text_query_plan (qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) pt
order by
qs.total_physical_reads desc;

Only I up to the end am not assured of reliability total_physical_reads.