Monitorització i tuning de concurrència a SQL Server

Quan es parla de concurrència a SQL Server, no es tracta només de quantes connexions hi ha obertes o quants nuclis estan disponibles. El que marca la diferència en producció és com es comporten les sessions actives en accedir simultàniament als mateixos objectes, com interactuen les transaccions entre si i quin efecte tenen certes decisions tècniques que es prenen gairebé per inèrcia. El focus està en el que passa quan múltiples processos volen llegir o escriure el mateix, i ho fan sota pressió.

Un entorn OLTP típic comença a mostrar símptomes de fricció per concurrència molt abans de saturar CPU, memòria o disc. El que es degrada primer és el temps de resposta d’operacions normals, sense que els comptadors de recursos mostrin res anòmal. És on la visibilitat fina i la presa de decisions específiques importen.

1. Què mirar primer quan hi ha bloquejos

Els sistemes SQL Server sota càrrega concurrent rara vegada es bloquegen per complet, però sí que acumulen esperes que afecten els temps de resposta. L’ error comú és buscar la causa en estadístiques d’ espera afegides o en diagnòstics genèrics.

La clau està en correlacionar en temps real:

• Quines sessions estan esperant.
• Quin recurs estan esperant (fila, pàgina, taula).
• Qui el té bloquejat i què està executant.
• Quant de temps porten esperant.

Això es pot obtenir directament combinant sys.dm_exec_requests, sys.dm_os_waiting_tasks, sys.dm_tran_locks i sys.dm_exec_sql_text.

SELECT
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS sql_text,
    w.resource_description
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type LIKE 'LCK%'

Amb això es poden detectar bloquejos en temps real i, sobretot, veure si les sessions bloquejades estan llegint o escrivint, si la sessió que bloqueja està activa o penjada, i si el recurs afectat és el mateix en diversos processos.

Evitar bloquejos duradors depèn menys del maquinari i més del disseny d’ accés a dades. Les sessions actives que retenen locks durant segons o minuts, fins i tot sense consumir CPU, són més danyoses que les que consumeixen recursos però alliberen ràpid.

2. Nivells d’aïllament i visibilitat: quan canviar i quan no

SQL Server treballa per defecte en READ COMMITTED, que manté bloquejos compartits mentre es llegeix. Això evita lectures brutes, però genera bloquejos innecessaris en entorns amb transaccions llargues o escriptures freqüents.

Una opció freqüent és canviar a READ COMMITTED SNAPSHOT, que reemplaça els locks de lectura per versions mantingudes en tempdb. Les sessions llegeixen una còpia consistent sense interferir amb les escriptures actives. L’impacte en rendiment és positiu en la majoria d’entorns OLTP, però hi ha matisos:

• Les escriptures continuen generant locks. El canvi afecta només com es comporten les lectures.
• El volum de dades a tempdb augmenta, especialment si hi ha moltes transaccions simultànies llegint les mateixes dades.
• Consultes que depenen de veure dades escrites en la mateixa transacció poden no comportar-se igual, encara que estiguin dins del mateix scope.

Activar aquesta opció requereix tenir clar quines transaccions podrien veure’s afectades i com es gestiona tempdb. En sistemes amb procediments que barregen lectura prèvia a l’update (per exemple, SELECT … FOR UPDATE simulat) pot ser necessari revisar la lògica.

No té sentit aplicar aquest canvi només per defecte o perquè d’altres ho facin. Hi ha entorns on les lectures són ràpides i les escriptures es fan sobre particions aïllades. Aquí, el model tradicional funciona bé. Però en bases amb accés mixt, canvis freqüents i sessions simultànies, RCSI sovint evita bona part dels bloquejos entre usuaris.

3. Patró comú: bloquejos invisibles que s’acumulen en transaccions llargues

SQL Server manté els locks fins que es confirma o cancel·la la transacció. No importa si l’operació ha acabat, si la sessió queda oberta sense fer res, el lock segueix aquí. I si el recurs és compartit, bloqueja altres processos.

Un patró especialment problemàtic:

• Aplicació obre connexió.
• Executa múltiples operacions dins d’ una mateixa transacció, sense SET IMPLICIT_TRANSACTIONS OFF.
• La transacció queda oberta en espera d’algun esdeveniment d’aplicació (resposta de l’usuari, confirmació externa).
• Mentrestant, les files tocades queden bloquejades.

Això és fàcil de detectar amb una consulta a sys.dm_tran_session_transactions, creuant amb sys.dm_exec_requests. Quan hi ha sessions sense activitat amb transacció activa, i bloquejos de tipus exclusiu (LCK_M_X), és un indici clar.

La solució no és tècnica, sinó funcional: assegurar-se que cada transacció és curta i predictible. Les operacions que requereixen interacció de l’ usuari o processos externs han de fer servir patrons asíncrons o emmagatzemar estats intermedis, no mantenir locks oberts esperant.

4. L’ordre d’accés importa

