sql server 体系结构

1. SQL Server 引擎概述
SQL Server有四大组件：协议（Protocol）、关系引擎（Relational Engine）（又称查询处理器（Query Processor））、存储引擎（Storage Engine）和SQLOS。任何客户端应用程序提交给SQL Server执行的每一个批处理（Batch）都必须与这四个组件进行交互。

1.1 协议组件：负责接收请求并把它们转换成关系引擎能够识别的形式。它还能够获取任意查询、状态信息、错误信息的最终结果，然后把这些结果转换成客户端能够理解的形式，最后再把它们返回到客户端。

1.2 关系引擎组件：负责接受SQL批处理然后决定如何处理它们。对T-SQL查询和编程结构，关系引擎层可以解析、编译和优化请求并检查批处理的执行过程。如果批处理被执行时需要数据，它会发送一个数据请求到存储引擎。

1.3 存储引擎组件：负责管理所有的数据访问，包括基于事务的命令（Transaction-based command）和大批量操作（Bulk Operation）。这些操作包括备份、批量插入和某些数据库一致性检查（Database Consistency Checker，DBCC）命令。

1.4 SQLOS组件：负责处理一些通常被认为是操作系统职责的活动，例如线程管理（调度），同步单元（Synchronization Primitive），死锁检测和包括缓冲池(Buffer Pool)的内存管理。


图-1 SQL Server数据库引擎的主要组件

1.5 如何观测数据库引擎行为: SQL Server 2005引入了一套新的系统对象。它们使开发人员和数据库管理员能够观测到以前所无法观测到的很多SQL Server的内部信息。这些元数据被称为动态管理视图(DMV)和动态管理函数（DMF）。DMV和DMF不是基于真实存在于数据库文件中的表，而是基 于SQL Server的一些内部结构，它们都存在于系统架构(sys schema)中，其名字都以dm_开头，后面跟着标明该对象功能类别的代码。下面列出一些常用类别：
■ dm_exec_* 包含与用户代码执行和相关数据库连接直接或间接相关的信息。
■ dm_os_* 包含如存储、锁和调度等系统底层信息。
■ dm_tran_* 包含当前事务的细节信息。
■ dm_io_* 跟踪网络和磁盘上的输入输出活动。
■ dm_db_* 包含数据库和数据库对象例如索引的细节信息。

2. SQL Server 引擎概述

2.1 协议组件概述
当一个应用程序与SQL Server数据库引擎通讯时，协议层提供的应用程序编程接口利用微软定义的表格格式数据流（Tabular Data Stream ，TDS) 信息包来规范通讯格式。在服务器和客户端上都有可供使用的网络库（Net-Libraries），它可以用来把TDS信息包封装为标准的通信协议（例如 TCP/IP和命名管道）信息包。在通信的服务器端，网络库是数据库引擎的一部分，该协议层在图1中有所描述。在通信的客户端，网络库是SQL Native Client协议的一部分。客户端和SQL Server实例的配置决定了实际使用哪一种协议。可用的协议有以下几种：
■ 共享内存  这是最简单的协议，无须配置。
■ 命名管道  为局域网(LAN)而开发的协议。
■ TCP/IP 因特网上使用最为广泛的协议。
■ 虚拟接口适配器 (VIA) 它是一种与VIA硬件一起使用的专门化的协议。

2.2 关系引擎组件概述
关系引擎又称为查询处理器。它包括用来确定某个查询所需要做的操作以及进行这些操作最佳方式的SQL SERVER组件。关系引擎也负责当其向存储引擎请求数据时查询的执行，并处理返回的结果。关系引擎和存储引擎之间的通讯一般以OLE DB行集的形式进行。（行集是OLE DB术语，等同于结果集。）关系引擎包含有以下子组件：

2.2.1 命令解析器
命令解析器处理发送给SQL Server的T-SQL语言事件。它可以检查T-SQL语法的正确性并把其翻译为可以执行的内部格式。这种内部格式称为查询树。

