October 27, 2009 at 8:51 am
Hi,
I want to measure server load in terms of following measures on daily basis.
1- Total queries ran
2- Max, AVG CPU
3- Max, AVG Read
4- Max, AVG Write
5- Max, AVG Duration
6- Top 10 CPU queries
7- Top 10 Read queries
8- Top 10 Write queries
9- Top 10 Duration queries
What's the best way to do it? Please note that this is NOT a one time analysis, i need this information on daily basis stored in a table from where i can generate a report for current and past day's data.
Thanks for your help.
October 27, 2009 at 10:04 am
You can use DMV views in SQL 2005. If you have SQL 2008, you can use it as Central Management Server for all SQl 2005 and it has also ready reports that you wanted to. Please check the scripts below:(the scripts below will tell what are the most expensive queries on the SQL 2005).
select top 50
db_name(qp.dbid) dbname, st.text,
substring(st.text, qs.statement_start_offset/2 + 1,
(case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2 + 1) stmt_text,
qs.last_execution_time, qs.execution_count,
qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, qs.max_worker_time,
qs.total_worker_time / qs.execution_count avg_worker_time,
qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads,
qs.total_logical_reads / qs.execution_count avg_logical_reads,
qs.total_logical_writes, qs.last_logical_writes, qs.min_logical_writes, qs.max_logical_writes,
qs.total_logical_writes / qs.execution_count avg_logical_writes,
qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
qs.total_elapsed_time / qs.execution_count avg_elapsed_time,
qs.total_clr_time, qs.last_clr_time, qs.min_clr_time, qs.max_clr_time,
qs.total_clr_time / qs.execution_count avg_clr_time
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
order by qs.total_logical_reads desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply