问题的诊断分,性能调优

时间:2019-10-05 00:22来源:江苏十一选五手机版数据库
一. 概述 sql server作为关系型数据库,供给进行数量存款和储蓄,那在运转中就能不停的与硬盘实行读写交互。假使读写不能科学快捷的产生,就能油可是生品质难点以及数据库损坏难点

一. 概述

 sql server作为关系型数据库,供给进行数量存款和储蓄, 那在运转中就能不停的与硬盘实行读写交互。假使读写不能科学快捷的产生,就能油可是生品质难点以及数据库损坏难点。上边讲讲引起I/O的发出,以及剖判优化。

一. SQL Server 哪天和磁盘打交道:

 

二.sql server  主要磁盘读写的作为

  2.1  从数据文件(.mdf)里, 读入新数据页到内部存款和储蓄器。前页陈说内部存款和储蓄器时我们通晓,如若想要的数码不在内部存款和储蓄器中时,就能够从硬盘的数据文件里以页面为最小单位,读取到内部存储器中,还包罗预读的多少。 当内部存款和储蓄器中留存,就不会去磁盘读取数据。丰硕的内部存储器能够最小化磁盘I/O,因为磁盘的速度远慢于内部存储器。

  2.2  预写日志系统(WAL),向日志文件(.ldf)写入增加和删除改的日志记录。 用来维护数据业务的ACID。

  2.3  Checkpoint 检查点发生时,将脏页数据写入到数据文件 ,在sp_configure的recovery interval 调节着sql server多长时间进行三回Checkpoint, 要是日常做Checkpoint,那每一次爆发的硬盘写就不会太多,对硬盘冲击不会太大。借使隔长日子贰次Checkpoint,不做Checkpoint时品质或者会不慢,但累积了多量的修改,大概要产生大批量的写,那时质量会受影响。在大部据气象下,暗中同意设置是相比较好的,没要求去修改。

  2.4   内部存款和储蓄器不足时,Lazy Write产生,会将缓冲区中期维修改过的多少页面同步到硬盘的数据文件中。由于内部存款和储蓄器的长空不足触发了Lazy Write, 主动将内部存款和储蓄器中非常久未有选取过的数据页和执行安插清空。Lazy Write常常不被平时调用。

  2.5   CheckDB,  索引维护,全文索引,计算消息,备份数据,高可用一块日志等。

  1. SQL 须要拜会的数额未有在Buffer pool中,第一次访谈时索要将数据所在的页面从数据文件中读取到内部存款和储蓄器中。(只读)

  2. 在insert/update/delete提交从前, 要求将日志记录缓存区写入到磁盘的日记文件中。(写)

  3. Checkpoint的时候,供给将Buffer pool中曾经发生修改的脏数据页面同步到磁盘的数据文件中。(写)

  4. 当Buffer pool空中不足的时候, 会触发Lazy writer, 主动将内部存款和储蓄器中的局地相当久未有运用过的数量页面和实行布置清空。假诺这个页面上的更换还并未有被检查点写回硬盘, Lazy writer 会将其写回。(写)

  5. DBCC checkDB, Reindex, Update Statistics, database backup等操作, 会带来非常大的硬盘读写。(读/写)

 

三. 磁盘读写的连锁解析

  3.1 sys.dm_io_virtual_file_stats  获取数据文件和日志文件的I/O 总计音讯。该函数从sql server 二〇〇九起来,替换动态管理视图fn_virtualfilestats函数。 哪些文件平时要做读num_of_reads,哪些常常要做写num_of_writes,哪些读写通常要等待io_stall_*。为了获取有意义的多寡,供给在长时间内对那么些多少开展快速照相,然后将它们同基线数据相相比较。

SELECT  DB_NAME(database_id) AS 'Database Name',
        file_id,
        io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
        io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0 

  io_stall_read_ms:客户等待文件,发出读取所用的总时间(纳秒)。

  io_stall_write: 客商等待在该公文中成就写入所用的总时间阿秒。

  江苏十一选五手机版 1

  3.2  windows 质量计数器:  Avg. Disk Sec/Read 这几个计数器是指每秒从磁盘读取数据的平均值

