SQL Server 2008 基础

SQL流程

     wKioL1fk86eD5RL_AAFeqBqTpX4092.jpg-wh_50

TDS是一种协议,一系列描述两个计算机间如何传输数据的规则。象别的协议一样,它定义了传输信息的类型和他们传输的顺序。总之,协议描述了线上的位,即数据如何流动。表格数据流协议是建立在TCP/IP Net-Library之上的,包含在TCP数据段内。TDS1433端口进行数据库通信。

TDS协议位于TCP的数据段内,结构如下:

IP

TCP

TDS

TDSDATA

    8位头

TDS客户端使用称为表格格式数据流 (TDS) SQL Server 专用应用程序级协议来发送 SQL 语句。数据包由用于 SQL Server Microsoft OLE DB 提供程序、SQL Server ODBC 驱动程序或 DB-Library DLL 生成。TDS 数据包然后被传递给 SQL Server 客户端 Net-Library,客户端 Net-Library TDS 数据包封装为网络协议数据包。在服务器上,服务器 Net-Library 接收网络协议数据包,且析取 TDS 数据包并将其传递给关系数据库服务器。在将结果返回给客户端时,这一进程反向执行。

 

表格数据流端点:一个网络协议一旦连接成功,就会创建一个对应的TDS端点,用于在客户端与服务器之间互传信息

         对于这一步,性能问题可能会出现在把请求的内容转换成SQL Server能处理的格式的过程中,如果数据量大,转换开销就高,不管是从外部传入SQL Server还是从SQL Server输出到外部,都是如此

命令解释器:TDS把请求转换成SQL Server内部可识别的格式后,首先会传到命令解释器处,这个组件会检查传入的T-SQL语法是否符合规定。如果语法符合要求,就好生成查询树传入查询优化器中

         命令解释器同时也会检查是否已经存在缓存了的执行计划,如果存在,就重用;如果不存在,就让查询优化器生成执行计划

查询优化器:该组件是SQL Server的核心部分,优化器的作用是对特定的请求进行一系列的优化,使其尽可能高效地对数据进行操作,最终选择最低开销的候选计划生成预估执行计划,并传入执行器执行查询.

查询执行器:查询执行器是执行查询的组件,但是实际上并不是真正执行,而是通过OLEDB协议访问并使用一系列的指令操作存储引擎进行查询的执行操作。存储引擎才是实际的查询执行者

数据访问方法:数据访问方法是提供数据和索引的存储、查询及修改的代码集合。它包含了索引操作数据的代码,但是并不由自己去数据操作数据,而是提交请求到缓冲管理器

缓冲区管理器:缓冲管理器是管理BufferPool的组件。Buffer PoolSQL Server内存的主要部分,它管理内存中的数据,并把适当的数据作为输出传输给(数据访问方法)。如果所需数据不在于内存中,缓冲管理器需要从磁盘读取对应的数据页到内存的数据缓存,然后把所需的数据返回给数据访问方法。在这个过程中可能会出现一种等待状态PageIOLatch

数据缓存:数据缓存是BufferPool中的最大部分,也可以说是SQL Server中占用内存最多的部分。它用于存放从磁盘加载到内存中的数据,以便后续操作使用

         对于当前数据缓存中每个数据库的缓存大小情况。可以使用一个DMV来查询

         ——sys.dm_os_buffer_descriptors

 

SELECT COUNT(*) * 8 / 1024 AS 'Cached Size(MB)' ,

CASE database_id

WHEN 32767 THEN 'ResourceDb'

ELSE DB_NAME(database_id)

END AS 'Database'

FROM sys.dm_os_buffer_descriptors

GROUP BY DB_NAME(database_id) , database_id

ORDER BY 'Cached Size (MB)' DESC

 

事务管理器

事务管理器主要包含两部分:锁管理器和日志管理器

1、  锁管理器通过使用锁机制来保证数据的并发性和隔离性。

2、日志管理器通过预写入日志机制,保证事务的ACID特性当需要修改数据时,数据访问方法会先到事务管理器中通过对事务日志进行操作,把请求传到Buffer管理器中完成,然后返回给数据访问方法并通过事务管理器写入日志文件中

 

数据库事务

事务是对数据库操作的工作单元,这个单元可以是一个select语句,也可以是包含多个selectupdatedaleteinsert等操作的命令集合。这个集合被认为是工作单元,SQL SERVER的很多特性都是基于这个工作单元,也就是事务

