tnt4-05. mejoras a la indexación lo que vamos a cubrir: mejoras al índice en sql server 2005...

Mejoras a la indexación

Lo que vamos a cubrir:

• Mejoras al índice en SQL Server 2005• Fragmentación del índice• Ajuste del índice

Conocimiento previo

Nivel 200Nivel 200

• Experiencia en administrar y dar mantenimiento en SQL Server 2000• Experiencia en administrar bases de datos• Familiaridad con Transact-SQL• Familiaridad con tablas e índices

Agenda

• Repaso• Mejoras a la indexación• Administrar fragmentación de índice• Ajuste del índice

RepasoDescripción general de tablas e índices particionados

100 200 300Valor de particionamiento:

Rango (IZQUIERDO):

Rango (DERECHO):

<=100<100

101-200100-199

201-300200-299

>300>=300

Función de partición

Esquema de partición

FG1 FG2 FG3 FG4

RepasoImplementar las particiones

CREATE PARTITION FUNCTION pf_EmployeeIDs (int)AS RANGE LEFTFOR VALUES (100, 200, 300)

CREATE PARTITION FUNCTION pf_EmployeeIDs (int)AS RANGE LEFTFOR VALUES (100, 200, 300)

CREATE PARTITION SCHEME ps_EmpSchemeAS PARTITION pf_EmployeeIDsTO (FG1, FG2, FG3, FG4, FG5)

CREATE PARTITION SCHEME ps_EmpSchemeAS PARTITION pf_EmployeeIDsTO (FG1, FG2, FG3, FG4, FG5)

CREATE TABLE EmployeeVacations(VacationEventID int, EmployeeID int, StartDate datetime)ON ps_EmpScheme(EmployeeID)

CREATE TABLE EmployeeVacations(VacationEventID int, EmployeeID int, StartDate datetime)ON ps_EmpScheme(EmployeeID)

RepasoImplementar las particiones

SWITCH MERGE SPLIT

• Utilice ALTER TABLE para SWITCH particiones• Utilice la ALTER PARTITION FUNCTION para MERGE y SPLIT

particiones

RepasoImplementar las particiones

Si una función de la partición utiliza los valores de límite 100, 200 y 300 y utiliza la creación de particiones RIGHT, ¿a qué partición corresponde el valor 100?

1. Partición 0.

2. Partición 1.

3. Partición 2.

4. Partición 3.

RepasoImplementar las particiones

Si una función de la partición utiliza los valores de límite 100, 200 y 300 y utiliza la creación de particiones RIGHT, ¿a qué partición corresponde el valor 100?

1. Partición 0.

2. Partición 1.

3. Partición 2.

4. Partición 3.

RepasoImplementar las particiones

¿Para qué usaría la operación SPLIT?

1. Eliminar una partición.

2. Crear una partición nueva.

3. Mover datos de una partición a una tabla.

4. Eliminar una partición de un grupo de archivos.

RepasoImplementar las particiones

¿Para qué usaría la operación SPLIT?

1. Eliminar una partición.

2. Crear una partición nueva.

3. Mover datos de una partición a una tabla.

4. Eliminar una partición de un grupo de archivos.

Agenda

• Repaso

• Mejoras a la indexación

• Administrar fragmentación de índice

• Ajuste del índice

Mejoras a la indexaciónFunciones nuevas en SQL Server 2005

• Instrucción ALTER INDEX• Operaciones de índice en línea• Operaciones de índice en paralelo• Opciones de bloqueo• Columnas incluidas

Mejoras a la indexaciónALTER INDEX

ALTER INDEX {index_name | ALL} ON [{database_name.[schema_name]. | schema_name.}] {table_or_view_name} { REBUILD [WITH(<rebuild_index_option>[,...n])] | REORGANIZE [ WITH( LOB_COMPACTION = {ON | OFF})] | DISABLE | SET (<set_index_option>[,...n]) }

ALTER INDEX {index_name | ALL} ON [{database_name.[schema_name]. | schema_name.}] {table_or_view_name} { REBUILD [WITH(<rebuild_index_option>[,...n])] | REORGANIZE [ WITH( LOB_COMPACTION = {ON | OFF})] | DISABLE | SET (<set_index_option>[,...n]) }

Mejoras a la indexaciónOperaciones de índice en línea

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREBUILD WITH (FILLFACTOR = 80, ONLINE = ON)

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREBUILD WITH (FILLFACTOR = 80, ONLINE = ON)

Fase de preparación

Fase final

Fase de inicio

Fuente Objetivo

SELECT

UPDATEINSERTDELETE

Mejoras a la indexaciónOperaciones de índice en paralelo

CREATE INDEX IX_Employee_ManagerIDON HumanResources.Employee(ManagerID)WITH (MAXDOP = 2)

CREATE INDEX IX_Employee_ManagerIDON HumanResources.Employee(ManagerID)WITH (MAXDOP = 2)

Mejoras a la indexaciónOpciones de bloqueo

CREATE INDEX IX_Employee_ManagerIDON HumanResources.Employee(ManagerID)WITH (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = OFF)

CREATE INDEX IX_Employee_ManagerIDON HumanResources.Employee(ManagerID)WITH (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = OFF)

