SQLServer2008引擎组件

　　首先让我们先来看看SQL Server2008的引擎组件，SQLServer2008有四大组件：协议、关系引擎、存储引擎和SQLOS。

　　协议层(Protocol Layer)

　　当一个应用程序与SQL Server数据库引擎通讯时，协议层提供的应用程序编程接口利用微软自定义的tabular data stream(TDS)package来规范通讯格式。这一层的意义在于向应用程序提供访问SQL Server的接口。

　　SQL Server Network Interface(简称SNI)

　　SNI是在服务器和客户端之间建立网络连接的一种协议，他提供一组在数据库引擎和SQL Server客户端使用的API函数。SNI代替了SQL Server2000下的Net- Libraries组件和MDAC组件。

　　SQL Server支持共享内存(Shared memory)、TCP/IP、命名管道(Named Pipes)、虚拟接口适配器(Virtual Interface Adapter，即VIA)四种协议。

　　一旦建立连接，SNI就会向服务器的TDS断点创建一条安全的连接，用来进行数据的请求和返回。

　　表格格式数据流端点(Tabular Data Stream，简称TDS)

　　TDS是一种微软的与数据库进行交互的私有协议，SQL Server在安装时为其支持的四种协议各创建一个端点，如果协议被激活，那么所有用户均可以使用这个协议。此外还有一个专门为专用管理员连接(DAC)而设置的端点。

　　一条SQL语句则会通过TCP/IP连接以TDS消息的形式发送给SQL Server。

　　协议层(Protocol Layer)

　　一旦协议层接收到TDS包，就会在反转和解包工作，以找到所包含的请求。协议层也负责打包结果和状态消息，并以TDS消息的形式返回客户端。

　　关系引擎(Relational Engine)

　　关系引擎又成为查询处理器，包括用来确定某个查询所要做的操作及进行这些操作非常好的方式的SQL Server组件。同时关系引擎也负责向存储引擎请求数据时查询的执行，并处理返回的结果。

　　命令解析器(Cmd Parser)

　　命令解析器处理发送给SQL Server的T-SQL语言事件。它会先检查T-SQL语法，并返回任何错误信息客户端，如果语法有效，就会进一步产生执行计划或者去查找一个已经存在的执行计划。命令解析器通过T-SQL哈希值向位于缓冲池中的Plan Cache发出匹配要求，以检查是否存在该执行计划;如果不存在则把T-SQL翻译成可以执行的内部格式，即查询树。

　　查询优化器(Optimizer)

　　查询优化器从命令解析器中获取查询树，并为它的实际执行做准备。

　　生成执行计划的第一步是对每个查询进行规范化，规范化的过程有可能将单个查询分解成多个粒度合适的查询。然后进行最优化，SQL Server的查询优化器是基于成本的，它会选择它认为成本最低的执行计划，它使用一些内部指标(内存需求、CPU利用率和I/O需求数目)作为选择的依据。此外查询优化器还会考虑请求语句的类型、检查受到影响的各表的数据量、表中的索引，以及SQL Server统计数据。

　　SQL管理器

　　SQL管理器负责管理与存储过程及其计划有关的事务，并负责管理查询的自动化参数。

　　数据库管理器

　　数据库管理器管理查询编译和查询优化所需的对元数据的访问。

　　查询执行器(Query Executor)

　　查询执行器运行查询优化器生成的执行计划，就像调度员负责调度执行计划中的所有命令。

　　存储引擎(Storage Engine)

　　存储引擎包括存取方法、事务管理和缓冲区管理器。

　　存取方法(Access Methods)

　　SQL Server需要定位数据库时，会调用存取方法代码。它提供了一组代码，用来创建和请求对数据页面和索引页面进行扫描，并且将准备好的OLE DB数据行集返回给关系引擎。存取方法并不真正进行操作，它只负责向缓冲区管理器发出请求。

　　事务管理器(Transaction Manager)

　　事务管理器包括两个组件：日志管理器和锁管理器。

　　锁管理器负责数据的并发保护和基于特定隔离级别的管理。日志管理器负责将事务日志提前记录于日志文件中，从而起到保护数据的作用。

　　缓冲区管理器(Buffer Manager)

　　缓冲区管理器用来管理缓冲区内存池中数据页面的分布。

　　SQLOS

　　SQLOS则可以理解为SQL Server2008的操作系统，主要负责处理与操作系统之间的工作，SQL Server通过该接口层向操作系统申请内存分配、调度资源、管理进程和线程以及同步对象。

　　缓冲池(Buffer Pool)

　　缓冲池在SQL Server中是内存的最大消耗者，主要包括数据缓冲池和执行计划缓冲池。

　　执行计划缓冲池(Plan Cache)