< 10 ms - 非常好
 10 ~ 20 ms 之间- 还可以
 20 ~50 ms 之间- 慢,需求关怀
> 50 ms –严重的 I/O 瓶颈

  3.4  I/O  物理内部存款和储蓄器读取次数最多的前50条

 SELECT TOP 50
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 qs. creation_time,
 qs.max_elapsed_time,
 qs.min_elapsed_time,
 SUBSTRING(qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset=-1
 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 qt.dbid,dbname=DB_NAME(qt.dbid),
 qt.objectid,
 qs.sql_handle,
 qs.plan_handle
 from sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_physical_reads DESC

 3.5 使用sp_spaceused查看表的磁盘空间

  exec sp_spaceused 'table_xx'

江苏十一选五手机版 2

reserved:保留的上空总的数量
data:数据选择的半空中总量
index_size:索引使用空间
Unused: 未用的空间量

 3.6  监测I/0运转情状 STATISTICS IO ON;

 

目录

 四  磁盘读写瓶颈的病症

  4.1  errorlog里告知错误 833

  4.2  sys.dm_os_wait_stats 视图里有恢宏等候景况PAGEIOLATCH_* 或 WriteLog。当数码在缓冲区里从未找到,连接的等待状态正是PAGEIOLACTH_EX(写) PAGEIOLATCH_SH(读),然后发起异步操作,将页面读入缓冲区中。像 waiting_tasks_count和wait_time_ms相比高的时候,平时要等待I/O,除在映未来数据文件上以外,还会有writelog的日志文件上。想要获得有含义数据,供给做基线数据,查看感兴趣的时刻距离。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' 
order by wait_type

  wait_type:等待类型
  waiting_tasks_count:该等待类型的等待数
  wait_time_ms:该等待类型的总等待时间(包蕴五个历程悬挂状态(Suspend)和可运营状态(Runnable)开支的总时间)
  max_wait_time_ms:该等待类型的最长等待时间
  signal_wait_time_ms:正在等候的线程从接受随机信号公告到其开端运维之间的时差(一个历程可运行处境Runnable花费的总时间)
  i/o等待时间==wait_time_ms - signal_wait_time_ms

二. 哪些SQL 配置会对I/O有震慑:

规定思路... 1

   五  优化磁盘I/O

   5.1 数据文件里页面碎片整理。 当表发生增加和删除改操作时索引都会生出碎片(索引叶级的页拆分),碎片是指索引上的页不再抱有大要接二连三性时,就能够爆发碎片。比方您询问10条数据,碎片少时,恐怕只扫描2个页,但零星多时大概要扫描越来越多页(后边讲索引时在前述)。

   5.2 表格上的目录。比如:建议各类表都包涵集中索引,这是因为数量存款和储蓄分为堆和B-Tree, 按B-Tree空间占用率更加高。 丰盛应用索引减少对I/0的须要。

   5.3 数据文件,日志文件,TempDB文件建议贮存不一样物理磁盘,日志文件放写入速度一点也不慢的磁盘上,比如RAID 10的分区

        5.4 文件空间管理,设置数据库增加时要按一定大小增进,而不能够按百分比,那样制止二遍升高太多或太少所推动的不需要麻烦。提议对十分小的数据库设置一次提升50MB到100MB。下图展现假设按5%来加强近10G, 假若有一个应用程序在品尝插入一行,不过未有空间可用。那么数据库恐怕会起来升高多个近10G, 文件的增加大概会耗用太长的日子,以致于客商端程序插入查询失败。

  江苏十一选五手机版 3

       5.5 幸免自动缩短文件,假使设置了此作用,sql server会每隔半钟头检查文件的选取,若是空闲空间>百分之三十三,会活动运营dbcc shrinkfile 动作。自动裁减线程的会话ID SPID总是6(未来可能有变) 如下展现自动减弱为False。

     江苏十一选五手机版 4

     江苏十一选五手机版 5

   5.6 假如数据库的恢复格局是:完整。 就须求定期做日志备份,幸免日志文件Infiniti的增高,用于磁盘空间。

    

     

1. ( Recovery Interval, 默认60秒)(Checkpoint pages/sec + Avg. Disk Queue Length + Batch Requests/sec) 

wait event的基本troubleshooting. 1

2. 数据文件和日志文件的活动增进和活动收缩。对于扭转数据库,要制止自动增加和自动收缩。

虚构文件音讯(virtual file Statistics)... 3

  1. 数据文件中的页面碎片程度 (Clustered index) :  dbcc showcontig('table_name') -- avg. Page Density(full)  碎片多,读取/写入的页面多(set statistics io on -- logical reads)

  2. 表上的目录结构: 聚集索引的表和堆表的存储管理不一样。

  3. 数据压缩: 能够减小I/O, 但会消耗CPU和内部存款和储蓄器能源。

天性目标... 4

6. 数据文件和日志文件分别位于不一样的硬盘上,日志要放在写入速度不慢的硬盘上, 如RAID10

实行安插缓冲的采取... 8

7. 数据文件能够有多少个分级放到区别硬盘上的文书, SQL server会将新数据遵照同贰个文本组的各种文件剩余空间的大大小小, 按百分比写入到持有有多余空间的文件中。  而日志文件则分歧, 在三个时间点只会写三个日志文件。 所以在不相同的硬盘上建日志文件对质量未有何样帮助。

总结... 9

 

 

三. 操作系统I/O难题的检查判断:

性子调优很难有贰个固定的辩白。调优本来就是拍卖部分特有的品质难点。

  1. 在认清SQL I/O难点在此以前,先看看Windows层面I/O是或不是符合规律。 借使很忙,再确认是或不是SQL产生的。

  2. LogicalDisk and PhysicalDisk: 

通常说来假如得到多少个服务器那么就先做一下属性检查。查看全数数据库是运作在什么的情形下的。

  %idle time: 

浅析搜集的数据想像这种情状是还是不是创制。

  %disk time: = %disk read time + %disk write time

分明思路

一个数据库操作的时光都以进行时间+等待时间,在不恐怕推测试行时间的时候看要探问等待时间。

那么等待时间分为锁等待时间和财富等待时间。

那正是说就先用 sys.dm_os_wait_stats动态性能视图,查看首要的情景。若是pageiolatch_sh等待不小,那么就印证,session在等候buffer pool的页。当二个session要select一些数目,可是刚刚好,这几个多少并从未在buffer pool 中,那么sql server 就能够分配一些缓存那么些缓存是属于buffer pool 的,用来贮存在从磁盘读收取来的数据,在读取的时候都会给这一个缓存上latch(可以视作是锁)。当存在io瓶颈的时候,那么磁盘上的数目不能够立即读到buffer pool 中就能冒出等待latch的场地。这几个或者是io过慢,也可以有希望是在做一些剩余的io造成的。

那么接下去查看sys.dm_io_virtual_file_stats 品质视图来显然哪些数据库形成了怎么大的推迟。何况经过physical disk avg.disk reads/sec和physical diskavg.disk writes/sec来规定终究数据库有稍许io负载。

接下去通过 sys.dm_exec_query_stats 查看实践安插,通过查看高物理读的sql和执行安排看看有未有优化的空间。如增多索引,修改sql,优化引擎访谈数据的不二等秘书诀。

有极大希望,sql 语句已经无法再优化,但是品质依旧特别,往往这种sql是报表查询类的sql,会从磁盘中读取大批量数码,很好多码往往在buffer pool 找不到那么就能够产生大气的pageiolatch_sh等待。这时,大家将要看看是还是不是是内部存款和储蓄器不足照成的,用perfmon 查看 page life expectancy(页寿命长短),free list stalls/sec(等待空页的次数)和Lazy writes/sec。 page life expectancy 波动相当棒,free list stalls/sec 一贯大于0,Lazy writes/sec 的量也十分的大,那么就说明buffer pool 远远不足大。不过也是有比异常的大可能率是sql 写的非常的大心,select了比相当多没供给的多寡。

 

在地方的troubleshooting 进度中,很轻易步向三个误区,sys.dm_io_virtual_file_stats 和一些质量目标,就能够很轻松看清说io有毛病,必要十分的预算来扩张io的习性,不过扩充io是比较贵的。io质量差强人意很有相当的大希望miss index可能buffer pool的下压力导致的。假如一味的丰硕物理设备,不过从未找到根本原因,当数据量拉长后,照旧会出现一样的主题材料。

 

  %disk read time

wait event的基本troubleshooting

 

wait statistics 是SQLOS跟踪获得的

SQLOS 是八个伪操作系统,是SQL Server 的一有的,有调整线程,内部存款和储蓄器管理等其余操作。

SQLOS比windows调治器越来越好的调治sql server 线程。SQLOS的调节器间的竞相,会比强占式的系统调治又越来越好的并发性

 

当sql server 等待二个sql 推行的时候,等待的岁月会被sqlos捕获,这么些日子都会存放在 sys.dm_os_wait_stats品质视图中。各个等待时间的长度,何况和其余的属性视图,质量计数器结合,能够很明确的看出品质难题。

 

对此未知的特性问题sys.dm_os_wait_stats 用来剖断质量难点是很好用的,不过在服务重视启只怕dbcc 命令清空 sys.dm_os_wait_stats后会很好分析,时间一长就很难深入分析,因为等待时间是一同的,搞不清楚哪个是您碰巧推行出来的年月。当然能够考虑先捕获一份,当sql 试行完后,再捕获一份,进行相比较。

 

翻看wait event,获得的音信只是骨子里品质难题的个中一个病症,为了更采纳wait event 音讯,你要求了然财富等待和非资源等待的分别,还应该有须要领会任何troubleshooting音信。

 

在sql server中有一对的sql是没难点的,能够运用一下sql 语句查看说一些 session的wait event

SELECT DISTINCT

wt.wait_type

FROM sys.dm_os_waiting_tasks AS wt

JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id

WHERE s.is_user_process = 0

因为一点都不小片段是常规的,所以提供了一个sql 来过滤常常查询操作

SELECT TOP 10

wait_type ,

max_wait_time_ms wait_time_ms ,

signal_wait_time_ms ,

wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

AS percent_total_waits ,

100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

AS percent_total_signal_waits ,

100.0 * ( wait_time_ms - signal_wait_time_ms )

/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0 -- remove zero wait_time

AND wait_type NOT IN -- filter out additional irrelevant waits

( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

'LAZYWRITER_江苏十一选五手机版,SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE' )

ORDER BY wait_time_ms DESC

反省wait event日常只关注前多少个等待音讯,查看高端待时间的守候类型。

CXPACKET:

     注明并发查询的守候时间,常常不会即时发生难题,也可能是因为其他性能难题,导致CXPACKET等待过高。

SOS_SCHEDULER_YIELD

     义务在施行的时候被调节器中断,被归入可进行队列等待被周转。这几个时刻过长恐怕是cpu压力变成的。

THREADPOOL

     一个职分必需绑定到贰个专门的学业义务能力试行,threadpool 便是task等待被绑定的小运。出现threadpool过高恐怕是,cpu非常不够用,也恐怕是大量的产出查询。

*LCK_**

     这中等候类型过高,表明或然session发生堵塞,能够看sys.dm_db_index_operational_stats 得到更深切的内容

PAGEIOLATCH_,IO_COMPLETION,WRITELOG*

     这个往往和磁盘的io瓶颈关联,根本原因往往都以功效极差的询问操作花费了过多的内部存款和储蓄器。PAGEIOLATCH_*和数据库文件的读写延迟相关。writelog和事务日               志文件的读写相关。这几个等待最棒和sys.dm_io_virtual_file_stats 关联明显难点是产生在数据库,数据文件,磁盘依然整个实例。

*PAGELATCH_**

     在buffer pool 中非io等待latch。PAGELATCH_* 大批量的等候常常是分配抵触。当tempdb中山高校量的对象要被删除恐怕创建,那么系统就能对SGAM,GAM和PFS的分配发生争执。

*LATCH_**

     LATCH_*和在那之中cache的护卫,这种等待过高会发生大气的主题素材。可以经过 sys.dm_os_latch_stats 查看详细内容。

ASYNC_NETWORK_IO

     这么些等待不完全申明网络的瓶颈。事实上非常多情景下是客户端程序一行一行的管理sql server 的结果集导致。产生这种主题素材那么就修改客户端代码。

一句话来讲的表达了重要的等候,减弱在剖判wait event 的时候走的弯路。

为了明确是还是不是业已去掉难点能够用DBCC SQLPERAV4F('sys.dm_os_wait_stats', clear)清除wait event。也足以用2个wait event 新闻相减。

  %disk write time

设想文件消息(virtual file Statistics)

普普通通,当使用wait event 解析难点的时候,都为感到很想io的属性难点。然而wait event 并不能够证实io是怎么产生的,所以很有希望会误判

 

那正是干吗要选取sys.dm_os_latch_stats 查看的原因,能够查看累计的io总括音信,每种文件的读写消息,日志文件的读写,能够测算读写的百分比,io等待的次数,等待的日子。

SELECT DB_NAME(vfs.database_id) AS database_name ,

vfs.database_id ,

vfs.FILE_ID ,

io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,

io_stall_write_ms / NULLIF(num_of_writes, 0)

AS avg_write_latency ,

io_stall / NULLIF(num_of_reads + num_of_writes, 0)

AS avg_total_latency ,

num_of_bytes_read / NULLIF(num_of_reads, 0)

AS avg_bytes_per_read ,

num_of_bytes_written / NULLIF(num_of_writes, 0)

AS avg_bytes_per_write ,

vfs.io_stall ,

vfs.num_of_reads ,

vfs.num_of_bytes_read ,

vfs.io_stall_read_ms ,

vfs.num_of_writes ,

vfs.num_of_bytes_written ,

vfs.io_stall_write_ms ,

size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,

physical_name

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id

AND vfs.FILE_ID = mf.FILE_ID

ORDER BY avg_total_latency DESC

翻看是或不是读写过大,平均延时是不是过高。通过那几个能够理解是或不是是io的难题。

只要数据文件和日志文件是共享磁盘队列的,avg_total_latency 比预期的要高,那么就有相当的大或许是io的主题素材了

 

要是当前的数据库是用来归档数据到比非常的慢的寄放中,或许会有异常高的PAGEIOLATCH_*和io_stall那么大家就须求规定怎么高的等候是或不是属于归档的线程,由此在troubleshooting的时候要注意你的服务器的门类。

若果您的磁盘读写比例是1:10,并且又相当高的 avg_total_latency 那么就思量把磁盘队列换来 raid5,为io读提供更加的多的主轴。

 

  Avg. disk sec/read

质量目标

在最最早的troubleshooting,质量指标是不行有效的。也足以用来注脚本身的判别是或不是准确。

PLA 是八个很好的属性日志分析工具. 缺憾未有中文版,当然能够去codeplex 下载源代码自身修改。那个工具内嵌了质量搜集中结,也正是见惯司空要搜罗的一对品质目的。也内嵌了阀值模板,能够在质量目标搜集完事后做深入分析。

 

sql server 对本身的品质指标有相应的性质视图 sys.dm_os_performance_counters。对于品质指标有个别是一同值,因而须要做2个快速照相,相减总括结果。

DECLARE @CounterPrefix NVARCHAR(30)

SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'

THEN 'SQLServer:'

ELSE 'MSSQL$' + @@SERVICENAME + ':'

END ;

-- Capture the first counter set

SELECT CAST(1 AS INT) AS collection_instance ,

[OBJECT_NAME] ,

counter_name ,

instance_name ,

cntr_value ,

cntr_type ,

CURRENT_TIMESTAMP AS collection_time

INTO #perf_counters_init

FROM sys.dm_os_performance_counters

WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'

AND counter_name = 'Full Scans/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'

AND counter_name = 'Index Searches/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'

AND counter_name = 'Lazy Writes/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'

AND counter_name = 'Page life expectancy'

)

OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'

AND counter_name = 'Processes Blocked'

)

OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'

AND counter_name = 'User Connections'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Locks'

AND counter_name = 'Lock Waits/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Locks'

AND counter_name = 'Lock Wait Time (ms)'

)OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Re-Compilations/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'

AND counter_name = 'Memory Grants Pending'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'Batch Requests/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Compilations/sec'

)

-- Wait on Second between data collection

WAITFOR DELAY '00:00:01'

-- Capture the second counter set

SELECT CAST(2 AS INT) AS collection_instance ,

OBJECT_NAME ,

counter_name ,

instance_name ,

cntr_value ,

cntr_type ,

CURRENT_TIMESTAMP AS collection_time

INTO #perf_counters_second

FROM sys.dm_os_performance_counters

WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'

AND counter_name = 'Full Scans/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'

AND counter_name = 'Index Searches/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'

AND counter_name = 'Lazy Writes/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'

AND counter_name = 'Page life expectancy'

)

OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'

AND counter_name = 'Processes Blocked'

)

OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'

AND counter_name = 'User Connections'

)OR ( OBJECT_NAME = @CounterPrefix + 'Locks'

AND counter_name = 'Lock Waits/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Locks'

AND counter_name = 'Lock Wait Time (ms)'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Re-Compilations/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'

AND counter_name = 'Memory Grants Pending'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'Batch Requests/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Compilations/sec'

)

