Supervisar y mantener los índices de la base de datos de Planning Server
Actualizado: 2009-04-30
En este artículo:
Examine Measure Group table indexes
Checking index fragmentation
Reorganizing indexes
Rebuilding indexes
Al igual que la mayoría de los sistemas de base de datos que usan índices con el fin de mejorar el rendimiento del sistema, el sistema Planning Server usa índices para lograr un rendimiento óptimo. En los índices (sean agrupados o no), INSERT/UPDATE provocará la división de las páginas, lo cual a su vez dará pie a la fragmentación. Con el tiempo, la fragmentación de índice será cada vez más complicada y reducirá el rendimiento del sistema. Es importante que el administrador de la base de datos supervise el estado de la fragmentación de índice y sepa cómo actuar cuando los índices estén muy fragmentados.
Si bien todas las tablas de la base de datos de la aplicación de planeación se deben supervisar para comprobar el estado de la fragmentación de índice, es necesario prestar especial atención a las tablas de grupos de medida, cuyos nombres comienzan por el prefijo "MG_". En las bases de datos de la aplicación, las tablas de grupos de medida soportan operaciones de eliminación, actualización e inserción exhaustivas. Entre los tipos de operaciones que realizan estos cambios se incluyen el envío de asignaciones y la ejecución de reglas de cálculo. Algunas tablas de grupos de medida tendrán más actividad que otras. Los índices de las tablas "MG_*" estarán cada vez más fragmentados con el tiempo, de modo que el rendimiento del sistema disminuirá notablemente si no se regeneran y organizan con regularidad.
Examinar los índices de las tablas de grupos de medida
Al crear un modelo, Planning Server crea un índice agrupado en la nueva tabla “MG_*” que incluye todas las claves de dimensión de dicha tabla. Planning Server establece el orden de las claves de dimensión en este índice agrupado de manera totalmente arbitraria. Probablemente este orden no sea el mejor indicativo del uso de la tabla de grupos de medida por parte de un cliente individual (por ejemplo, tareas de reescritura, carga de datos y cálculo basado en reglas). En consecuencia, puede que el administrador de la base de datos necesite centrarse en el orden de las columnas del índice agrupado para reorganizar el orden.
Por ejemplo, el siguiente índice agrupado se ha creado en la tabla MG_Strategic_Plan_MeasureGroup_default_partition con el siguiente orden de columnas ya establecido. Se puede volver a organizar el orden de las columnas de este índice para que se ajuste al uso de la tabla de grupos de medida. Asimismo, puede que desee mover la columna Entity_MemberID a la primera columna del índice, dado que los usuarios del Complemento PerformancePoint para Excel realizan operaciones de reescritura en función de la entidad con bastante frecuencia.
CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition]
(
[Scenario_MemberId] ASC,
[Time_Month] ASC,
[Account_MemberId] ASC,
[BusinessProcess_MemberId] ASC,
[Entity_MemberId] ASC,
[TimeDataView_MemberId] ASC,
[Currency_MemberId] ASC,
[BusinessDriver_MemberId] ASC,
[Product_MemberId] ASC,
[Flow_MemberId] ASC,
[Intercompany_MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Ejemplo
El siguiente ejemplo pone de manifiesto el modo de volver a generar un índice con la opción en línea en una tabla de grupos de medida de una base de datos de la aplicación de planeación.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
El administrador de la base de datos debe crear un trabajo en el equipo que ejecuta Microsoft SQL Server 2005 para desfragmentar el índice en cada tabla de grupos de medida. Según cuál sea la situación de fragmentación, programe el trabajo para reorganizar el índice y volver a generarlo en momentos diferentes.
Puede optar por reorganizar el índice una vez al día y volver a generarlo una vez a la semana. Es posible que sea necesario volver a generar algunos índices con mayor frecuencia, como ocurre con los índices agrupados de la tabla de grupos de medida. Programe los trabajos para volver a generar índices en horas de poca actividad, como a medianoche. Ambos métodos pueden realizarse en línea, a excepción de las tablas, que tienen tipos de datos de línea de negocio. Si decide volver a generar un índice sin conexión en lugar de en línea, antes deberá poner la aplicación sin conexión. Para obtener información sobre cómo poner una aplicación sin conexión, consulte la ayuda de la Consola de administración de planeación.
Comprobar la fragmentación de índice
Con el tiempo, las modificaciones realizadas en la base de datos pueden hacer que la información en el índice esté muy dispersa o fragmentada. Los índices muy fragmentados pueden afectar negativamente al rendimiento de las consultas y provocar que la aplicación responda con lentitud.
En SQL Server 2005, la fragmentación de índice se puede resolver ya sea reorganizando un índice o volviendo a generar uno. Antes de decantarse por el método de desfragmentación que se va a utilizar, es necesario analizar el índice para determinar el nivel de fragmentación.
Ejemplo
El siguiente ejemplo refleja el modo de supervisar la fragmentación de índice.
Cuando se ejecuta la siguiente instrucción de código:
SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
se devuelve un conjunto de resultados similar al aquí indicado.
| index_id | name | avg_fragmentation_in_percent |
|---|---|---|
1 |
MG_Strategic_Plan_MeasureGroup_default_partition_PK |
23.076923076923077 |
Para obtener más información acerca de la fragmentación, consulte SQL Server Books Online. En este ejemplo, la solución recomendada consiste en reorganizar MG_Strategic_Plan_MeasureGroup_default_partition_PK, ya que avg_fragmentation_in_percent es inferior al 30 por ciento.
Si la fragmentación hubiera sido superior al 30 por ciento, se hubiera aconsejado volver a generar el índice.
Nota
Este 30 por ciento que se menciona es la recomendación que se realiza en SQL Server Books Online. Esta cifra puede ser otra para ajustarse a las necesidades de su organización más adecuadamente.
Reorganizar índices
Cuando el índice no está excesivamente fragmentado, una práctica recomendada consiste en reorganizarlo. Sin embargo, si el índice está muy fragmentado, obtendrá mejores resultados si lo vuelve a generar. Para obtener directrices sobre la fragmentación, consulte Checking Index Fragmentation.
Ejemplo
El siguiente ejemplo muestra el modo de reorganizar el índice de clave principal en una tabla de grupos de medida en Planning Server. Para reorganizar uno o varios índices, use la instrucción ALTER INDEX con la cláusula REORGANIZE.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;
La reorganización del índice no es una tarea tan exhaustiva como volver a generarlo, puesto que sólo limpia la fragmentación en el nivel de hoja. No mueve el objeto para obtener una mejor densidad de exploración de extensión. El comando para reorganizar índices siempre se ejecuta en línea. Durante la reorganización de un índice no se producen bloqueos de larga ejecución. Para obtener información sobre cómo llevar a cabo una reorganización de índice y conocer las ventajas e inconvenientes de reorganizar o volver a generar un índice, consulte SQL Server Books Online.
Volver a generar índices
Un índice se puede volver a generar anulándolo y creando uno nuevo. Cuando se crea un nuevo índice, se quita la fragmentación.
El siguiente ejemplo muestra cómo volver a generar un índice con la opción en línea en una tabla de grupos de medida en Planning Server.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
Programe un trabajo de SQL Server para desfragmentar el índice de cada tabla de grupos de medida. Una vez establecido el nivel de fragmentación, programe el trabajo para reorganizar el índice y volver a generar uno en momentos distintos. Puede optar por reorganizar un índice una vez al día (en horas de poca actividad, como medianoche) y, a continuación, establecer que se vuelva a generar un índice sólo una vez a la semana. Ambos métodos pueden realizarse en línea, a excepción de las tablas, que tienen tipos de datos de línea de negocio.
Normalmente, volver a generar un índice produce mejores resultados que reorganizarlo, en tanto que quita todos los niveles de fragmentación en el nivel de hoja y en el de árbol b, lo cual reequilibra el árbol. Además, actualiza las estadísticas al actuar del mismo modo que un “análisis completo” con estadísticas precisas.
Para obtener más información sobre cómo volver a generar índices, consulte SQL Server Books Online.