MSSQL 저장프로시져 SP 모니터하기

view source

print?

01	-- 1.저장프로시져별 실행수 뽑기

02	select db_name(st.dbid) DBName

03	,object_schema_name(st.objectid,dbid) SchemaName

04	,object_name(st.objectid,dbid) StoredProcedure

05	,sum(qs.execution_count) Execution_count

06	from sys.dm_exec_cached_plans cp

07	join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle

08	cross apply sys.dm_exec_sql_text(cp.plan_handle)st

09	where DB_Name(st.dbid) is not null and cp.objtype = 'proc'

10	group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)

11	order by sum(qs.execution_count) desc

12

13

14	--2. CPU소모량이 많은 저장프로시져 뽑기

15	select db_name(st.dbid) DBName

16	,object_schema_name(st.objectid,dbid) SchemaName

17	,object_name(st.objectid,dbid) StoredProcedure

18	,sum(qs.execution_count) Execution_count

19	,sum(qs.total_worker_time) total_cpu_time

20	,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time

21	from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle

22	cross apply sys.dm_exec_sql_text(cp.plan_handle) st

23	where db_name(st.dbid) is not null and cp.objtype='proc'

24	group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)

25	order by sum(qs.total_worker_time) desc

26

27	--3. IO량이 많은 저장프로시져 뽑기

28	select db_name(st.dbid) DBName

29	,object_schema_name(objectid,st.dbid) SchemaName

30	,object_name(objectid,st.dbid) StoredProcedure

31	,sum(execution_count) execution_count

32	,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO

33	,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum

34	(execution_count) avg_total_IO

35	,sum(qs.total_physical_reads) total_physical_reads

36	,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read

37	,sum(qs.total_logical_reads) total_logical_reads

38	,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read

39	,sum(qs.total_logical_writes) total_logical_writes

40	,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes

41	from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st

42	join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

43	where db_name(st.dbid) is not null and cp.objtype = 'proc'

44	group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)

45	order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc

46

47	--4. 처리시간이 긴 저장프로시져 뽑기

48	select db_name(st.dbid) DBName

49	,object_schema_name(objectid,st.dbid) SchemaName

50	,object_name(objectid,st.dbid) StoredProcedure

51	,sum(execution_count) execution_count

52	,sum(qs.total_elapsed_time) total_elapsed_time

53	,substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23)  total_elapsed_time_ms

54	,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time

55	from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st

56	join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

57	where db_name(st.dbid) is not null and cp.objtype = 'proc'

58	group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)

59	order by sum(qs.total_elapsed_time) desc

출처 : http://unions5.tistory.com/m/post/view/id/98