263 lines
12 KiB
Plaintext
263 lines
12 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 CONEXIONES ACTIVAS
|
|
SELECT @@ServerName AS server,
|
|
NAME AS dbname,
|
|
LOGINAME AS LoginName,
|
|
PROGRAM_NAME as Aplicacion,
|
|
COUNT(STATUS) AS number_of_connections,
|
|
GETDATE() AS timestamp
|
|
FROM sys.databases sd
|
|
LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid
|
|
WHERE database_id NOT BETWEEN 1 AND 4
|
|
AND LOGINAME IS NOT NULL
|
|
GROUP BY NAME,LOGINAME,PROGRAM_NAME;
|
|
|
|
|
|
-- MUESTRA TRANSACIONES ABIERTAS SIN FINALIZAR (POR EJEMPLO ROLLBACKS PERPETUOS)
|
|
dbcc opentran
|
|
|
|
|
|
-- MUESTRA BLOQUEOS Y A CONTINUACION LOS PROCESOS EXISTENTES
|
|
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
|
|
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
|
|
exec sp_who2
|
|
GO
|
|
|
|
|
|
-- INFORMACION DE BLOQUEOS Y BLOQUEANTES CON INFORMACION EXTENDIDA
|
|
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
|
|
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
|
|
,session_cpu_time,session_reads,session_writes,session_logical_reads
|
|
,percent_complete,est_completion_time,request_start_time,request_status,command
|
|
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
|
|
,session_status,group_id,query_hash,query_plan_hash)
|
|
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
|
|
, LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
|
|
, sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
|
|
, req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
|
|
, sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
|
|
, CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
|
|
, req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
|
|
, req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
|
|
, LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
|
|
FROM sys.dm_exec_sessions AS sess
|
|
LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
|
|
LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id
|
|
)
|
|
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
|
|
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
|
|
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
|
|
, head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
|
|
, head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
|
|
FROM cteHead AS head
|
|
WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
|
|
AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
|
|
UNION ALL
|
|
SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
|
|
blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
|
|
h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
|
|
FROM cteHead AS blocked
|
|
INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
|
|
WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
|
|
)
|
|
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query
|
|
FROM cteBlockingHierarchy AS bh
|
|
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;
|
|
|
|
|
|
-- CAPTURAR TRANSACCIONES DE LARGA DURACION O NO CONFIRMADAS
|
|
SELECT [s_tst].[session_id],
|
|
[database_name] = DB_NAME (s_tdt.database_id),
|
|
[s_tdt].[database_transaction_begin_time],
|
|
[sql_text] = [s_est].[text]
|
|
FROM sys.dm_tran_database_transactions [s_tdt]
|
|
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
|
|
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
|
|
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
|
|
|
|
|
|
-- Para terminar la sesión que bloquea, ID 74 por ejemplo, usamos el comando KILL.
|
|
KILL 74
|
|
GO
|
|
|
|
|
|
-- MUESTRA PROCESOS ORDENADOS POR CONSUMO DE CPU
|
|
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
|
|
|
|
|
|
-- 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_worker_time DESC -- CPU time
|
|
|
|
|
|
-- PROCESOS MAS COSTOSOS
|
|
SELECT TOP 5
|
|
qs.total_worker_time AS [Total CPU Time],
|
|
qs.execution_count AS [Ejecuciones],
|
|
qs.total_worker_time/qs.execution_count as [Tiempo Medio CPU],
|
|
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 sentencia
|
|
FROM
|
|
sys.dm_exec_query_stats qs
|
|
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
|
|
ORDER BY [Tiempo Medio CPU] DESC
|
|
|
|
|
|
-- ESTADISTICAS DE SERVIDOR
|
|
SELECT * FROM sys.dm_os_performance_counters
|
|
WHERE object_name LIKE '%General Statistics%';
|
|
|
|
|
|
-- 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
|
|
|
|
|
|
-- MUESTRA TODOS LOS TRABAJOS EXISTENTES EN EL SERVIDOR
|
|
USE msdb;
|
|
GO
|
|
EXEC dbo.sp_help_jobactivity;
|
|
GO
|
|
|
|
|
|
-- MUESTRA TODOS LOS TRABAJOS EJECUTADOS, CUANDO Y CUANTO DURARON
|
|
SET NOCOUNT ON;
|
|
SELECT sj.name,
|
|
sh.run_date,
|
|
sh.step_name,
|
|
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
|
|
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
|
|
FROM msdb.dbo.sysjobs sj
|
|
JOIN msdb.dbo.sysjobhistory sh
|
|
ON sj.job_id = sh.job_id
|
|
|
|
|
|
-- MUESTRA LOS TRABAJOS EN EJECUCION
|
|
SELECT
|
|
ja.job_id,
|
|
j.name AS job_name,
|
|
ja.start_execution_date,
|
|
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
|
|
Js.step_name
|
|
FROM msdb.dbo.sysjobactivity ja
|
|
LEFT JOIN msdb.dbo.sysjobhistory jh
|
|
ON ja.job_history_id = jh.instance_id
|
|
JOIN msdb.dbo.sysjobs j
|
|
ON ja.job_id = j.job_id
|
|
JOIN msdb.dbo.sysjobsteps js
|
|
ON ja.job_id = js.job_id
|
|
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
|
|
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
|
|
AND start_execution_date is not null
|
|
AND stop_execution_date is null
|
|
ORDER BY Js.step_name
|
|
|
|
|
|
-- MUESTRA LAS ESPERAS EXISTENTES
|
|
SELECT TOP 10
|
|
[Wait type] = wait_type,
|
|
[Wait time (s)] = wait_time_ms / 1000,
|
|
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
|
|
/ SUM(wait_time_ms) OVER())
|
|
FROM sys.dm_os_wait_stats
|
|
WHERE wait_type NOT LIKE '%SLEEP%'
|
|
ORDER BY wait_time_ms DESC;
|
|
********************************************************************************
|
|
'CLR_SEMAPHORE'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de CLR y espera un semáforo.
|
|
'LAZYWRITER_SLEEP'.- Tiene lugar cuando se suspenden tareas de escritura diferida. Ésta es una medida del tiempo invertido por las tareas en segundo plano que esperan.
|
|
'RESOURCE_QUEUE'.- Tiene lugar durante la sincronización de diferentes colas internas de recursos.
|
|
'SLEEP_TASK'.- Tiene lugar cuando una tarea se mantiene inactiva mientras espera que se produzca un evento genérico.
|
|
'SLEEP_SYSTEMTASK'.- Tiene lugar durante el inicio de una tarea en segundo plano mientras se espera que tempdb finalice el inicio.
|
|
'SQLTRACE_BUFFER_FLUSH'.- Tiene lugar cuando una tarea está esperando a que una tarea en segundo plano vuelque los búferes de seguimiento al disco cada cuatro segundos.
|
|
'WAITFOR'.- Tiene lugar como resultado de una instrucción WAITFOR de Transact-SQL. La duración de la espera viene determinada por los parámetros de la instrucción. Se trata de una espera iniciada por el usuario.
|
|
'CLR_AUTO_EVENT'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de Common Language Runtime (CLR) y espera que se inicie un evento automático determinado. Son habituales las esperas largas y no indican ningún problema.
|
|
'CLR_MANUAL_EVENT'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de CLR y espera que se inicie un evento manual específico.
|
|
********************************************************************************
|
|
|
|
|
|
-- MUESTRA CUANTAS PAGINAS USADAS HAY POR MEMORY BOOFER POOL
|
|
SELECT (CASE
|
|
WHEN ( [database_id] = 32767 ) THEN 'Resource Database'
|
|
ELSE Db_name (database_id)
|
|
END ) AS 'Database Name',
|
|
Sum(CASE
|
|
WHEN ( [is_modified] = 1 ) THEN 0
|
|
ELSE 1
|
|
END) AS 'Clean Page Count',
|
|
Sum(CASE
|
|
WHEN ( [is_modified] = 1 ) THEN 1
|
|
ELSE 0
|
|
END) AS 'Dirty Page Count'
|
|
FROM sys.dm_os_buffer_descriptors
|
|
GROUP BY database_id
|
|
ORDER BY DB_NAME(database_id);
|
|
|
|
|
|
-- VACIA MEMORY BUFFER POOL
|
|
DBCC DROPCLEANBUFFERS
|
|
|
|
|
|
-- DISMINUIR TEMP-DB
|
|
>Primero comprobar cuanto espacio está ocupado
|
|
use tempdb;
|
|
exec sp_spaceused
|
|
>Dependiendo del espacio ocupado disminuir la bbdd en porcentaje (ej. 10%)
|
|
dbcc shrinkdatabase (tempdb, 10);
|
|
|
|
|