Processes in SQL Server and script to Kill All Inactive Sessions – Kill Sleeping Sessions from sp_who2

We have different kind of Process status in SQL Server, which we can find by using below query

  SELECT * FROM master.dbo.sysprocesses

  OR 

  SP_WHO2

As we know every process need Disk, Thread and CPU to run. so below is the explanation to understand each process.

Process Status	THREAD	CPU	DISK	Description
Pending	Not available	Not available	Available	The Process has no thread, no CPU, but waiting for them to available.
Runnable	Available	Not available	Available	The Process has thread but no CPU, but waiting for CPU to available.
Running	Available	Available	Available	The Process has all three and is in running state
Suspended	Available	Available	Not available	The Process is waiting for some event to get completed (May be lock or IO is not available)
Sleeping	Not available	Not available	Not available	The Process is doing nothing and waiting for further commands
Dormant				The SQL Server is reseting this process
Background				These background process run by SQL Server for any job
SpinLock				This Process is busy waiting in CPU for it's own turn.

The below script to kill all inactive sessions. To kill sleeping sessions from sp_who2 can also use this script.

DECLARE @user_spid INT

DECLARE CurSPID CURSOR FAST_FORWARD

FOR

       SELECT SPID

       FROM master.dbo.sysprocesses (NOLOCK)

       WHERE spid>50 -- avoid system threads

       AND status='sleeping' -- only sleeping threads

       AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours

       AND spid<>@@spid -- ignore current spid

       OPEN CurSPID

 

       FETCH NEXT FROM CurSPID INTO @user_spid

       WHILE (@@FETCH_STATUS=0)

       BEGIN

              PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)

              EXEC('KILL '+@user_spid)

              FETCH NEXT FROM CurSPID INTO @user_spid

       END

       CLOSE CurSPID

       DEALLOCATE CurSPID

GO

Move TempDB location for best SQL Server performance

We should move our TempDB to a faster drive for better performance.

Generally , when we install SQL Server by default and we do not customize it, it is quite possible that our TempDB is located on the same drive where our Operating System is installed. It is never a good idea to install any database on the same drive as Operating System. so we should move that TempDB to the another faster drive location.

To check the location of our TempDB, execute below store procedure.

USE TempDB
GO
EXEC sp_helpfile
GO

For moving this TempDB file location execute below query

Use Master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME= tempdev, FILENAME= 'D:\tempdb_mssql_1.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME= templog, FILENAME= 'D:\templog.ldf')
GO

After moving file to D:\ we should restart the SQL Server, other wise system will still pointing the old location. 

Note: Remember to check the security or permission of the new TempDB location.