-- Calculate the cumulative counter values

SELECT i.OBJECT_NAME ,

i.counter_name ,

i.instance_name ,

CASE WHEN i.cntr_type = 272696576

THEN s.cntr_value - i.cntr_value

WHEN i.cntr_type = 65792 THEN s.cntr_value

END AS cntr_value

FROM #perf_counters_init AS i

JOIN #perf_counters_second AS s

ON i.collection_instance + 1 = s.collection_instance

AND i.OBJECT_NAME = s.OBJECT_NAME

AND i.counter_name = s.counter_name

AND i.instance_name = s.instance_name

ORDER BY OBJECT_NAME

-- Cleanup tables

DROP TABLE #perf_counters_init

DROP TABLE #perf_counters_second

重要搜罗一下品质目标:

• SQLServer:Access MethodsFull Scans/sec

• SQLServer:Access MethodsIndex Searches/sec

• SQLServer:Buffer ManagerLazy Writes/sec

• SQLServer:Buffer ManagerPage life expectancy

• SQLServer:Buffer ManagerFree list stalls/sec

• SQLServer:General StatisticsProcesses Blocked

• SQLServer:General StatisticsUser Connections

• SQLServer:LocksLock Waits/sec

• SQLServer:LocksLock Wait Time (ms)

• SQLServer:Memory ManagerMemory Grants Pending

