Sessions details and check the application codes Isolation level

USE [DBA]

GO

/****** Object:  StoredProcedure [dbo].[p_SessionDetails]    Script Date: 28/05/2015 12:08:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[p_SessionDetails]

as begin

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sessiondetails]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[sessiondetails](

       [SESSION_ID] [smallint] NULL,

       [SESSION_REQUEST_ID] [int] NULL,

       [STATUS] [nvarchar](max) NULL,

       [HOST_NAME] [nvarchar](max) NULL,

       [CLIENT_NET_ADDRESS] [varchar](max) NULL,

       [LOGIN_NAME] [nvarchar](max) NULL,

       [PROGRAM_NAME] [nvarchar](max) NULL,

       [DATABASE_NAME] [nvarchar](max) NULL,

       [COMMAND] [nvarchar](max) NULL,

       [QUERY_TEXT] [nvarchar](max) NULL,

       [XML_QUERY_PLAN] [xml] NULL,

       [CURRENT_WAIT_TYPE] [nvarchar](max) NULL,

       [LAST_WAIT_TYPE] [nvarchar](max) NULL,

       [BLOCKING_SESSION_ID] [smallint] NULL,

       [ROW_COUNT] [bigint] NULL,

       [GRANTED_QUERY_MEMORY] [int] NULL,

       [OPEN_TRANSACTION_COUNT] [int] NULL,

       [USER_ID] [int] NULL,

       [PERCENT_COMPLETE] [real] NULL,

       [TRANSACTION_ISOLATION_LEVEL_NAME] [varchar](max) NULL,

       [date] datetime

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

INSERT INTO [dbo].[sessiondetails]

           ([SESSION_ID]

           ,[SESSION_REQUEST_ID]

           ,[STATUS]

           ,[HOST_NAME]

           ,[CLIENT_NET_ADDRESS]

           ,[LOGIN_NAME]

           ,[PROGRAM_NAME]

           ,[DATABASE_NAME]

           ,[COMMAND]

           ,[QUERY_TEXT]

           ,[XML_QUERY_PLAN]

           ,[CURRENT_WAIT_TYPE]

           ,[LAST_WAIT_TYPE]

           ,[BLOCKING_SESSION_ID]

           ,[ROW_COUNT]

           ,[GRANTED_QUERY_MEMORY]

           ,[OPEN_TRANSACTION_COUNT]

           ,[USER_ID]

           ,[PERCENT_COMPLETE]

           ,[TRANSACTION_ISOLATION_LEVEL_NAME]

                 ,[date]

                 )

  

SELECT

R.SESSION_ID,

R.REQUEST_ID AS SESSION_REQUEST_ID,

R.STATUS,

S.HOST_NAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ' (' + S.ORIGINAL_LOGIN_NAME + ')' END AS LOGIN_NAME,

S.PROGRAM_NAME,

DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,

R.COMMAND,

ST.TEXT AS QUERY_TEXT,

QP.QUERY_PLAN AS XML_QUERY_PLAN,

R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

R.LAST_WAIT_TYPE,

R.BLOCKING_SESSION_ID,

R.ROW_COUNT,

R.GRANTED_QUERY_MEMORY,

R.OPEN_TRANSACTION_COUNT,

R.USER_ID,

R.PERCENT_COMPLETE,

CASE R.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN 'UNSPECIFIED'

WHEN 1 THEN 'READUNCOMITTED'

WHEN 2 THEN 'READCOMMITTED'

WHEN 3 THEN 'REPEATABLE'

WHEN 4 THEN 'SERIALIZABLE'

WHEN 5 THEN 'SNAPSHOT'

ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME,

getdate() as Date

FROM

SYS.DM_EXEC_REQUESTS R

LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID

LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP

WHERE

R.STATUS NOT IN ('BACKGROUND','SLEEPING')

End

GO