Bloqueo de fila

Bloqueo de página

Bloqueo de tabla

Mejoras a la indexaciónColumnas incluidas

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeIDON HumanResources.Employee(EmployeeID)INCLUDE (LoginID, Title)

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeIDON HumanResources.Employee(EmployeeID)INCLUDE (LoginID, Title)

12

56

15

1: AW\Guy1, Técnico2: AW\Kevin0, Asistente demarketing

5: AW\Thierry0, Diseñador6: AW\David0, Gerente

Mejoras al índiceMejoras al índice

Crear un índice en líneaCrear un índice en línea Limitar el paralelismoLimitar el paralelismo Controlar la granularidad del bloqueoControlar la granularidad del bloqueo Incluir columnasIncluir columnas

demodemo

RepasoMejoras a la indexación

¿Cómo debe modificar un índice para cambiar su factor de llenado?1. Eliminar el índice y recrearlo con la configuración FILLFACTOR

deseada.

2. Utilizar una instrucción ALTER INDEX, especificando una cláusula establecida con la configuración FILLFACTOR deseada.

3. Utilizar una instrucción ALTER INDEX, especificando la cláusula de REBUILD con la configuración FILLFACTOR deseada.

RepasoMejoras a la indexación

¿Cómo debe modificar un índice para cambiar su factor de llenado?1. Eliminar el índice y recrearlo con la configuración FILLFACTOR

deseada.

2. Utilizar una instrucción ALTER INDEX, especificando una cláusula establecida con la configuración FILLFACTOR deseada.

3. Utilizar una instrucción ALTER INDEX, especificando la cláusula de REBUILD con la configuración FILLFACTOR deseada.

RepasoMejoras a la indexación

¿Cómo puede mantener índices mientras maximiza la disponibilidad?1. Realizar operaciones de índice utilizando la opción ONLINE

donde sea posible.

2. Crear una instantánea de la base de datos para que los usuarios puedan acceder a esta durante las ventanas de mantenimiento

3. Utilizar la instrucción ALTER INDEX en preferencia a comandos DBCC.

4. Utilice solamente índices que no estén en clúster

RepasoMejoras a la indexación

¿Cómo puede mantener índices mientras maximiza la disponibilidad?1. Realizar operaciones de índice utilizando la opción ONLINE

donde sea posible.

2. Crear una instantánea de la base de datos para que los usuarios puedan acceder a esta durante las ventanas de mantenimiento

3. Utilizar la instrucción ALTER INDEX en preferencia a comandos DBCC.

4. Utilice solamente índices que no estén en clúster

RepasoMejoras a la indexación

¿Cómo puede minimizar los costos administrativos de los recursos del sistema provocados por grandes números de bloqueos a nivel de fila?

1. Recrear el índice en clúster con la opción PADINDEX.

2. Alterar los índices para desactivar la opción ALLOW_ROW_LOCKS.

3. Alterar los índices para activar la opción IGNORE_DUP_KEY.

4. Alterar los índices para desactivar la opción ALLOW_PAGE_LOCKS.

RepasoMejoras a la indexación

¿Cómo puede minimizar los costos administrativos de los recursos del sistema provocados por grandes números de bloqueos a nivel de fila?

1. Recrear el índice en clúster con la opción PADINDEX.

2. Alterar los índices para desactivar la opción ALLOW_ROW_LOCKS.

3. Alterar los índices para activar la opción IGNORE_DUP_KEY.

4. Alterar los índices para desactivar la opción ALLOW_PAGE_LOCKS.

Agenda

• Repaso• Mejoras a la indexación• Administrar fragmentación de índice• Ajuste del índice

Administrar fragmentación de índiceVer la fragmentación del índice

• Propiedades del índice– Llenado de la página– Fragmentación– Opción para reorganizar

Administrar fragmentación de índiceVer la fragmentación del índice

• Función sys.dm_db_index_physical_stats

SELECT TableName, IndexName, AvgPageFullness, AvgFragmentationFROM sys.dm_db_index_physical_stats(DEFAULT, '*', DEFAULT, 'DETAILED')ORDER BY TableName, IndexName

TableName IndexName AvgPageFullness AvgFragmentation

Address AK_Address_rowguid 99.5027180627625 1.78571428571429

Address IX_Address_Address 99.4655300222387 1.4218009478673

Address IX_Address_State 98.701371386212 3.7037037037037

Address PK_Address_Address 99.0230911786509 0.359712230215827

AddressType AK_AddressType_Nm 1.87793427230047 0

AddressType AK_AddressType_row 1.68025698047937 0

AddressType PK_AddressType_Ad 4.10180380528787 0

Administrar fragmentación de índiceDesfragmentación de índices

• Fragmentación promedio <= 30%– ALTER INDEX … REORGANIZE

• Reemplaza DBCC INDEXDEFRAG

– Siempre se realiza en línea– Desfragmenta y compacta páginas de hojas– Incluye LOBs por predeterminación

• Fragmentación promedio > 30%– ALTER INDEX … REBUILD WITH (ONLINE = ON)

• Reemplaza DBCC DBREINDEX

