Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
21 de Octubre, 2011 · Indices-SQL-Server

Depura tus INDICES. SQL Server

Uno de los primeros elementos que debe aparecer en cualquier lista de comprobación para la optimización del rendimiento de SQL Server es el ajuste de los índices de una base de datos. La capacidad del optimizador de consultas de SQL Server para hacer un buen uso de los índices durante la ejecución de una consulta depende no sólo de la creación de índices eficaces sino también del estado de estos. Una serie de vistas y funciones de administración dinámicas (DMV y DMF, respectivamente), incluidas por primera vez en SQL Server™ 2005, puede ayudar a los administradores de bases de datos a determinar la eficacia de los índices y a descubrir cualquier problema de rendimiento.

 

Las DMV y DMF permiten mirar en el interior de un servidor y obtener información sobre su estado que le ayuda a supervisar el buen estado y rendimiento de la instancia de servidor y a diagnosticar problemas. Los administradores de bases de datos familiarizados con versiones anteriores de SQL Server descubrirán que estas DMV y DMF reemplazarán el uso de comandos DBCC, la ejecución de algunos procedimientos almacenados del sistema, la consulta de numerosas tablas del sistema y la captura de eventos con el Analizador de SQL Server.

Hay disponibles tres funciones y vistas clave (concretamente, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats y sys.dm_db_index_operational_stats) para ayudarle a comprobar si los índices funcionan tal y como esperaba. Estas funciones y vistas permiten revisar la E/S y los patrones de bloqueo de los índices, y averiguar si el optimizador de consultas utiliza los índices de un modo que no conduce a contenciones innecesarias en la base de datos.

 

Fragmentación de índices

La DMF sys.dm_db_index_physical_stats se ha creado para sustituir a DBCC SHOWCONTIG y mostrará una fragmentación del índice. Sin embargo, a diferencia de DBCC SHOWCONTIG, que coloca un bloqueo compartido (S) en la tabla que contiene el índice, sys.dm_db_index_physical_stats sólo coloca un bloqueo compartido intencionado (IS), que reduce notablemente el bloqueo de la tabla durante la ejecución de la función.

Para determinar la fragmentación de un índice cuando se usa sys.dm_db_index_physical_stats, examine una combinación de tres columnas en los resultados de la función. Para determinar la fragmentación lógica de índices (fragmentación de extensión de montones), consulte el valor devuelto en la columna avg_fragmentation_in_percent. La fragmentación lógica es el porcentaje de páginas que no están ordenadas en el nivel de hoja de un índice, mientras que la fragmentación de extensiones es el porcentaje de extensiones desordenadas en el nivel de hoja de un índice. Las fragmentaciones lógicas y de extensiones pueden afectar al rendimiento de un índice al requerir E/S adicional y el desplazamiento del encabezado del disco, ya que este debe omitirse para leer las páginas en orden. Deberá esforzarse para que los dos tipos de fragmentación, lógica y de extensiones, se mantengan lo más cerca posible del valor cero.

La fragmentación interna de un índice es el porcentaje del llenado de página. Por supuesto, querrá que la página de índice esté tan llena como sea posible, pero también necesitará equilibrar el llenado con respecto al número de inserciones en las páginas de índice para que el número de páginas divididas sea lo menor posible.

Examine el argumento avg_page_space_used_in_percent de sys.dm_db_index_physical_stats para determinar el llenado de las páginas de índice. Para configurar correctamente la proximidad de este número con respecto al 100 por cien, ajuste el factor de llenado de un índice al tiempo que observa el número de divisiones de página que se producen. En algún momento, el número de divisiones de página empezará a aumentar notablemente, lo que indica que ha establecido el factor de llenado del índice con un valor demasiado alto. El ajuste de un factor de llenado de índice conlleva tiempo y la realización de pruebas, y no debe realizarse sin la planeación adecuada. Los índices que no tienen inserciones aleatorias en un índice pueden tener los factores de llenado establecidos con un valor cercano a 100 sin preocuparse por el aumento de las divisiones de página.

Para determinar los niveles de fragmentación de todos los índices de la tabla AdventureWorks.HumanResources.Employee, podría utilizar una instrucción como la siguiente:

 

