要想明白SQL的运作原理,必先梳理好SQL在内存中的使用概念
学习之前先来个趣图大家轻松的去学习:
正文开始:首先我们先来分析下SQL server的内存空间及内存管理
①SQL SERVER 内存空间主要可分为两部分:
1.1、可执行代码(Executable Code)
主要包括SQL SERVER 实例中的一些EXE和DLL文件
(1)SQL SERVER 引擎(SQL SERVER ENGINE),SQL SERVER 数据库的主程序;
(2)服务端网络库(SERVER NET-LIBRARY),用于与客户端网络库通讯,将TDS包交给开放数据服务(OPEN DATA SERVICES);
(3)开放数据服务(OPEN DATA SERVICES),用于将TDS包中的SQL 语句还原,交给关系引擎;
(4)分布式查询(DISTRIBUTED QUERIES),用于支持从异构数据源中访问数据(也包括从SQL SERVER到SQL SERVER),这里加载访问异构数据源的OLE DB PROVIDER;
(5)扩展存储过程(EXTENDED STORED PROCEDURES),在后续版本中将彻底用CLR集成来取代扩展存储过程;
(6)OLE自动化(OLE AUTOMATION),用于SQL SERVER引用OLE自动化对象,事实上也是扩展存储过程,单独列出只是因为其功能特殊性;
1.2、缓冲池(Buffer Pool)
(1)系统级数据结构(SYSTEM-LEVEL DATA STRUCTURES),数据实例上的全局信息,包括数据库描述、锁、正在引用的对象及数据库等;
(2)连接上下文(CONNECTION CONTEXT),有点类似WEB应用程序中SESSION的概念,包含当前数据库连接及状态,存储过程的参数、游标的当前位置、所引用的表等;
(3)过程缓存(PROCEDURE CACHE),所有存储过程或SQL语句的执行计划,如果SQL语句存在相应的执行计划,则直接使用该执行计划,无须再次编译SQL语句。性能计数器PLAN CACHE中的缓存命中率(CACHE HIT RATIO)就是指的这里。
(4)数据缓存(DATA CACHE), 用于数据库数据或索引页的读写,性能计数器BUFFER MANAGER中的缓存命中率(BUFFER CACHE HIT RATIO)就是指的这里。在物理内存充足的情况下,通常这个值会高于90%乃至更高,只有很少的数据页需要去物理读,如果低于90%可能是需要扩展物理内存了;
(5)日志缓存(LOG CACHE),用于数据库日志页的读写,每一次的DML都会在这里生成相应的REDO日志操作,如果回滚事务的话,还会根据REDO日志生成相应的反向REDO日志;
备注:只有过程缓存、数据缓存、日志缓存的大小是由SQL SERVER根据性能需要动态管理的,而其他内存空间则是随着用户请求的发起在变化。
②SQL SERVER内存管理
(1)SQL SERVER的缓冲池统一以8k作为内存块大小,大于8k的大内存块是被独立管理的,从缓冲池外通过操作系统直接分配;
(2)AWE内存只能用于数据缓存,其他内存组件是无法使用AWE内存的;
(3)锁定内存页(Lock Pages In Memory),将这个操作系统策略指定给启动SQL SERVER服务的用户,以阻止SQL SERVER缓存从物理内存到虚拟内存的交换。很多书本资料上都提出建议:无论在32位或64位机器上都打开这个策略。事实上在32位机上,用户模拟地址空间很少,当一些非数据缓存的内存组件吃掉大量内存空间时,又不能使用虚拟内存,此时就会内存告急了(虽然这种情况很少会遇到);
(4)SQL SERVER随着用户连接和请求的增加,会不断去获取物理内存,直至达到目标内存(TARGET MEMORY)和最大服务器内存(MAX SERVER MEMORY)中较小的一个。如果SQL SERVER服务器上操作系统没有自由内存,或其他应用程序有内存压力存在,SQL SERVER会调整其目标内存大小,也会释放物理内存,但不会释放到低于最小服务器内存(MIN SERVER MEMORY);如果没有其他内存压力,SQL SERVER在繁忙时获取的内存不会在空闲时主动释放。
然后我们再来聊一下SQL事务运行过程中关于内存的使用。
当commit提交事物的时候,sqlserver的预写机制是先将操作写入事务日志
(1)每当事务结束时。
(1.1)事务结束的前提是日志缓存成功写入到日志文件中,此时,数据库才会返回事务结束的响应;
(1.2)日志缓存并不是一定要等到事务结束时才刷新到日志文件的;
(2)每当遇到LOG WRITER时。
(2.1)当遇到长事务时,不必等到发出事务结束命令,LOG WRITER也会周期性地将脏日志刷新到日志文件,以保证用户发出COMMIT时快速响应并结束;
(2.2)微软并没有公布SQL SERVER 除去COMMIT外,LOG WRITER将脏日志刷新到日志文件的周期,这里可以参考ORACLE的:每3秒;日志缓冲区1/3满或已经包含1M的脏日志;
(3)遇到LAZY WRITER
(3.1)LAZY WRITER周期性扫描缓存(默认1s),维护自由页面列表,根据LRU算法将非脏页释放;
(3.2)如果是脏页则刷新到磁盘,同样也是先将脏日志刷新到日志文件中,然后再将脏页刷新到数据文件中,最终内存页释放并加入自由页面列表;
(4)遇到检查点CHECKPOINT时。
(4.1)CHECKPOINT同LAZY WRITER一样也会刷新脏页到数据文件中,但不会维护内存自由页面列表;
(4.2)可以设置SP_CONFIGURE ‘RECOVERY INTERVAL’选项来改变CHECKPOINT发生的频率。
备注:带上个CHECKPOINT与LAZY WRITER 的区别便于大家的理解:1)Checkpoint和lazyWriter都会将缓冲区内脏页写入到磁盘;2)lazywriter会更新缓存区空闲可用列表,而checkpoint不会;3)checkpoint操作会被记录到数据库日志中,而lazywriter不会;4)lazywriter是内存达到一个临界值的时候自动发生,确保缓存的使用,而检查点则不关心缓存用了多少,而是自动发生.
最后:附上张SQL内存结构图方便大家的理解