2.2.2 查询优化器
查询优化器从命令解析器获得查询树，并为它的实际执行作准备。不能优化的语句例如控制流和DDL命令将会被编译成一种内部格式。可优化的语句会被标记并随 后传送给优化器。查询优化器主要关注DML语句，包括：SELECT, INSERT,UPDATE和DELETE。这些语句可以有多种处理方式，由查询优化器来判断哪种处理方式是最佳的。查询优化器将编译整个批命令，优化可 以优化的查询并检查安全性。查询优化和编译的结果就是一个执行计划。

2.2.3 SQL 管理器
SQL 管理器负责管理与存贮过程及其执行计划有关的一切事务。它会判断什么时候一个执行计划需要重新编译，并管理存储过程的缓冲区以便其它进程能够重用这些缓冲 区。SQL管理器也负责管理查询的自动参数化。在SQL Server 2005中，某些定制的查询会被视为参数化的存储过程，SQL Server会为这些查询生成并保存执行计划。但是在一些情况下复用保存的执行计划也许并不合时宜，从而需要重新编译该执行计划。

2.2.4 数据库管理器
数据库管理器管理查询编译和查询优化所需的对元数据的访问，这使我们可以看清其实所有这些单独的模块都不能完全脱离其它模块来运行。元数据被作为数据存储 并由存储引擎来进行管理，但是某些元数据要素例如各数据列的数据类型和一张表上可用的索引必须在实际的查询执行开始之前就能够访问。

2.2.5 查询执行器
查询执行器运行查询优化器生成的执行计划，它就像一个调度员负责调度执行计划中的所有命令。该模块逐步地运行执行计划中的每一个命令直到该批命令结束。其中大多数命令都需要与存储引擎进行交互来修改或取回数据以及管理事务和锁。

2.3存储引擎组件概述
传统上认为SQL Server 存储引擎包括了与处理数据库中数据有关的所有组件。SQL Server 2005从全部这些组件中抽出一些组成一个称为SQLOS的模块。实际上，微软SQL Server存储引擎团队的工作可以分为三个领域：存取方法，事务管理和SQLOS。

2.3.1存取方法
当SQL Server需要定位数据时，它会调用存取方法代码。存取方法代码创建和请求对数据页面和索引页面进行扫描，并且准备好OLE DB数据行集来返回给关系引擎。类似地当插入数据时，存取方法代码可以从客户端取回一个OLE DB数据行集。存取方法代码包含有用来打开一张表，取回合格的数据和更新数据的所有组件。存取方法代码并不真正取回数据页面。它向缓冲区管理器发出请求， 缓冲区管理器负责最终从缓冲区中提供数据或者从磁盘上把数据读到缓冲区中。当扫描开始后，有一种预查机制会检查一个数据页上的数据行和索引项是否合格。取 出符合指定标准的数据的过程称为“有效取出”。存取方法代码不仅被用于查询（select）操作，还被用于有效的更新和删除操作（例如，含有WHERE子 句的UPDATE语句）以及需要对索引项进行修改的任何数据修改操作。

2.3.2事务服务
SQL Serve的一个核心特性就是它能够保证事务的原子性—那就是一个事务要么全部做完，要么干脆不做。另外，事务必须是持久的，也就是说如果一个事务已经被 提交了，SQL Server必须做到不论在什么情况下（即使是整个系统在该事务提交生效之后1毫秒就出故障了）也能够恢复该事务。实际上一个事务要同时具备四种我们称之 为ACID的属性：原子性、持续性、隔离性和持久性。

2.3.2 SQLOS
SQL Server 2005之前的SQL Server版本在存储引擎和实际的操作系统之间有一层很薄的接口层，SQL Server通过该接口层向操作系统申请分配内存，调度资源，管理进程和线程以及同步对象。但是访问该层所需要的服务可以分布在SQL Server引擎的任意部分中。现在SQL Server2005对内存管理，调度器和对象同步等的需求已经变得更加复杂了。SQL Server没有对其引擎中所有涉及访问操作系统的部分分别进行增强来支持功能的增长，而是选择了将所有需要访问操作系统的服务归为一组并纳入单个功能单 元，该单元我们称之为SQLOS。总的来讲SQLOS就像SQL Server内部的操作系统。它提供了内存管理，工作调度，IO管理，锁和事务管理的框架，死锁探测，还有包括副本制作，例外处理等各种通用功能。

