sql server性能调优(SQL Server性能调优之缓存)
sql server性能调优
SQL Server性能调优之缓存在执行任何查询时,SQL Server都会将数据读取到内存,数据使用之后,不会立即释放,而是会缓存在内存Buffer中,当再次执行相同的查询时,如果所需数据全部缓存在内存中,那么SQL Server不会产生Disk IO操作,立即返回查询结果,这是SQL Server的性能优化机制。
一,主要的内存消费者(Memory Consumer)
1,数据缓存(Data Cache)
Data Cache是存储数据页(Data Page)的缓冲区,当SQL Server需要读取数据文件(File)中的数据页(Data Page)时,SQL Server会把整个Page都调入内存(内存中的一个Page叫做buffer),Page是数据访问的最小单元。
当用户修改了某个Page上的数据时,SQL Server 会先在内存中修改Buffer,但是不会立即将这个数据叶写回硬盘,而是等到CheckPoint或lazy Writer进程运行时集中处理。当用户读取某个Page后,如果SQL Server没有内存压力,它不会在内存中删除这个Page,因为内存中的数据页始终存放着数据的最新状态,如果有其他用户使用这个Page,SQL Server 不需要从硬盘中读取一次,节省语句执行的时间。理想情况是SQL Server将用户需要访问的所有数据都缓存在内存中,SQL Server 永远不需要去硬盘读取数据,只需要在CheckPoint 或 lazy Write运行时把修改过的页面写回硬盘即可
2,查询计划缓存(Query Plan Cache)
存储查询语句和存储过程的执行计划,以供重用,而不需要重新编译(Compile),因为编译查询语句产生执行计划是一个非常耗费资源的过程。
二,查看内存消耗
在SQL Server中,只有内存书记员(Memory Clerk)能够分配内存,Memory Clerk会记录已经分配内存的数量,任何一个需要使用内存的对象,必须创建自己的Memory Clerk,并使用该Memory clerk来分配内存。
1,查看Memory clerk分配的内存量
select memory_node_id, type, pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, shared_memory_reserved_kb, shared_memory_committed_kb, page_size_in_bytes from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLQERESERVATIONS'
对于内存结点64,只在DAC中使用。
2,统计Memory Clerk分配的内存总量
select mc.type,mc.name, sum(mc.pages_kb) as AllocatedPages_KB, sum(mc.virtual_memory_reserved_kb) as VM_Reserved_KB, sum(mc.virtual_memory_committed_kb) as VM_Committed_KB, --sum(mc.shared_memory_reserved_kb) as ShareMem_Reserved_KB, --sum(mc.shared_memory_committed_kb) as ShareMem_Committed_KB, max(mc.page_size_in_bytes)/1024 as SinglePageSize_KB from sys.dm_os_memory_clerks mc group by mc.type,mc.name order by AllocatedPages_KB desc,mc.type,mc.name
消耗内存较大的Clerk是:
- MEMORYCLERK_SQLBUFFERPOOL:基本是Buffer Pool中page的大小
- OBJECTSTORE_LOCK_MANAGER:锁结构使用的内存,当发生严重的锁阻塞时,这表明系统中,存储大量锁,造成锁管理占用大量的内存;
- CACHESTORE_OBJCP:触发器和存储过程等模块(Module)的执行计划占用的缓存空间;
- CACHESTORE_SQLCP:动态TSQL语句,即席(Adhoc)查询和预编译(Prepared) TSQL的执行计划缓存;
- CACHESTORE_COLUMNSTOREOBJECTPOOL:列存储索引(ColumnStore Index)占用的缓存
3,查看缓存中的数据页
当数据页从硬盘读取到内存之后,该数据页被复制到缓冲池(Buffer Pool),供SQL Server重用。每个缓存的数据页都有一个缓存描述器(Buffer Descriptor),用户唯一标识内存中的数据页,在SQL Server实例中缓存的每一个数据页,都能从 sys.dm_os_buffer_descriptors 查看缓存描述的信息。
select DB_NAME(bd.database_id) as dbname, OBJECT_NAME(p.object_id) as ObjectName, i.name as IndexName, count(0) as BufferCounts, sum(bd.free_space_in_bytes)/1024 as TotalFreeSpace_KB, cast(sum(bd.free_space_in_bytes)/(8*1024.0)/count(0) as decimal(10,4))*100 as FreeSpaceRatio, sum(cast(bd.is_modified as int)) as TotalDirtyPages, sum(bd.row_count) as TotalRowCounts from sys.allocation_units au inner join sys.dm_os_buffer_descriptors bd on au.allocation_unit_id=bd.allocation_unit_id inner join sys.partitions p on au.container_id=p.hobt_id inner join sys.indexes i on p.object_id=i.object_id and p.index_id=p.index_id inner join sys.objects o on p.object_id=o.object_id where bd.database_id=DB_ID(N'database_name') and o.type<>N'S' group by bd.database_id,p.object_id,i.name order by BufferCounts desc,dbname,ObjectName
4,查看计划缓存
产生执行计划是十分消耗CPU资源的,SQL Server会在内存的Plan Cache中存储每个查询计划(Query Plan),及其占用的内存空间,重用次数等信息。
select cp.objtype,cp.cacheobjtype, sum(cp.size_in_bytes) as TotalSize_B, COUNT(cp.bucketid) as CacheCounts, sum(cp.refcounts) as TotalRefCounts, sum(cp.usecounts) as TotalUseCounts from sys.dm_exec_cached_plans cp group by cp.objtype,cp.cacheobjtype order by TotalSize_B desc
三,清空缓存
在调优存储过程性能时,清空缓存是必需的,缓冲池(Buffer Pool)是SQL Server的缓存管理器,包含了SQL Server的绝大部分缓存数据(Cache),例如,执行计划缓存(Plan cache),数据缓存(Data cache)等。
清空缓存常用的命令有如下三个:
CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE
Checkpoint和DBCC DROPCLEANBUFFERS 用于清理数据缓存(Data Cache)中的脏页(dirty pages)和干净页(clean pages),而DBCC FREEPROCCACHE 用于清空所有的计划缓存(Plan Cache)。
1,清空数据缓存
checkpoint 用于将脏页(Dirty Pages)写入硬盘,脏页(Dirty Pages)是指数据页读入缓存后,被修改过,导致内存中数据页和硬盘中的数据页中的内容不同;干净页(Clean Pages)是指数据页被读入缓存后,没有被修改过,所以,内存中的数据页和硬盘中的数据页中的内容相同。不管是Dirty pages 还是 Clean pages 都是Data Cache,在性能调优时,都必须从内存中清理掉,否则,查询性能将忽略掉数据从硬盘加载到内存的IO消耗,影响查询语句的执行情况。
CHECKPOINT 命令用于产生冷缓存(Cold buffer Cache),该命令将当前数据库产生的所有脏页写入到硬盘,并清理内存buffer;在执行CHECKPOINT命令之后,执行 DBCC DROPCLEANBUFFERS 用于从缓冲池中清空所有的干净页。
在性能测试时,使用DBCC DROPCLEANBUFFERS从SQLSERVER的数据缓存池中清除所有的clean缓存数据,需要注意的是该命令只移走干净的缓存,不移走脏缓存。因此,在执行这个命令前,应该先执行CheckPoint,将所有脏页写入磁盘,这样在运行DBCC RROPCLEANBUFFERS 时,可以保证所有的数据缓存被清理,而不是其中的一部分。
2,清空计划缓存
计划缓存(Plan Cache)用于缓存查询语句的执行计划,每一条查询语句在执行之后,其查询计划都会缓存Plan Cache中。在产品环境中,不要轻易清理掉Plan Cache。如果检测到某个Plan Cache产生参数嗅探问题,导致性能十分低下,推荐修改查询语句,重新编译存储过程,以单独刷新该SP的计划缓存。
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]
计划缓存,之前叫做过程缓存(procedure cache),执行DBCC FREEPROCCACHE 命令,释放所有的计划缓存,这回导致存储过程,AdHoc 查询等查询必须重新编译,产生新的计划缓存。
四,强制重新编译执行计划
修改存储过程,触发器等模块(Module)能够使其执行计划重新编译,除此之外,还有其他方法,能够强制重新编译执行计划
1,标记,下次重新编译
使用该存储过程,标记一个执行模块(SP,Trigger,User-Defined Function)在下次执行时,重新编译执行计划
sys.sp_recompile [ @objname = ] 'object'
2,不复用执行计划
在创建存储过程时,使用WITH RECOMPILE 选项,在每次执行SP时,都重新编译,使用新的执行计划。
CREATE PROCEDURE dbo.usp_procname @Parameter_Name varchar(30) = 'Parameter_default_value' WITH RECOMPILE
3,执行时重新编译
在执行存储过程时,重新编译存储过程的执行计划
exec dbo.usp_procname @Parameter_name='Parameter_value' WITH RECOMPILE
4,语句级别的重新编译
在SP中,使用查询选项 option(recompile),只重新编译该语句级别的执行计划
select column_name_list from dbo.tablename option(recompile)
SQL Server在执行查询之后,查询提示(RECOMPILE)指示存储引擎将计划缓存抛弃,在下次执行存储过程时,强制查询优化器重新编译,生成新的执行计划。在重新编译时,SQL Server 优化器使用当前的变量值生成新的计划缓存。
附:
冷缓存,热缓存,脏缓存和干净缓存名词解释:
- 净缓存页(Clean Buffer) 是指内存中未被修改的数据页,DBCC DROPCLEANBUFFERS 用于从缓冲池(Buffer Pool)移除干净页,释放Buffer。
- 脏缓存页(Dirty Buffer)是指数据页在内存中被修改,但是还没有写入到硬盘中,导致硬盘中的数据不同于内存,通常情况下,脏页通过CHECKPOINT进程来自动同步,CHECKPOINT 将脏页数据写入到硬盘中,使内存和硬盘文件中的数据保持一致,能够减少数据还原的时间。
- 冷缓存页(Cold Buffer)是指,在数据处理阶段,最近没有被使用的缓存页。
- 热缓存页(Hot Buffer)是指,在数据处理阶段,最近经常被使用的缓存页。
参考文档:
Recompile a Stored Procedure
What is a COLD, DIRTY or CLEAN Buffer
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持开心学习网!
- sqlserver两表查询语句格式(sql server实现在多个数据库间快速查询某个表信息的方法)
- SqlServer数据库中文乱码问题解决方法(SqlServer数据库中文乱码问题解决方法)
- sql server优化性能(SQLServer地址搜索性能优化)
- sqlserver的图形表(SQL Server纵表与横表相互转换的方法)
- sqlserver2014怎么重新激活(解决Windows 10家庭版安装SQL Server 2014出现.net 3.5失败问题)
- sqlserverdatetime时区(解析SQL Server中datetimeset转换datetime类型问题)
- sqlserver2008远程连接设置(如何开启SqlServer 远程访问)
- sqlserver查询自定义时间数据(SQLServer查询某个时间段购买过商品的所有用户)
- SqlServer 复制中将大事务分成小事务分发的方法(SqlServer 复制中将大事务分成小事务分发的方法)
- SqlServer2016模糊匹配的三种方式及效率问题简析(SqlServer2016模糊匹配的三种方式及效率问题简析)
- sqlserver分区查询语句(SQL Server中的集合运算: UNION, EXCEPT和INTERSECT示例代码详解)
- sqlserver分表后如何查询(SQL Server中row_number分页查询的用法详解)
- sqlserver如何可视化表(sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日)
- sqlserver常用数据结构图(Sql Server数据库常用Transact-SQL脚本推荐)
- sqlserver数据库同步复制(SqlServer将数据库中的表复制到另一个数据库)
- sqlserver栏位说明(SQL Server重置IDENTITY属性种子值操作)
- 《倚天屠龙记》再遭翻拍,关晓彤主演赵敏,蒋劲夫演张无忌,你怎么看(倚天屠龙记再遭翻拍)
- 吴启华与曾舜晞两代张无忌同框,戏里经典的他却没活出原著的潇洒(吴启华与曾舜晞两代张无忌同框)
- 经常发这三种 朋友圈 的人,要迅速屏蔽(经常发这三种朋友圈)
- 有种尴尬叫朋友圈忘屏蔽,大学生上演社死现场,父母亲自下场吐槽(有种尴尬叫朋友圈忘屏蔽)
- 朋友圈屏蔽你的人,可以直接看淡了(朋友圈屏蔽你的人)
- 金球奖只青睐那些会戴珠宝的女人(金球奖只青睐那些会戴珠宝的女人)
热门推荐
- python的静态方法和类方法(深入解析python中的实例方法、类方法和静态方法)
- python爬取微博登录数据(Python实现爬取马云的微博功能示例)
- laravel如何设置默认模块(laravel-admin 中列表筛选方法)
- 计算机改名后无法连接TFS
- sql中having用法(sql中的 where 、group by 和 having 用法解析)
- python3列表的使用教程(对Python3 pyc 文件的使用详解)
- docker最新服务条款(解决Mac Docker x509证书的问题)
- html5单选项怎么调选项间距(html5实现输入框fixed定位在屏幕最底部兼容性)
- dedecms导航条调用(dede调用其他栏目的文章或者缩略图列表且有分页效果的方法)
- 宝塔面板扩展安装(宝塔面板一键安装配置sg11组件教程)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9