Preguntas y respuestas acerca de SQLBúsqueda de bloqueos, consultas de gran tamaño, estadísticas de E/S y mucho más.

Editado por Nancy Michell

Descargar el código de este artículo: SQLQandA2007_08.exe (151KB)

P Necesito ver los nombres de los objetos en todas las bases de datos que los tienen bloqueados. ¿Cómo puedo hacerlo?

R En SQL Server™ 2000, puede consultar la tabla de sistema syslocks en Masters o ejecutar sp_lock para obtener la información actual de bloqueo, tal como se indica a continuación:

SELECT * FROM master..syslocks
EXEC sp_lock

Sin embargo, digamos que quiere traducir los nombres de objeto reales del ObjID de la salida OUTPUT del procedimiento almacenado sp_lock (o de la columna id en master..syslocks).

En SQL Server 2005 SP1 y versiones anteriores, la función OBJECT_NAME sólo permitía pasar un parámetro: object_id. De modo que, para obtener correctamente el nombre de objeto, se requería trabajar en la base de datos CURRENT antes de ejecutar OBJECT_NAME. Esto hacía difícil la supervisión de bloqueos actuales, porque requería el esfuerzo de escribir código personalizado para recorrer cada base de datos y obtener el OBJECT_NAME correcto.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 mejora al agregar un segundo parámetro, database_id. Este nuevo parámetro le permite consultar nombres de objeto independientemente de la base de datos CURRENT a la que está conectado.

OBJECT_NAME ( object_id [, database_id ] )

Ahora puede consultar sys.dm_tran_locks y recuperar el nombre de objeto de cada base de datos, de esta forma:

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

Observe, no obstante, que sólo funciona en SQL Server 2005 SP2 y versiones posteriores. Si lo ejecuta en una versión anterior de SQL Server 2005, obtendrá el siguiente error:

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

Otro ejemplo, que se muestra en la figura 1, une sys.dm_exec_sessions para obtener información acerca de los identificadores de proceso de servicios (SPIDS) implicados en los bloqueos. Para obtener más información, consulte los Libros en pantalla de SQL Server en la dirección technet.microsoft.com/library/ms130214(sql.90).aspx.

Figure 1 Búsqueda de identificadores de proceso de servidor

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

P Tengo una consulta SQL dinámica y de gran tamaño que a veces parece que supera la longitud de NVARCHAR(max). ¿Existe alguna forma de evitar esto y que todavía me permita ejecutar una cadena de gran tamaño?

R Si supera la longitud de NVARCHAR(max), obtendrá una consulta de 2 GB. Probablemente necesita convertir también todas las cadenas que concatena a NVARCHAR(max). Sin embargo, una solución alternativa más útil, que también tiene la ventaja de funcionar en versiones de SQL Server anteriores a SQL Server 2005, es concatenar varias cadenas más pequeñas entre sí. Por ejemplo:

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

Sugerencia: Uso de la cláusula OUTPUT

Ahora puede auditar los cambios que realiza mediante instrucciones DML (lenguaje de manipulación de datos) incluso sin usar desencadenadores. SQL Server 2005 incorpora una cláusula OUTPUT como parte de las instrucciones DML que permiten realizar el seguimiento de los cambios realizados durante cualquier operación de DML. La cláusula OUTPUT puede guardar resultset en una tabla o en una variable de tabla.

La funcionalidad es similar a la que tenían los desencadenadores con tablas INSERTED y DELETED, que se usaban para obtener acceso a las filas modificadas durante la operación de DML. Para ver el funcionamiento, cambiemos la dirección de la tabla de direcciones al orden inverso del valor original, tal como se muestra en la figura siguiente. También puede usar la lógica mostrada en este código para realizar el seguimiento de todos los cambios realizados en los datos y almacenarlos en una tabla.

El resultado de la consulta tendrá el siguiente aspecto:

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

Cambio de una dirección

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

P Tengo una instalación en clúster de misión crítica de SQL 2005 en Windows Server ®2003. El coordinador de transacciones distribuidas de Microsoft® (MS DTC) se configuró en clúster en el mismo grupo de Cluster Quorum, pero usa un recurso de disco dedicado (MS DTC comparte el mismo nombre de red y dirección IP del grupo Cluster Quorum). Deseo que esta configuración cumpla con las recomendaciones de Microsoft. Por lo tanto, necesito consejo para mover MS DTC a un grupo de clúster dedicado. ¿Es suficiente con quitar los servicios de MS DTC mediante la herramienta Cluster Admin y volverlo a crear en un grupo dedicado?

R Dado que ya cuenta con un recurso de disco dedicado para MS DTC, debería poder simplemente eliminar el recurso y volver a crearlo en un grupo nuevo. También debe crear un nuevo nombre de red y dirección IP virtual en el nuevo grupo.

Como alternativa, podría crear el nuevo nombre de red y dirección IP en el grupo de clúster y cambiar las dependencias a los nuevos recursos. A continuación, puede arrastrar MS DTC a un nuevo grupo y de esta manera tomará consigo el disco dedicado y los nuevos recursos.

