笔记34-徐 任务调度和CPU问题

笔记34-徐 任务调度和CPU问题

--任务调度和CPU问题
--SQL作为一个企业级数据库平台，一个基本的要求就是要有能力顺畅地同时处理
--成百上千的用户请求，SQL能使线程调度得更加适应高并发的数据库应用

--SQL还开发出一套任务调度的监视机制，定期检查各个任务调度的运行状态。
--如果发现某个任务在CPU上占据了较长时间，就会报警。所以如果SQL
--服务发生不响应的问题server hang，一般在日志文件error log 里都会看到报警信息

--SQLSERVER独特的任务调度算法（SQLOS）
--SQL在Windows的基础上又开发出了一套自己的任务调度机制。所以SQL作为一个应用程序，
--有抽象出一般由操作系统代为管理的功能
--任务调度管理子系统
--内存管理
--错误，异常处理机制
--死锁侦测和解决机制
--运行第三方代码（dll，extended SP等）机制


--SQL的管理功能组件又叫SQLOS SQL OPERATING SYSTEM ,而内存管理和任务调度管理是SQLOS的两大核心内容

--对于SQLSERVER来讲，除了从DAC dedicated administrator connection过来的连接，其他
--用户连接对SQL来讲都是同等重要的，而这样的连接在同一个时间点，可能会有成百上千。
--如果SQL完全依赖Windows任务调度就不行了

--在早期的SQLSERVER曾经依赖Windows的线程调度。随着硬件水平和并发用户的增多，这种方法缺点越发明显
--SQL开发出自己的一套任务调度机制，特点如下：
--1、只有需要运行任务的连接才会被分配线程。出于空闲状态的连接，在SQL里会以一组数据结构表示，
--不会占用线程资源。大大降低SQL进程需要的线程数目

--2、对于每一个CPU，SQL内部会有一个调度（scheduler）,由这个scheduler决定在某个时间点，到底是哪个
--SQL线程去运行。所以在Windows层面，每个CPU最多只会对应一个处于运行状态的线程。大大降低Windows
--层面的上下文切换context switch


--实践证明：很多有着1000~2000个并发用户的SQL，线程数也只需要一两百个。SQL完成的批处理量每秒钟可以
--达到3000~4000个。

--这个角度看，SQL的确是一个在Windows上高并发应用的典范


--scheduler
--对于每个逻辑CPU，SQL会有一个scheduler与之对应，在SQL层面上代表CPU对象
--只有拿到scheduler所有权的任务worker才能在这个逻辑CPU上运行

--所谓逻辑CPU，就是SQL从Windows层面上看到的CPU数目，如果是一个双核的CPU，
--那么一个物理CPU在SQL看来就是两个逻辑CPU。如果系统还使用了超线程hyper-threaded
--那对SQL来讲就是4个逻辑CPU

--SQL Server 上，每一个CPU通常会对应一个Scheduler, 有几个额外的系统的Scheduler，只是用来执行一些系统任务。
--对用户来讲，我们只需要关心User Scheduler就可以了。如果有4个CPU的话，那么通常就会有4个User Scheduler。

--规则：
--每个scheduler上的最大worker数目等于SQL的最大线程数除以scheduler的数目
--在同一个时间点，只能有一个拥有scheduler的worker处于运行状态，其他worker
--都必须处于等待状态。这样能降低每个逻辑CPU上的处于正在运行状态的线程数目，降低
--context switch，提供可扩展性

--scheduler是SQL的一个逻辑概念，他不与物理CPU相绑定。也就是说，一个scheduler可以
--被Windows安排一会儿在这个CPU上，一会儿在那个CPU上。但是，如果在sp_configure里设置
--了CPU affinity mask，那么scheduler就会固定在某个特定的CPU上

--worker
--每个worker跟一个线程（或纤程fiber）相对应，是SQL任务的执行单位。SQL不直接调度线程/纤程，
--而是调度worker，使得SQL能够控制任务调度

--规则：
--每个worker会固定代表一个线程（或纤程），并且和一个scheduler相绑定。如果scheduler是
--固定在某个CPU上的（通过设置CPU affinity mask），那么worker也会固定在某个CPU上

--每个scheduler有worker的上限值，并且可以根据SQL工作负荷创建或释放worker
--每次worker都会去运行一个完整的任务（task）。在任务做完之前不会退出，除非这个任务主动
--进入了等待状态

--scheduler只在有新任务要运行，而当前没有空闲的worker的情况下，才会创建新的worker。

--某个worker空闲超过15分钟，scheduler可能会删除这个worker，以及其对应的线程。当SQL
--遇到内存压力的时，也会大量删除处于空闲状态的worker，以节省multi-page内存开销

--各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
--CPU数                 32位计算机                        64位计算机
--<=4                    256                               512
--8                       288                              576
--16                      352                              704
--32                      480                              960


--task
--在worker上运行的最小任务单元。最简单的task就是一个简单batch。例如，客户发过来下面
--的请求：
SELECT @@SERVERNAME
GO
SELECT GETDATE()
GO
--那么这两个batch就分别是两个task。SQL会先分配给第一个batch（select @@servername）一个
--worker，将结果返回给客户端，再分配第二个batch（select getdate()）一个worker。这两个
--worker可能是不同的worker，甚至在不同的scheduler上

--只要一个task开始运行，他就不会从这个worker上被移出。例如，如果一个select语句被
--其他连接阻塞住，worker就不能继续运行，只能进入等待状态。但是这个select task 不会
--将这个worker释放，让他做其他任务。所以结果是，这个worker所对应的线程会进入等待状态


--yieding
--SQLOS的任务调度算法的核心，就是所有在逻辑scheduler上运行的worker都是非抢占式的
--（non-preemptive）。worker始终在scheduler上运行，直到他运行结束，或者主动将
--scheduler让出给其他worker为止。这个“让出”scheduler的动作，我们叫yieding

--每个scheduler都会有一个runnable列表，所有等待CPU运行的worker都会在这个列表里排队，
--以先进先出的算法，等待SQL分配给他scheduler运行

--SQL定义了很多yieding的规则，约束一个task在scheduler运行的时间。如果task比较复杂，
--不能很快完成，会保证task在合适的时间点做yieding，不至于占用scheduler太多时间。

--常见时间点：
--当worker每次要去读数据页的时候，SQL会检查这个worker已经在scheduler上运行了多久，
--如果已经超过4ms，就做yieding

--每做64KB的结果集排序，就会做一次yieding

--在做语句编译compile的过程中（这个过程比较占CPU资源），经常会有yieding

--如果客户端不能及时把结果集取走，worker就会做yieding

--一个batch里的每一句话做完，都会做一次yieding


--正常来讲，哪怕一个task要做很久，他使用的worker是会经常做yieding的，不会长时间
--占用CPU不放。如果在一个scheduler上同时有很多worker要运行，SQL通过worker自动
--yieding的方式调度并发运行。这个比Windows用上下文切换context switch这麽粗鲁
--不分青红皂白地打断更有效



--对于每个CPU，SQL都会有一个scheduler与之对应。在每个scheduler里，会有若干个worker，对应
--于每个线程。在客户端发过来请求之后，SQL会将其分解成一个或多个task。根据每个scheduler的繁忙程度，
--task会被分配到某个scheduler上。如果scheduler里有空闲的worker，task就会被分配到某个worker上。
--如果没有，scheduler会创建新的worker，供task使用。如果scheduler里的worker已经到了他的上限值，
--而他们都有task要运行，那么新的task只好进入等待worker的状态


--动态管理视图查看每个scheduler的状态
SELECT
scheduler_id AS schedulerid,
cpu_id AS cpuid,
parent_node_id AS parentnodeid,
current_tasks_count AS currenttaskcount,
runnable_tasks_count AS runnabletaskcount,
current_workers_count AS currentworkercount,
active_workers_count AS activeworkercount,
work_queue_count AS workqueuecount
from sys.dm_os_schedulers

--字段：
--current_tasks_count：与此scheduler关联的当前任务数，包括等待工作线程worker资源的任务数task
--和已经拿到worker，当前正在等待或运行的任务处于suspended或runnable状态

--runnable_tasks_count：已分配任务task并且正在可运行队列runnable list中等待被调度的工作线程数。
--只要不为0，就说明这个scheduler对应的CPU当时正在做事情，如果SQL CPU使用率不高，这个值在大部分
--情况下会是0

--current_workers_count：与此计划程序scheduler关联的工作线程数，包括有task要运行的worker和正处于
--空闲状态的worker

--current_workers_count：处于活动状态的工作线程数，他们必须有关联的任务，并且必须处于正在
--运行running，可运行runnable或挂起suspend状态中

--work_queue_count ：队列中等待空闲worker来执行的任务数。通常这个值应该为0。如果不为0，意味着
--SQL存在线程用尽的压力





--SPID:60,51,64,87,52,93,73,59,56,55

--其中SPID60占据了一个scheduler，正在CPU上面运行，所以他的状态是running
--SPID55正在等待系统资源，73被阻塞，59等待客户取走结果集，56等待同步，
--现在他们都是waiter list里，等待获得所需要的资源

--waiter list(resource wait)队列：一般阻塞，等待资源就会放在这个队列里
--runnable queue队列：一切准备就绪，等待在scheduler运行的task做yieding
--running：正在scheduler里运行，一个scheduler同时只能有一个task



--调度：放在waiter list里的task准备好运行之后就放在runnable queue，当在runnable queue的时候，
--前面没有人排队，他就可以进去scheduler里运行了，当在scheduler里运行的task突然要等待
--资源的时候，他会做yieding，然后又重新回到waiter list

