CODEWITHSUNDEEP
sql-serverdatabase-performancedatabase-administration
SamFenton
SamFenton

Reputation: 33

Database using excessive proportion of the server disk I/O

I have a client where one of the databases (SQL Server 2008 R2) is using an excessive proportion of the Server's disk I/O. They have observed 300-1000MB/s reads sustained for upwards of 10 minutes at a time, which is affecting other services.

This is really outside of our work that I have as and I do not know how to tackle this issue. They want to minimize the impact this is having and want to know any recommendations.

I would be grateful if someone can tell me where to start to analyse this behavior.

Thanks.

Upvotes: 1

Views: 52

Answers (1)

pacreely
pacreely

Reputation: 1931

Try this query to see what's going on.

SELECT 
    DB_NAME(qs.database_id) AS DBName
    ,qs.database_id
    ,qs.object_id
    ,OBJECT_NAME(qs.object_id,qs.database_id) AS ObjectName
    ,qs.cached_time
    ,qs.last_execution_time
    ,qs.plan_handle
    ,qs.execution_count
    ,total_worker_time
    ,last_worker_time
    ,min_worker_time
    ,max_worker_time
    ,total_physical_reads
    ,last_physical_reads
    ,min_physical_reads
    ,max_physical_reads
    ,total_logical_writes
    ,last_logical_writes
    ,min_logical_writes
    ,max_logical_writes
    ,total_logical_reads
    ,last_logical_reads
    ,min_logical_reads
    ,max_logical_reads
    ,total_elapsed_time
    ,last_elapsed_time
    ,min_elapsed_time
    ,max_elapsed_time
FROM 
    sys.dm_exec_procedure_stats qs
ORDER BY
    total_physical_reads DESC

Upvotes: 1

Related Questions