SQL Server ama i blocchi.
SQL Server usa il blocco “pessimistico” per default- le vostre query sono naturalmente difensive. Questo può portare a problemi di blocco che vi impediscono di scalare SQL Server.
Corso di formazione video gratuito – prima di risolvere problemi di blocco e deadlock, iniziate con How to Think Like the Engine. È una lezione di 90 minuti che spiega come funzionano gli interni degli indici clustered e non clustered, e che è vitale per capire come l’indicizzazione influisce sul blocco. È gratuito – perché crediamo che ogni professionista di SQL Server dovrebbe iniziare la sua formazione qui. È davvero così importante – roba buona.
Strumenti per diagnosticare il Lock Contention
“Ho delle attese di blocco in questo momento? Usa sp_BlitzFirst® per diagnosticare se le attese di blocco hanno attualmente un impatto sul tuo SQL Server.
“Quali tabelle hanno i peggiori problemi di blocco? Esegui sp_BlitzIndex® e cerca “Aggressive Index Disorder” per identificare quali tabelle hanno avuto elevate quantità di attese di blocco (per tabelle e indici attualmente in memoria).
“Come faccio a dare un senso al rapporto di processo bloccato? Quando si acquisisce il rapporto sul processo bloccato di SQL Server (script qui sotto), il Visualizzatore di rapporti sul processo bloccato di Michael J Swart è uno strumento gratuito che aiuta a interpretare i risultati.
“E se la mia applicazione non cattura gli errori di deadlock?” Amit Banerjee del team CSS di Microsoft ti aiuta a interrogare gli eventi estesi di SQL Server nella sessione System Health per i dettagli (SQL Server 2008+).
Contatori di prestazioni relative al blocco
Questi contatori di prestazioni possono essere molto utili per configurare un allarme dell’agente SQL Server basato sul contatore di perf in modo che SQL Server possa notificarti quando il blocco supera le soglie che hai impostato.
- Sono elencati prima OBJECT, poi COUNTER
- QL Server: Statistiche generali – Processi bloccati
- SQL Server: Chiusure – Tempo di attesa del blocco (ms)
- SQL Server: Locks – Number of Deadlocks/sec
Scopri altri contatori perf in SQL Server qui.
Leggi gli articoli sul locking, i livelli di isolamento e i deadlock
Leggi un’introduzione al locking a due fasi e ai deadlock da Jeremiah.
Impara da Kendra come puoi testare le modifiche al codice per scoprire quali blocchi sono necessari.
Jeremiah ti insegna come trovare e prevenire i deadlock.
Impara come implementare il locking ottimistico in SQL Server da Kendra.
Leggi di più sui livelli di isolamento in SQL Server – controlla la nostra pagina di link e riferimenti completi.
Script per usare il Blocked Process Report
Il “Blocked Process Report” esiste da molto tempo in SQL Server ed è ancora uno strumento utile. Per default in SQL Server, il rilevatore di deadlock si sveglia ogni 5 secondi per controllare se le vostre query sono in un “abbraccio mortale”. È possibile attivare un’opzione che chiede a SQL Server di utilizzare questa risorsa per controllare il blocco a lungo termine, ed emettere un rapporto.
AVVERTENZA: Si dovrebbe configurare il rapporto sul processo bloccato solo per valori di cinque secondi o superiori. Microsoft avverte che se si imposta questo da 1-4, si potrebbe causare l’esecuzione continua del rilevatore di deadlock e uccidere le prestazioni.
Per utilizzare il rapporto sul processo bloccato, è necessario configurare una traccia che cerchi l’evento “Rapporto sul processo bloccato” sotto “Errori e avvisi”. Raccomandiamo di usare una traccia lato server e naturalmente di eseguirla solo quando ne hai bisogno.
Vuoi vedere una demo di questo? Guardatene una qui.
Gli script in questa sezione sono esempi di modi comuni di eseguire questi compiti – dovete testare e rivedere di conseguenza il vostro ambiente, ed eseguirli a vostro rischio e pericolo.
Passo 1: Attivare il rapporto di processo bloccato. Questo cercherà qualsiasi blocco che richiede 20 secondi o più.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–Assicurati di non avere modifiche in sospeso
SELECT *
FROM sys.configurazioni
WHERE value <> value_in_use;
GO
exec sp_configure ‘show advanced options’, 1;
GO
RECONFIGURA
GO
exec sp_configure ‘blocked process threshold (s)’, 20;
GO
RECONFIGURA
GO
|
Step 2: Impostare una traccia per catturare il rapporto del processo bloccato. Eseguilo come traccia lato server.
- Dovrebbe contenere un evento: Errori e avvertimenti -> Rapporto processo bloccato.
- Ha bisogno solo di due colonne: Text, spid
Questo esempio di traccia lato server verrà eseguito per cinque minuti e si fermerà automaticamente. Devi cambiare il percorso in una directory valida in cui SQL Server abbia il permesso di scrivere.
|
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
|
— Creato da: SQL Server 2012 Profiler
— Creare una coda
dichiarare @rc int
dichiarare @TraceID int
dichiarare @maxfilesize bigint
dichiarare @DateTime datetime
———Aggiunto una funzione qui:
set @DateTime = DATEADD(mi,5,getdate()); /* Esegui per cinque minuti */
set @maxfilesize = 5
— Sostituisci il testo InsertFileNameHere, con un appropriato
— nome del file preceduto da un percorso, es.g., c:\MyFolder\MyTrace. L’estensione .trc
— sarà aggiunta automaticamente al nome del file. Se stai scrivendo da
— server remoto all’unità locale, usa il percorso UNC e assicurati che il server abbia
— accesso in scrittura alla tua condivisione di rete
———–Imposta il mio nome file qui:
exec @rc = sp_trace_create @TraceID output, 0, N’S:Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— File e tabella lato client non possono essere scriptati
— Imposta gli eventi
dichiara @on bit
imposta @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Impostare i filtri
declare @intfilter int
declare @bigintfilter bigint
— Impostare lo stato della traccia su start
exec sp_trace_setstatus @TraceID, 1
— visualizzare l’id della traccia per riferimenti futuri
select TraceID=@TraceID
goto finish
errore:
select ErrorCode=@rc
finish:
go
|
Passo 3: guardare le tracce in esecuzione e ottenere l’id della traccia:
|
1
2
|
SELECT * from sys.tracce;
GO
|
Ferma una traccia, se necessario
|
1
2
3
4
5
|
–Inserire il traceid corretto dalla query precedente
EXEC sp_trace_setstatus @traceid =2, @status = 0; — ferma la traccia (supponendo che sia l’ID traccia 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — chiude la traccia (supponendo che sia l’ID traccia 2)
GO
|
Step 4: Leggere i dati del rapporto sul processo bloccato.
In questo esempio usiamo il visualizzatore gratuito di rapporti sui processi bloccati di Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Step 5: Pulisci!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Assicurati che la tua traccia sia sparita
SELECT * from sys.traces;
GO
–Spegnere il report dei processi bloccati quando non lo si usa.
–Assicurarsi di non avere modifiche in sospeso
SELECT *
FROM sys.configurazioni
WHERE value <> value_in_use;
GO
exec sp_configure ‘blocked process threshold (s)’, 0;
GO
RECONFIGURA
GO
exec sp_configure ‘soglia processo bloccato (s)’;
GO
|
