转载地址:http://blog.csdn.net/leamonjxl/article/details/6790832
SQL Server里面4步定位性能问题的理论,具体如下:
1,资源瓶颈
i. 内存 ii. CPU iii. IO
2, Tempdb瓶颈
3,找出执行慢的语句,可以通过三个方面来寻找
i. 统计信息 ii. 缺失索引 iii. 阻塞
4,缓存执行计划分析
可以看到,一遇到系统性能问题时,第一步是确定资源是否存在瓶颈,在CPU,内存,IO 三者之间,最容易形成瓶颈的是IO子系统。其实IO子系统的内涵是很深的,能够影响IO子系统性能的因素有磁盘的数目,大小,和转速;文件分配单元大小(file allocation unit size);HBA;网络带宽;磁盘缓存;控制器;是否使用SAN(storage area networks);RAID级别;总线速度;IO通道等等。
作为SQL Server的使用者,通常很少会去调整IO子系统的配置,一则重视不够,二是缺少这方面的相关知识和技能。但了解这方面的相关问题还是很有必要的,除了可以更好发挥硬件的作用外,在碰到系统性能问题时,也能很好的进行定位分析。
SQL Server 引擎有自己的磁盘IO内部管理机制。理解SQL Server的IO处理机制是很有必要的。微软有2部非常好的白皮书,叫《SQL Server I/O Basics Chapter 1》《SQL Server I/O Basics Chapter 2》,对此进行了深入阐述,如果对这方面有兴趣的朋友,是不能错过。但只有英文版,两份加起来有100多页左右。下面对SQL Server IO的一些要点进行简要阐述,更多详情,参考这两份白皮书吧。
同步I/O指的是I/O API 会等待I/O请求完成后,才进行下一个处理;异步I/O指的是I/O API只需发出I/O请求,然后继续处理其他内容,并在一会之后回头查看该I/O是否已经完成。
SQL Server 上98%使用的是异步I/O,这允许SQL Server在写入或者读取一个页之后继续有效的使用CPU和其他资源。Windows平台处理异步I/O是使用了OVERLAPPED这个结构来保存相关的I/O信息,并使用HasOverlappedIOCompleted来标识I/O是否已经完成。在SQL Server 2005后引入了sys.dm_io_pending_io_requests 这个动态管理视图,其中的IO_PENDING列与HasOverlappedIOCompleted对应。
Scatter / Gather I/O
在SQL Server 2000以前,SQL Server的checkpoint要将buffer pool的脏数据页写入磁盘时,需要维护一个脏数据页的列表,然后按顺序的写入脏数据页,因此如果某个页在写入时遇到I/O问题,则会引起整个checkpoint的性能下降。因此SQL Server 2000以后引入了Scatter/Gather I/O的方式,Scatter 是指从磁盘往内存读取数据页时,不用在内存分配连续的页,可以将页分布在buffer pool里不同的地方,通过调用ReadFileScatter这个API来实现的;Gather指的是从内存往磁盘写数据页时,不必维护之前的那种脏数据页列表,而是在扫描整个buffer pool后,直接将脏数据页写入磁盘某块连续区域,通过调用WriteFileGather这个API来实现。很明显可以看到这种处理方式更为有效,不仅应用在SQL I/O路径上,还应用在Page File 上。
Lazywriter是一个定期扫描buffer pool来检查free list 大小的线程。在SQL Server 2008后,引入了TLA(TIME LAST ACCESS)算法,这个算法是对LRU的改进。Lazywriter根据该算法对标记为脏页的页进行判断,如果已经过时(aged),则调用WriteMultiple将相关dirty page写入磁盘。
稀疏文件主要用于在线DBCC和快照数据库中。稀疏文件一般情况下实际占用空间远小于文件大小值。在创建snapshot数据库时,会伴随着copy on write的行为,copy on write 指的是当一个页将要被写入内容时,会发生一个检查,确定该页是否被copy到了snapshot数据库,如果没有,则在该页被改变之前会被写入到snapshot数据库上,从而保证snapshot的内容一直为某一时刻的。为了维护snapshot数据,在parent库上会有file control block chaining(FBCs)来管理snapshot与parent的对应关系,这样copy on write就能快速定位。
Scribbler(s)
Scribbler寓意小孩在图片的线框外乱涂颜色,表示一个组件在内存中的不属于它的区域改变数据。这会造成数据损坏。在SQL Server 2000中,为了防止这种行为的出现,引入了Torn page的校验机制;而在sql server 2005后,还引入checksum机制。
如果page_audit设置为checksum时,lazywriter会检查内存中的页,并重新计算页上的checksum值,如果值不一致,就会纪录错误并将该页直接从内存中消除,这就表明发生了一次“scribbler”。追踪页“scribbler”是比较困难的,但有trace flag –T831 可以开启,从而获取更详细的内容。
页的校验是SQL Server IO上很重要的一个内容,更多内容可以参考SQL Server IO basic 这份白皮书。
可以看到SQL Server 提供了丰富的内部I/O管理机制。理解这些概念,也就能更好的理解SQL Server的工作机制,在碰上一些内部错误或者I/O子系统设置时便能应付自如。更多详情请参阅SQL Server IO basic 白皮书。
在SQL Server上,涉及I/O的计数器有:
Average read bytes/sec,平均每秒的读取字节数
Average write bytes/sec,平均每秒的写入字节数
Reads/sec, 每秒多少读
writes/sec,每秒多少写
Disk read bytes/sec, 每秒读取的传输速率
Disk write bytes/sec,每秒写入的传输速率
Average disk sec/read, 平均每个读花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题;
Average disk sec/write,平均每个写花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题。
Average disk queue length,在磁盘等待的IO个数,最好在1.5和2倍转轴个数(spindles)以内,如果高于这个值,一般说明IO子系统有一定的问题,但现在普遍使用RAID或者磁盘虚拟化,如果不详细了解具体的I/O子系统配置,很难从这个计数器判断出问题。
C:\>diskpart
Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: ASPIRINGGEEK
DISKPART> list disk
Disk ### Status Size Free Dyn GPT
-------- ---------- ------- ------- --- ---
Disk 0 Online 186 GB 0 B
Disk 1 Online 100 GB 0 B
Disk 2 Online 120 GB 0 B
Disk 3 Online 150 GB 150 GB
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait
更多详情,可以参考白皮书《Disk Partition Alignment Best Practices for SQL Server》http://msdn.microsoft.com/en-us/library/dd758814.aspx
C, 其他最佳实践
有很多这样的文章,如
《Storage Top 10 Best Practices》http://msdn.microsoft.com/en-us/library/cc966534.aspx
《Physical Database Storage Design》http://technet.microsoft.com/en-us/library/cc966414.aspx
里面会有一些关于I/O方面的设置指南,值得参考。