--这种做法可以最大程度消除Windows做context switch的需求

--这个算法的缺点是当SQLSERVER代码质量有问题或者系统资源出问题，那么某个task意外运行
--很长时间都没有做yieding，那么他会长时间占用CPU，问题轻的话，会使SQL产生不良影响
--重的话，整个SQL都可能没有响应



--为了及时发现问题，SQLSERVER在SQL2000 SP3以后开发出一套scheduler的健康监测机制。
--当SQL发现某个或某些scheduler有问题时，会及时在错误日志里记录下相关信息，并且生成
--一个mini——dump文件，把SQL这个进程当时在内存里的重要信息保存在文件里。
--通过错误日志和mini-dump，微软技术支持工程师可以分析当时每个scheduler的状态，
--以及SQL为什么认为scheduler有问题。

--分析mini-dump里每个线程的call stack，了解当时出问题的task究竟在运行什么函数，以及
--各个函数的参数。总之通过debug手段，mini-dump会为分析提供很多关键信息

--dump文件的debug对专业知识要求比较高，有时候需要对SQL的底层设计有所了解。大部分情况下
--只是对专业SQLSERVER支持工程师的要求。

--错误报警的具体含义：
--17883 -某个scheduler疑似有问题
--SQL有一些优先级更高的线程会定期检查每个scheduler上运行的task。如果发现某个task运行了
--超过60秒钟都没有做过yieding，SQL就会打印出一个17883错误。从SQL启动以来第一次遇到17883
--错误的话，还会生成mini-dump文件

--SQL会每隔5秒钟检查一下所有的scheduler。如果发现哪个task没有yieding，SQL就开始统计
--这个线程的各项指标。60秒过后还没有yieding就正式报告17883错误。

--如果usermode的时间很长：很有可能当前线程所运行的代码进入了一个循环，很长时间都出不来。
--这种问题在SQL代码里不应该出现。如果出现，常常是怀疑是SQL代码哪里写得不够优化。建议升级
--SQL到最新的服务包版本，以避免一切已知的问题


--如果kernelmode的时间很长：说明当前线程主要都是在运行操作系统管理的核心态功能。如果
--要找到问题的根本原因，可能要作kernel mode的debug。怀疑的方向是某个驱动程序，或者是
--操作系统本身。建议升级操作系统的补丁包和有关驱动的版本


--如果usermode和kernelmode的时间都不高：线程一般是正在等某个API返回，例如：waitforsingleobject
--,sleep,writefile,readfile。这些函数按道理应该很快返回，所以SQL就没有设计在这里做yieding。
--但是当时却因为某种未知原因而长时间地没有返回。其中I/O问题导致的writefile和readfile长时间不
--返回，是17883的一个最常见原因。这时候伴随系统I/O问题，DBA要检查一下各个磁盘的吞吐量是否正常


--如果系统空闲率system idle%和进程使用率process utilization%都很低：很可能是因为由于SQL进程
--以外的其他应用或操作系统本身产生了CPU100%的现象，使得SQL拿不到CPU资源去运行线程，进而导致
--task没有及时做完。这个要观察性能监视器和CPU有关的计数器的值就能确认




--17884和17888 -所有scheduler都疑似有问题
--在SQL调度算法里，scheduler本身就是一个资源，是资源就难免产生死锁的现象
--以下情形：
--一个SPID开启了一个事务，在某张表上申请了一个X锁。指令运行完毕以后，这个事务没有提交
--，锁还被这个连接持有。但是连接进入空闲状态，线程因此被释放，连接没有放回连接池，只是线程释放

--其他用户发来请求，需要读取这张表。由于申请不到锁，task被阻塞住。这时，连接将持有线程，处于等待
--状态，直到拿到锁为止

--由于太多用户发来类似请求，越来越多的线程进入了被阻塞的状态。最后所有的线程都被使用完，而他们
--几乎都在等待锁资源

--阻塞源头的那个SPID的用户发来请求，要求commit这个事务。如果事务能commit，阻塞就会被解除
--。但是这时候SQL已经没有空闲的线程来运行事务提交这个task。所以这个SPID进入了等待thread状态

--这种情况下，SQL进入了死锁状态。大量线程被阻塞，而阻塞源头找不到线程来帮他commit事务


--SQL配置的最大线程数256中，其中一部分用来运行系统任务!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--由于这种死锁资源不是单单是锁资源，SQL传统的死锁检测机制无法起作用。但是SQL任务调度检测机制
--可以检测到

--如果SQL发现每个scheduler都没有thread能够有进展，就会报告17884错误

--如果SQL发现50%以上的thread等待都是类似的资源，例如：锁，网络等，就会报告：17888错误


--常见的17884/17888错误产生原因：
--所有scheduler都遇到了17883错误
--所有的worker都被某个关键资源阻塞
--所有的worker都在运行一个很长时间才能返回的语句



