Preguntas y respuestas acerca de SQLCuellos de botella de CPU, restauración y traslado de bases de datos y mucho más

Editado por Nancy Michell

P. El uso de CPU de My SQL Server™ empezó de repente a alcanzar picos excesivos, pero no ha cambiado nada. No se agregaron usuarios nuevos, no se quitó ningún hardware, no se crearon tablas nuevas. ¿Qué está pasando?

Sugerencia: Acceso durante la creación de índices

A veces deseará crear índices en tablas grandes (lo cual puede ocupar gran cantidad de tiempo), pero durante el proceso desea que los datos estén disponibles. ¿Cómo puede lograr ambos objetivos?

Siempre que se crean, eliminan o regeneran índices en clúster, SQL Server pone un bloqueo de modificación de esquema (SCH-M) sobre la tabla, lo que impide cualquier acceso de usuario a los datos subyacentes durante la operación. Este sería el caso cuando se crea un índice de clúster sobre una tabla. En cambio, cuando se crea un índice que no es de clúster sobre una columna, SQL Server coloca un bloqueo compartido (S) sobre la tabla y ello, aunque también impide la actualización de datos de la tabla subyacente, al menos permite ejecutar instrucciones SELECT, por lo que se pueden leer los datos.

Si la disponibilidad de lectura sobre la tabla es importante durante la creación del índice en clúster, se puede crear un índice sobre la tabla y convertirlo en una operación en línea. El comando es el siguiente:

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

Cuando usa operaciones de índice en línea sobre tablas, SQL Server sigue poniendo un bloqueo SCH-M para un índice en clúster o un bloqueo compartido (S) para un índice no de clúster sobre la tabla subyacente, pero sólo durante un espacio de tiempo muy corto; durante la fase inicial y final de la operación del índice. Por lo tanto, esta opción ofrece un mejor acceso para consultar y actualizar la tabla subyacente durante el proceso de creación del índice. Tenga en cuenta que la creación y las operaciones en línea con índices sólo están disponibles con SQL Server 2005 Enterprise Edition.

R. Un cuello de botella de CPU que sucede de repente e inesperadamente sin hacer ningún cambio y sin cargas adicionales en el servidor puede tener como origen varios motivos, pero algunos de los más comunes son un plan de consulta no óptimo, una configuración pobre de SQL Server, diseño deficiente de aplicación/base de datos y recursos de hardware insuficientes.

La primera cosa que hacer en una situación como esta es identificar si el servidor está limitado en CPU y, si lo está, identificar las instrucciones que consumen más CPU en el sistema SQL Server local. Puede usar el Monitor de rendimiento para determinar si el servidor está limitado en CPU sólo con mirar al contador PROCESADOR: % DE TIEMPO DE PROCESADOR. Si descubre que el valor del tiempo usado por la CPU es igual o superior al 75 por ciento, tiene un cuello de botella en la CPU.

También debe supervisar los programadores de SQL Server consultando la vista de administración dinámica (DMV) del sistema, que se llama SYS.DM_OS_SCHEDULERS para ver el valor de las tareas ejecutables. Un valor distinto de cero indica que las tareas tienen que esperar su segmento de tiempo para ejecutarse; los valores altos de este contador también son síntoma de un cuello de botella de CPU.

Puede usar la consulta siguiente para que se muestren todos los programadores y examinar el número de tareas ejecutables:

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Para obtener las 50 primeras instrucciones SQL según su uso de CPU, use la consulta de la figura 1.

Figure 1 Las 50 primeras causas de atascos de CPU

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

P. Tuve que recuperar una base de datos de SQL Server 2005 de cinta. Después de la restauración, se perdieron la mayor parte de los permisos que tenían los usuarios en la base de datos. ¿Podría saber qué hice mal durante la restauración? Los datos eran correctos, pero los permisos eran un desastre.

R. Lo más probable es que no restauró la base de datos maestra en el mismo punto en el tiempo, y que los identificadores de inicio de sesión de la base de datos de usuario no coincidían con la base de datos maestra actual. Necesita sincronizarlos. La barra lateral "Dónde obtener ayuda para trasladar una base de datos" muestra los recursos más útiles para llegar al fondo de los problemas que se experimentan al restaurar o mover bases de datos.

P. He desarrollado una aplicación en la que uso un procedimiento almacenado que desmenuza datos XML en tablas relacionales usando OpenXML en SQL Server 2005 SP1. El documento XML tiene como máximo 5 KB (el tamaño promedio es de 2,5 KB). Se llama al procedimiento almacenado varias veces en paralelo (hasta 50 veces).

Estoy sufriendo problemas graves de contención de bloqueo y creo que pueden deberse a OpenXML. ¿Qué opina?

R. Aunque OpenXML puede ser más rápido para desmenuzar o convertir datos en un único subproceso que el método de los nodos, éste último normalmente escala mejor, especialmente si se usa en paralelo. Sin embargo, si va a usar OpenXML de todas formas, debería usar las instrucciones siguientes para mejorar el rendimiento general de OpenXML.