– Elimina y vuelva a crear el índice

Administrar fragmentación de Administrar fragmentación de índiceíndice

Supervisar la fragmentación del índice Supervisar la fragmentación del índice Desfragmentación de índicesDesfragmentación de índices

demodemo

RepasoAdministrar fragmentación de índice

Un índice con una fragmentación promedio de 35% se debe reorganizar.

1. Verdadero.

2. Falso.

RepasoAdministrar fragmentación de índice

Un índice con una fragmentación promedio de 35% se debe reorganizar.

1. Verdadero.

2. Falso.

RepasoAdministrar fragmentación de índice

¿Cómo debe fragmentar y compactar las páginas de hojas en un índice?

1. ALTER INDEX … REBUILD.

2. ALTER INDEX … RESTORE.

3. ALTER INDEX … REORGANIZE.

4. DBCC DBREINDEX

RepasoAdministrar fragmentación de índice

¿Cómo debe fragmentar y compactar las páginas de hojas en un índice?

1. ALTER INDEX … REBUILD.

2. ALTER INDEX … RESTORE.

3. ALTER INDEX … REORGANIZE.

4. DBCC DBREINDEX

Agenda

• Repaso

• Mejoras a la indexación

• Administrar fragmentación de índice

• Ajuste del índice

Ajuste del índiceIntroducción del Database Tuning Advisor • Nueva herramienta de ajuste en SQL Server 2005

– Evolucionó del Index Tuning Wizard en SQL Server 2000

Ajuste del índiceAnalizar la base de datos

Paso Consideraciones

1. Crear una sesión de DTA

Nombre de la sesión

2. Especificar configuraciones de carga de trabajo

Fuente de la carga de trabajoBases de datos y tablas

3. Especificar opciones de ajuste Ajuste de tiempos

Estructuras físicas de diseño a usar

Particiones

Estructuras físicas de diseño a mantener

Opciones avanzadas

Ajuste del índiceVer los resultados de análisis

• Recomendaciones– Índices nuevos sugeridos– Índices que se deben eliminar– Particiones

• Informes– Estadísticas de la carga de trabajo– Informe de resumen

• Exportar como XML

Utilizar el Asesor de ajuste de la Utilizar el Asesor de ajuste de la base de datosbase de datos

Analizar una carga de trabajoAnalizar una carga de trabajo Aplicar recomendacionesAplicar recomendaciones

demodemo

¿Cuál de los siguientes NO se puede usar como una fuente de la carga de trabajo?

1. Un archivo que contenga instrucciones Transact-SQL.

2. Tabla de trace creada por SQL Profiler.

3. Log del Performance Monitor.

4. Archivo de trace del SQL Profiler.

RepasoAjuste del índice

¿Cuál de los siguientes NO se puede usar como una fuente de la carga de trabajo?

1. Un archivo que contenga instrucciones Transact-SQL.

2. Tabla de trace creada por SQL Profiler.

3. Log del Performance Monitor.

4. Archivo de trace del SQL Profiler.

RepasoAjuste del índice

RepasoAjuste del índice

¿Cómo se puede asegurar que se apliquen todas las recomendaciones DTA con interrupciones mínimas a los usuarios?

1. Configurar las opciones de ajuste para detenerse después de 30 minutos.

2. Configurar las opciones de ajuste avanzadas para que consideren sólo las recomendaciones de índice en línea.

3. Configurar las opciones de ajuste para mantener todas las estructuras de diseño físicas existentes.

4. Configurar las opciones de ajuste para eliminar todas las estructuras de diseño físicas existentes.

RepasoAjuste del índice

¿Cómo se puede asegurar que se apliquen todas las recomendaciones DTA con interrupciones mínimas a los usuarios?

1. Configurar las opciones de ajuste para detenerse después de 30 minutos.

2. Configurar las opciones de ajuste avanzadas para que consideren sólo las recomendaciones de índice en línea.

3. Configurar las opciones de ajuste para mantener todas las estructuras de diseño físicas existentes.

4. Configurar las opciones de ajuste para eliminar todas las estructuras de diseño físicas existentes.

Resumen de la sesión• Utilizar ALTER INDEX para administrar los índices• Administrar los índices en línea para minimizar los tiempos de paro• Limitar el grado de paralelismo para controlar la utilización del procesador• Configurar apropiadamente la granularidad de los bloqueos• Utilizar columnas incluidas para mejorar el rendimiento• Analizar el uso del índice con el Database Tuning Advisor

Pasos a seguir

1. Información del producto SQL Server 2005:http://www.microsoft.com/sql/2005/default.asp

2. Actualice sus habilidades de administración de base de datos a SQL Server 2005:

http://www.microsoft.com/learning/syllabi/ en-us/2733afinal.mspx

3. Difusión por el Web de SQL Server 2005 en MSDN:

http://msdn.microsoft.com/sql/2005/2005Webcasts/ default.aspx

www.microsoft.com/technet/tntx-xx

Para mayores informes…

• Visite TechNet en www.microsoft.com/technet

• Para obtener información adicional sobre los libros, cursos y otros recursos de la comunidad que respalden esta sesión visite