8/17/2019 SESIONES ACTIVAS

1/2

/*SESIONES ACTIVAS*/

SELECTSessions.session_id AS SessionID, Requests.request_id AS RequestID,Requests.database_id AS DatabaseID, databases.name AS DatabaseName,Sessions.login_name AS LoginName, Sessions.host_name AS HostName, Sessio

ns.program_name AS ProgramName,Sessions.client_interface_name AS ClientInterfaceName,Requests.blocking_session_id AS BlockedBySessionID,ISNULL(BlockRequests.BlockingRequestCount,0) AS BlockingRequestCount,Requests.wait_type AS WaitType,Requests.wait_time AS WaitTime, Requests.cpu_time AS CPUTime, Requests.t

otal_elapsed_time AS ElapsedTime,Requests.reads AS Reads, Requests.writes AS Writes, Requests.logical_rea

ds AS LogicalReads,dm_os_tasks.PendingIOCount, Requests.row_count AS [RowCount],Requests.granted_query_memory*8 AS GrantedQueryMemoryKB,CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*1

0+Requests.writes*10+Requests.logical_reads+1)) AS Score,Statements.text AS BatchText,LEN(Statements.text) AS BatchTextLength,Requests.statement_start_offset/2 AS StatementStartPos,CASE

WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(nvarcha

r(MAX),Statements.text))*2ELSE Requests.statement_end_offsetEND/2 AS StatementEndPos,(CASE

WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2

ELSE Requests.statement_end_offsetEND - Requests.statement_start_offset)/2 AS StatementTextLength,CASE

WHEN Requests.sql_handle IS NULL THEN ' 'ELSE

SubString(Statements.text,

(Requests.statement_start_offset+2)/2,(CASEWHEN Requests.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2ELSE Requests.statement_end_offset

END - Requests.statement_start_offset)/2)

END AS StatementText,QueryPlans.query_plan AS QueryPlan

FROMsys.dm_exec_sessions AS SessionsJOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.se

ssion_id

LEFT OUTER JOIN sys.databases ON Requests.database_id=databases.database _id

LEFT OUTER JOIN (SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FRO

M sys.dm_exec_requests GROUP BY blocking_session_id) AS BlockRequests ON Requests.session_id=BlockRequests.blocking_session

 _idLEFT OUTER JOIN (

SELECT request_id, session_id, SUM(pending_io_count) AS PendingIOCount

8/17/2019 SESIONES ACTIVAS

2/2

FROM sys.dm_os_tasks WITH (NOLOCK)GROUP BY request_id, session_id

) AS dm_os_tasks ONRequests.request_id=dm_os_tasks.request_idAND Requests.session_id=dm_os_tasks.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS StatementsCROSS APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans

ORDER BY score DESCGO

--dbcc sqlperf(logspace)--go