　　生成执行计划是比较消耗资源和时间的，因此在Plan Cache缓存这些执行计划，有助于执行计划的重用。

　　数据缓冲池(Data Cache)

　　数据缓冲池负责将数据页和索引页放在数据告诉缓冲池中，以便多个用户可以共享数据。

　　SQL查询示意图

　　以下为一条基本的SQL查询示意图。

▲

　　首先客户端的SNI通过TCP/IP协议和SQL Server服务端的SNI建立连接，然后建立连接到TDS的连接，并以TDS消息的方式传送SELECT命令。SQL Server服务端的SNI对TDS消息进行解包，并把SQL命令传给语法解析器;语法解析器在缓冲池中检查是否存在执行计划，如果没有则创建一个查询树，并交给优化器;优化器产生相应的执行计划;查询执行器通过OLE DB接口向存储引擎中的访问存取方法发出请求读取数据;存取方法则向缓冲区管理器发起数据请求，如果在缓冲池的数据缓冲池中存在相应的数据，如果没有则从磁盘上读取数据页放到数据缓冲池中，并把数据返回给存取方法;存取方法在把结果返回给关系引擎，并最终以TDS消息的形式返回给客户端。

　　CPU性能诊断

　　CPU架构

　　目前的主流企业服务器基本可以分为三类：SMP(Symmetric Multi Processing，对称多处理架构)，NUMA(Non-Uniform Memory Access，非一致存储访问架构)和MPP(Massive Parallel Processing，海量并行处理架构)

　　SMP(Symmetric Multi Processing)

　　SMP是非常常见的一种架构。在SMP模式下，多个处理器均对称的连接在系统内存上，所有处理器都以平等的代价访问系统内存。它的优点是对内存的访问是平等、一致的;缺点是因为大家都是一致的，在传统的 SMP 系统中，所有处理器都共享系统总线，因此当处理器的数目增多时，系统总线的竞争冲突迅速加大，系统总线成为了性能瓶颈，所以目前 SMP 系统的处理器数目一般只有数十个，可扩展性受到很大限制。

▲

　　MPP (Massive Parallel Processing)

　　MPP则 是逻辑上将整个系统划分为多个节点，每个节点的处理器只可以访问本身的本地资源，是完全无共享的架构。节点之间的数据交换需要软件实施。它的优点是可扩展 性非常好;缺点是彼此数据交换困难，需要控制软件的大量工作来实现通讯以及任务的分配、调度，对于一般的企业应用而言过于复杂，效率不高。

▲

　　NUMA(Non-Uniform Memory Access)

　　NUMA架构则在某种意义上是综合了SMP和MPP的特点：逻辑上整个系统也是分为多个节点，每个节点可以访问本地内存资源，也可以访问远程内存资源，但访问本地内存资源远远快于远程内存资源。它的优点是兼顾了SMP和MPP的特点, 易于管理，可扩充性好;缺点是访问远程内存资源的所需时间非常的大。

　　不过目前世界上排名靠前的大型机都是基于MPP架构的，AMD最早支持NUMA架构，而尽管Intel一直是SMP的支持者，但从Xeon处理器和酷睿i7开始也逐步走向了NUMA架构之路。

　　SQL Server从2000版本的SP4起开始增加了对NUMA的支持，从2005版本起得到了全面的采用;SQL Server2008 R2版本起夜增加了对MPP架构的支持;不过从目前SQL Server的情况来看，主要是支持SMP和NUMA两种架构。

▲

　　Windows性能监控

　　使用管理工具中的“性能”管理工具可以自动从本地或远程计算机收集性能数据。可以使用“系统监视器”查看记录的计算机数据，也可以将数据导出到电子表格程序或数据库进行分析并生成报告。该工具类似于linux下的top，iostat，vmstat等监控命令。

　　对于SQL Server占用CPU资源的监控主要集中在消耗时间的百分比和处理器队列长度上，下面提供了几种对象、计数器和相应的阈值及描述。

▲

　　SQL Server性能监控

　　SQL Server内部监控对象及其计数器可以通过性能工具进行收集和评估，但从SQL Server2005之后提供了一个叫sys.dm_os_performance_counters的系统视图，这个视图返回的信息列举与SQL Server直接相关的性能统计。

　　下面语句可以统计出SQL Server所有的监控对象和计数器情况。

     在本机中共计27中SQLServer对象和334个计数器，其中加粗部分字体为最常关注的对象。

　 以下是对象类别、计数器数量和对象描述。

　　MSSQL$MYSQLSERVER:Access Methods 43 搜索并测量 SQL Server 数据库对象的分配(例如，索引搜索数或分配给索引和数据的页数)。

　　MSSQL$MYSQLSERVER:Broker Activation 6 提供有关已激活 Service Broker 的任务的信息。

