sql测试及性能分析方法

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 

示例：

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

b、开启所有的执行计划明细，包括各个属性的评估值 
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 

五、执行计划


执