• SQLServer:SQL StatisticsBatch Requests/sec

• SQLServer:SQL StatisticsSQL Compilations/sec

• SQLServer:SQL StatisticsSQL Re-Compilations/sec

 

那边又2个 Access Methods 品质目的,表达了拜谒数据库分裂的方法,full scans/sec 表示了发生在数据库中索引和表扫描的次数。

假若io出现瓶颈,并且伴随着大量的扫描现身,那么很有不小或者便是miss index 恐怕sql 代码不地道照成的。那么有个别次数到某个时得以以为有毛病呢?在平时境况下 index searches/sec 比 full scans/sec 高800-一千,要是 full sacans/sec过高,那么很有相当大大概是miss index 和多余的io操作引起的。

 

Buffer Manager 和 memory manager 日常用来检验是或不是留存内部存款和储蓄器压力,lazy writes/sec,page life expectancy ,free list stalls/sec 用来佐证是不是处于内部存款和储蓄器压力。

洋洋网络的稿子和论坛都说,假使Page Life expectancy 低于300秒的时候,存在内部存款和储蓄器压力。但是那只是对于从前唯有4g内部存款和储蓄器的服务器的,未来的服务器日常都以32g上述内部存储器5分钟的阀值已经不能够在印证难点了。300秒就算早就不再适用,可是大家能够用300来作为基值来测算当前的PLE的阀值 (32/4)*300 = 2400那么一旦是32g的服务器设置为2400只怕会比较适宜。

 

