índices Y Rendimiento (performance)

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