--17884/17888错误比17883影响大，由于是所有的scheduler
--一般在错误日志里看到17883/17884的警告，很多情况下，17883错误是由于磁盘响应太慢导致的
--磁盘瓶颈消失，17883问题也会自动消失，SQL任务调度会自动恢复正常。偶尔有一个17883错误
--用户没有所谓，也可以忽略，如果用户有所谓就要好好分析了




-------------------------案例分析------------------------------------------------------------
--不可否认，分析17883/17884问题，对dump文件的debug是非常重要的

--在SQL上运行下面的指令，了解scheduler和用户连接所使用的thread的情况
DBCC SQLPERF(umsstats)
SELECT * FROM sys.sysprocesses WHERE kpid<>0

--联机丛书说kpid就是 线程ID

--其他DMV
SELECT * FROM sys.dm_os_schedulers
SELECT * FROM sys.dm_os_workers
SELECT * FROM sys.dm_os_threads
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_ring_buffers





--收集日志-----------------------------------------------
--由于出问题的时间不固定，SQL又非常繁忙，收集SQL Trace的话日志很大，不太可行
--开销太大，对正常的性能也会有影响。
--我们收集性能监视器里的日志，以及用Osql.exe每隔15秒运行一段TSQL脚本，查询
--各个关键DMV，将结果输出到文本文件里。

--使用这样的方法，对系统性能影响比较小，用户基本不会有感觉，使得长时间收集变为可能



---------------------------SQL CPU100%问题---------------------------------------------------
--这是除了server hang以外另一个和CPU相关的问题。SQL对自己任务调度的检测是非常严格的。
--如果errorlog里没有报告17883/17884这一类错误，但是SQL的CPU很高，一般都是工作负载太高
--导致的，SQL本身没有什么问题，只不过在辛苦地完成用户发过来的各项请求




--如果一台SQL服务器的CPU使用率大部分时间超过60~70%，就已经算是比较高的了。

--SQL做下面操作会集中使用CPU资源
--1、编译和重编译
--不是所有的执行计划都可以被重用。在很多时候，由于数据量发生了变化，统计信息发生了变化，或者数据结构发生了变化，
--同样一句话执行，还有再次把执行计划做一遍。这个过程叫重编译recompile,重编译会提高CPU使用量


--2、排序sort和聚合计算aggregation
--在查询的时候，经常会做order by ,distinct这样的操作，也会做avg,sum,max,min这样的聚合计算，在数据
--已经加载到内存以后，就要使用CPU把这些计算做完。所以这些语句操作使CPU使用量增多


--3、表连接操作join
--做两张表连接的时候，SQL常常会选择nested loop或hash算法。算法的完成要运行CPU，有时候join也会带来
--CPU使用，尤其是SQL选择错了连接算法的时候。


--和CPU有关的设置主要都在sp_configure

--1、priority boost 提升SQLSERVER的优先级
--如果设置为1，SQL进程会以比较高的优先级创建，在Windows进程调度里，会比较优先被运行

--但是这个设置是不推荐的，因为会打乱Windows正常的进程调度。如果SQL进程优先级较高，那么当
--SQL出现CPU100%时，Windows上的正常优先级的进程会受到影响，甚至会影响到Windows的健康，从而
--影响SQL的正常运行。

--2、affinity mask 自动设置所有处理器的处理器关联掩码
--设置SQL固定使用某几个CPU。当服务器上除了SQL，还要运行其他同样重要的应用服务时，为了防止
--SQL使用掉所有CPU资源，可以设置affinity mask，让SQL只使用其中几个CPU。还有一种情形，就是
--当DBA怀疑SQL的scheduler经常在不同的CPU上切换，进而影响性能时，也可以使用affinity mask，
--让SQL的每个scheduler固定在CPU上，看看是不是对整体性能有帮助
--按实际情况设置


--3、lightweight pooling 使用Windows纤程（轻型池）
--是否使用纤程技术，默认是关闭的。开启以后，SQL会在使用线程（thread）的时候使用纤程（fiber）
--纤程调度技术能够降低系统的context switch数目，所以对一些应用来讲，能提高性能
--但是SQL本身已经用scheduler技术，有意识地在SQL一级降低context switch的需求，所以
--SQL是个context switch不严重的应用（相对于他的工作负荷）。在实际应用中，很少遇到
--lightweight pooling 能提高系统性能的案例。纤程调度毕竟比普通线程调度要复杂，所以
--这个设置一般很少推荐使用


--4、max degree of parallelism
--定义SQL最多使用多少个线程来并行执行一条指令
--当SQL发现一条指令比较复杂时，会决定用多个线程并行执行，从而提高整体响应时间。
--例如：一条指令要读入100W条记录。如果一个线程做，需要10秒钟，如果10个线程做
--每个线程读10W，每个线程需要一秒，再加上线程间同步时间，总共2秒就做完了。

