sql server 磁盘瓶颈

1.确认磁盘瓶颈

PhysicalDIsk--Avg.Disk Queue Length 这个计数器报告了每个磁盘的队列长度，如果队列长度持续大于2，则可能会影响到性能。

PhysicalDisk--Avg.Disk sec/Read和Avg.Disk sec/Write 表示每次读出或写入的平均磁盘时间，如果存在HBA队列深度，控制器使用率或性能等问题，那么将会看到这两个值高于正常值，理想情况下值应小于10ms.

SQL Server:Access Methods--Full scans/sec这个计数器给出了每秒请求完全索引扫描或完全表扫描的数目。如果发现扫描平率每秒大于1，则可能是索引较差或较少的指示。

 SQL Server:Access Mehtods--Page Splits/sec Page Splits 是一个I/O密集型的操作，他发生在8KB的数据页中没有足够的空间完成一个插入或者更新操作，可以适当的索引维护以及选择合适的填充因子避免他们的发生。

基于配置的磁盘瓶颈

a.数据文件和日志文件布局

  可以将数据文件和日志文件存放在不同的LUN上。

b.数据文件和日志文件的自动增长

合理的设置数据文件和日志文件的扩展值，可以预防一些严重损害性能的大的数据文件增长。

c.Tempdb配置

   SQL Server在连接，排序，计算等操作的过程中将tempdb作为等候区，同时还将它用于版本存储。正确的指定Tempdb的初始值，可以避免TEMPDB的增长。TEMPDB被所有数据库共享，有时候TEMPDB自身会成为一个资源瓶颈，解决此问题的办法是为每个CPU创建一个数据，使CPU能够循环访问TEMPDB文件，以达到比使用单独数据文件更好的性能。

d.TEMPDB内部争用

Tempdb是临时的，只存在于SQL Server服务运行过程中。TEMPDB是一个全局共享资源，意味着如果一个数据库严重依赖于TEMPDB，相同实例中的数据库可能会遭受性能问题，而且这种情况不由他们控制。下面的计数器可以显示TEMPDB 的工作负荷。

SQL SERVER:General Statistics-Temp Tables Creation Rate:显示每秒钟创建的临时表或临时变量的数目。

SQL SERVER:General Statistics-Temp Tables For Destruction:显示不再需要，正在等待由执行清理工作的系统线程销毁的临时表数目。

以下SQL 查询，可以确定每个服务器进程所使用的TEMPDB空间

select tsu.session_id,
       tsu.request_id,
       tsu.alloc_count,
       er.sql_handle,
       er.statement_start_offset,
       er.statement_end_offset,
       er.plan_handle
from (
     select session_id,
            request_id,
            sum(internal_objects_alloc_page_count) as alloc_count,
            sum(internal_objects_dealloc_page_count) as dealloc_count
      from sys.dm_db_task_space_usage
     group by session_id,request_id
      )as tsu
      ,
    sys.dm_exec_requests as er
where tsu.session_id=er.session_id and tsu.request_id=er.request_id
order by tsu.alloc_count desc

e. 磁盘/控制器争用

这个需要硬件厂商提供专业的评估工具。

基于模式的磁盘争用

a.索引不当或缺少索引

b.大表适当的分区，可以将I/O分不到多个分区中

c.索引/表破碎

索引碎片定时整理