请教数据库的"等待类型"

大方向往小方向走了:    1:如果是多个磁盘,先找到磁盘IO最多的.
     2:再找到IO等待,占用最多的库以及库文件.
    3:接着就细化到对象了,这个只有PROFILE去跟踪和统计.
........等等
这个东西,综合技能要求特别高,我也没有很好的具体的一些步骤代码.如果是2005,有很多动态视图关于IO的可以查和统计,2000就不太好弄,就只能PROFILE.

WITH DBIO AS
(
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_Id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id),MF.type
)
SELECT
*,
ROW_NUMBER() OVER(ORDER BY io_stall_pct DESC) AS rn
FROM(
SELECT
db,
file_type,
CAST(1. * io / (1024 * 1024) AS DECIMAL(12,2)) AS io_mb,
CAST(io_stall / 1000. AS DECIMAL(12,2)) AS io_stall_s,
CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10,2)) AS io_stall_pct
FROM DBIO
)A
WHERE io_stall_pct > 1
AND db NOT IN('msdb','master','distribution','tempdb')
ORDER BY io_stall_pct DESC--给你一个代码，如果你是在2005下,这个可以查出各数据库文件IO读写,等待的统计
--当然很有很多你需要去检查的,这个工作复杂性太高,还是要根据具体的环境来应变和判断,说也说不清楚.

谢谢各位的指教,忘了说环境了补充如下:sql2000\4CPU\4G内存\一个硬盘

除了上面几位的，看了：sql2000\4CPU\4G内存\一个硬盘估计你的磁盘会有瓶颈。可以抓一下perfmon ，参考一下微软的推荐值：磁盘 PhysicalDisk Avg. Disk sec/Read 磁盘读操作平均延迟时间（秒） <0.008
PhysicalDisk Avg. Disk sec/Write 磁盘写操作平均延迟时间（秒） <0.008

另外， 4 G 内存也有可能瓶颈，是否打开 /3gb 了？看看 perfmon 的Available Mbytes 系统总可用物理内存（MB）是否 <100你的300分我要定了，嘿嘿

数据库并发比较多，查到主要等待事件如下：write log、PAGELATCH_SH、PAGELATCH_EX事件。当发生这些事件时，用windows自带的“性能监视器”检测到操作系统的物理IO满负荷，此时系统操作响应很慢（点击电脑短时间内没反应，而不出现这些等待事件时点击电脑是很快的），1. 是否这个问题一直存在？还是你最近有哪些改动，比如硬件，网络，补丁， batch request 等。2. 问题发生时，是否有其他问题，比如是否cpu 也高？ 3. 是否缺乏必要的index 等造成大量io？需要你抓profiler trace 来进一步分析哪些语句造成的

---------------------
1、这个问题在系统业务量较小时，不会存在这样的问题，当随着业务量大，并发多了后才出现；至于“改动”方面人为的没有；数据库补定是SP3。2、当问题发生时，cpu利用率很低（即CPU繁忙率30%左右）3、很多表基于查询的需要，都已经建立了对应索引。

另外请教下：用探看器跟踪到 write log日志很频繁，在oracle里面可以用批量提交事务的方法来减少频繁日志交换写，不知道SQL SERVER里面是否有类似这样的优化处理方法呢？

楼主你再关注一下blocked ,waittime、WaitResources、status、phyiscal_io 这几个字段当出现阻塞时，这几个字段的值是否在发生变化.查到最终引起阻塞的那个SPID, 它到底是在等待什么资源.这样才好下结论。

你的数据库模式是否有设成"Simple"呀?

你说的事件都是与磁盘I/O有关的
你只有一个硬盘
如果你不增加硬盘，唯一的办法是减少I/O操作
可能不前只有一个有效方法，把你的恢复模式改成简单，用个job调度好备份

呵呵,只能是自己注意缩小事务的范圍咯。若要对大数据量的table做操作，就分批次进行像.这种事务也会分批提交的.SET ROWCOUNT 5000
DELETE TABLE WHERE 条件...
WHILE @@ROWCOUNT>0
DELETE TABLE WHERE 条件...
SET ROWCOUNT 0另
simple模式下，日志若涨到70%的话会截斷的。
碰到阻塞时，手动执行一下 CHECKPOINT 看有没效果.------
以上都是些边角料料，最主要还是要找到引起阻塞的SPID所等待的资源.

