sql2008 等待资源类型Buffer I/0和Logging 数据量，是什么性能瓶颈？

Question

资源等待

Answer 1

这个等待类别只是大类，最好找到具体的子类别。这种等待很有可能是闩锁等待，比如：PAGEIOLATCH_SH
PAGEIOLATCH_UP
PAGEIOLATCH_EX或者是IO_COMPLETION

Answer 2

楼主机器是32位还是64位机？
Buffer IO还是与Buffer Pool的大小有关系

Answer 3

谢谢各位了。 我现在就两块盘，而且是做的raid1，所以tempdb 还有自己用的数据库都在一个盘上。
而且我的盘是sata的。我的机器是64位的。windows server 2008，装的是data center，数据库也是64位的 。
内存是4G。

Answer 4

可能是4G不够吃，数据需要等待，你看看性能计数器里面的life page ex啥啥的那个，看看会不会持续很低的值，并且时间很久。300S是微软建议，不过不要随意用，一般来说3000s比较好

Answer 5

内存4G好像有点小了，你不会用普通的pc把，我的笔记本电脑都有6G内存呢

Answer 6

我也觉得有点小，还有一个服务器，内存是32G的，还没到。领导让我想办法改善，说万一用了32G，你的sq还不行呢。

Answer 7

给各位说一下我的应用场景：有三个表 
auth_today ,存放当天数据。这个数据不是太固定，可能会从10几万到30-40万。
auth_area，存放单区域数据，现在有15个单区域的这种表，分别存放15个区域的，当前的数据量是从12万到360万左右。
auth_info，存放所有区域数据。目前是1500万记录。因为有三个应用，要分别从这单个表查询数据。还有一个应用，要给这三个表（另加一个日志表）写入数据，我上面发的资源等待的，就是把查询的停掉后，只允许写入的，出现的问题。写入是用的存储过程，先判断有没有记录，有的话update，没有的话insert。共查询4个表，写入4个表。
我现在的速度是慢的时候一秒都小于10条，快的时候可以100多。

Answer 8

对的。xe_dispatcher_wait 是在用于扩展事件会话的后台线程等待事件缓冲区进行处理时发生。你应该是用了这个扩展事件把。另外，checkpoint_queue 和 logmr_queue 是写藏数据，和日志的，这个当检查点任务正在等待下一个检查点请求时出现，也没什么问题

Answer 9

上面的都是总的等待，你可以看看top 语句，比如运行时间最长的语句，可以考虑对这些语句进行优化。

Answer 10

你order by wait_time_ms那列看看

Answer 11

这个是从那个视图里查一下？--SQL Server启动以来累计使用CPU资源最多的语句。  
select   
    highest_cpu_queries.*,  
      
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,  
     q.[text]  
from   
(  
    select top 10 qs.*  
    from sys.dm_exec_query_stats qs  
    order by qs.total_worker_time desc  
) as highest_cpu_queries  
  
cross apply sys.dm_exec_sql_text(plan_handle) as q  
--where text like '%%'  
order by highest_cpu_queries.total_worker_time desc  

Answer 12

虽然不能单靠一个值来评估问题，不过从你的描述来说，内存是有问题了，这个值是说数据页缓存在buffer中的时间，内存足够的时候，当然越久越好，可以避免频繁滴从磁盘加载数据到内存或者从内存写入数据到磁盘。减少这部分的I/O等待。优化的话，最快的手段当然就是增加内存，4G的确少了。而且还要留给OS用。如果内存一时间没办法处理，那就只能优化代码，减少不必要的扫描。加索引等等。

Answer 13

这个是从那个视图里查一下？--SQL Server启动以来累计使用CPU资源最多的语句。  
select   
    highest_cpu_queries.*,  
      
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,  
     q.[text]  
from   
(  
    select top 10 qs.*  
    from sys.dm_exec_query_stats qs  
    order by qs.total_worker_time desc  
) as highest_cpu_queries  
  
cross apply sys.dm_exec_sql_text(plan_handle) as q  
--where text like '%%'  
order by highest_cpu_queries.total_worker_time desc  我用这个查询，前10个里面怎么有我程序调用的那个存储过程，好几个create procedure ？

