sql测试及性能分析方法
一、sql缓存的处理
1.清除缓存
--清除所有缓存
DBCC DROPCLEANBUFFERS
--清除存储过程缓存
DBCC FREEPROCCACHE
--清除会话缓存
DBCC FREESESSIONCACHE
--清除系统缓存
DBCC FREESYSTEMCACHE
---清空特定缓存存储区中的执行计划
DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'
GO
--将当前数据库的全部脏页写入磁盘。
CHECKPOINT
--查看数据库打开事务状态等
DBCC OPENTRAN
--查看全部游标
sp_cursor_list
2.查看缓存里当前的内容
SET SHOWPLAN_ALL ON
XML方式展现查询计划
2.通过设置STATISTICS来查看时间
SET STATISTICS PROFILE ON:显示分析、编译和执行查询所需的时间(以毫秒为单位)。
SET STATISTICS IO ON:报告与语句内引用的每个表的扫描数、逻辑读取数(在高速缓存中访问的页数)和物理读取数(访问磁盘的次数)有关的信息。
SET STATISTICS TIME ON:显示每个查询执行后的结果集,代表查询执行的配置文件。
使用方法:打开SQL SERVER 查询分析器,输入以下语句:
一、sql缓存的处理
1.清除缓存
--清除所有缓存
DBCC DROPCLEANBUFFERS
--清除存储过程缓存
DBCC FREEPROCCACHE
--清除会话缓存
DBCC FREESESSIONCACHE
--清除系统缓存
DBCC FREESYSTEMCACHE
---清空特定缓存存储区中的执行计划
DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'
GO
--将当前数据库的全部脏页写入磁盘。
CHECKPOINT
--查看数据库打开事务状态等
DBCC OPENTRAN
--查看全部游标
sp_cursor_list
示例:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE ( 'ALL' )
--要接着执行你的查询,不然SQLServer会时刻的自动往缓存里读入最有可能需要的数据页.
2.查看缓存里当前的内容
SELECT * FROM sys.dm_os_buffer_descriptors where page_type IN ('DATA_Page','INDEX_Page') ORDER BY row_count DESC
二、查看查询计划
文本方式查看查询计划
a、只开启执行计划,不包括详细的评估值SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
XML方式展现查询计划
SET STATISTICS XML ON
三、查看执行语句花费的时间
1.手工添加语句,计算执行时间来查看执行语句花费了的时间
declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
/*你的SQL脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2.通过设置STATISTICS来查看时间
SET STATISTICS PROFILE ON:显示分析、编译和执行查询所需的时间(以毫秒为单位)。
SET STATISTICS IO ON:报告与语句内引用的每个表的扫描数、逻辑读取数(在高速缓存中访问的页数)和物理读取数(访问磁盘的次数)有关的信息。
SET STATISTICS TIME ON:显示每个查询执行后的结果集,代表查询执行的配置文件。
使用方法:打开SQL SERVER 查询分析器,输入以下语句:
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO /*--你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
GO /*--你的SQL脚本结束*/
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
四、内存
--内存使用情况
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN('Target Server Memory (KB)','Total Server Memory (KB)')
-- 内存状态
DBCC MemoryStatus
--查看最小最大内存
SELECT configuration_idas id,name as 名称,minimum as配置最小值, maximumas 最大值,
is_dynamic as 是否动态值, is_advanced as 是否优先, value_in_use AS 运行值,
description as 描述 FROM sys.configurations
--分类查看缓存所占用的内存
select * from sys.dm_os_memory_cache_counters
--示例:通过动态调整 Sql Server可用的物理内存设置来强制释放内存
CREATE procedure [dbo].ClearMemory
as
begin
--清除所有缓存
DBCC DROPCLEANBUFFERS
--打开高级配置
exec sp_configure'show advanced options', 1
--设置最大内存值,清除现有缓存空间
exec sp_configure'max server memory', 256
EXEC ('RECONFIGURE')
--设置等待时间
WAITFOR DELAY '00:00:01'
--重新设置最大内存值
EXEC sp_configure'max server memory',4096
EXEC ('RECONFIGURE')
--关闭高级配置
exec sp_configure'show advanced options',0
GO
五、执行计划
执