En vez de llamar a OpenXML cinco veces con el mismo patrón de filas (como hacía en su solución), debe extraer todos los datos con el mismo patrón de filas en una tabla temporal y, a continuación, realizar sus selecciones a partir de la tabla temporal. Intente liberar la memoria con sp_xml_removedocument lo antes posible. También es una buena idea evitar usar caracteres comodín como * y //, siempre que sea posible; ofrecer la ruta de acceso explícita producirá una consulta de mayor rendimiento.

P. DBCC SHRINKFILE se ejecuta muy despacio en mi servidor. ¿Obtendría mejor rendimiento en un equipo multiprocesador? ¿Qué puedo hacer para mejorar la situación?

R. DBCC SHRINKFILE es una operación de un único subproceso, lo que significa que no aprovecha múltiples CPU. Mueve páginas desde la parte posterior a la parte frontal del archivo, una página a la vez. Y shrink, como se le denomina a menudo, no realiza la desfragmentación; de hecho en muchas situaciones, shrink aumenta la fragmentación lógica.

Algunas maneras de mejorar el rendimiento de shrink son trasladar páginas a índices en clúster. Si dispone de pilas y esas pilas tienen muchos índices fuera de clúster, la velocidad sería apreciablemente más lenta (en comparación con una situación de índices en clúster).

Tenga en cuenta también que trasladar páginas de datos blob de objetos grandes (LOB) es lento, porque los datos de la fila tienen que leerse para encontrar la raíz de los datos LOB.

Si la mayoría de los contenidos de un índice o tabla reside al final del archivo, puede volver a generar los índices para trasladarlos al extremo frontal del archivo. Volver a generar los índices aprovecha múltiples CPU y podría usar menos espacio de registro en modo bulk_logged. Después de ello, la ejecución de shrink resultará más rápida.

Para obtener más información acerca de las operaciones de reducción, empiece con las entradas de blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx y blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Dónde obtener ayuda para trasladar una base de datos

• Cómo trasladar bases de datos entre equipos que ejecutan SQL Server
• Cómo mover bases de datos de SQL Server a una nueva ubicación mediante las funciones Detach y Attach de SQL Server
• INF: Uso de la opción WITH MOVE con la instrucción RESTORE
• Cómo resolver problemas de permisos al trasladar una base de datos entre servidores SQL Server
• INF: Cómo transferir inicios de sesión y contraseñas entre servidores SQL Server
• INF: Descripción y solución de problemas del Asistente para copiar bases de datos en SQL Server 2000
• Los inicios de sesión y los permisos de usuario de una base de datos pueden ser incorrectos después de restaurar la base de datos
• No es posible restaurar copias de seguridad de la base de datos del sistema a una compilación diferente de SQL Server
• Cómo recuperar tablas o filas específicas desde copias de seguridad de bases de datos o desde copias de seguridad de registros de transacciones en SQL Server

Sugerencia: Cambio de puertos

Cualquiera que esté familiarizado con SQL Server sabe que el número de puerto predeterminado en el que escucha SQL Server es el 1433. Aunque las instancias con nombre de SQL Server están configuradas para usar puertos dinámicos (lo que significa que pueden seleccionar cualquier puerto disponible cuando se inicia una instancia de SQL Server), la sesión predeterminada de SQL Server siempre escucha en el puerto 1433. Así que, si el servidor está escuchando en el puerto predeterminado y no está bien protegido, está en peligro. Sin embargo, puede frustrar los ataques si cambia el puerto predeterminado. Aquí está la solución.

Abra el Administrador de configuración de SQL Server y expanda Configuración de red de SQL Server 2005 y después Protocolos. A continuación, haga doble clic en TCP/IP. La lista de propiedades de TCP/IP y sus funciones se muestra en el gráfico siguiente, así que ajústelas tal como se indica.

Observe que el motor de base de datos de SQL Server puede escuchar en varios puertos desde la misma dirección IP. Por lo tanto, debe incluir los puertos que desea usar delimitándolos con una coma, con el formato 1433,1500,1501. Si desea configurar una única dirección IP para escuchar en varios puertos, también debe establecer el parámetro Escuchar todo en No, en la ficha Protocolos del cuadro de diálogo Propiedades de TCP/IP.

Ahora, haga clic con el botón secundario en cada dirección y seleccione Propiedades para identificar la dirección IP que desea configurar. Si el cuadro de diálogo Puertos dinámicos TCP contiene 0, lo que indica que el motor de la base de datos escucha en puertos dinámicos, elimine el 0. Dentro del área Propiedades IP del cuadro Puerto TCP, escriba el número de puerto en el que desea que escuche esta dirección IP y, a continuación, haga clic en Aceptar. En el panel de consola, haga clic en Servicios de SQL Server 2005 y en el panel de detalles, haga clic con el botón secundario en SQL Server (<nombre de instancia>). A continuación, haga clic en Reiniciar, para detener y reiniciar SQL Server.

Después de haber configurado SQL Server para que escuche en un puerto específico, hay tres maneras de que un cliente se conecte al puerto. Puede ejecutar el servicio Explorador de SQL Server en el servidor para conectar a la sesión del motor de base de datos por su nombre; puede crear un alias en el cliente, especificando el número de puerto; o puede programar el cliente para que se conecte usando una cadena de conexión personalizada.

Gracias a los siguientes profesionales de TI de Microsoft por responder las preguntas de este mes: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal y Wayne Yu.

© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.