3 调度器
调度器是SQLOS的一个重要组成部分。在SQL Server 7.0之前，调度完全依赖于底层的Windows操作系统。虽然这意味着SQL Server能够利用Windows工程师辛勤工作的成果来增强产品的可扩展性和CPU的利用率，但它也有一些局限。因为Windows调度程序对关系数 据库系统的需求一无所知，因此它如同对待任何其它运行在系统上的进程一样对待SQL Server工作线程。不过像SQL Server这样对性能有很高要求的系统只有当调度程序能够满足其特殊需要的情况下才会有最佳表现。在SQL Server 7.0和SQL Server 2000中，由于调度器主要是运行在用户模态而非核心模态，所以被称为用户模式调度器（UMS）。SQL Server 2005的调度器称为SOS调度器，它相对UMS有很多的改进。

SQL Server调度器不论是UMS还是SOS，和Windows调度器最主要的区别是SQL Server调度器是作为一个协作的而非先入为主的调度器。这意味着依赖于工作线程或纤程自发生成的调度器常常已经足够使用，所以一个SQL Server进程或纤程不会像Windows进程那样有时会独占系统资源。

3.1 SQL Server 工作程 可以认为SQL Server调度器是SQL Server工作程使用的一个逻辑CPU。该工作程可以是一个线程也可以是一个绑定到逻辑调度器的的纤程。如果设置了关联掩码选项，每个调度器都与某个 CPU相关联。因此，每个工作程也都会与单个CPU相关联，每个调度器被指派的工作程数目极限取决于所设置的最大工作线程（Max Worker Threads）和调度器的数量，每个调度器跟据需要负责创建和销毁工作程。一个工作程不能从一个调度器转移到另一个调度器，但是通过创建和销毁工作程， 可以使得工作程看起来能够在调度器之间迁移。调度器收到请求（执行某个任务）并且没有空闲工作程存在时会创建新的工作程。一个工作程如果空闲时间超过15 分钟或者SQL Server内存紧张，就可能被销毁。每个工作程在32位系统中至少可以使用0.5MB内存，在64位操作系统中至少可以使用2MB内存。所以销毁多个工 作程并释放它们所占用的内存在内存紧张的系统上可以收到立杆见影的性能改善。

3.2 SQL Server调度器  在SQL Server 2005中，每个CPU(不论是超线程还是物理CPU)都会在SQL Server启动时创建一个线程。即使是在关联掩码选项的设置是SQL Server 不能使用全部的可用CPU时，该启动线程也会被创建。在SQL Server 2005中，每个调度器的状态或者是联机或者是离线，具体设置取决于关联掩码的设置。在缺省情况下，所有的调度器都是联机的。通过设置关联掩码我们可以改 变调度器的状态为离线，并且该设置不需要重启SQL Server即可生效。

3.3 SQL Server 任务  SQL Server工作程的单位是请求或者说是任务，我们可以将其视为与从客户端到服务器的单个批处理等价。一旦SQL Server收到一个请求，该请求会被绑定到一个工作程，这个工作程会在处理其它别的请求之前处理整个请求。即使出于某种原因，比如等待I/O完成和锁资 源该请求被阻塞也是这样。这个特殊的工作程不会处理任何别的新请求，但是会等待直到阻塞条件消除和请求完成。需要注意的是SPID(会话ID)并不与任务 相同。一个SPID是一个连接或者通道，通过它请求可以被送出，但是并不是任意某个SPID都有活动的请求。

在SQL Server 2000中，创建连接后每个SPID都分配有一个调度器，并且所有通过同样的SPID送出的请求由同一个调度器来负责调度。是否分配一个SPID给一个调 度器取决于已经分配给该调度器的SPID的数量，新的SPID会被分配给当前用户最少的调度器。虽然这提供了初步的负载均衡形式，但它却未考虑那些完全不 活动和那些有着巨大工作量（如数据装载）的SPID。在SQL Server 2005中，一个SPID并不会绑定到一个特定的调度器。每个SPID有一个优先调度器，该调度器就是该SPID最新请求服务过的调度器。开始时SPID 会被分配给负载最轻的调度器（可以通过查看在DMV dm_os_scheduler的load_factor列来了解每个调度器的负载状况）。然而，当该SPID发出后续的请求时，如果另一个调度器的负载 系数小于所有调度器负载系数均值的一定百分比，新任务会被分配给有着最小负载系数的调度器。

