La mayor diferencia entre una base pequeña de desarrollo y una completa de producción es la distribución estadística de datos. Esto choca con las estimaciones potenciales de costo y diferentes planes de ejecución. En estas circunstancias, el desarrollador se encontrará con problemas de performance hasta que el actual plan de ejecución pueda ser verificado en la base de datos real de producción.
Una solución sobre los diferentes planes de ejecución puede ser, transferir las estadísticas de la base de datos en producción a una pequeña de desarrollo. Idealmente, esta capacidad debe ser interna a SQL Server, debido a que no lo es presentamos un método alternativo.
Estadísticas en SQL Server
SQL Server utiliza la optimización basado en el costo. La clave de la optimización basada en el costo, es un método de estimar las filas y páginas involucradas en cada paso del plan de ejecución. Esta es la razón por la cual SQL Server genera y mantiene la distribución de estadísticas. Las estadísticas son generadas sobre las claves de los índices y también sobre columnas que no contienen índices. La tabla sysindexes posee una entrada por cada índice y por cada colección de estadísticas no asociadas con algún índice. Cada tabla posee una entrada en la tabla sysobjectes con una identificación única para la base de datos. La columna id en la tabla sysindexes es el id del objeto que identifica la tabla. Las columnas id e indid identifican en forma única a la fila en la tabla sysindexes. El nombre de la columna en sysindexes es el nombre del índice o el nombre de la colección de estadísticas. Cualquier colección de estadísticas puede visualizarse mediante el siguiente comando:
DBCC SHOW_STATISTICS ( table , target )
El target es el nombre del índice o el nombre de la colección de estadísticas. Un ejemplo de la salida de DBCC SHOW_STATISTICS para una colección de estadísticas basada en índices se muestra más abajo. El primer conjunto de datos contiene información general incluyendo el día de la última actualización, total de filas, filas incluidas, etc. El segundo conjunto de datos contiene el promedio general de distribución por cada clave en sucesión. En este ejemplo, la clave principal es eventPlannerID, y la segunda y última columna clave es ID. La primera fila muestra la información sobre la distribución promedio general, por cada valor distinto de la primera clave, y la segunda fila muestra la distribución de cada valor distinto de cada clave combinada con la segunda clave.
Proceso de Transferencia de Estadísticas
A continuación se describe el proceso para transferir las estadísticas de una base a otra con el mismo esquema:
1) Actualizar las estadísticas de la base de producción en forma completa (opcional, pero recomendado).
2) Crear a nueva base de datos con la versión completa de la base de datos fuente.
3) Setear AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS off.
4) Crear usuarios, tipos de datos, tablas, restricciones (constraints), índices cluster (incluyendo claves primarias) y todos los demás objetos excepto los índices no clusters.
5) Crear tablas que contengan tablas y nombre del usuario, con el objetivo de tener un mapeo entre la base de datos original y la nueva. Cargue la tabla y los nombres de usuarios en las tablas mapeadas.
6) Crear y cargar una tabla con una copia de la tabla sysindexes tomada de la base de datos original (Opcional)
7) Ejecutar sp_configure para permitir la actualización de las tablas del sistema.
8) Insertar la colección de estadísticas no asociadas con los índices en la tabla sysindexes de la nueva base de datos.
9) Crear todos los índices no clusters.
10) Actualizar las entradas en sysindexes para los valores de las estadísticas relacionadas con todas las filas de índices.
{mospagebreak}
Actualización de Estadísticas
Este paso no es necesario, pero si estamos dispuestos a realizar todo el esfuerzo para transferir estadísticas, también es necesario transferir información precisa. El método más simple para actualizar estadísticas es ejecutar la siguiente sentencia:
exec sp_updatestats
Esta sentencia ejecuta UPDATE STATISTICS sobre todas las tablas en la base de datos actual. Las nuevas estadísticas heredarán los ratios desde las viejas estadísticas.
Sería deseable cambiar el ratio (% de recorrido) por defecto a un ratio total, en cuyo caso, ejecute las siguientes sentencias para generar un script para actualizar las estadísticas en forma total sobre cada tabla. Note que con SQL Server Entreprise Edition, las vistas pueden contener índices. Se asume también que no se desea transferir estadísticas para tablas creadas por SQL Server durante la instalación.
SELECT 'UPDATE STATISTICS ' + o.name + ' WITH FULLSCAN'
FROM sysobjectso
WHERE ( OBJECTPROPERTY(o.id, N'IsUserTable'
OR OBJECTPROPERTY(o.id, N'IsUserView') = 1 )
AND OBJECTPROPERTY(o.id, N'IsMSShipped') = 0
ORDER BY o.name
También ejecute DBCC UPDATEUSAGE para corregir los datos de las columnas de la tabla sysindexes.
Crear Nueva Base de Datos y deshabilitar las Estadísticas Automáticas
El siguiente script es un ejemplo para crear una nueva base de datos.
CREATE DATABASE [sut] ON PRIMARY (NAME = N'sut_data',
FILENAME = N'C:MSSQLDatasut_data.mdf' , SIZE = 32)
LOG ON (NAME = N'sut_log',
FILENAME = N'C:MSSQLDatasut_log.ldf' , SIZE = 16)
COLLATE SQL_Latin1_General_CP1_CI_AS
Luego de crear la nueva base de datos, deshabilite la administración automática de estadísticas de la siguiente forma:
ALTER DATABASE [sut] SET AUTO_CREATE_STATISTICS OFF
GO
ALTER DATABASE [sut] SET AUTO_UPDATE_STATISTICS OFF
GO
Crear Usuarios, Tipos de Datos y Tablas, Excepto No Cluster Índices
Crear los usuarios requeridos (aquellos que son dueños de objetos en la base de datos original), tipos de datos, tablas, cluster índices, restricciones (constraints) y otros objetos excepto índices no cluster. No es complicado generar scripts usando el Administrador Corporativo dos veces, una vez con los índices pero no claves, defaults y restricciones (constraints), y la segunda vez con índices pero con claves, defaults y restricciones (constraints). Es también sencillo separar los índices no cluster de cualquier default. La razón para no crear los índices no cluster en este momento, es que los índices y las estadísticas no asociadas con índices, pueden tener valores entremezclados en indid. Si los índices no cluster fueran creados ahora, podría ser necesario re-mapear los valores indid para las estadísticas solo de filas de la base de datos original.
{mospagebreak}
Crear y Cargar la Tabla de Mapeo
Cuando los objetos como tablas son creados en una nueva base de datos, los objetos probablemente tengan diferentes identificadores con respecto a la base de datos original. Ya que la tabla sysindexes utiliza el identificador (object id) para identificar tablas, es necesario crear una tabla de mapeo que contenga los originales y nuevos objetos identificadores para cada tabla. La tabla siguiente también contiene los userid. Esto puede no ser necesario, pero este conjunto de scripts no ha sido probado en bases de datos donde las tablas tienen dueños diferentes a dbo. Tampoco es necesario tener el nombre de la columna, pero se incluye por conveniencia.
CREATE TABLE [tobjects] ( name sysname , oid int , ouid int , id int , uid int )
Vuelva a la base de datos original, y use el siguiente script para cargar los identificadores de objeto en la tabla de mapeo.
INSERT [sut]..[tobjects] (name, oid, ouid, id, uid)
SELECT o.name, o.id, o.uid, n.id, n.uid
FROM sysobjects o
INNER JOIN [sut]..sysobjects n ON n.name = o.name
WHERE ( OBJECTPROPERTY(o.id, N'IsUserTable') = 1
OR OBJECTPROPERTY(o.id, N'IsUserView') = 1 )
AND OBJECTPROPERTY(o.id, N'IsMSShipped') = 0
ORDER BY o.name
Crear una Copia de la Tabla Original Sysindexes
Este paso también es opcional, pero es útil realizar copias permanentes de la tabla sysindexes de la base de datos original a la nueva. El siguiente script crea la tabla con las mismas columnas que la tabla sysindexes.
CREATE TABLE tindexes (
id int , status int , first binary (6) , indid smallint , root binary(6) , minlen smallint ,
keycnt smallint , groupid smallint , dpages int , reserved int , used int , rowcnt bigint ,
rowmodctr int , reserved3 tinyint , reserved4 tinyint , xmaxlen smallint ,
maxirow smallint , OrigFillFactor tinyint , StatVersion tinyint , reserved2 int ,
FirstIAM binary (6), impid smallint , lockflags smallint , pgmodctr int ,
keys varbinary (1088), name sysname , statblob image , maxlen int )
GO
Volver a la base de datos original y cargar la tabla de arriba con una copia de la tabla sysindexes de la base de datos original.
INSERT [sut]..tindexes
(id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,
rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,
reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob,maxlen,rows)
SELECT
i.id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,
rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,
reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,i.name,statblob,maxlen,rows
FROM sysindexes i
INNER JOIN [sut]..[tobjects] t ON t.oid = i.id
El inner join a la tabla [tobjects] permite que solamente las filas con mapeo apropiado sean transferidas. El identificador del objeto (object id) no es mapeado a la nueva base de datos en este momento. No hay razones específicas para esta elección.
Reconfigurar para Permitir la Actualización de las Tablas del Sistema
Por defecto, actualizaciones directas sobre las tablas del sistema no son permitidas por razones obvias. Los siguientes comandos cambian este seteo. (Generalmente no se recomienda estos cambios, pero para esta aplicación es necesario).
Exec sp_configure ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
GO
Tenga en cuenta que cualquier procedimiento (store procedure) creada con “allow updates” activado, también tendrá la capacidad de actualizar las tablas del sistema aunque más tarde se desactivado. Es recomendable que “allow updates” sea desactivado rápidamente, y asegurarse que no se puedan crear procedimientos mientras se encuentre activo.
{mospagebreak}
Insertar las Estadísticas
Insertar las colecciones de estadísticas no asociadas con índices en la tabla sysindexes de la nueva base de datos. La columna status con una máscara de bit 64, corresponde a estadísticas no asociadas a índices. Este paso debe ejecutarse antes de la creación de los índices no cluster. Con los índices cluster creados solamente, en la nueva base de datos, los únicos valores de indid en la tabla sysindexes son 0, representando las tablas organizadas en modo heap, y con valor 1 representan los índices cluster. Las estadísticas no asociadas con índices, ni con índices no cluster tienen valores de indid entre 2 y 254. El valor 255 representa texto o imágenes. Esto permite una copia simple de la totalidad de las filas de la tabla sysindexes, desde la base de datos original a ser insertadas en tabla sysindexes, de la nueva base de datos con los identificadores nuevos y originales. Note que la última columna de sysindexes no está seteada debido a que es una columna del tipo computed.
INSERT sysindexes(
id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,
rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,
reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob)
SELECT o.id, status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,
rowmodctr,reserved3, reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,
reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,s.name,statblob
FROM [tindexes] s
INNER JOIN [tobjects] o ON > o.oid = s.id
WHERE (s.status & 64) = 64
GO
Crear Índices no Cluster y Actualizar Sysindexes
Una vez que las estadísticas no asociadas con los índices han sido insertadas en la tabla sysindexes, los índices no cluster pueden ser creados. No tiene importancia que los valores de indid para los índices no cluster concuerden con los valores originales de indid. Las sentencias siguientes, actualizan la nueva tabla sysindexes con las estadísticas de datos requeridas desde la copia de la tabla original sysindexes.
UPDATE i SET dpages = s.dpages, reserved = s.reserved, used = s.used, rowcnt = s.rowcnt, rowmodctr = s.rowmodctr, statblob = s.statblob
FROM sysindexes i
INNER JOIN [tobjects] t ON t.id = i.id
INNER JOIN [tindexes] s ON s.id = t.oid AND s.name = i.name
WHERE (s.status & 64) = 0
GO
Ejecute sp_configure para deshabilitar la actualización de las tablas del sistema en este momento. Cualquier dato necesario para la nueva y pequeña base de datos puede ser transferido en cualquier momento durante este proceso.
Resumen
La descripción de los pasos para transferir las estadísticas de una base a otra ha sido presentada. Esto es, probablemente, más útil cuando es necesario analizar el plan de ejecución que podría ser usado en una base de datos muy grande, desde una pequeña. Los desarrolladores pueden trabajar en la base de datos generada por los scripts, y aún observar el plan de ejecución, con la excepción del paralelismo de ejecución, si el equipo en el cual se desarrolla tiene un solo procesador.
Otro potencial uso es permitir analizar los problemas de performance. Dado que los planes de ejecución pueden ser solamente vistos desde las estadísticas de la base de datos en producción, los scripts para las tablas e índices necesarios pueden ser transferidos a una nueva base de datos conjuntamente con las estadísticas. Este pequeño conjunto de datos puede ser enviado por correo electrónico para analizar problemas en forma remota.
El articulo original lo pueden consultar en:
http://www.sql-server-performance.com/jc_transferring_statistics.asp
2004 Joe Chang. Todos los derechos reservados.
Publicado con el permiso del autor.
http://www.sql-server-performance.com/joe_chang.asp