SELECT *

FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')

,OBJECT_ID('HumanResources.Employee')

,NULL

-- NULL to view all indexes;

-- otherwise, input index number

,NULL -- NULL to view all partitions of an index

,'DETAILED') -- We want all information
 

Asegúrese de que esta DMF determine automáticamente los índices que hay que volver a generar, los que hay que volver a organizar y los que no necesitan mantenimiento. El examen de los valores de las columnas avg_page_space_used_in_percent y avg_fragmentation_in_percent de esta DMF para la fragmentación de índices que queda fuera del umbral lógico y de densidad aceptado puede ayudarle a determinar qué operación debe llevar a cabo en el índice.

En función del estado de los índices, es posible que no devuelva datos en la base de datos por ejemplo la de AdventureWorks, pero puede adaptarse fácilmente a otras bases de datos.

 

--Reorganize the following indexes in the AdventureWorks database

USE AdventureWorks

GO

 

SELECT OBJECT_NAME([object_id]) AS 'Table Name',

index_id AS 'Index ID'

FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')

,NULL -- NULL to view all tables

,NULL -- NULL to view all indexes; otherwise, input index number

,NULL -- NULL to view all partitions of an index

,'DETAILED') --We want all information

WHERE ((avg_fragmentation_in_percent > 10

AND avg_fragmentation_in_percent < 15) -- Logical fragmentation

OR (avg_page_space_used_in_percent < 75

AND avg_page_space_used_in_percent > 60)) --Page density

AND page_count > 8 -- We do not want indexes less than 1 extent in size

AND index_id NOT IN (0) --Only clustered and nonclustered indexes

 

--Rebuild the following indexes in the AdventureWorks database

USE AdventureWorks

GO

 

SELECT OBJECT_NAME([object_id]) AS 'Table Name',

index_id AS 'Index ID'

FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')

,NULL -- NULL to view all tables

,NULL -- NULL to view all indexes; otherwise, input index number

,NULL -- NULL to view all partitions of an index

,'DETAILED') --We want all information

WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation

OR (avg_page_space_used_in_percent < 60)) --Page density

AND page_count > 8 -- We do not want indexes less than 1 extent in size

AND index_id NOT IN (0) --Only clustered and nonclustered indexes
 

Debería resultar fácil almacenar los resultados de las consultas en una variable de tabla y, a continuación, recorrer la variable de tabla con el fin de generar una cadena dinámica para la instrucción ALTER INDEX correcta

 

--Rebuild the following indexes in the AdventureWorks database

USE AdventureWorks

GO

 

--Table to hold results

DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,

index_id INT)

 

INSERT INTO @tablevar (objectid, index_id)

SELECT [object_id],index_id

FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')

,NULL -- NULL to view all tables

,NULL -- NULL to view all indexes; otherwise, input index number

,NULL -- NULL to view all partitions of an index

,'DETAILED') --We want all information

WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation

OR (avg_page_space_used_in_percent < 60)) --Page density

AND page_count > 8 -- We do not want indexes less than 1 extent in size

AND index_id NOT IN (0) --Only clustered and nonclustered indexes

 

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'

+ OBJECT_NAME(objectid) + ' REBUILD'

FROM @tablevar tv

INNER JOIN sys.indexes ind

ON tv.objectid = ind.[object_id]

AND tv.index_id = ind.index_id

INNER JOIN sys.objects ob

ON tv.objectid = ob.[object_id]

INNER JOIN sys.schemas sc

ON sc.schema_id = ob.schema_id

 

 

Uso de los índices

Aunque sys.dm_db_index_physical_stats es un sustituto eficaz de DBCC SHOWCONTIG y puede ayudar a averiguar el estado de un índice, a menudo afrontará el problema más complejo de determinar qué índices son útiles para las consultas ejecutadas en una tabla. A menudo los desarrolladores o los administradores de bases de datos generan índices en una tabla que creen que el optimizador utilizará durante la ejecución de una consulta. En versiones anteriores de SQL Server, averiguar si estos índices se usaban realmente era mucho más difícil. Había que eliminar el índice y comprobar si el rendimiento de las consultas resultaba afectado, o capturar los planes de ejecución de las consultas y observar el uso del índice.