3.4 线程与纤程  就像前面提到的那样，用户模态调度器的设计用途是与运行在线程或纤程上的工作程一起工作。Windows纤程相关的开销比线程要小，多个纤 程可以运行在单个线程上。可以通过设置Lightweight Pooling 选项的值为1来使SQL Server运行在纤程模式。但是当SQL Server运行在纤程模式时，SQL Server的某些组件不能工作或不能工作的很好。这些组件包括SQLMail和SQLXML。其它组件如异构查询和CLR查询在纤程模式下根本不被支 持，因为它们需要Windows提供的某些线程专有的功能。虽然SQL Server可以切换到线程模式来处理需要该模式的请求，但是引起的开销有可能比使用纯线程模式的开销还要大。纤程模式实际上是为了某些特殊场合和情况而 设计的，在这些场合里由于花费太多的时间联机程上下文和用户模态与核心模态之间进行切换，SQL Server的可扩展性达到极限。在大多数环境里，使用纤程对性能的提升与对其它方面进行调优所带来的性能改善相比相当有限。即使能够确定我们所遇到的境 况需要使用纤程，也还是需要在生产服务器上设置该选项之前对它进行彻底地测试。

3.5 动态关联 在SQL Server 2005中（除了SQLExpress之外的所有版本），可以动态地控制处理器关联。当SQL Server启动时，所有的调度器任务都开始执行，所以每个CPU都有一个调度器。如果已经设置了关联掩码，其中一些调度器会被标示为离线，也不会有任务 分配给它们。当改变关联掩码来包括额外的CPU时，新的CPU会上线。调度器监视器随后会注意到负载的不均衡并开始将工作程迁移到新的CPU。当通过改变 关联掩码来使一个CPU下线，那个CPU的调度器会继续运行活动的工作程，但调度器自身会迁移到其它仍然联机的CPU上。不会有新的工作程被分配给这个已 经离线的调度器，当所有活动的工作程都完成后，调度器就会停止。

3.6绑定调度器到CPU   需要记住正常情况下调度器并不是按照严格的一比一的关系绑定到CPU上，即使调度器的数量同CPU的数量一样多。调度器只有在设置了关联掩码后才会绑定 到CPU。对一个八处理器的计算机，关联掩码的值为3（比特串为00000011）意味着只会使用CPU 0和1，并且将会有两个调度器绑定到这两个CPU上。如果设置关联掩码的值为255(比特串为11111111)，那么就像缺省情况那样所有的CPU都会 被用到。然而一旦关联掩码被设置，这8个CPU将会被绑定到8个调度器上。


3.7 观察调度器内幕  SQL Server 2005有数个动态管理对象可以提供关于调度器、工作和任务的信息。这些对象主要是为微软客户支持服务而准备的。不过我们也可以使用它们来获得更多超值的 SQL Server跟踪信息。需要注意的是所有这些对象都需要SQL Server 2005具有称为“观察服务器状态”的权限。缺省情况下，只有管理员才具有该权限，但该权限也可以被赋予他人。下面列出几个主要的相关动态管理对象：
sys.dm_os_schedulers  这个视图为SQL Server中的每个调度器返回一行。每个调度器都与SQL Server中的单个的CPU相对应。我们可以使用这个视图来监视一个调度器的状态，从而识别失控的任务。
sys.dm_os_workers  该视图为系统中的每个工作程返回一行。
sys.dm_os_tasks  该视图为SQL Server实例上每一个活动的任务返回一行。
sys.dm_os_waiting_tasks  这个视图返回正在等待资源的任务队列的信息。

