adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Как узнать какие запросы грузят диск

SELECT

SUM(qs.total_physical_reads) as physical_reads,

SUM(qs.total_logical_reads) as logical_reads

into T1 FROM (

select top 100000 * from

sys.dm_exec_query_stats qs

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc

) as qs;

select top 100

(qs.total_physical_reads) as physical_reads,

(qs.total_logical_reads) as logical_reads,

qp.query_plan,

st.text,

dtb.name,

qs.*,

st.dbid

INTO T2

FROM

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

left outer join sys.databases as dtb on st.dbid = dtb.database_id

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc;

select

(T2.physical_reads*100/T1.physical_reads) as percent_physical_reads,

(T2.logical_reads*100/T1.logical_reads) as percent_logical_reads,

T2.*

from

T2 as T2

INNER JOIN T1 as T1

ON 1=1

order by T2.total_physical_reads desc

;

drop table T2

;

drop table T1

;

Какие бд грузят диск

WITH DB_Disk_Reads_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 

              FROM sys.dm_exec_plan_attributes(qs.plan_handle)

              WHERE attribute = N'dbid') AS F_DB

 GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],

       DatabaseName, [physical_reads], 

       CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]

FROM DB_Disk_Reads_Stats

WHERE DatabaseID > 4 -- system databases

AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>