菜单

金沙国际棋牌下载官网sql server I/O硬盘交互

2019年11月6日 - 计算机数据
金沙国际棋牌下载官网sql server I/O硬盘交互

一. 概述

 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
2009从头,替换动态管理视图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
数据文件里页面碎片整理。 当表产生增加和删除改操作时索引都会时有发生碎片(索引叶级的页拆分卡塔 尔(英语:State of Qatar),碎片是指索引上的页不再抱有大意一而再三回九转性时,就能够发生碎片。比方你询问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 SQLPEENVISIONF(‘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-1000,假使 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,

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图