举个例子PEL一直低于阀值,并且 lazy writes/sec一直很高,那么有望是buffer pool压力变成的。假若今年full scans/sec值也非常高,那么请先检查是或不是miss index 也许读取了剩下的多少。

 

general statisticsprocesses blocked,lockslock waits/sec和lockslock wait time(ms)假如那3个值都以非0那么数据库会生出堵塞。

 

SQL Statistics 计数器表明了sql 的编写翻译也许重编写翻译的速度,sql compilations/sec和 batch requests/sec 成正比,那么很有异常的大或然多量sql 访谈都是 ad hoc情势无法通过实行安排缓冲优化它们,假若 SQL Re-compilations/sec 和 batch requests/sec 成正比,那么应用程序中也许又强制重新编写翻译的选项。

 

memory managermomory grants pending 表示等待授权内部存款和储蓄器的等候,假设这些值异常高那么扩展内部存款和储蓄器也许会有成效。不过也会有希望是大的排序,hash操作也恐怕导致,能够行使调治目录或许查询来减小这种景色。

**

**

  Avg. disk sec/write:   很好:<10ms    一般:10-20ms   有点慢:20-50ms   非常慢:> 50ms

实行安插缓冲的应用

奉行布署缓冲是sql server 的内部组件,能够运用 sys.dm_exec_query_stats 查询,上边有个sql查询物理读前十的安顿