关系数据库包含4大核心事务特性:原子性、一致性、隔离性和持久性,简称ACID特性

 

1、原子性

         原子性意味着一个事务内的所有操作必须全部完成或者全部回滚。典型的例子是在ATM机上操作时,要么取钱、存钱或者转账成功,要么等于从来都没有做过任何操作。如果转账成功但是对方未收到款项,势必造成很大问题

2、一致性

         一致性要求整个事务在运行的前后数据库的状态是一致的,比如库存不能为负值、性别字段的值要么为要么为等。为了确保事务的一致性,事务不能打破数据定义中的一致性约束

3、隔离性

         隔离性保证在同一时间中,一个事务的运行不能被另外一个事务所影响。但是SQL Server实际是允许这个操作的

4、持久性

         持久性意味着事务一旦成功提交,将永久存储到服务器的文件系统中,即使系统在中途崩溃,所发生的效果都不会丢失,这个会通过日志来保证

 

         提交事务意味着永久把事务的影响记录到数据库中,回滚意味着把发生的所有操作去除,等于没有做过,不过日志其实还是会记录这个操作

wKiom1fk89HQ2pKDAAEBZ9_jAbI627.jpg-wh_50

关系引擎和存储引擎是SQLServer的两大组件

         1、关系引擎,也叫查询处理器,因为它的主要作用是优化和执行查询。关系引擎中包含的命令解析器用于检查语法和转换查询树,而查询优化器则用于优化查询,还有一个查询执行器负责执行查询

    2、存储引擎,负责管理所要数据及涉及的I/O,其中包含的数据访问方法用于处理对行、索引、也、行版本、空间分配等的I/O请求,缓冲区管理器用于管理SQL Server的主要内存消耗组件——Buffer Pool,事务管理器将通过所管理数据及维护事务的ACID属性

缓冲池:在数据库性能及管理领域中属于相当重要的一个组件,也是SQL Server中内存消耗最大的组件。缓冲池包含了SQL Server的所有缓存,如计划缓存和数据缓存等

 

下面是逻辑CPU 格式在不同位数的操作系统中

能支持的最大工作者线程数。

1)对于32 位操作系统:

总可用逻辑CPU<=4 时,最大工作者线程=256

总可用逻辑CPU>4 时,最大工作者线程=256+((逻辑CPU-4)×8)。

2)对于64 位操作系统:

总可用逻辑CPU<=4 时,最大工作者线程=512

总可用逻辑CPU>4 时,最大工作者线程=512+((逻辑CPU-4)×16)。

还有一个比较简单的方法可用于检查当前系统的最大线程数。比如执行下面的脚本:

SELECT max_workers_count

FROMsys.dm_os_sys_info

 

通常出现threadpool 类型的等待意味着当前有大量并行执行计划,或者遇到了CPU

颈,但是不管是什么情况,都需要经常检查这部分的数据,以确保有足够的线程可用对

于每个工作线程,在64 位系统中都要消耗2MB 的内存,在32 位系统需要消耗0.5MB

内存,所以SQL Server 只会在需要的时候才创建工作线程。这部分的信息可以通过sys.

dm_os_workers 这个DMV 来查看。

SELECT COUNT(*)

FROMsys.dm_os_workers

 

SQL Server NUMA CPU 配置

 

SELECT scheduler_id ,

cpu_id ,

parent_node_id ,

status

FROM sys.dm_os_schedulers

 

当前已缓存的计划数及所占的MB

 

SELECT COUNT(*) AS 'Number of Plans' ,

SUM(CAST(size_in_bytes AS BIGINT)) / 1024 /1024 AS 'Plan Cache Size (MB)'

FROM sys.dm_exec_cached_plans

 

查看缓存对象的对应内存数

 

SELECT objtype AS 'Cached Object Type' ,

COUNT(*) AS 'Number of Plans' ,

SUM(CAST(size_in_bytes AS BIGINT)) / 1024 /1024 AS 'Plan Cache Size (MB)' ,

AVG(usecounts) AS 'Avg Use Count'

FROM sys.dm_exec_cached_plans

GROUP BY objtype

 

 

查询数据库路径

 

USE master  

go  

SELECT physical_name  

FROM   sys.master_files  

WHERE  database_id = DB_ID('RJBDB')

 

检查证书

select name, start_date, expiry_date

from sys.certificates

where name not like '##%'

 

查看镜像端点

SELECT * FROM sys.endpoints e

 

查询镜像状态

 

select *

from sys.database_mirroring

where database_id=DB_ID('RJBDB')