--这个设置是不是总是有好处，要DBA去判断
--在这2秒钟里，有10个CPU需要全力运行这10个线程，别的用户发过来的指令会受到影响，甚至可能
--会拿不到CPU执行。
--所以对于并发度要求高，每个用户都要求有及时响应的OLTP系统，一般会建议设置每个指令都只用
--一个线程执行，从而保证SQL在任何时间点，都有多个CPU可以响应多个请求。要把
--max degree of parallelism设置为1

--对于并发用户少的，经常有复杂查询的（例如：数据仓库），用户希望查询早点做完。
--要把max degree of parallelism设置为CPU的数量值。例如：SQL服务器有16个CPU，就
--设置成16。如果也要考虑并发用户数，可以设置小一点，例如：8，4 等




--5、cost threshold of parallelism
--当SQL在做编译的时候，同时会计算候选执行计划的cost。SQL总是先做非并行的执行计划。
--当他发现这个执行计划的值将大于cost threshold of parallelism的设定值，SQL就会改用
--并行执行计划
--所以如果提高cost threshold of parallelism的值，SQL会更不容易选择并行执行，从而
--有更好的并发度
--他的默认值是5，一般很少去修改他


--6、max worker threads 最大工作线程数
--定义SQL进程最多线程数.max worker threads的默认值是0,允许SQL在启动时自动配置
--工作线程数。对于大多数系统而言，该设置为最佳设置，一般很少去修改他





--SQL2005 /2008自动设置的最大工作线程数
--各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
--CPU数                 32位计算机                        64位计算机
--<=4                    256                               512
--8                       288                              576
--16                      352                              704
--32                      480                              960




--解决CPU 100%问题------------------------------------------------------------------------
--1、确定服务器CPU使用率到底是多少，其中多少是SQL贡献的
--如果SQL的CPU使用率不是很高，而是其他应用导致的，那也不用再检查SQL了


--2、确定当时SQL是否工作正常，看有没有17883/17884之类的问题发生，有没有访问越界access violation
--之类的严重问题发生
--这个看errorlog


--3、找出CPU 100%的时候SQL里正在运行的最耗CPU资源的语句，对它们进行优化
--这个比较艰难，尤其是没有DMV的SQL2000
--如果有SQL Trace一般可以找出这些语句，但是SQL系统的CPU使用率已经很高了，再开SQL Trace
--负载会更高。

--SQL2005 DMV SQL启动以来累计使用CPU资源最多的语句 前50名
SELECT
highest_cpu_queries.*,
highest_cpu_queries.total_worker_time,
DB_NAME(q.dbid)  AS dbname,
q.[text] AS qtext
from
(SELECT TOP 50 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
ORDER BY highest_cpu_queries.total_worker_time DESC



--找到最经常做重编译的存储过程
SELECT TOP 25
sql_text.text AS sqltext,
sql_handle AS sqlhandle,
plan_generation_num AS plangenerationnum,
execution_count AS execcount,
DB_NAME(dbid) AS dbname,
objectid AS objectid
from sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num>1
ORDER BY plan_generation_num DESC

--局限性：
--服务器出问题时，DBA不在现场，DBA到现场分析的时候已经时过境迁了
--问题发生时，常常不允许DBA做长时间分析，而采取一些手段（关闭一些程序，重启SQL，归档历史数据）
--DMV始终无法替代SQL Trace的功能，很多时候还需要收集一份SQL Trace


--4、降低系统负载或升级硬件
--如果修改程序和数据库设计是一件非常耗时的事，那么解决方向就是
--1把系统一部分负载移到其他服务器上
--2升级硬件

--但是随着负载的继续提高，总有一天问题会再次出现，所以在问题暂时消失后，有必要
--做系统设计审核。从设计上优化，往往是解决问题的最有效方法





----------------OLTP系统和数据仓库系统的差别和常用性能阀值-----------------------------------
--SQL支持两大类应用模式：OLTP和数据仓库
--这两大类应用模式有非常大的差别，用户发过来的请求类型会很不一样，用户期望的响应时间
--也很不相同，他们对系统资源的使用也有差别。

--所以要设计一套优化的数据库应用，必须要把这两类应用分开，让他们使用不同的数据库，不同的
--数据库服务器。从而防止两类应用相互影响


--OLTP系统----------------------------------------------------------------------------------------
--特点是应用有大量的并发程度较高的小事物，包括select ,insert,update,delete。这些操作简单，
--事务时间不会很长，但是要求返回时间很严格，基本上要在几秒钟之内必须返回





--支持生产流水线的数据库应用
--一件产品从原材料到组装成最后的产品，中间有很多工序。
--每道工序并不复杂，不会花很多时间。工厂需要数据库应用记录和监督每一道工序。在流水线上，
--工人可以扫描产品上的条形码，快速地输入产品加工、处理或检验结果。这些输入和修改过程
--都很简单，而且很多在数据库里会是insert,update,delete动作。但是应用的响应速度要求非常高，
--最好等待时间可以忽略不计。如果工人输入一个条形码以后要等几秒钟，那么他在处理每一件产品
--的时候，都会多花几秒钟。如果他要花几十秒，整个流水线的运转就会很慢。如果系统出问题，
--他每处理一个产品都要花几分钟，那么流水线就会瘫痪，工人们可以去喝茶了。
--DBA将面对心急如焚的管理高层



--OLTP系统在设计的时候要非常小心，由于一条语句导致整个服务器范围的阻塞，是绝对要避免的

--OLTP系统要注意避免出现的问题主要体现如下：
--1、数据库设计
--经常运行语句超过4个表做join      降低数据库设计范式级别，增加一些冗余字段，用空间换数据库效率
--经常更新的表有超过3个索引        索引太多会影响更新效率
--语句会做大量I/O  表扫描          语句缺少合适索引
--未使用的索引                     避免定义没有用的索引，凭空增加SQL的维护负担

--返回最经常运行的100条语句
SELECT TOP 100
cp.cacheobjtype,
cp.usecounts,
cp.size_in_bytes,
qs.statement_start_offset,
qs.statement_end_offset,
qt.dbid,
qt.objectid,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 statement
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
AND cp.usecounts>4
ORDER BY dbid,usecounts DESC


--返回最经常被修改的100个索引
--通过他们的databaseid,objectid,indexid和partitionnumber
--可以找到他们是哪个数据库上的哪个索引

SELECT TOP 100 DB_NAME(database_id),*
FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,null)
ORDER BY leaf_insert_count+leaf_delete_count+leaf_update_count DESC

