90 lines
2.8 KiB
Plaintext
90 lines
2.8 KiB
Plaintext
|
||
-- Proporciona información acerca de los usuarios actuales, sesiones y procesos en una instancia de SQL Server.
|
||
USE [master]
|
||
GO
|
||
exec sp_who; -- sp_who2 da más datos.
|
||
-- sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
|
||
|
||
|
||
-- MUESTRA BLOQUEOS
|
||
USE [master]
|
||
GO
|
||
SELECT spid,status,loginame,hostname,blk = CONVERT(char(3), blocked),dbname=SUBSTRING(DB_NAME(dbid),1, 10),cmd,waittype
|
||
FROM master.dbo.sysprocesses
|
||
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
|
||
GO
|
||
|
||
|
||
-- MUESTRA PROCESOS BLOQUEADOS (sesión_id) Y BLOQUEANTES (block_session_id),
|
||
-- qué tipo de espera está involucrado y cuál es el tiempo de espera de ese bloqueo, así como los recursos involucrados.
|
||
USE [master]
|
||
GO
|
||
SELECT session_id,blocking_session_id,wait_time,wait_type,last_wait_type,wait_resource,transaction_isolation_level,lock_timeout
|
||
FROM sys.dm_exec_requests
|
||
WHERE blocking_session_id <> 0
|
||
GO
|
||
|
||
|
||
-- MUESTRA PROCESOS ORDENADOS POR CONSUMO DE CPU
|
||
USE [master]
|
||
GO
|
||
SELECT TOP 10
|
||
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
|
||
SUBSTRING(qt.text,qs.statement_start_offset/2,
|
||
(case when qs.statement_end_offset = -1
|
||
then len(convert(nvarchar(max), qt.text)) * 2
|
||
else qs.statement_end_offset end -qs.statement_start_offset)/2)
|
||
as query_text,
|
||
qt.dbid, dbname=db_name(qt.dbid),
|
||
qt.objectid
|
||
FROM sys.dm_exec_query_stats qs
|
||
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
|
||
ORDER BY
|
||
[Avg CPU Time] DESC
|
||
|
||
|
||
-- MUESTRA CONTADORES DE EJECUCION
|
||
USE [master]
|
||
GO
|
||
SELECT TOP 10
|
||
qs.execution_count,
|
||
SUBSTRING(qt.text,qs.statement_start_offset/2,
|
||
(case when qs.statement_end_offset = -1
|
||
then len(convert(nvarchar(max), qt.text)) * 2
|
||
else qs.statement_end_offset end -qs.statement_start_offset)/2)
|
||
as query_text,
|
||
qt.dbid, dbname=db_name(qt.dbid),
|
||
qt.objectid
|
||
FROM sys.dm_exec_query_stats qs
|
||
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
|
||
ORDER BY
|
||
qs.execution_count DESC
|
||
|
||
|
||
-- QUERYS CON MAYOR CONSUMO
|
||
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
|
||
((CASE qs.statement_end_offset
|
||
WHEN -1 THEN DATALENGTH(qt.TEXT)
|
||
ELSE qs.statement_end_offset
|
||
END - qs.statement_start_offset)/2)+1),
|
||
qs.execution_count,
|
||
qs.total_logical_reads, qs.last_logical_reads,
|
||
qs.total_logical_writes, qs.last_logical_writes,
|
||
qs.total_worker_time,
|
||
qs.last_worker_time,
|
||
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
|
||
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
|
||
qs.last_execution_time,
|
||
qp.query_plan
|
||
FROM sys.dm_exec_query_stats qs
|
||
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
|
||
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
|
||
ORDER BY qs.total_logical_reads DESC -- logical reads
|
||
-- ORDER BY qs.total_logical_writes DESC -- logical writes
|
||
-- ORDER BY qs.total_worker_time DESC -- CPU time
|
||
|
||
|
||
-- Para terminar la sesión que bloquea, ID 74 en este caso, usamos el comando KILL.
|
||
KILL 74
|
||
GO
|