P Necesito ver estadísticas de E/S en los archivos de base de datos físicos en una base de datos. ¿Qué debería usar?

R La función del sistema fn_virtualfilestats, disponible en SQL Server 2000 y SQL Server 2005, o sys.dm_io_virtual_file_stats (sólo en SQL Server 2005) le permite hacer exactamente lo que busca. La función devuelve información estadística recopilada desde la última vez que se inició la instancia de SQL Server. Los resultados de ejemplo se muestran en la figura 2.

Figure 2 Visualización de estadísticas de E/S en una base de datos

Comprender el impacto de E/S en los archivos de datos subyacentes le puede ayudar a planear mejor cosas como, por ejemplo, la ubicación física de archivos y grupos de archivos en los volúmenes de datos, detectar posibles cuellos de botella de E/S, realizar el mantenimiento de base de datos en el nivel de archivos, entre otras tareas. Esta función es especialmente útil a la hora de examinar el impacto de E/S en bases de datos de gran tamaño, donde puede haber múltiples archivos y grupos de archivos.

La consulta para mostrar la información de E/S de archivos para SQL Server 2000 tiene el aspecto siguiente:

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

Para ver un databaseID específico, pase el identificador de la base de datos, tal como se indica a continuación:

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

Este es el código de SQL Server 2005 que muestra las estadísticas de archivos de todas las bases de datos en el servidor:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

La siguiente consulta devuelve las estadísticas de archivos sólo para la base de datos actual:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

En SQL Server 2005, existe también una nueva función de sistema denominada sys.dm_io_virtual_file_stats, que está pensada para reemplazar en algún momento a la función heredada fn_virtualfilestats:

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

A continuación se muestra cómo se usa:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Si desea generar un informe más legible que muestre los nombres de base de datos y nombres de archivo reales de la salida, puede usar el código de la descarga que acompaña en SQL Server 2000 o SQL Server 2005. El código está disponible en el sitio web de TechNet Magazine.

P Necesito una manera fácil de ver si una transacción de eliminación ha activado un desencadenador. ¿Sabe cómo puedo obtener esta información?

R Cuando se trabaja con desencadenadores que controlan acciones de eliminación, inserción y actualización, las personas comúnmente usan varias técnicas diferentes para determinar si un desencadenador fue activado por una transacción de eliminación. Un método popular es comparar los contadores de las tablas virtuales insertadas y eliminadas para ver si coinciden. Sin embargo, hay un método más sencillo: Puede usar la función Columns_Updated.

Eso se debe a que cuando un desencadenador se activa mediante una transacción de eliminación, Columns_Updated siempre devuelve el valor varbinary 0x. Con la acción siguiente se comprobará que el desencadenador fue activado por una eliminación:

IF Columns_Updated() = 0x

Sugerencia: Inicialización de los archivos de datos y de registro

¿Sabía que los archivos de datos y registro se inicializan para sobrescribir todos los datos existentes de archivos anteriormente eliminados que quedan en el disco? Los archivos de datos y registro se inicializan por primera vez completando los archivos con ceros cuando se crea una base de datos; se agregan archivos, de registro o datos, a una base de datos existente; se aumenta el tamaño de un archivo existente (incluidas las operaciones autogrow) o se restaura un grupo de base de datos o archivos. La inicialización de archivos hace que estas operaciones lleven más tiempo. Sin embargo, cuando los datos se escriben por primera vez a los archivos, el sistema operativo no tiene que completar los archivos con ceros.

En SQL Server 2005, los archivos de datos se pueden inicializar instantáneamente. Esta capacidad permite la ejecución rápida de las operaciones de archivo mencionadas. La inicialización instantánea de archivos recupera espacio en disco usado sin completar ese espacio con ceros. En lugar de eso, el contenido del disco se sobrescribe a medida que los nuevos datos se escriben en los archivos. Los archivos de registro no se pueden inicializar instantáneamente. La inicialización instantánea de archivos sólo está disponible en Windows XP Professional y Windows Server 2003 o en versiones posteriores.

Debido a que el contenido eliminado del disco se sobrescribe sólo cuando los nuevos datos se escriben en los archivos, personal no autorizado podría tener acceso al contenido eliminado. Aunque el archivo de base de datos se adjunta con la sesión de SQL Server, esta amenaza de divulgación de información se reduce mediante la lista de control de acceso discrecional (DACL) en el archivo. Esta DACL permite que sólo la cuenta de servicio de SQL Server y el administrador local tengan acceso a los archivos. Sin embargo, cuando el archivo se separa, un usuario o servicio que no tenga se_manage_volume_name puede tener acceso a él. Una amenaza similar existe cuando se realiza la copia de seguridad de la base de datos. El contenido eliminado puede pasar a estar a disposición de un usuario o servicio no autorizado si el archivo de copia de seguridad no está protegido con una DACL apropiada.

Si la posibilidad de divulgación de contenido eliminado representa un problema, siempre debe asegurase que todos los archivos de datos y de copia de seguridad separados tengan DACL restrictivas. Además, deshabilite la inicialización instantánea de archivos para la sesión de SQL Server mediante revocación de se_manage_volume_name de la cuenta de servicio de SQL Server.

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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