En sistemes amb moltes taules relacionades, el que genera bloquejos no sol ser el volum de dades sinó l’ordre d’accés. Si dos processos diferents accedeixen a les mateixes taules en diferent ordre i fan operacions d’ escriptura, el deadlock és una possibilitat real.

SQL Server selecciona de forma automàtica quin matar, però l’impacte segueix aquí: pèrdua de treball parcial, reintents, major càrrega. És fàcil de reproduir en entorns on:

• Hi ha diverses operacions en paral·lel que actualitzen taules relacionades.
• No hi ha control explícit de l’ ordre d’ accés.
• Es fan servir cursors o loops sobre dades sense aïllar correctament els lots.

La manera d’evitar-ho no és refer tota la lògica, sinó establir una convenció clara de l’ordre d’accés quan es treballa amb múltiples objectes. Si una operació sempre accedeix primer a Clients, després a Comandes, i després a Detalls, aquesta convenció ha de ser idèntica a tots els processos que actualitzen aquestes taules.

5. Índex i bloquejos

Un índex mal definit no només degrada les lectures, també pot interferir en l’ escriptura, especialment si imposa una restricció d’ unicitat o si concentra massa escriptures en poques pàgines. Un cas habitual: índexs per data o número seqüencial on tots els inserts cauen a la mateixa pàgina.

Això genera contenció en PAGELATCH_EX, que no és un bloqueig de tipus LCK_, però afecta igual el throughput. Les escriptures han d’ esperar torn per modificar la mateixa pàgina, encara que les dades no se solapin.

Es pot comprovar observant els tipus d’ espera en sys.dm_os_wait_stats o, en temps real, veient les sessions amb espera de tipus PAGELATCH_* en tempdb o en taules d’ alt trànsit.

En aquests casos, les estratègies útils inclouen:

• Fer servir una partició horitzontal real (si la versió i l’edició ho permeten).
• Introduir aleatorització controlada a les claus, per dispersar els inserts.
• Redefinir l’ índex amb una altra columna d’ ordre, si la consulta no depèn de l’ ordre estricte actual.

No cal eliminar l’índex. El punt està en revisar el seu disseny davant el patró real d’ accés.

6. Actualització d’estadístiques i operacions de manteniment que bloquegen sense avisar

Els jobs de manteniment programats solen ser font d’interferències silencioses. Rebuilds d’ índexs, actualitzacions d’ estadístiques o neteja de registres antics generen escriptures massives que s’ executen amb lock de taula o bloquejos prolongats en pàgines específiques.

En entorns actius, aquestes operacions competeixen amb els processos normals, de vegades bloquejant-los per complet. Les consultes afectades apareixen com esperant LCK_M_S o LCK_M_U, i poden passar desapercebudes si no es monitoritza amb freqüència.

Per a evitar-ho, cal planificar el manteniment en franges horàries fora de càrrega, dividir els processos en lots petits, o aplicar opcions com ONLINE = ON quan estigui disponible.

També convé revisar les actualitzacions automàtiques d’ estadístiques. En taules grans, si el llindar de canvi s’ assoleix en horari crític, l’ actualització es dispara en el pitjor moment, amb recompilacions i bloqueig inclòs. Canviar ASYNC_STATS_UPDATE redueix aquest risc.

7. Consultes paral·leles que afecten altres processos

Les esperes CXPACKET o CXCONSUMER indiquen que hi ha paral·lelisme actiu. No són bloquejos en si, però mostren que hi ha sessions que estan repartint càrrega entre nuclis. Això és útil per a certes consultes, però en sistemes amb càrrega OLTP densa, pot acabar robant recursos a processos més crítics.

SQL Server permet ajustar MAXDOP per base de dades o per consulta. La recomanació general de posar-lo a 1 a OLTP pur no sempre aplica, però sí que és raonable establir un valor baix (2, 4) i controlar que les consultes més freqüents no estiguin paral·lelitzant per defecte.

El cost threshold for parallelism, que segueix en 5 en moltes instal·lacions, s’hauria d’ajustar a valors més alts (20 o més) perquè només paralitzin les consultes que realment ho necessitin.

8. Plans d’execució que es comporten diferent sota càrrega

Hi ha plans que funcionen bé en proves però es degraden quan el sistema està ocupat. Això passa especialment amb plans que depenen d’estimacions de cardinalitat imprecises, i que sota càrrega generen accessos massius no previstos.

En condicions normals, una taula amb 10 milions de registres pot ser recorreguda eficientment amb un index seek. Però si les estadístiques estan desactualitzades, o si la consulta fa servir paràmetres variables, l’optimitzador pot generar un scan complet. Si la taula està sent accedida per altres sessions en paral·lel, l ‘scan bloqueja pàgines compartides durant més temps i retarda les escriptures.

El pla d’execució, en aquest cas, no mostra un error evident, però contribueix a retenció de locks per més temps del necessari. L’ efecte acumulatiu es nota quan:

