Hi Folks,
Whenever we get any performance issue on SQL database server our first question is always "what’s going on my server?"
Whenever we get any performance issue on SQL database server our first question is always "what’s going on my server?"
To check it we have SQL inbuilt stored procedure sp_who2
active, but I hate to use it because first thing this gives me system sessions
(SPID<51) which are not much useful for me ๐ and second thing this does not
give me all information which I am looking for in first sight ๐.
In solution there is best stored procedure I ever have seen sp_WhoIsActive developed by Adam Machanic ๐, but thing is we have to download it from here and create on our SQL server which little difficult in large environment and not having registered server/CMS. But you can use it where you have few severs and user guide for this is here.
So finally to mitigate this I tried to create simple query and sharing same with you. This gives SPID, status, blocking session if it’s blocked, host name with respective login name and program name, useful performance parameters CPU, Memory, I/O , reads, writes, duration of session in mins and most important is wait type of session with execution plan and SQL text which helps me to analyze session. Also this does not show system session ๐
Use it and suggestions/feedback are always welcome!!
select
er.session_id as SPID,
er.blocking_session_id
as blk_by,
er.status,
es.loginame,
es.hostname,
db_name(er.database_id) as [database],
er.command,
er.open_transaction_count
as open_trans,
es.last_batch,
DATEDIFF (MINUTE,er.start_time,GETDATE()) as
duration_mins,
er.reads,
er.writes,
es.cpu,
es.memusage,
es.physical_io as [IO],
er.row_count,
st.text,
qp.query_plan,
er.last_wait_type,
es.program_name
from sys.dm_exec_requests
er
inner join
sys.sysprocesses es on
er.session_id=
es.spid
CROSS APPLY
sys.dm_exec_query_plan(er.plan_handle) AS qp
CROSS APPLY
sys.dm_exec_sql_text(er.plan_handle) AS st
where er.session_id
<> @@SPID
order by
DATEDIFF (MINUTE,er.start_time,GETDATE()) desc,es.cpu desc, er.reads desc, er.writes desc
|
No comments:
Post a Comment