SELECT TOP 10

execution_count ,

statement_start_offset AS stmt_start_offset ,

sql_handle ,

plan_handle ,

total_logical_reads / execution_count AS avg_logical_reads ,

total_logical_writes / execution_count AS avg_logical_writes ,

total_physical_reads / execution_count AS avg_physical_reads ,

t.text

FROM sys.dm_exec_query_stats AS s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

ORDER BY avg_physical_reads DESC

在试行安排在那之中的这几个值可以见见哪些查询物理io操作很频仍,也能够和wait event 和设想文件结合深入分析有题目标io操作。

咱俩也得以选取sys.dm_exec_query_plan()查看存在内部存款和储蓄器里面的进行布置。

这边又2本书深远的叙说了询问试行陈设:《SQL Server 二零零六 Query performance tuning distilled》,《Inside Microsoft SQL Server 二零零六:T-SQL Querying》。

sys.dm_exec_query_stats还用来询问 cpu时间,最长推行时间,或许最频仍的sql

在sql server 二〇一〇中加盟了2个附加的列,query_hash,query_plan_hash用来聚合相似的sql的。对于ad hoc 过大的服务器能够用来剖判相似的sql,分歧的编写翻译的总和。

 

  Avg. disk bytes/transfer

总结

上边各种部分都讲了三个惦念,贰个思路。要想质量调优调的好,那么就先系统系统布局,你要知道如前方说的miss index 一旦产生,那么不知会影响io,还有或许会影响内存和cpu。接下来要会分析,从一同始的粗略的性质总结信息,往下深入分析,用别的总计音信排除难题,获得质量难点的的确原因。