4. 内存管理
内存管理是一个很大的话题，本文将主要讨论两部分内容：第一部分将提供关于SQL Server如何使用内存资源的足够信息，利用这些信息我们可以判断某个系统上的SQL Server的内存管理是否良好；第二部分将描述用户内存管理的各个方面，这可以帮助我们了解在何时进行何种控制。缺省情况下，SQL Server 2005几乎是完全动态地管理它的内存的。在分配内存时，SQL Server必须持续地与操作系统通信，这是SQL Server引擎中SQLOS层如此重要的原因之一。

4.1缓冲池和高速数据缓冲区
SQL Server主要的内存组件是缓冲池。所有不被其它内存组件使用的存储器都保留在缓冲池里以便用做从磁盘上的数据库文件读取数据页的高速数据缓冲区。缓冲 管理器管理磁盘I/O功能，将数据页和索引页放在数据高速缓冲区中以便多个用户可以共享数据。当其它组件需要内存时，它们能够从缓冲池中申请一块缓冲区。 一块缓冲区就是内存中的一个数据页，其大小与数据页和索引页相同。我们可以认为它就是一个可以容纳来自数据库页面的页框架。取自缓冲池为其它存储器组件使 用的缓冲块使用其它种类的高速缓冲区，其中最大的主要用来作为存储过程和查询计划的高速缓冲区，通常称为“过程高速缓冲区”。

SQL Server 偶尔需要申请大于8KB的连续内存块 ，缓冲池却只能提供8KB的内存块。这时只能从缓冲池外分配内存。因为一般情况下系统会控制尽量少地使用大内存块，所以直接通过操作系统分配的内存只占SQL Server内存使用的很少一部分。

4.2访问内存中的数据页
访问数据高速缓冲区中的页面必须非常迅速。当拥有数以GB计的数据时，即使是在真实内存中，以扫描整个高速缓冲区来寻找一个数据页的方法也是非常荒谬而低 效的。因此高速缓冲区中的数据都经过哈希处理以支持高速数据访问。哈希方法是一种统一地利用经过一组哈希漏斗的哈希函数来映射一个键值的方法。一个哈希表 是内存中的一个结构，它包含有一组指针（作为链接列表）指向缓冲区页面。如果一个哈希页面无法容纳指向缓冲页面的所有指针，那么链接列表会指向下一个哈希 页面。

4.3管理数据高速缓冲区中的页面

只有当数据页和索引页存在于内存中时我们才能够读取它。因此数据高速缓冲区必须有可用缓冲区块来容纳读入的页面。保证供即时使用的缓冲区块供给充足是一种 重要的性能优化手段。如果没有可供即时使用的缓冲区块，SQL Server将不得不搜索大量的内存页来寻找一个可以释放并可作为工作空间使用的缓冲区块。在SQL Server 2005中有一种机制既负责将改动过的页面写入磁盘，又负责将很久不被引用的页面标为自由页面。SQL Server维护有一个由自由页面地址组成的链接列表，需要一个缓冲区页的工作线程可以使用该列表所指向的第一个页面。

4.4检查点
检查点过程也会周期性地扫描高速缓存并且将特定数据库中的脏数据页写入磁盘。检查点过程和惰性写入器（或者说是工作线程的页面管理）的不同在于检查点过程 从不会把缓存块加入自由列表。检查点过程的目的只是为了保证在某个特定时间之前写入的页面被写入磁盘，从而使内存中的脏数据页的数量保持在最小，这可以保 证SQL Server在发生故障之后恢复一个数据库所需要的时间保持为最小。在某些情况下，如果大多数的脏数据页都被工作过程或两个检查点之间的惰性写入器写入磁 盘，那么检查点会发现可写入磁盘的脏数据页非常之少。

当一个检查点发生时，SQL Server会将该检查点的记录写入事务日志，事务日志中含有所有活动事务的记录。这使得恢复过程能够建立一个包含所有可能是脏数据页的页面列表。检查点能够按照规律间隔自动地发生，也可以人为地请求其发生。

检查点在如下几种情况下会被触发：
        数据库所有者明确地发出检查点指令在数据库中执行检查点操作。
        日志快要被充满时（已达到其容量的70%）并且数据库是在SIMPLE恢复模式。
        估计出的恢复时间非常之长。当预测的恢复时间比配置选项中的恢复间隔还要长时，一个检查点就会被触发。
        SQL Server收到正常的没有附加NOWAIT选项的关闭请求之后，将会有一个检查点操作在SQL Server实例的每一个数据库上执行。