你的这些等待是由事务和硬盘的瓶竟引起的.
一般情况下,对过程的优化有时会收到一些效果,不过,最重要的还是事务日志和TEMPDB上,只要将SQLSERVER的日志文件移到一个独立的磁盘中,把TEMPDB移到另一个独立的磁盘中,这种等待的时间将会减少很多.一.移动TEMPDB方法.--确定逻辑路径名。
use tempdb
go
sp_helpfile
go
use tempdb
go
sp_helpfile
go--移动 TEMPDB ，重启SQLSERVER会删除旧文件。
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\SqlServerTempdb\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\SqlServerTempdb\templog.ldf')
go
二.移动SQLSERVER日志文件的方法.首先，右击企业管理器中的SQL Server并选择属性。然后，单击开始参数如图A所示。正如我们在图A中所看到的，有以下参数出现在对话框中：-d 是指保存master数据库数据文件的路径全名。-e 是指保存错误日志文件的路径名。-l 是指保存master数据库日志文件的路径全名。现在，如果你要移动文件，那么需要移除当前入口并利用正确的路径重新创建新入口。例如，我们打算将Master数据库日志文件移动到（C:Test）。这样的话，我们将会通过突出高亮显示旧参数并选择移除来删除-l [path]（如图B所示）。然后，我们将会添加下面的入口（如图C）-l (c:Testmastlog.ldf)并单击两次确定。现在，将会停止SQL Server并将mastlog.ldf移动到新位置。注意：请务必确保将mastlog.ldf移动到在开始参数中配置的位置。如果移动到了一个未在开始参数中指明的位置，SQL Server将不能启动。最后，开启SQL Server,你已经成功地移动了Master数据库.

看了这么多，感觉还是memory ，或者 i/o 有瓶颈

还有,你试一下用一个CPU(第四个) 工作看看.

1.首先你说的“物理IO满负荷”，指的那些计数器，具体的值是多少？
2.你反映的“此时系统操作响应很慢”，更像是CPU负荷较重时的状态。
3.你列出的主要等待类型中的：PAGELATCH_SH，PAGELATCH_EX 并不是IO相关的典型等待，这类等待主要是
Buffer Pool中页的访问冲突，不涉及物理IO，物理IO相关（典型）应该是这样一类PAGEIOLATCH_*等待。
要解决这一类等待(PAGELATCH_*)，必要的时候要去分析，到底是那些页上的等待（即：这个页是那个表或者是索引所拥有的）
这样有助于你分析出，为什么会在这些页上有较重的访问冲突。
4.你的数据库规模如何？当前的硬件配置是否能胜任工作负荷？有建立性能基线嘛？
5.在出现性能问题时，执行这两个语句DBCC SQLPERF(WAITSTATS) ，DBCC MEMORYSTATUS
可以对当前的数据库状态有个了解，也有助于分析问题（最好能和基线做比较）。当然还有一些深入到session（request,task）级分析，不过前提是你已经对发生的问题有了初步判断了。

楼主，需要找几本SQL Server Tuning相关的书或者是资料，来熟悉一下Tuning的基本方法论。先推荐两个
1.SQL Server Inside 系列（7.0,2k,2k5）都是值得一看的。
2.胡百敬的关于SQL Server效能调校的书

一句话，磁盘是瓶颈，建议上2块硬盘做RAID0再上另一块单独硬盘专门坐备份。或者直接上RAID5也可以。
单块硬盘明显影响了你的事务和程序的读写

还是抓一些perfmon 数据看看，这些信息差远了

我猜是是服务器内存资源耗光光了，因为大量阻塞，导至每个spid都持有大量的锁，都在等待。我之前有个客户就是这样，sp_lock查出二十多万条记录，服务器几乎动弹不得。

抓以下即可：CPU Processor % Processor Time
内存 Memory Available Mbytes
磁盘 PhysicalDisk Avg. Disk sec/Read
PhysicalDisk Avg. Disk sec/Write
SQL批处理统计 SQLServer:SQL Statistics Batch Requests/Sec
SQLServer:SQL Statistics SQL Compilations/sec
SQL Server缓存 SQLServer:Buffer Manager Lazy writes/sec
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Page reads/sec

问题出现之前，和出现时，各抓最少1小时

另外，建议profiler 也至少同时抓1小时。

调试易

请教数据库的"等待类型"

解决方案 »