--返回做I/O数目最多的50条语句以及他们的执行计划
SELECT TOP 50
total_logical_reads/execution_count AS avg_logical_read,
total_logical_writes/execution_count AS avg_logical_write,
total_physical_reads/execution_count AS avg_phys_read,
execution_count AS execcount,
statement_start_offset AS stmt_start_offset,
statement_end_offset AS stmt_end_offset,
SUBSTRING(sql_text.text,(statement_start_offset/2)),CASE
WHEN (statement_end_offset-statement_start_offset)/2<=0 THEN 64000
ELSE (statement_end_offset-statement_start_offset)/2 END) AS exec_statment,
sql_text.text AS text,
plan_text.*
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS plan_text
ORDER BY
(total_logical_reads+total_logical_writes)/execution_count DESC



--2、CPU
--signal waits   指令等待CPU资源的时间占总时间的百分比，如果超过25%，说明CPU资源紧张
--执行计划重用率   OLTP系统的核心语句，必须有大于95%的执行计划重用率
--并行运行的cxpacket等待状态     并行意味着SQL在处理一句代价很大的语句，要不就是没有合适
--的索引，要不就是筛选条件没能够筛选掉足够的记录，使得语句要返回大量的结果。这个在OLTP系统
--里都是不允许的
--其次，并行运行会影响OLTP系统整体响应速度，也是不推荐的

--计算signal wait占整个wait时间的百分比
SELECT CONVERT(NUMERIC(5,4),SUM(signal_wait_time_ms)/SUM(wait_time_ms))
FROM sys.dm_os_wait_stats


--计算cxpacket占整个wait时间的百分比
DECLARE @cxpacket BIGINT
DECLARE @sumwait BIGINT
SELECT
@cxpacket=wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type='cxpacket'

SELECT
@sumwait=SUM(wait_time_ms)
FROM sys.dm_os_wait_stats

SELECT CONVERT(NUMERIC(5,4),@cxpacket/@sumwait)

--3、内存
--page life expectancy       OLTP系统的操作都比较简单，所以他们不应该要访问太多的数据
--如果数据页不能长时间缓存在内存里，势必会影响性能，同时也说明某些语句没有合适的索引

--memory grants pending        等待内存授予的用户数目，如果大于1，一定有内存压力

--sql cache hit ratio          这个值不能长时间例如：60秒钟小于90%。否则，常常意味着内存有压力


--4、I/O
--average disk sec/read           在没有I/O压力的情况下，读操作应该在4~8ms以内完成
--average disk sec/write          对于像日志文件这样的连续写，应该在1ms内完成
--big IOs table scan/range scan   语句缺少合适的索引
--排在前两位的等待状态有下面几个
--asynch_io_completion,            这些等待状态意味着有I/O等待
--io_completion,
--logmgr
--writelog
--pageiolatch_x



--5、阻塞
--阻塞问题在OLTP系统里危害巨大，是要严格避免的
--阻塞发生频率          阻塞发生频率
--阻塞事件报告          在SQL Trace里自动报告超过30秒钟的阻塞语句
--平均阻塞时间          阻塞发生的长短
--排在前两位的等待状态以这样开头LCK_M_??              说明系统经常有阻塞
--经常有死锁    每个小时超过5个       打开/t 1204      死锁往往伴随着阻塞同时发生