检查点过程会遍历整个缓存池，以非线性的顺序扫描数据页，并且当它发现一个脏数据页时，它会查看是否有成片脏的而且物理上连续的页面以便整块写入磁盘。但 是这意味着（举例来说）：当发现缓存块14是脏页时，它也许会将缓存块14、200、260和1000写入磁盘。（即使那些页面在缓冲池中相距，它们仍可 能会有连续的物理位置。在这个例子中，这些缓冲池中并不连续的页面能够作为单个操作写入磁盘，这种操作我们称之为聚集写入。）该检查点过程会继续扫描缓存 池直到到达页面1000。在某些情况下，一个已经写入的页面可能会再度变脏，并且需要第二次写入磁盘。



4.5调节内存大小
当谈到SQL Server内存时，实际上并不仅仅是在讨论缓存池。SQL Server内存实际上是由三部分组织起来的，缓存池通常是最大和使用最为频繁的。因为缓存池是作为一组8-KB的缓存块使用的，所以大于8KB的大内存 快需求是被单独管理的。名为sys.dm_os_memory_clerks的动态管理视图有一个称为multi_pages_kb的列来显示一个内存组 件使用了多少缓存池之外的空间：

SELECT type, sum(multi_pages_kb)
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type

如果SQL Server实例被配置为使用地址窗口化扩展（AWE）内存，该部分内存可以认为是第三种内存区域。AWE是一种允许32位应用程序访问32位地址限制之 外物理内存的应用程序接口。因为只有数据高速缓存页面才能够使用AWE内存，所以虽然AWE内存是作为缓冲池的一部分来分派的，但是必须对其进行单独跟 踪。


4.6调节缓存池大小
当SQL Server启动时，它会计算SQL Server进程的虚拟地址空间（VAS）的大小。每个运行在Windows上的进程都有自己的VAS。所有可供进程使用的虚拟地址的集合构成了VAS的 大小。VAS的大小取决于体系结构和操作系统。VAS只是所有可能地址的集合；它可以比计算机上的物理内存大上很多。

一个32位的计算机只能够直接寻址4GB内存，并且缺省情况下Windows自身会将2GB的地址空间留作己用，这样一来只剩下仅仅2GB作为所有应用程 序（例如SQL Server）的最大虚拟地址空间。我们可以通过启用操作系统的Boot.ini文件中/3GB选项来允许应用程序拥有一个高达3GB的虚拟地址空间。如 果系统有多于3GB的RAM内存，使32位计算机能够使用这些内存的唯一方法就是启用AWE选项。

4.6.1观察内存内幕
SQL Server 2005包含了一些提供有关内存和各种高速缓存信息的动态管理对象。比如包含调度器有关信息的动态管理对象，这些对象主要是为客户支持服务工程师查看 SQL Server当前正在做什么而准备的，其实我们自己也可以利用这些对象做这些事情。 要查询这些对象，需要具备观察服务器状态（View Server State）权限。
sys.dm_os_memory_clerks 为SQL Server实例上的每个内存书记返回一行。
sys.dm_os_memory_cache_counters  为用户存储仓库和高速缓冲仓库类型的每个高速缓冲返回一份关于其健康情况的快照。
sys.dm_os_memory_cache_hash_tables  为SQL Server实例中的每个活动的高速缓存返回一行。
sys.dm_os_memory_cache_clock_hands 通过cache_address列可与其它高速缓冲动态管理视图进行连接操作。

观察内存使用的另一种工具是命令DBCC MEMORYSTATUS，该命令在SQL Server 2005中得到了极大的增强。

4.6.2 预读
SQL Server支持一种称为预读的机制，我们可以凭此预期对数据和索引页面的需求，并且可以在页面被实际需要前将它们读入缓冲池。这种性能优化可以使我们能够有效地处理大数据量的数据。预读完全是系统内部管理的，不需要对其进行任何的配置和调整。