　　MSSQL$MYSQLSERVER:Broker Statistics 37 提供 Service Broker 的常规信息。

　　MSSQL$MYSQLSERVER:Broker TO Statistics 9

　　MSSQL$MYSQLSERVER:Broker/DBM Transport 34 提供有关Service Broker网络的信息。

　　MSSQL$MYSQLSERVER:Buffer Manager 21 提供有关 SQL Server 所用的内存缓冲区的信息，如可用内存和 buffer cache hit ratio。

　　MSSQL$MYSQLSERVER:Buffer Node 9 提供有关SQL Server请求和访问可用页的频率的信息。

　　MSSQL$MYSQLSERVER:Buffer Partition 3 提供有关SQL Server请求和访问可用页的频率的信息。

　　MSSQL$MYSQLSERVER:Catalog Metadata 4

　　MSSQL$MYSQLSERVER:CLR 1 提供有关公共语言运行时 (CLR) 的信息。

　　MSSQL$MYSQLSERVER:Cursor Manager by Type 9 提供游标信息。

　　MSSQL$MYSQLSERVER:Cursor Manager Total 3 提供游标信息。

　　MSSQL$MYSQLSERVER:Databases 26 提供有关 SQL Server 数据库的信息，如可用的日志空间数量或数据库中活动事务数。这个对象可有多个实例。

　　MSSQL$MYSQLSERVER:Deprecated Features 1 对使用不推荐使用的功能的次数进行计数。

　　MSSQL$MYSQLSERVER:Exec Statistics 4 提供了有关执行统计信息的信息。

　　MSSQL$MYSQLSERVER:General Statistics 24 提供有关服务器范围内的常规活动的信息，如连接到 SQL Server 实例的用户数。

　　MSSQL$MYSQLSERVER:Latches 7 提供有关加在 SQL Server 所用的内部资源(如数据库页)上的闩锁的信息。

　　MSSQL$MYSQLSERVER:Locks 8 提供有关 SQL Server 的单个锁请求的信息，如锁超时和死锁。这个对象可有多个实例。

　　MSSQL$MYSQLSERVER:Memory Manager 14 提供有关 SQL Server 内存的使用信息，如当前分配的锁结构总数。

　　MSSQL$MYSQLSERVER:Plan Cache 5 提供有关 SQL Server 高速缓存的信息，该高速缓存用于存储如存储过程、触发器和查询计划这样的对象。

　　MSSQL$MYSQLSERVER:Resource Pool Stats 15 提供了有关资源调控器资源池统计的信息。

　　MSSQL$MYSQLSERVER:SQL Errors 1 提供有关 SQL Server 错误的信息。

　　MSSQL$MYSQLSERVER:SQL Statistics 11 提供有关 SQL查询各个方面的信息，如 SQL Server 收到的 Transact-SQL 语句的批数。

　　MSSQL$MYSQLSERVER:Transactions 14 提供了有关 SQL Server 中活动事务的信息，如事务总数和快照事务数。

　　MSSQL$MYSQLSERVER:User Settable 1 执行自定义监视。每个计数器可以是一个自定义的存储过程或任何返回一个被监视值的Transact-SQL语句。

　　MSSQL$MYSQLSERVER:Wait Statistics 12 提供有关等待的信息。

　　MSSQL$MYSQLSERVER:Workload Group Stats 12 提供了有关资源调控器工作负荷组统计的信息。

　　关于CPU的资源消耗基本来自于两个方面，首先是低效率的查询计划，再次是过度编译和重编译。

　　过度编译和重编译，SQL语句的编译和重编译都是CPU密集的活动，发生大量的重编译，则CPU利用率会增加。所以对SQL Server在CPU上的关注会集中在这些SQL统计的编译/重编译计数器上。

▲

     下面语句。

　 SQL Server中，我们也可以确定平均或累计占用CPU时间最多的查询，SQL Server优化器是基于成本的，通过该查询语句的定位，再进一步分析，是统计信息问题还是索引和连接难题。

      以下为SQL Server 2008关于CPU的一些系统视图。

▲

　　dm_os_nodes提供了一个名为 SQL OS 的内部组件可创建模拟硬件处理器位置的节点结构。

　　dm_os_schedulers 对于 SQL Server(每个计划程序都映射到其中的单个处理器)中的每个计划程序，相应地返回一行。使用此视图可以监视计划程序的情况或标识失控任务

　　dm_os_workers 则对于系统中的每个工作线程，相应地返回一行。

　　dm_os_threads 对于系统中的所有SQLOS工作线程，相应地返回一行。

　　sys.dm_os_tasks 为 SQL Server 实例中的每个活动任务返回一行。

　　计算可运行状态下的工作进程数量，来观察CPU压力

　　也可以查找用户会话和操作系统线程的对照关系