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.
