SQL Q&AReconstrucción de índices, longitud de colas de discos y otros aspectos
Editado por Nancy Michell
P ¿Cómo se vuelven a generar los índices de SQL Server? Necesito saber por qué DBCC DBREINDEX consume todo mi espacio de disco y por qué no se libera espacio cuando el comando produce un error. El tamaño de mi base de datos es de 90 GB y la tabla más grande tiene 70 GB.
Cuándo ejecuto DBCC DBREINDEX, dejando el 10 por ciento de espacio libre, consume todo el espacio de disco disponible y el comando produce un error. El archivo de base de datos tiene inicialmente 90 GB, pero cuando se produce el error del comando DBCC, el archivo pasa a tener 160 GB y los 70 GB adicionales no se liberan. Tengo que reducir manualmente la base de datos varias veces para recuperar ese espacio.
Si el comando funciona, el tamaño del archivo de base de datos es también de 160 GB, pero el espacio se libera automáticamente unas horas después, mediante la autoreducción. ¿Sería aceptable hacer una desfragmentación en vez de ejecutar el comando DBREINDEX para consumir menos espacio? No tengo índices agrupados y uso el modo de recuperación sencilla.
R En el nivel más sencillo, los índices se vuelven a generar mediante una nueva copia del índice y, a continuación, eliminando la vieja. Esto significa que, básicamente, hay dos copias del índice durante un breve periodo de tiempo. La creación del índice nuevo podría requerir tanto espacio de archivo de base de datos como el índice original, y si la regeneración requiere una operación de ordenación, se necesita un 20 por ciento adicional del tamaño del índice para la ordenación.
Es decir, en el peor de los casos la regeneración de un índice requiere 1,2 veces el espacio del índice original. Si el archivo de base de datos no tiene suficiente espacio libre, el archivo tendrá que aumentar de tamaño al avanzar la operación. Es posible que si no está habilitado el crecimiento automático o no hay espacio suficiente en el volumen de disco, puede no haber suficiente espacio libre disponible y se producirá un error de la operación de regeneración.
Tanto si se produce un error de la operación como si no, el espacio adicional asignado al archivo de base de datos no se libera cuando finaliza la operación de regeneración. Se asume que el espacio se usará para operaciones normales de la base de datos.
Es casi seguro que la ejecución de la reducción (manual o automática) causará la fragmentación del índice, como consecuencia de la forma de trabajar del algoritmo. Para obtener más información, consulte Motor de Almacenamiento de SQL Server. La autoreducción puede perjudicar especialmente al rendimiento si la base de datos necesita espacio libre para operaciones normales, porque se podría iniciar un ciclo de crecimiento automático-autoreducción-crecimiento automático-autoreducción desastroso para la fragmentación y el rendimiento.
El uso de DBCC INDEXDEFRAG (o de ALTER INDEX ... REORGANIZE en SQL Server™ 2005) tiene la ventaja de que prácticamente no utiliza espacio adicional de archivo de base de datos, aunque puede tardar más y generar muchos más registros de transacciones que la reconstrucción de un índice. Para DBCC INDEXDEFRAG se crea siempre un registro completo, independientemente del modo de recuperación que se use, mientras que en el modo de recuperación simple la reconstrucción del índice se registrará de forma masiva. Cada método tienen sus ventajas e inconvenientes, que se explican pormenorizadamente en el artículo Procedimientos recomendados para la desfragmentación del índice de SQL Server.
Antes de tomar la decisión de cómo corregir la fragmentación, debe decidir si en realidad debe hacerlo. Dependiendo del tipo de operaciones para las que se use el índice, la fragmentación puede no tener efecto alguno en el rendimiento y, por lo tanto, corregirla no es más que una pérdida de recursos. El artículo ofrece información muy detallada.
Conclusión: asegúrese de que elige el método más indicado para eliminar la fragmentación en su entorno y que la eliminación contribuye a mejorar el rendimiento de las consultas.
P He configurado la creación de reflejos de base de datos correctamente entre dos instancias de SQL Server 2005. Mi aplicación se conecta a SQL Server mediante un inicio de sesión de SQL Server y se ha creado mediante ADO y el cliente nativo de SQL. La cadena de conexión y la configuración de la conexión especifican la información correcta, incluido el asociado de conmutación por error adecuado. También he creado todos los mismos inicios de sesión del servidor principal en el servidor reflejado. Al probar un error de la base de datos, el servidor reflejado asume la función de principal y todo parece ser correcto en la instancia de SQL Server. (Puedo incluso conectarme al servidor reflejado con el inicio de sesión de Windows®). Sin embargo, al tratar de volver a conectar la aplicación se muestra un error similar al siguiente:
Cannot open database "<db name>" requested by the login. The login failed.
Parece que el inicio de sesión no está asociado con un usuario en la nueva base de datos principal (que era inicialmente la reflejada). Ejecuto sp_change_users_login para sincronizar los usuarios e inicios de sesión de la base de datos, y obtengo un mensaje que dice que se han corregido varios usuarios huérfanos. A continuación, la aplicación se vuelve a conectar correctamente al nuevo servidor principal. He intentado realizar varias conmutaciones por error, y siempre se produce el mismo comportamiento: se pierde la asociación entre el inicio de sesión y el usuario.
¿Hay alguna manera de configurar la creación de reflejos para que no se produzca este problema?
R Sí. El problema se debe a que los identificadores de seguridad (SID) de los inicios de sesión de SQL Server de cada servidor no coinciden, aunque los nombres de los inicios de sesión sean los mismos. Este problema no existe con los inicios de sesión de Windows/dominio usuario/grupo, porque los SID para estos inicios de sesión se crean en función del SID de dominio del usuario/grupo y, por ello, será el mismo para el mismo usuario/grupo concreto, independientemente del SQL Server al que se agregue.
Para que el paso de sincronización de sp_change_users_login no sea necesario, tendrá que crear los inicios de sesión de SQL Server en el servidor reflejado no sólo con el mismo nombre, si no también con el mismo SID que el del servidor principal. Debe hacerlo mediante la especificación de SID de la instrucción CREATE LOGIN al crear los inicios de sesión en el servidor reflejado, de la siguiente manera:
CREATE LOGIN <loginname> WITH PASSWORD = <password>,
SID = <sid for
same login on principal server>,...
Puede recuperar el SID de cada inicio de sesión del servidor principal consultando la vista de catálogo sys.sql_logins. En la Figura 1 se muestra el ejemplo de una consulta que generará una instrucción CREATE LOGIN real para cada inicio de sesión de SQL Server/Windows en un servidor determinado.
Figure 1 Generar la instrucción CREATE LOGIN
select 'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) +
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' +
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'
P ¿Cuál debe ser la longitud media de la cola de disco? Por ejemplo, si tengo 20 cilindros de discos físicos independientes en una Red de área de almacenamiento (SAN) con una configuración RAID 01, ¿cómo puedo calcular la longitud media de la cola de disco? ¿Es Longitud media de cola de disco/20 o Longitud media de cola de disco/2?
R En primer lugar, debe observar la latencia de disco antes de malgastar tiempo en la longitud media de la cola de disco en un entorno SAN. En realidad, depende de lo que trate de averiguar. Verá por qué en un momento.
La explicación de este contador (de Perfmon) es "La longitud media de la cola de disco es el promedio de solicitudes de lectura y escritura puestas en la cola del disco seleccionado durante el intervalo de tiempo de muestra". Es un contador de disco físico o de disco lógico, de modo que el número que obtenga dependerá de cómo se presenta el almacenamiento subyacente al sistema operativo.
Vamos a considerar su caso. Tiene 20 cilindros de discos en una configuración RAID 01, lo que significa que están seccionados y reflejados (o reflejados y seccionados, según se lea 01 o 10). El aspecto clave de la matriz de almacenamiento es que hay 10 cilindros de discos en el conjunto seccionado.
Lo que sucede es que carezco de información esencial, como el tamaño de las secciones, el tamaño de escritura, y qué clase de E/S emite (lectura, escritura, secuencial o aleatoria).
Si no tenemos en cuenta esta información por ahora, si la longitud media de la cola de disco es 10, el sistema operativo ha puesto en cola 10 E/S en la matriz de discos. Teóricamente, podría haber una operación de E/S en cada uno de los 10 conjuntos reflejados de la sección, o podría haber habido 10 E/S en un solo disco. No hay manera de saber cuál de las dos cosas sucedió.
Y aquí es donde se necesita la información que falta. Asumamos que el tamaño de la sección es 64 KB, el tamaño de escritura es 8 KB, y va a realizar todo un grupo de escrituras en secuencia. Ésta es una situación típica de la actividad de almacenamiento de SQL Server. En este caso, es bastante probable que ocho de las diez E/S fueran al primer disco, y las otras dos fueran al disco siguiente. Así, si desea calcular la longitud de la cola de disco por disco en esta situación, es 8 para el primer disco, 2 para el segundo disco, y 0 para los demás ocho discos de la matriz.
A continuación, vamos a cambiar la situación a un tamaño teórico de la sección de 8 KB y un tamaño del bloque de escritura de 64 KB, y vamos a mantener la longitud de la cola de disco en 10. En este caso, cada bloque de 64 KB se distribuye en 8 discos, de modo que una E/S se escribe en 8 discos y las 10 E/S de la cola se distribuyen en 80 escrituras de disco entre los 10 discos de la matriz. Si desea calcular la longitud de la cola de disco por cada disco de la matriz, será 8 para cada uno de ellos.
Seamos realistas y agreguemos otro nivel de incertidumbre a esta situación. En la mayoría de los casos, el almacenamiento de SAN se conectará al servidor mediante uno o varios HBA en el servidor, varias fibras para conectar el HBA al SAN, varios puertos en el cliente de SAN, y quizás un tipo de conmutador de fibra entre el servidor y la red SAN. A continuación, nos centramos en la arquitectura interna de los buses del interior de SAN, y en cómo se conectan los discos a los puertos del cliente de SAN.
Cualquier puesta en cola notificada en Perfmon puede ser un síntoma de una latencia alta o una cola larga en cualquiera de los puntos entre el lugar en que el sistema operativo mide la longitud de la cola de disco y la superficie de los discos. Por eso, debe fijarse en la latencia y basar sus decisiones en ese contador en lugar de en la longitud media de la cola de disco.
P Uso la réplica transaccional y sé que se han cambiado muchas filas manualmente en una tabla del suscriptor, así que recibo errores cuando el publicador trata de actualizar una fila que ya no existe en el suscriptor.
Necesito saber si hay alguna manera mediante la réplica para reinicializar sólo esta tabla desde el publicador en lugar de volver a solicitar una instantánea completa. He estudiado la función tablediff, que da la impresión que hará lo que deseo, pero desearía saber cómo interactúa con la réplica.
Por ejemplo, ¿toma tablediff una instantánea en el tiempo de la tabla del publicador y la compara con la equivalente de suscripción? ¿Debo detener la réplica para usar la función tablediff y garantizar la coherencia de los datos? ¿Hay algo más que deba saber?
R En primer lugar, tablediff no toma una instantánea literal ni de la tabla del publicador ni del suscriptor. En esta situación concreta, hay un par de opciones que se podrían considerar.
La primera sería detener temporalmente la réplica y ejecutar la utilidad. Si le preocupa que los usuarios traten de modificar los datos, puede usar los parámetros -sourcelocked y -destinationlocked, que establecerán un bloqueo exclusivo en las dos tablas mientras se ejecuta la utilidad. Si esto no es posible, otra opción sería observar los parámetros -rc y -ri mientras se ejecuta la réplica. Así, tablediff se ejecutará una vez y, a continuación, se ejecutará consecutivamente en los errores detectados, eliminando los que pudieran deberse a demoras de propagación de la réplica. Tenga en cuenta, sin embargo, que con esta opción, dependiendo de la demora de la réplica, podría no detectar todas las filas que se han cambiado en el suscriptor.
Mis agradecimientos a los siguientes profesionales de TI de Microsoft por la aportación de sus conocimientos técnicos: Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe y Steven Wort.
© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.