sqlserver的日常检查脚本归结

print '                             '

print '-------------------------------------------------------'

print '30.查看 客户端连接IP'

print '-------------------------------------------------------'

SELECT distinct client_net_address FROM sys.dm_exec_connections 

WHERE session_id >50 and session_id != @@SPID and client_net_address 

not like '%<local machine>%'

go

print '                             '

print '                             '

print '-------------------------------------------------------'

print '31.查看消耗性能的存储过程名、存储过程内容'

print '-------------------------------------------------------'

select distinct procname,text,b.cached_time,

b.last_execution_time,b.total_elapsed_time,

b.avg_elapsed_time,

b.last_elapsed_time,b.execution_count

 from (

select top 1000 sql_text.text as text,

sql_handle,

plan_generation_num,

execution_count,

dbid,

objectid

from sys.dm_exec_query_stats a

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num > 1

order by plan_generation_num desc

)  a,

(

SELECT TOP 1000 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'procname', 

d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],

d.last_elapsed_time, d.execution_count

FROM sys.dm_exec_procedure_stats AS d

ORDER BY [total_worker_time] DESC

) b where a.objectid=b.object_id

order by avg_elapsed_time,execution_count desc

go