• Les consultes comencen a tardar diversos segons sense canvis visibles a CPU ni IO.
• Augmenten les sessions amb espera LCK_M_S o LCK_M_U durant lectures.
• Disminueix el throughput sense saturació de recursos.

Controlar aquests casos implica revisar regularment els plans en caché (sys.dm_exec_query_stats, sys.dm_exec_cached_plans) i buscar patrons com:

• Scans innecessaris sobre taules grans.
• Lookups en índexs que podrien evitar-se amb un INCLUDE.
• Sensibilitat excessiva a l’ ordre dels joins o a la forma en què es passen els paràmetres.

Si la mateixa consulta mostra plans diferents en diferents execucions, pot ser símptoma de parameter sniffing, que en concurrència produeix efectes més greus que en escenaris simples. Fer servir OPTION (RECOMPILE) en alguns procediments crítics és un mal menor si la variació del pla és alta.

9. Monitorització contínua i senyals d’advertència reals

La monitorització de concurrència no es basa només a mirar wait stats. Hi ha senyals més específics que convé tenir presents:

• Comptatge de sessions bloquejades per minut. Llindar raonable: més de 5 sessions simultàniament bloquejades en hores pic és un senyal de fricció.
• Nombre de deadlocks diaris. Per sobre de 2-3 per hora implica revisar patrons d’ accés.
• Temps mitjà de retenció de locks. No sempre és fàcil de mesurar, però Extended Events pot capturar l’inici i alliberament de locks amb prou detall.
• Transaccions actives sense comandaments executant-se (open_transaction_count > 0, sense activitat visible). Moltes vegades són connexions de l’aplicació que han perdut el control del cicle transaccional.

Configurar mètriques que s’ emmagatzemen cada minut en una taula específica, o integrar amb un sistema extern d’ observabilitat, és l’ únic que permet veure si els canvis tenen efecte o si el sistema es degrada amb el temps.

10. Decisions que degraden el sistema amb el temps

A molts entorns, el problema de concurrència no apareix el primer dia. S’ acumula amb decisions aparentment innòcues que, sota càrrega, esdevenen punts de contenció. Algunes de les més comunes:

• Procediments que accedeixen a massa taules en una sola transacció.
• Reutilització de connexions amb transaccions implícites actives sense commit.
• Jobs batch que actualitzen milions de files sense particionar ni pausar.
• Insercions concurrents sobre taules sense cap mecanisme de partició ni distribució.
• Ús de MERGE mal controlat, generant bloquejos en files innecessàries.

Moltes d’aquestes pràctiques funcionen en entorns de prova, amb pocs usuaris. En sistemes amb 200 o 300 connexions simultànies, es converteixen en colls d’ ampolla que el maquinari no compensa.

11. Accions preventives que eviten problemes posteriors

Hi ha mesures que, aplicades des del començament, eviten moltes intervencions posteriors:

• Establir convencions clares d’ ordre d’ accés a taules en procediments.
• Activar READ_COMMITTED_SNAPSHOT on el model d’ accés ho permeti.
• Separar processos de manteniment dels horaris de càrrega regular.
• Definir límits de temps per a cada tipus de transacció en l’ aplicació.
• Revisar els índexs no només per lectura, sinó pel seu impacte en escriptures concurrents.

També és útil revisar periòdicament els procediments més utilitzats. No només per veure si continuen funcionant ràpid, sinó per veure si han començat a generar més locks o esperes que abans. SQL Server no llença alertes per això, cal anar a buscar-lo.

12. Documentar els símptomes abans que les solucions

En ambients on intervenen diversos equips (infraestructura, desenvolupament, suport), el més útil no és un llistat de solucions genèriques, sinó una documentació clara de símptomes concrets:

• Quin tipus d’esperes es apareixen.
• Quines sessions estan implicades.
• Quines taules o índexs són el focus del bloqueig.
• Quin patró d’ accés o transacció causa la retenció.
• Com es resol el problema (reordenant accessos, canviant aïllament, dividint operacions).

Documentar això amb precisió no serveix només per a evitar que es repeteixi. Serveix per reconèixer quan el mateix patró torna a aparèixer mesos després, sota un altre nom.

Conclusió

SQL Server dona eines suficients per treballar bé sota concurrència, però cal conèixer-les des del costat del comportament real, no des dels valors per defecte o les bones intencions. Cada sistema té un punt d’equilibri diferent, i el que importa és trobar-lo sense esperar que el sistema comenci a fallar.

La majoria dels problemes de concurrència no venen de pics puntuals, sinó del disseny diari que no considera com es comporten 100 sessions simultànies escrivint sobre la mateixa taula. Identificar els patrons abans que escalin, i tenir capacitat d’ observació a nivell de sessió, transacció i lock, és el que permet que el sistema aguanti la càrrega sense necessitat d’ escalar maquinari cada sis mesos.

Hi ha marge de maniobra gairebé sempre. El difícil és veure-ho a temps.