sql2005 性能调校 观察各种资源的使用情况

查询某个数据库内各对象使用内存缓存区资源的统计

CREATE SCHEMA myScript
GO
CREATE PROC myScript.spBufferUsed @db sysname
as
DECLARE @sql varchar(8000)
SET @sql='
select 	p.object_id
		,OBJECT_SCHEMA_NAME(object_id, database_id) as SchemaName
		,object_name(p.object_id,b.database_id) as objname
		,p.index_id
		,buffer_count=count(*)
from ' + @db + '.sys.allocation_units a,
	 ' + @db + '.sys.dm_os_buffer_descriptors b,
	 ' + @db + '.sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id('''+@db +''')
group by b.database_id,p.object_id, p.index_id
order by buffer_count desc'
exec(@sql)
GO

EXEC myScript.spBufferUsed 'Northwind'

清空缓存区信息并重新查询后，观察缓存区的使用情况

--清空内存缓存区中现有的信息
DBCC DROPCLEANBUFFERS 

--故意利用查询记录，将硬盘信息留在内存缓存区中
select * from adventureWorks.Person.Contact
select * from adventureWorks.HumanResources.Employee

--利用先前查询内存缓存区的存储过程列出使用状态
exec myScript.spBufferUsed 'AdventureWorks'

显示累计最消耗 CPU 时间的前50个运行计划

CREATE PROC myScript.spHighestCPUTime
AS
SELECT TOP 50
	total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
	qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
	SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
		(case when qs.statement_end_offset = -1 
		then DATALENGTH(qt.text) 
		else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) 
	as [使用CPU的语法], qt.text [完整语法],
	qt.dbid, dbname=db_name(qt.dbid),
	qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY 
        total_worker_time DESC

清空运行计划缓存后，重复运行语法，观察缓存运行计划被重复使用与消耗时间的纪录

--查看缓存中的执行计划已被执行的次数与累计所耗的时间
EXEC myScript.spHighestCPUTime

--清空执行计划内存
--再重新通过相同的存储过程运行时，可以看到所有的记录都被清空
DBCC FREEPROCCACHE

--重新建立新的存储过程计划被重复使用的记录，查看相关的时间
WHILE 1=1
  SELECT * FROM AdventureWorks.Person.Contact
GO
EXEC myScript.spHighestCPUTime

显示累计最常重新编译的25个运行计划

CREATE PROC myScript.spListRecompile
AS
select top 25
	plan_generation_num,
	SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
		(case when qs.statement_end_offset = -1 
		then DATALENGTH(qt.text) 
		else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) 
		as stmt_executing,
	qt.text,
	execution_count,
	sql_handle,
	dbid,
	db_name(dbid) DBName,
	objectid,
	object_name(objectid,dbid) ObjectName 
from sys.dm_exec_query_stats as qs
	Cross apply sys.dm_exec_sql_text(sql_handle) qt
where plan_generation_num >1
order by plan_generation_num

查询某个数据库内各对象使用内存缓存区资源的统计

CREATE PROC myScript.spReusedPlans
AS
declare @single int, @reused int, @total int

select @single=
	sum(case(usecounts)
		when 1 then 1
		else 0
	end),
	@reused=
	sum(case(usecounts)
		when 1 then 0
		else 1
	end),
	@total=count(usecounts)
from sys.dm_exec_cached_plans

select 
'只使用过一次的运行计划的数量(usecounts=1)'= @single,
'重复使用的运行计划的数量(usecounts>1)'= @reused,
'重复使用之运行计划所占比例%'=cast(100.0*@reused / @total as dec(5,2)),
'运行计划总数量'=@total,
'只使用过一次的计划所消耗字节数'=(
select sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1)
GO

EXEC spReusedPlans

显示锁定与被锁定之间的链状关系

create proc myScript.spBlockInfo
as
select t1.resource_type as [信息锁定类型]
	,db_name(resource_database_id) as [数据库名]
	,t1.resource_associated_entity_id as [锁定的对象]
	,t1.request_mode as [等待者需求的锁定类型]
	,t1.request_session_id as [等待者sid]  
	,t2.wait_duration_ms as [等待时间]	
	,(select text from sys.dm_exec_requests as r  
		cross apply sys.dm_exec_sql_text(r.sql_handle) 
		where r.session_id = t1.request_session_id) as [等待者要运行的批处理]
	,(select substring(qt.text,r.statement_start_offset/2+1, 
			(case when r.statement_end_offset = -1 
			then datalength(qt.text) 
			else r.statement_end_offset end - r.statement_start_offset)/2+1) 
		from sys.dm_exec_requests as r
		cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
		where r.session_id = t1.request_session_id) as [等待者正要运行的语法]
	 ,t2.blocking_session_id as [锁定者sid] 
     ,(select text from sys.sysprocesses as p		
		cross apply sys.dm_exec_sql_text(p.sql_handle) 
		where p.spid = t2.blocking_session_id) as [锁定者的语法]
	from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address

通过sys.dm_io_pending_io_requests系统查看和sys.dm_io_virtual_file_stats 动态管理函数监控是否有 IO 延迟的状况

select 
    db_name(i.database_id) db, 
    name,
	physical_name, 
    io_stall [用户等待文件完成I/O 的总时间(以毫秒为单位)] ,
	io_type [I/O 要求的类型],
    io_pending_ms_ticks [个别IO 在队列(Pending queue)等待的总时间]
from sys.dm_io_virtual_file_stats(NULL, NULL) i
join sys.dm_io_pending_io_requests as p
on i.file_handle = p.io_handle
join sys.master_files m
On m.database_id=i.database_id and m.file_id=i.file_id

以sys.dm_exec_query_stats 动态管理查看查询最耗 IO 资源的 SQL 语法

select --top 5 
(total_logical_reads/execution_count) as [平均逻辑读取次数],
(total_logical_writes/execution_count) as [平均逻辑写入次数],
(total_physical_reads/execution_count) as [平均对象读取次数],
 Execution_count 运行次数, 
substring(qt.text,r.statement_start_offset/2+1, 
(case when r.statement_end_offset = -1 
then datalength(qt.text) 
else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法]
from sys.dm_exec_query_stats  as r
	cross apply sys.dm_exec_sql_text(r.sql_handle) as qt 
order by 
 (total_logical_reads + total_logical_writes) Desc