小说来源:Troubleshooting SQL Server: A Guide for the Accidental DBA 若是看不懂的照旧想更透顶摸底的,能够看原稿。

 

  Avg. disk queue length: 不该长日子>2  (SAN 盘就分化)

  Avg. disk read queue length

  Avg. disk write queue length

  Disk Bytes/sec:  好:20-40MB   一般:10-20MB

  Disk Read Bytes/sec

  Disk Write Bytes/sec

  Disk Transfers/sec

  Disk Reads/sec

  Disk Writes/sec

  Current Disk queue length

 

四. SQL Server 内部分析:

 1. 检查sys.dm_exec_requests 或者 sys.dm_os_wait_stats:

  select wait_type,

    waiting_tasks_count,

    wait_time_ms

  from sys.dm_os_wait_stats

  where wait_type like 'PAGEIOLATCH'   -- PAGEIOLATCH_EX(写)   PAGEIOLATCH_SH(读) 首要反映数据文件上的I/O等待

  order by wait_type

  1. 搜索非常数据库哪个文件总做I/O,是数据文件依旧日志文件, 平时读,依旧日常写:

  select db.name as database_name, f.fileid as file_id, f.filename as file_name,

    i.num_of_reads, i.num_of _bytes_read, i.io_stall_read_ms,

    i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms,

    i.io_stall, i.size_on_disk_bytes

  from sys.database db inner join sys.sysaltfiles f on db.database_id=f.dbid

  inner join sys.dm_io_virtual_file_stats(NULL,NULL) i  on i.database_id=f.dbid and i.file_id=f.fileid

 

  select database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address  -- check every pending I/O request

  from sys.dm_io_virtual_file_stats(NULL,NULL) t1, sys.dm_io_pending_io_requests as t2

  where t1.file_handle=t2.io_handle

 

  -- check which table in buffer pool and how mang size of it

  declare @name nvarchar(100)   

  declare @cmd nvarchar(1000)

  declare dbname cursor for

    select name from master.dbo.sysdatabases

  open dbname

  fetch next from dbname into @name

  while @@fetch_status = 0

  begin

    set @cmd= 'select b.databse_id, db=dbname(b.database_id), p.object_id, p.index_id, buffer_count=count(*) from ' + @name + '.sys.allocation_units a, '

        + @name + '.sys.dm_os_buffer_descriptions b, ' + @name+ '.sys.partitions p

        where a.allocation_unit_id=b.allocation_unit_id

        and a.container_id=p.hobt_id

        and b.database_id=db_id(''' + @name+ ''')

        group by b.database_id, p.object_id, p.index_id

        order by b.database_id, buffer_count desc'

    exec(@cmd)

    fetch next from dbname into @name

  end

  close dbname

  deallocate dbname

  go

 

五. 和SQL相关的计数器:

  1. Buffer manager:

    page reads/sec  and page writes/sec

    Lazy writes/sec

    Checkpoint writes/sec

    Readahead pages/sec

  2. Access Methods:

    Freespace scans/sec

    Page splits/sec

    Page allocations/sec

    Workfiles/sec

    Worktables/sec

    Full scans/sec

    Index Searches/sec

  3. Database(Log Activity)

    Log flushes/sec

    Log Bytes flushed/sec

    Log flush wait time

    Log flush waits/sec

 

六. 硬盘压力测量试验:

  SQLIO 下载地址: 

      SQLIO 已经进级成 DiskSPD。 在上头的链接中下载readme.pdf, 该文件中有下载地址。  Diskspd-v2.0.17.zip 解压之后如下图:

   江苏十一选五手机版 6

  UsingDiskspdforSQLServer.docx里面有详实的利用验证和剖判方法。

 



 

编辑:江苏十一选五手机版数据库 本文来源:问题的诊断分,性能调优

关键词: