SQL 技术问答查找锁、大型查询、I/O 统计信息等

编辑:Nancy Michell

下载这篇文章的代码: SQLQandA2007_08.exe (151KB)

问:我需要查看具有锁的所有数据库中对象的名称。应该怎样操作?

答:在 SQL Server™ 2000 中,您可以在 Master 中查询 syslocks 系统表或运行 sp_lock 来获取当前锁信息,例如:

SELECT * FROM master..syslocks
EXEC sp_lock

不过,假设您要从 sp_lock 存储过程的输出中的 ObjID(或者从 master..syslocks 的 id 列)转换实际对象 NAMES。

在 SQL Server 2005 SP1 和更早版本中,OBJECT_NAME 函数仅允许您传递一个参数:object_id。因此,为了正确获得对象名称,您必须在运行 OBJECT_NAME 之前使用当前数据库。这使得监视当前锁变得困难,因为您必须努力编写自定义代码遍历每个数据库才能获取正确的 OBJECT_NAME。

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 通过添加另一个参数 database_id 对此进行了改进。此新参数允许您查询对象名称,而不考虑当前连接到了哪个数据库。

OBJECT_NAME ( object_id [, database_id ] )

现在您可以查询 sys.dm_tran_locks 并检索每个数据库的对象名称,例如:

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

不过,请注意,它仅适用于 SQL Server 2005 SP2 和更高版本。如果您在早期版本的 SQL Server 2005 中运行它,将出现以下错误:

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

另一个示例,如图 1 所示,连接 sys.dm_exec_sessions 以获取关于这些锁中涉及的服务进程 ID (SPID) 的信息。有关更多信息,请参见 technet.microsoft.com/library/ms130214(sql.90).aspx 上的 SQL Server 联机丛书。

Figure 1 查找服务器进程 ID

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'

问:我有一个大型的动态 SQL 查询,它似乎有时超出了 NVARCHAR(max) 的长度。有没有解决此问题的方法,让我仍可以执行一个大字符串?

答:如果您超过了 NVARCHAR(max) 的长度,则您已经获得了 2GB 的查询!您可能还需要将连接的所有字符串转换为 NVARCHAR(max)。不过,另一种更简单方便的解决方法是将许多较小的字符串连接在一起,这种方法还具有适用于 SQL Server 2005 之前的各种版本的优势。示例如下:

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

提示:使用 OUTPUT 子句

现在,您可以使用数据操作语言 (DML) 语句(甚至不必使用触发器)审计所做的更改。SQL Server 2005 引入了 OUTPUT 子句作为 DML 语句的一部分,DML 语句可帮助您跟踪在任何 DML 操作期间所做的更改。OUTPUT 子句可将结果集保存到表或表变量中。

该功能与触发器对 INSERTED 和 DELETED 表执行的操作相似,后者用于访问在 DML 操作期间已修改的行。要查看它如何工作,让我们将地址表中的地址更改为原始值的反值,如下图所示。您可能还使用该代码中所显示的逻辑来跟踪您对数据进行的任何更改并将其存储在表中。

此查询的输出如下所示:

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

更改地址

--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

问:我拥有 Windows Server® 2003 上的关键任务 SQL Server 2005 群集安装。Microsoft® 分布式事务处理协调器 (MS DTC) 与群集仲裁在相同的组上构建群集,但是前者正在使用专用磁盘资源(MS DTC 正在共享仲裁群集组的相同网络名称和 IP 地址)。我希望该配置与 Microsoft 最佳实践一致。因此,我需要有关将 MS DTC 移动到专用群集组的建议。使用群集管理工具删除 MS DTC 服务并在专用组上重新创建它就足够了吗?

答:由于您已经拥有 MS DTC 的专用磁盘资源,您应该可以简单地删除资源并在新组中创建它。您还需要在新组中创建新的网络名称和虚拟 IP 地址。

或者,您可以在群集组中创建新的网络名称和 IP 地址并更改对新资源的依赖关系。然后,您可以将 MS DTC 拖放到新组中,专用磁盘和新资源会随之移动。

