SQL Server adora los bloqueos.
SQL Server utiliza bloqueos «pesimistas» por defecto – sus consultas son naturalmente defensivas. Esto puede dar lugar a problemas de bloqueo que le impiden escalar SQL Server.
Clases de formación en vídeo gratuitas: antes de solucionar problemas de bloqueo y bloqueos, comience con Cómo pensar como el motor. Es una clase rápida de 90 minutos que explica cómo funcionan los índices internos agrupados y no agrupados, y que es vital para entender cómo la indexación afecta al bloqueo. Es gratuito, porque creemos que todos los profesionales de SQL Server deberían empezar su formación aquí. Realmente es así de importante – buen material.
Herramientas para diagnosticar la contención de bloqueos
«¿Tengo esperas de bloqueos ahora mismo?» Utilice sp_BlitzFirst® para diagnosticar si las esperas de bloqueo están afectando actualmente a su SQL Server.
«¿Qué tablas tienen los peores problemas de bloqueo?» Ejecute sp_BlitzIndex® y busque «Aggressive Index Disorder» para identificar qué tablas han tenido altas cantidades de esperas de bloqueo (para tablas e índices actualmente en memoria).
«¿Cómo le doy sentido al informe de procesos bloqueados?» Cuando capture el informe de procesos bloqueados de SQL Server (guiones más abajo), el visor de informes de procesos bloqueados de Michael J Swart es una herramienta gratuita que le ayuda a interpretar los resultados.
«¿Qué ocurre si mi aplicación no detecta errores de bloqueo?» Amit Banerjee, del equipo de CSS de Microsoft, le ayuda a consultar la sesión de Salud del sistema de eventos extendidos de SQL Server para obtener detalles (SQL Server 2008+).
Contadores de rendimiento relacionados con los bloqueos
Estos contadores de rendimiento pueden ser muy útiles para configurar una Alerta de SQL Server basada en contadores de perf para que SQL Server le notifique cuando los bloqueos superen los umbrales que usted establezca.
- Se enumeran primero OBJECT y luego CONTADOR
- SQL Server: Estadísticas generales – Procesos bloqueados
- SQL Server: Bloqueos – Tiempo de espera de bloqueos (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Aprenda sobre más contadores de perf en SQL Server aquí.
Lea artículos sobre bloqueos, niveles de aislamiento y deadlocks
Lea una introducción a los bloqueos de dos fases y deadlocks de Jeremiah.
Aprende cómo puedes probar los cambios en el código para saber qué bloqueos son necesarios de la mano de Kendra.
Jeremiah te enseña a encontrar y prevenir los deadlocks.
Aprende a implementar el bloqueo optimista en SQL Server de la mano de Kendra.
Lee más sobre los niveles de aislamiento en SQL Server – consulta nuestra página de enlaces y referencias completas.
Scripts para utilizar el Informe de Procesos Bloqueados
El «Informe de Procesos Bloqueados» ha existido durante mucho tiempo en SQL Server- y sigue siendo una herramienta útil. Por defecto en SQL Server, el detector de bloqueo se despierta cada 5 segundos para comprobar si sus consultas están en un «abrazo mortal». Puede activar una opción que pide a SQL Server que utilice este recurso para comprobar si hay bloqueos de larga duración y emitir un informe.
ADVERTENCIA: Sólo debe configurar el informe de procesos bloqueados para que se emita para valores de cinco segundos o superiores. Microsoft advierte que si configura esto de 1 a 4, podría hacer que el detector de bloqueos se ejecute continuamente y acabe con su rendimiento.
Para utilizar el informe de procesos bloqueados, debe configurar una traza que busque el evento «Informe de procesos bloqueados» en «Errores y advertencias». Le recomendamos que utilice un rastreo del lado del servidor y, por supuesto, que sólo lo ejecute cuando lo necesite.
¿Quiere ver una demostración de esto? Vea una aquí.
Los scripts en esta sección son muestras de formas comunes de hacer estas tareas – usted debe probar y revisar en consecuencia para su entorno, y ejecutar bajo su propio riesgo.
Paso 1: Activar el informe de procesos bloqueados. Esto buscará cualquier bloqueo que tome 20 segundos o más.
|
1
2
3
5
6
7
8
9
10
11
12
13
14
15
|
–Asegúrese de que no tiene ningún cambio pendiente
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE
GO
exec sp_configure ‘blocked process threshold (s)’, 20;
GO
RECONFIGURE
GO
|
Paso 2: Configurar una traza para capturar el informe de procesos bloqueados. Ejecútelo como una traza del lado del servidor.
- Debe contener un evento: Errors and Warnings -> Blocked Process Report.
- Sólo necesita dos columnas: Texto, spid
Este ejemplo de rastreo del lado del servidor se ejecutará durante cinco minutos y se detendrá automáticamente. Debe cambiar la ruta a un directorio válido en el que SQL Server tenga permiso para escribir.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
— Creado por: SQL Server 2012 Profiler
— Crear una cola
declarar @rc int
declarar @TraceID int
declarar @maxfilesize bigint
declarar @DateTime datetime
———Añadir una función aquí:
set @DateTime = DATEADD(mi,5,getdate()); /* Ejecutar durante cinco minutos */
set @maxfilesize = 5
— Sustituye el texto InsertFileNameHere, por un apropiado
— nombre de archivo prefijado por una ruta, e.g., c:\MiCarpetaMiTraza. La extensión .trc
— se añadirá al nombre del archivo automáticamente. Si está escribiendo desde
— el servidor remoto a la unidad local, utilice la ruta UNC y asegúrese de que el servidor tiene
— acceso de escritura a su recurso compartido de red
———–Establezca mi nombre de archivo aquí:¡
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Client side File and Table cannot be scripted
— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Establecer los Filtros
declarar @intfilter int
declarar @bigintfilter bigint
— Establecer el estado de la traza en start
exec sp_trace_setstatus @TraceID, 1
— mostrar id de traza para futuras referencias
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
Paso 3: Mirar las trazas en ejecución y obtener el id de la traza:
|
1
2
|
SELECT * from sys.traces;
GO
|
Detenga una traza, si es necesario
|
1
2
3
4
5
|
–Introducir el traceid correcto de la consulta anterior
EXEC sp_trace_setstatus @traceid =2, @status = 0; — detener la traza (asumiendo que es el ID de traza 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — cerrar la traza (asumiendo que es el ID de traza 2)
GO
|
Paso 4: Leer los datos del informe del proceso bloqueado.
En este ejemplo utilizamos el visor gratuito de informes de procesos bloqueados de Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Paso 5: ¡Limpiar!
No olvides este paso.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Asegúrate de que tu rastro ha desaparecido
SELECT * from sys.traces;
GO
–Apague el informe de procesos bloqueados cuando no lo esté utilizando.
–Asegúrese de que no tiene ningún cambio pendiente
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‘blocked process threshold (s)’, 0;
GO
RECONFIGURAR
GO
exec sp_configure ‘umbral de procesos bloqueados (s)’;
GO
|