--查询阻塞
SELECT TOP 2 wait_type FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC


--查询当前数据库上所有用户表在row lock上发生的阻塞频率
use GPOSDB   --要查询阻塞的数据库
DECLARE @dbid INT
SELECT @dbid=DB_ID()
SELECT
dbid=database_id,
objectname=OBJECT_NAME(s.object_id),
indexname=i.name,
i.index_id,
partition_number,
row_lock_count,
row_lock_wait_count,
[block%]=CAST(100*row_lock_wait_count/(1+row_lock_count)AS NUMERIC(15,2)),
row_lock_wait_in_ms,
[avg row lock waits in ms]=CAST(1*row_lock_wait_in_ms/(1+row_lock_wait_count)AS NUMERIC(15,2))
FROM sys.dm_db_index_operational_stats(@dbid,NULL,NULL,null) AS s,
sys.indexes AS i
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')=1
AND i.object_id=s.object_id
AND i.index_id=s.index_id
ORDER BY row_lock_wait_count DESC




--6、网络传输
--网络有延时，或应用太频繁地和数据库交互           网络不能支持应用和数据库服务器的交互流量
--网络带宽用尽                  由于网络太忙，有packet在传输中丢失


--优化方面
--对于经常update  insert  delete的表，在设计时要选择最小数量的索引
--可以通过提高执行计划重用和降低join的数目降低CPU使用率
--可以通过优化索引设计，降低join数目和提高页面在内存里的缓存生命周期，缓解I/O瓶颈
--如果Page life expectancy不会突然下降的话，说明内存的database page部分没有瓶颈
--可以通过优化索引和缩短事务大小来减少阻塞





--data warehouse系统------------------------------------------------------------------------------------
--对于一个数据库，用户总会有数据分析的需求，总是要基于一些历史数据的报表来做业务分析
--这也是数据库应用的重要需求。可是这样的大查询天生与insert update delete操作相冲突
--他们会互相阻塞，最后双方的速度都会大受影响

--这是数据仓库技术产生的一个重要背景，对于一个重要的OLTP系统，用户都会开发一套平行的
--数据仓库系统，定期把OLTP系统数据更新同步到数据仓库系统。在数据仓库系统里会存放
--所有历史数据，而OLTP系统只存放当前业务所需的数据，历史数据会被定期归档，以确保事务
--尽可能简单。所有数据分析请求都会指向数据仓库，在数据仓库系统里，并发用户会比
--OLTP系统少，语句数量也会少很多。但是语句的平均复杂度比OLTP系统高很多。而且用户对
--这些复杂的语句，能容忍十几秒，几十秒甚至更长时间

--优化：
--1、数据库设计
--对于经常要运行的查询，他们要做的排序或RID lookup操作可以用covered indexes来优化            数据仓库数据更新一般以周期性的任务进行，而终端用户只做查询工作。所以可以建立比较多的索引，最大程度地优化查询速度。


--尽可能少的碎片，最好小于25%                       数据页面碎片会增加读取同等数据所要读取的页面数，增加内存和I/O负荷，要用重建索引的方式严格控制碎片比率

--由于会有一些很复杂的查询，全表扫描是难免的，但是要注意不要缺少重要的索引                    缺少索引会大大降低查询的性能

--要避免没有用的索引                              没有用的索引会凭空增加SQLSERVER的维护负担


--返回当前数据库所有碎片率大于25%的索引并进行重建索引
--运行本语句会扫描很多数据页面
--避免在系统负载比较高时运行
-------------------------------------------------------------------------------------------------------------
USE master       --改为你要扫描索引碎片的那个数据库
DECLARE @dbid INT
SELECT @dbid=DB_ID()
SELECT * FROM sys.dm_db_index_physical_stats(@dbid,NULL,NULL,NULL,null)
WHERE  avg_fragmentation_in_percent>25
ORDER BY avg_fragmentation_in_percent DESC

--查索引名,某个object_id里面有几个索引，即某个表里有几个索引
SELECT i.*
FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id=o.object_id
WHERE i.object_id=1115151018

--查表名
SELECT name AS N'表名',* FROM sys.objects WHERE type='u' and object_id=1115151018  --用户表

--重建索引
USE master   ----改为你要扫描索引碎片的那个数据库
ALTER INDEX spt_valuesclust ON dbo.spt_values REBUILD WITH(online=on)
ALTER ix2_spt_values_nu_nc ON dbo.spt_values REBUILD WITH(online=on)

-----------------------------------------------------------------------------------------------------------------


--当前数据库可能缺少的索引
SELECT
d.*,
s.avg_total_user_cost AS avgtotalusercost,
s.avg_user_impact AS avguserimpact,
s.last_user_seek AS lastuserseek,
s.unique_compiles AS uniquecompile
FROM sys.dm_db_missing_index_group_stats s,
sys.dm_db_missing_index_groups g,
sys.dm_db_missing_index_details d
WHERE s.group_handle=g.index_group_handle
AND d.index_handle=g.index_handle
ORDER BY s.avg_user_impact DESC


