SQL Q&A
Finding Locks, Large Queries, I/O Statistics, and More
Edited by Nancy Michell
Q I need to view the names of the objects in all databases that have locks on them. How can I do this?
A In SQL Server™ 2000, you can query the syslocks system table in Master or run sp_lock to get current lock information, like so:
SELECT * FROM master..syslocks
EXEC sp_lock
However, let's say you want to translate the actual object NAMES from the ObjID from the OUTPUT of the sp_lock stored procedure (or from the id column in master..syslocks).
In SQL Server 2005 SP1 and earlier, the OBJECT_NAME function only let you pass one parameter: object_id. So, in order to properly get the object name, you had to be working in the CURRENT database before running OBJECT_NAME. This made monitoring the current locks difficult, as you had to go to the effort of writing custom code to loop through each database to get the correct OBJECT_NAME.
USE DBNAME
SELECT OBJECT_NAME(object_id)
SQL Server 2005 SP2 improves on this by adding a second parameter, database_id. This new parameter allows you to query object names regardless of which CURRENT database you're connected to.
OBJECT_NAME ( object_id [, database_id ] )
Now you can query sys.dm_tran_locks and retrieve the object name for each database, like this:
SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'
Note, however, that this only works on SQL Server 2005 SP2 and later. If you run this in an earlier version of SQL Server 2005, you'll get this error:
Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).
Figure 1 Finding server process IDs
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'
Q I have a large, dynamic SQL query that seems to sometimes exceed the length of NVARCHAR(max). Is there any way around this that still permits me to execute one big string?
A If you're exceeding the length of NVARCHAR(max), you've got a 2GB query! You probably need to convert all of the strings you concatenate to NVARCHAR(max) as well. However, a more convenient workaround, which also has the advantage of being effective in versions of SQL Server before SQL Server 2005, is to concatenate a number of smaller strings together. Here's an example:
DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)
Q I have a mission-critical SQL Server 2005 cluster installation on Windows Server® 2003. The Microsoft® Distributed Transaction Coordinator (MS DTC) has been "clustered" on the same group with the Cluster Quorum, but is using a dedicated disk resource (MS DTC is sharing the same network name and IP address of the Quorum Cluster Group). I want to align this configuration with Microsoft best practices. Therefore, I need advice on moving the MS DTC to a dedicated cluster group. Is it enough to remove the MS DTC services using the Cluster Admin Tool and recreate it on a dedicated group?
A Since you already have a dedicated disk resource for MS DTC, you
should be able to simply remove the resource and create it in a new group. You need to create a new network name and virtual IP address in the new group too.
Alternatively, you could create the new network name and IP address in the cluster group and change the dependencies to the new resources. You can then drag MS DTC to a new group and it will take the dedicated disk and new resources with it.
Q I need to be able to view I/O statistics on the physical database files in a database. What should I use?
A The system function fn_virtualfilestats, available in both SQL Server 2000 and SQL Server 2005, or sys.dm_io_virtual_file_stats (in SQL Server 2005 only) allow you to do exactly what you're looking for. The function returns statistical information gathered since the last time the instance of SQL Server was started. Sample results are shown in Figure 2.
Figure 2 Viewing I/O statistics on a database
| DbId |
FileId |
TimeStamp |
NumberReads |
NumberWrites |
BytesRead |
BytesWritten |
IoStallMS |
| 20 |
1 |
250765718 |
381 |
0 |
3350528 |
0 |
951 |
| 20 |
2 |
250765718 |
12 |
8 |
409600 |
491520 |
0 |
| 20 |
3 |
250765718 |
5 |
0 |
40960 |
0 |
16 |
Understanding the I/O impact on the underlying data files can help you better plan such things as the physical placement of files and file groups on data volumes, detecting possible I/O bottlenecks, performing file-level database maintenance, and other such tasks. This function is particularly useful for examining the I/O impact for large databases, where you may have multiple files and file groups.
The query for displaying file I/O info for SQL Server 2000 looks like this:
SELECT *
FROM ::fn_virtualfilestats(default,default)
GO
To view a specific databaseID, pass the ID for the database, like so:
SELECT *
FROM ::fn_virtualfilestats(7,default)
GO
Here's the SQL Server 2005 code that shows file statistics for all databases on the server:
SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO
The following query returns file statistics for only the current database:
SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO
For SQL Server 2005, there is also a new system function called sys.dm_io_virtual_file_stats, which is intended to eventually replace the legacy function fn_virtualfilestats:
sys.dm_io_virtual_file_stats(
{ database_id | NULL },
{ file_id | NULL }
)
Here's how to use it:
SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)
If you want to generate a more readable report that shows the actual database names and file names from the output, you can use the code in the accompanying download on either SQL Server 2000 or SQL Server 2005. The code is available from the
TechNet MagazineWeb site.
Q I need an easy way to see if a delete transaction has fired a trigger. Do you have any ideas for how I can get this information?
A When dealing with triggers that handle delete, insert, and update actions, people commonly use a number of different techniques for determining whether a trigger was fired by a delete transaction. A popular method is to compare the counts of the inserted and deleted virtual tables to see if they match. However, there is a simpler method: you can use the Columns_Updated function instead.
That's because when a trigger is fired by a delete transaction, Columns_Updated always returns the varbinary value 0x. The following check will verify that the trigger was fired by a delete:
IF Columns_Updated() = 0x
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. All rights reserved; reproduction in part or in whole without permission is prohibited.