问:我需要能够查看关于数据库中物理数据库文件的 I/O 统计信息。我应该使用什么?

答:要查看这些信息,可使用系统函数 fn_virtualfilestats(在 SQL Server 2000 和 SQL Server 2005 中提供)或 sys.dm_io_virtual_file_stats(只在 SQL Server 2005 中提供)。函数将返回自上次启动 SQL Server 实例以来收集的统计信息。示例结果将显示在图 2 中。

Figure 2 查看数据库上的 I/O 统计信息

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

了解对基本数据文件的 I/O 影响可以帮助您更好地计划数据卷上文件和文件组的物理位置、检测可能的 I/O 瓶颈、执行文件级数据库维护及其他此类任务等。该功能对于检查大型数据库(其中可能有多个文件和文件组)的 I/O 影响非常有用。

显示 SQL Server 2000 的文件 I/O 信息的查询如下所示:

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

要查看特定的 databaseID,请传递数据库的 ID,如下所示:

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

显示服务器上所有数据库的文件统计信息的 SQL Server 2005 代码如下:

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

以下查询只返回当前数据库的文件统计信息:

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

对于 SQL Server 2005,还有一个称为 sys.dm_io_virtual_file_stats 的新系统函数,旨在最终取代原有函数 fn_virtualfilestats:

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

以下是该函数的使用方法:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

如果您要生成更具可读性的报告,其中显示输出中的实际数据库名称和文件名称,您可以使用 SQL Server 2000 或 SQL Server 2005 上附带下载中的代码。这些代码可以从 TechNet 杂志网站获得

问:我需要查看删除事务是否触发触发器的简单方法。我应该如何获得该信息?

答:当处理与删除、插入和更新操作相关的触发器时,人们通常使用许多不同的技术来确定触发器是否由删除事务触发。常用的方法是比较插入和删除虚拟表的统计以查看它们是否一致。然而,有一种更简单的方法:使用 Columns_Updated 函数。

这是因为删除事务触发触发器时,Columns_Updated 总是返回 varbinary 值 0x。以下检查将验证触发器是否由删除触发:

IF Columns_Updated() = 0x

提示:初始化数据和日志文件

您知道初始化数据和日志文件会覆盖以前删除的文件中保留在磁盘上的任何现有数据吗?创建数据库时,首先通过用零填充文件来初始化数据和日志文件;将文件、日志或数据添加到现有数据库;增加现有文件的大小(包括自动增长操作);或还原数据库或文件组。文件初始化导致这些操作花费更长的时间。但是,当数据第一次写入文件时,操作系统不必用零填充文件。

在 SQL Server 2005 中,数据文件可以即时初始化。该功能允许快速执行前面提到的文件操作。即时文件初始化会回收已用磁盘空间而不必用零填充该空间,而在将新数据写入文件时,磁盘内容将被覆盖。日志文件不能即时初始化。即时文件初始化只在 Windows XP Professional 和 Windows Server 2003 或更高版本上可用。

因为删除的磁盘内容只在将新数据写入文件时覆盖,所以删除的内容可能会由未经授权的主体访问。尽管数据库文件附加到了 SQL Server 的实例,但文件上的自由访问控制列表 (DACL) 减少了这种信息泄露威胁。该 DACL 只允许对 SQL Server 服务帐户和本地管理员的文件访问。但是,当分离文件时,不具有 SE_MANAGE_VOLUME_NAME 的用户或服务可能会访问它。备份数据库时,类似的威胁也会存在。如果备份文件没有获得适当的 DACL 保护,则删除的内容可能会由未经授权的用户或服务获得。

如果需要防止公开已删除的内容,则您应该始终确保任何分离的数据文件和备份文件都具有限制性的 DACL。另外,请通过从 SQL Server 服务帐户吊销 SE_MANAGE_VOLUME_NAME 禁用 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 与 CMP Media, LLC.保留所有权利;不得对全文或部分内容进行复制.