--推荐建立索引的字段
DECLARE @handle INT
SELECT @handle=d.index_handle
FROM sys.dm_db_missing_index_group_stats s,
sys.dm_db_missing_index_groups g,
sys.dm_db_missing_index_details d
WHERE s.group_handle=g.index_group_handle
AND d.index_handle=g.index_handle
SELECT * FROM sys.dm_db_missing_index_columns(@handle)
ORDER BY column_id

--2、CPU
--signal waits          指令等待CPU资源运行的时间占总时间的百分比。如果超过25%，说明CPU资源紧张

--避免执行计划重用      数据仓库系统里用户发过来的指令量比OLTP要少很多，但是每一句都会复杂很多，
--要做多得多的I/O动作，所以保证使用最贴切的执行计划比避免compile要重要得多

--并行执行计划应该被广泛使用，cxpacket应该是最常见的等待状态           并行执行计划对主要运行复杂查询的数据仓库系统
--比较合适。如果不是这个等待状态最多，要不就是查询还不够复杂，不用并行就已经能达到良好的速度，要不就是系统还有其他
--瓶颈







--3、内存
--memory grants pending计数器               SQLSERVER MEMORY MANAGER计数器        等待内存分配的用户数目。如果有这样的情况发生
--，一定有内存压力

--page life expectancy计数器                SQLSERVER BUFFER MANAGER              由于查询会访问一些历史数据，很难保证SQL能够
--将所有要访问的数据都缓存在内存里，所以在数据仓库里，时不时有一些database paging的动作是难免的。在这方面的要求比OLTP系统要低得多。但是如果page life expectancy经常地下降，说明内存很缺乏，整体性能会受影响，还是要检查一下是不是可以通过索引来优化



--4、I/O
--数据仓库的磁盘读要比OLTP系统要高很多，这是难免的。但是只要是I/O瓶颈，就会影响系统的性能。所以还是要检查，
--是否有优化的空间

--average disk  sec/read计数器           physical disk    在没有I/O压力的情况下，读操作应该在4~8ms以内完成

--average disk sec/write                 pyhsical disk    对于像日志文件这样的连续写，应该在1ms以内完成

--big scans                                语句缺少合适的索引

--if top 2 values for wait stats are any of the following:
--asynch_I/O_completion
--I/O_completion
--logmgr
--writelog
--pageiolatch_x                         select top 2 wait_type from sys.dm_os_wait_stats order by wait_time_ms desc
--这些等待状态意味着有I/O瓶颈





--5、阻塞
--和其他资源不同，阻塞对数据仓库系统的危害和对OLTP系统一样严重，一样要严格避免。唯一有点不同的是，
--由于数据仓库系统的修改量在工作时间一般比较少，可以考虑使用row versioning（行版本）技术，避免写阻塞读的情况

--阻塞发生频率                可以检查用户表在row lock 上发生阻塞的频率
--阻塞事件报告                在SQL Trace 里自动报告超过30秒的阻塞语句
--平均阻塞时间                阻塞发生时间的长短
--排在前两位的等待状态以这样开头：LCK_M_??   select top 2 wait_type from sys.dm_os_wait_stats order by wait_time_ms desc
--说明系统经常有阻塞，可以考虑使用row versioning行版本技术，避免写阻塞读的情况


--和OLTP系统相反，数据仓库系统里指令量比较少，并发度低，以查询为主，但是每条指令很复杂。

--1、数据仓库里的数据库的表可以建立多一些索引
--这是因为在非工作时间里数据一般不会被修改，主要的数据同步工作都以批处理任务的方式在工作时间进行
--2、宁可多做一些recompile，少重用他人的执行计划
--对于复杂的查询，执行时间通常会远大于compile时间，而执行时间的长短和执行计划的优劣密切相关。因此，每次执行都做一次编译，
--确保SQLSERVER能够选择最好的执行计划常常是合算的

--3、如果有很大结果集的排序，可以考虑加一个索引来避免
--4、对每个SQL认为缺少的索引，都应该加以分析，看看应该怎麽解决
--5、如果大的扫描是难以避免的，那么数据在磁盘上连续存放对性能会极有帮助。同时要用reindex的方法把碎片降低到最小限度
--6、通常情况下，并发执行对数据仓库里的语句会有帮助

 

 

 

 

--SQL2005 DMV SQL启动以来累计使用CPU资源最多的语句 前50名
SELECT
highest_cpu_queries.*,
highest_cpu_queries.total_worker_time,
DB_NAME(q.dbid)  AS dbname,
q.[text] AS qtext
from
(SELECT TOP 50 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
ORDER BY highest_cpu_queries.total_worker_time DESC

 

f