Transcript
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Filegroups Índices y rendimiento (Performance) en el SQL Server
un archivo o filegroup puede ser usado solo por una base de datos Un archivo puede ser miembro de un solo filegroup. El log no es parte de ningún filegroup y no puede ser parte de ningún archivo. Un archivo es una unidad de paralelismo y de recuperación. Los filegroups permiten asignar objetos a archivos especificos.
Ing. J os e M ariano A lvarez j o s e . m a r i a n o . a l va r e z @g m a i l . c o m ww w . s q l t o t a l c o n s u l t i n g . c o m h t t p : / / B l o g . j o s e m a r i a n o a l va r e z . c o m
2
Filegroups (2)
Filegroups (3)
Las tablas solo se pueden asignar a un filegroup. Se puede crear varios archivos para que use en paralelo los discos. Crea un thread por cada archivo. Si el RAID es por hardware puede que no mejore
Permite realizar stripping Usar un archivo por disco físico si Disk Queue length <= 3 Verificar que se produce una mejora si se usa Separar los índices no clustered de las tablas en conjuntos de discos separados Combinar Hardware con filegroups separando carga
3
4
Páginas Indices
6
1
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Extents
Árboles B y Heap
7
8
¿ Qué es un índice ?
Índices clustered
Solo puede haber uno por tabla porque define el orden físico Al crearlo se requiere el doble de espacio de la tabla porque se copian Recomendado para Grandes resultados con Scan por rango Obtención de datos ordenados
No recomendado Columnas que se actualizan habitualmente Claves anchas
9
Clustered
10
Seek - Cluster
11
12
2
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Range - Cluster
Índices non Clustered Puede haber varios por tabla (hasta 249) Son estructuras secundarias a la tabla Accesos puntuales Requieren alta selectividad Requiere Bookmark Lookup porque depende del clustered index Son muy útiles con resultados de pocas filas de tablas grandes
13
Nonclustered
14
Traversing Bookmark look-up
15
Seek – Non Cluster
16
Range – Non Cluster
17
18
3
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Características de los índices
Definición de índices
Unicidad
Ayuda a las estrategias de acceso
Indica cuan llenas deben estar las páginas de un índice
Selectividad
0: 100% de todas las páginas (Read Only) 100: 100% de las páginas hoja
Es el porcentaje de la tabla accedida Selectividad = filas accedidas / Total
Fillfactor
Selectividad de un join
Padd index Indica cuan llenas deben estar las páginas intermedias Se usa junto a fillfactor
Es el número de filas de una tabla inner de la relación con respecto a una tabla outer definida 19
Fill Factor
20
Análisis de las estadísticas de una tabla Muestra la información estadística de una tabla que usa el SQL Server para seleccionar el mejor plan Densidades
Define la selectividad de un índice Density All density
Histograma
21
22
Planes de ejecución
SET SHOWPLAN_TEXT
SET SHOWPLAN_ALL
Operadores
Muestra la información detallada del plan aproximado (no ejecuta la instrucción)
Describe la operación algebraica relacional usada para procesar una sentencia
Muestra la información detallada del plan aproximado y de los recursos utilizados (no ejecuta la instrucción)
Operadores lógicos
Análisis gráfico desde el Query Analyzer
Operadores físicos Describe la implementación física del algoritmo usado para procesar una sentencia
Plan estimado (CTRL+L) Plan ejecutado (CTRL+K)
23
24
4
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Estadísticas de ejecución
Sugerencias
Statistics time ON
Crear primero el Clustered y luego los non clustered Borrarlos en el orden inverso Crear la menor cantidad de índices Hacer los índices tan pequeños como se pueda, especialmente en los clustered Crear índices non clustered que sean altamente selectivos, UNIQUE de ser posible
Muestra información de tiempo en ms relativa al parsing, compilación y ejecución de cada sentencia Indica que el proceso actualice la columna CPU de la tabla sysprecesses No funcione en el modo FIBER (lightweight pooling ON)
Statistics IO ON Muestra información relacionada con el acceso a disco
Table scan count logical reads physical reads read-ahead reads
25
26
Cover index
Index intersection
Cover index
Permite el Uso de más de un índice por tabla y por query Obtienen un subconjunto que es la intersección de múltiples índices
Todos los datos requeridos están en el índice No accede a las paginas de datos (sin Bookmark Lookup) Solo para índices Non Clustered Actúa como un índice clustered (efectividad y ordenamiento) Es costoso mantenerlos
27
28
Index Join
SARGS (search argument)
Utiliza solo índices para satisfacer la consulta mediante técnicas de join Usa técnicas de
Limitan las búsquedas mediante un valor exacto o rango No puede haber dos columnas en la condición0/ columna (Operador)
Index intersection Covering index
29
Operadores =, >, <, =>, <=, BETWEEN, y LIKE (con restricciones) Un SARG puede tener varias condiciones con AND si pueden ser resueltas por un índice Un OR puede dar lugar a dos SARG
30
5
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Otras consideraciones
Heurísticas usadas cuando no hay estadísticas disponibles
Tipos de join Hash: Es efectivo si
Ambos conjuntos difieren de tamaño pero no tienen ordenamiento por las columnas del join En resultados intermedios no indexados
Merge: Es efectivo si
= 10% > 30% < 30% BETWEEN 10%
Los conjuntos están ordenados por la columna del join Uno de los conjuntos no esta ordenado pero ambos son del mismo tamaño (Se realiza un SORT)
Nested loops: Es efectivo
Cuando uno de los conjuntos del join es muy chico (< 10 reg)
Constraints Pueden provocan accesos adicionales para su verificación 31
32
Conceptos de índices
Estadísticas
Las tablas sin clustered index tienen una fila en sysindexes con indid = 0 Las tablas con clustered index tienen una fila en sysindexes con indid = 1 Las tablas con nonclustered indexes tienen una fila en sysindexes con indid >= 2 Una tabla nunca tendrá indid = 0 e indid = 1 a la vez en sysindexes !
Las estadísticas también se almacenan como nonclustered indexes en sysindexes con indid >= 2 !! Se almacenan en la columna StatsBlob Cuantas más estadísticas haya en una tabla, menos cantidad de índices se podrán crear !!
33
Resumiendo
34
Interacción de las Transacciones
Valor en indid Sin clustered
0
Con clustered
1
Non-clustered y estadísticas
2 - 250
Columna text / ntext / image
255
SQL Server Cliente #3
Cliente #1 35
Cliente #2 36
6
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Niveles de Aislamiento
Posibles problemas Dirty Read
NonRepeatable Read
Phantoms
Conflicto en Update
Read Uncommitted
Si
Si
Si
No
Read Committed
No
Si
Si
No
REPEATABLE READ
Repeatable Read
No
No
Si
No
SERIALIZABLE
Serializable
No
No
No
No
READ UNCOMMITTED READ COMMITTED (default)
37
38
Cambio del nivel de aislamiento
Cambio del nivel de aislamiento
Permanece en vigencia hasta que la conexión se cierre o se lo cambie nuevamente
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED SELECT … UPDATE … INSERT … … SET TRANSACTION ISOLATION LEVEL READ COMMITED
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
SELECT … UPDATE … INSERT …
…
39
40
READ UNCOMMITTED
READ COMMITTED
Especifica que las instrucciones pueden leer filas que han sido modificadas por otras transacciones pero todavía no se han confirmado.
41
Especifica que las instrucciones no pueden leer datos que hayan sido modificados, pero no confirmados, por otras transacciones. Esto evita las lecturas no actualizadas. Otras transacciones pueden cambiar datos entre cada una de las instrucciones de la transacción actual, dando como resultado lecturas no repetibles o datos ficticios. Esta opción es la predeterminada para SQL Server. 42
7
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
REPEATABLE READ
SERIALIZABLE
Especifica que las instrucciones no pueden leer datos que han sido modificados pero aún no confirmados por otras transacciones y que ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que ésta finalice.
Las instrucciones no pueden leer datos que hayan sido modificados, pero aún no confirmados, por otras transacciones. Ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que la transacción actual finalice. Otras transacciones no pueden insertar filas nuevas con valores de clave que pudieran estar incluidos en el intervalo de claves leído por las instrucciones de la transacción actual hasta que ésta finalice.
43
44
E quivalencia nivel de aislamiento Locking Hints
SNAPSHOT (2005)
Especifica que los datos leídos por cualquier instrucción de una transacción vean la versión coherente, desde el punto de vista transaccional, de los datos existentes al comienzo de la transacción. La transacción únicamente puede reconocer las modificaciones de datos confirmadas antes del comienzo de la misma. Las instrucciones que se ejecuten en la transacción actual no verán las modificaciones de datos efectuadas por otras transacciones después del inicio de la transacción actual. El efecto es el mismo que se obtendría si las instrucciones de una transacción obtuviesen una instantánea de los datos confirmados tal como se encontraban al comienzo de la transacción.
R E A D U N C O M M I TTE D
R E A D C O M M I TTE D
R E P E A TA B L E R E A D
S E R I A L I ZA B L E
N O L O C K o R E A D U N C O M M I TTE D
R E A D C O M M I TTE D
R E P E A TA B L A R E A D
HOLDLOCK
SELECT * FROM MI_TABLA WITH (NOLOCK) 45
46
Modos de las transacciones
Características Tipo de lockeo adquirido (Lock mode) Unidad de datos lockeada (Lock resource) Duración del lockeo (Lock duration)
SET IMPLICIT_TRANSACTIONS { ON / OFF }
Implícito Modo
Transacción Implícita Estos datos se mantienen en la
Explícito (default)
pseudo-tabla SysLockInfo Transacción Explícita
47
48
8
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Tipos de locks adquiridos (Lock mode)
U nidad de datos lockeada (Lock resource)
S hare – ‘S ’ E xclus ive – ‘X’ U pdate – ‘U ’ Intent – ‘IS ’ / ‘IX’ / ‘IU ’
Row Page Extent
Ta b l e Index key Range index key
Los Extents se lockean en operaciones de grow de tablas o índices. Tienen lugar dependiendo de la existencia o no de un clustered index.
49
50
Duración de los locks Lock Mode Share
Update
Exclusive
Read Committed
Repeatable Read
sp_lock
Serializable
Hasta que los datos se leen y procesan
Hasta el final de la transacción
Hasta el final de la transacción
Hasta que los datos se leen y procesan, a menos que sea escalada a Exclusive y duran hasta el final de la transacción
Hasta el final de la transacción
Hasta el final de la transacción A menos que sea escalada a Exclusive
Hasta el final de la transacción
Hasta el final de la transacción
Hasta el final de la transacción
EXEC sp_lock [ [ @spid_1] , [@spid_2] ] EXEC sp_lock @@spid
51
52
Otros tipos de locks B ulk U pdate y Schema
Compatibilidad Modo actualmente existente Modo solicitado
IS
S
U
IX
SIX
X
Intent Share (IS)
SI
SI
SI
SI
SI
NO
Shared (S)
SI
SI
SI
NO
NO
NO
Update (U)
SI
SI
NO
NO
NO
NO
Intent Exclusive (IX)
SI
NO
NO
SI
NO
NO
Shared con Intent Exclusive (SIX)
SI
NO
NO
NO
NO
NO
Exclusive
NO
NO
NO
NO
NO
NO
53
Bulk Update Lock
Stability (Sch-S) Schema Modification (Sch-M)
54
9
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Range locks
Escalamiento automático de locks
Solamente aplica en el SERIALIZABLE ISOLATION LEVEL Afecta un rango de claves sin incluir el inicio. Ejemplo en T-SQL sería:NOMBRE >’JORGE’ AND NOMBRE <= ‘JUAN’
SQL Server escalará los locks que sean posibles cuando el uso de los mismos supere el 40% de la memoria destinada a ellos Para modificar esto utilizar sp_configure ‘locks’, valor Optimizer Hints
RowLock PageLock TabLock TabLockX
55
56
Latches
Controlando los locks
Mantienen la consistencia de los datos mientras éstos son leídos Raramente se ven errores 844 y 845 Ver el artículo 310834 en http://support.microsoft.com
Niveles de aislamiento / ISOLATION LEVEL SET LOCK_TIMEOUT (nuevo en SQL Server 7.0) Error 1222 es time-out de lock pero no hace ROLLBACK ! Setear SET XACT_ABORT ON READPAST Locking Hint (solo para SELECT)
57
Deadlocks
58
Error con deadlocksServer: Msg 1205, … Transaction (Process ID xxx) was deadlocked with another process and has been chosen as deadlock victim. Rerun the transaction.
Databases SPID 1
SET DEADLOCK_PRIORITY { LOW / NORMAL / @variable_char } SQL Server SPID 2 59
60
10
Índices y rendimiento (Performance) en el SQL Server
03/11/2009
Controlando los deadlocks
Trace flags Análisis de Performance
DBCC TRACEON ( #traza [ ,...n ] ) Trace Flags – Otra forma de usarlos … DBCC TRACESTATUS ( #traza [ ,...n ] ) DBCC TRACEOFF ( #traza [ ,...n ] )
Utilizar el trace-flag 1204 para registrar en el Log de errores de SQL Server todos los deadlocks.
61
62
11