Answer 14

这个是从那个视图里查一下？--SQL Server启动以来累计使用CPU资源最多的语句。  
select   
    highest_cpu_queries.*,  
      
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,  
     q.[text]  
from   
(  
    select top 10 qs.*  
    from sys.dm_exec_query_stats qs  
    order by qs.total_worker_time desc  
) as highest_cpu_queries  
  
cross apply sys.dm_exec_sql_text(plan_handle) as q  
--where text like '%%'  
order by highest_cpu_queries.total_worker_time desc  我用这个查询，前10个里面怎么有我程序调用的那个存储过程，好几个create procedure ？修改一下，找每次执行，需要最长时间的：
--SQL Server启动以来累计使用CPU资源最多的语句。  
select   
    highest_cpu_queries.*,  
      
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,  
     q.[text]  
from   
(  
    select top 10 qs.*  
    from sys.dm_exec_query_stats qs  
    order by qs.total_worker_time desc  
) as highest_cpu_queries  
  
cross apply sys.dm_exec_sql_text(plan_handle) as q  
--where text like '%%'  
order by highest_cpu_queries.total_worker_time/execution_count  desc  

Answer 15

应该是交易频繁导致日志没有及时的刷新到磁盘，看一下放LOG的磁盘 读写速度是否有瓶颈。另外是否真的有这么大的频繁交易导致日志量很大。

Answer 16

另外，在问各位一个问题：
做update的时候，如果一个表有几千万数据，怎么才能让update的读次数少一些？

Answer 17

可以少量的更新，避免锁升级，同时可以减小Transcation的量。

Answer 18

观察等待事件,你要掌握一个原则, 任何关系型数据库的等待事件都是常态的,是数据库的正常表现.  这句话的意思是: 你上图排在首位的一些等待事件不需要特别关注,例如: lazywriter_sleep, request_for_deadlock_search, sqltrac_incremental_flush_sleep, FT_IFTS_SCHEDULER_IDLE_WAIT, LOGMGR_QUEUE etc. 如果你在任意的数据库上,用同样dmv语句查询,它们都会排在top 10里. 除非一种情况它们和某些特定的等待事件同时出现. 例如LOGMGR_QUEUE和ASYNCH_IO_COMPLETION， IO_COMPLETION，WRITELOG，AGEIOLATCH_X同时出现。你的情况是大量的INSERT,  那么我很快注意到 WRITELOG, PAGEIOLATCH_EX, ASYNC_IO_COMPLETION 都排在很前面, . 应为SQL 必须保证预写日志的成功,再去从buffer pool flush data into disk. 看了下你的writelog, 平均需要等待cpu running 6ms, 最大需要6s, 而你一次insert 写才0.12ms ，说明 cpu 够快（或者利用到了并行cpu),  瓶颈在写日志上的IO。 用perfmon观察下Disk QUeue Length， 将log file 放到单独的磁盘，如果是虚拟机，必须制定独立的log LUN. 另外你的life page expentancy(PLE) 330 s ,  小了点，说明内存不足。可能也是瓶颈之一。微软建议 厥值300 以下有 SQL Buffer Pool压力， 你的这个值很接近这个微软建议厥值了。 更何况现在多数系统的PLE厥值 =  SQL 最大内存(MB)/1024/4*300 ，已经不用微软建议了，会更大。另外我还会检查下CPU的并行，我相信你的 SQL 肯定启动了DOP（默认），但是微软有个undocumented的DOP设置，貌似是小于 1ms (1ms 是假设，具体的忘记)， SQL不会用多cpu core的，是单core 运行。我从前遇到的案例就是百万级别的cursor insert, insert单条很快，但整体 运行很慢，结果是没有利用16 core cpu, 每次运行单cpu core. 微软建议改写代码，批处理或者SSIS,要么就是增大你的cpu 频率

Answer 19

在update的key 上 (where, join, on ) 加 non cluster index, covered index 最大限度的减少逻辑读。另外update 的 column 千万别建立cluster index