Hay una nueva vista de administración dinámica, sys.dm_db_index_usage_stats, que facilita la comprensión de cómo utiliza los índices el optimizador de consultas y de las consultas ejecutadas en una tabla. Esta vista puede examinarse para determinar la utilidad de un índice, lo que permite eliminar cualquier índice que el optimizador de consultas no use. Ya no es necesario preocuparse de si un índice desperdicia espacio de almacenamiento o de si el mantenimiento de los índices no usados reduce el rendimiento de la base de datos.

Mediante el examen de los resultados de esta DMV para los índices con búsquedas y exploraciones de cero, puede determinar si un índice se ha utilizado desde la última vez que se inició SQL Server. No obstante, recuerde que hay numerosas DMV y DMF que no se guardan y que se restablecerán solas a cero cuando se reinicie SQL Server. Tenga esto en cuenta cuando utilice una DMV o DMF para determinar el uso de un índice. El índice puede sencillamente no haber sido necesario desde la última vez que se reinició el servicio, pero quizás lo necesiten las consultas de informes de final de semana, final de mes y trimestrales.

Para ver todos los índices de una instancia que no se han utilizado desde la última vez que se reinició el servicio de SQL Server, puede utilizar la instrucción siguiente:

 

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])

FROM sys.dm_db_index_usage_stats

WHERE user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND system_seeks = 0

AND system_scans = 0

AND system_lookups = 0

 

Actividad funcional de los índices

Si quiere comprender la actividad funcional de sus índices, encontrará muy útil la DMF sys.dm_db_index_operational_stats. Puede utilizarla para ver la E/S, el bloqueo, el cierre y la actividad del método de acceso de cada índice de una base de datos, lo cual puede ayudarle a comprender cómo se utilizan los índices y a diagnosticar los problemas de bloqueo de índices debidos a una actividad exhaustiva de E/S o a la existencia de alguna "zona activa" en el índice.

Utilice las columnas de espera de cierre de esta DMF para establecer con mayor facilidad la cantidad de tiempo que tardan las operaciones de lectura y escritura en obtener acceso a los recursos de un índice. De esta forma, podrá determinar si el subsistema de disco utilizado para almacenar el índice es el adecuado para la actividad de E/S del índice. También puede indicar si el diseño y el uso del índice han introducido una zona activa en la que la actividad exhaustiva en una o más páginas de un índice resulta problemática para los datos contenidos en estas páginas. Esta contención conduce a menudo al bloqueo excesivo de las operaciones que intentan leer o escribir en esta área.

Determinar los patrones de bloqueo y de E/S de todos los índices de la tabla AdventureWorks.HumanResources.Employee.

 

SELECT page_latch_wait_count --page latch counts

,page_latch_wait_in_ms --page latch wait times

,row_lock_wait_in_ms --row lock wait times

,page_lock_wait_in_ms --page lock wait times

,row_lock_count --row lock counts

,page_lock_count --page lock counts

,page_io_latch_wait_count --I/O wait counts

,page_io_latch_wait_in_ms --I/O wait times

FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')

,OBJECT_ID('HumanResources.Employee')

,NULL -- NULL to view all indexes; otherwise, input index number

,NULL -- NULL to view all partitions of an index

)

 

Más información

Las DMV y DMF presentadas en este artículo tienen diversos usos además de los aquí descritos. Emplee el tiempo necesario en revisar los artículos de los libros en línea de SQL Server, en los que se describen estas funciones y vistas, que se indican en la barra lateral "Recursos adicionales", para entender la gran variedad de información que se puede devolver y examinar mediante su uso.

Para obtener más información sobre alguna DMF y DMV adicional de índice que no se haya descrito en este artículo, consulte el blog que publica el equipo de optimización de consultas de SQL Server en blogs.msdn.com/queryoptteam/570176.aspx (en inglés).


Apuntes y recopilaciones por  Norman M. Pardell 

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 05:33 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Como renombrar una instancia de SQL Server. sp_dropserver. sp_addserver
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad