Friday, April 10, 2020

What’s up SQL SERVER?



Hi Folks,
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