Find what is going on with your SQL Server

Hello Guys, 

This blog is to tell you the way how you can find the things which are running in your SQL Server. Those things can be locks on your SQL Server, batch jobs, SQL queries, procedure etc. 

The way which i already know was executing procedure of either "SP_WHO" or new optimized with blockby column"SP_WHO2". 

Today i have learnt new way to do that from blog of SQL consultant who has optimized this process more in really broad way. Things which i have learnt from his blog , i am going to right them down below and i hope that will help you guys too.

-- Get the details whats going on in your SQL server

EXEC SP_WHO

-- It will give you few extra column from sp_who proc Which will help you to find which SPID is block by which SPID by having a quick watch in blockBy column

EXEC SP_WHO2

-- Here 55 is SPID which i took from sp_who or sp_who2 proc which is in blkby column

DBCC INPUTBUFFER(55)

 -- "SP_WHOISACTIVE" that you need to download from a blog by Adam (http://sqlblog.com/files/folders/beta/entry42453.aspx) and run the SQL script in your SQL server it will give you the statistics of your SQL Query

-- it will return the actual Query which are doing something with your SQL Server

-- you can also see the locks, waiting time, read and write time etc.

-- First you should start by executing "Sp_WHOISACTIVE" without any parameter. After when you feel comfortable with result then you should go a head with parameters.

-- I have learnt about two parameters till now which will give you the result with client statistics.

-- ** Note : if your server is really busy then try to optimize query first to get specific SPID which is causing issue according to you because in busy server there would be a lot of things going on and if you go with all then it will take long time to determine the things.

EXEC SP_WHOISACTIVE

         @get_plans=1,

           @get_transaction_info=1