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

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

view source print ?

01	CREATE SCHEMA myScript

02

GO

03	CREATE PROC myScript.spBufferUsed @db sysname

04

as

05	DECLARE @sql varchar(8000)

06	SET @sql='

07	select  p.object_id

08	,OBJECT_SCHEMA_NAME(object_id, database_id) as SchemaName

09	,object_name(p.object_id,b.database_id) as objname

10	,p.index_id

11	,buffer_count=count(*)

12	from ' + @db + '.sys.allocation_units a,

13	' + @db + '.sys.dm_os_buffer_descriptors b,

14	' + @db + '.sys.partitions p

15	where a.allocation_unit_id = b.allocation_unit_id

16	and a.container_id = p.hobt_id

17	and b.database_id = db_id('''+@db +''')

18	group by b.database_id,p.object_id, p.index_id

19	order by buffer_count desc'

20	exec(@sql)

21

GO

22

23	EXEC myScript.spBufferUsed 'Northwind'

 

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

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

2	DBCC DROPCLEANBUFFERS

3

4	--故意利用查询记录，将硬盘信息留在内存缓存区中

5	select * from adventureWorks.Person.Contact

6	select * from adventureWorks.HumanResources.Employee

7

8	--利用先前查询内存缓存区的存储过程列出使用状态

9	exec myScript.spBufferUsed 'AdventureWorks'

 

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

01	CREATE PROC myScript.spHighestCPUTime

02

AS

03	SELECT TOP 50

04	total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

05	qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],

06	SUBSTRING(qt.text,qs.statement_start_offset/2+1,

07	(case when qs.statement_end_offset = -1

08	then DATALENGTH(qt.text)

09	else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)

10	as [使用CPU的语法], qt.text [完整语法],

11	qt.dbid, dbname=db_name(qt.dbid),

12	qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

13

14	FROM sys.dm_exec_query_stats qs

15	cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

16

ORDER BY

17	total_worker_time DESC

 

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

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

02	EXEC myScript.spHighestCPUTime

03

04	--清空执行计划内存

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

06	DBCC FREEPROCCACHE

07

08	--重新建立新的存储过程计划被重复使用的记录，查看相关的时间

09

WHILE 1=1

10	SELECT * FROM AdventureWorks.Person.Contact

11

GO

12	EXEC myScript.spHighestCPUTime

 

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

01	CREATE PROC myScript.spListRecompile

02

AS

03	select top 25

04	plan_generation_num,

05	SUBSTRING(qt.text,qs.statement_start_offset/2+1,

06	(case when qs.statement_end_offset = -1

07	then DATALENGTH(qt.text)

08	else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)

09	as stmt_executing,

10

qt.text,

11	execution_count,

12	sql_handle,

13

dbid,

14	db_name(dbid) DBName,

15

objectid,

16	object_name(objectid,dbid) ObjectName

17	from sys.dm_exec_query_stats as qs

18	Cross apply sys.dm_exec_sql_text(sql_handle) qt

19	where plan_generation_num >1

20	order by plan_generation_num

 

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

show source

 

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

01	create proc myScript.spBlockInfo

02

as

03	select t1.resource_type as [信息锁定类型]

04	,db_name(resource_database_id) as [数据库名]

05	,t1.resource_associated_entity_id as [锁定的对象]

06	,t1.request_mode as [等待者需求的锁定类型]

07	,t1.request_session_id as [等待者sid]

08	,t2.wait_duration_ms as [等待时间]

09	,(select text from sys.dm_exec_requests as r

10	cross apply sys.dm_exec_sql_text(r.sql_handle)

11	where r.session_id = t1.request_session_id) as [等待者要运行的批处理]

12	,(select substring(qt.text,r.statement_start_offset/2+1,

13	(case when r.statement_end_offset = -1

14	then datalength(qt.text)

15	else r.statement_end_offset end - r.statement_start_offset)/2+1)

16	from sys.dm_exec_requests as r

17	cross apply sys.dm_exec_sql_text(r.sql_handle) as qt

18	where r.session_id = t1.request_session_id) as [等待者正要运行的语法]

19	,t2.blocking_session_id as [锁定者sid]

20	,(select text from sys.sysprocesses as p

21	cross apply sys.dm_exec_sql_text(p.sql_handle)

22	where p.spid = t2.blocking_session_id) as [锁定者的语法]

23

from

24	sys.dm_tran_locks as t1,

25	sys.dm_os_waiting_tasks as t2

26

where

27	t1.lock_owner_address = t2.resource_address

 

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

01

select

02	db_name(i.database_id) db,

03

name,

04	physical_name,

05	io_stall [用户等待文件完成I/O 的总时间(以毫秒为单位)] ,

06	io_type [I/O 要求的类型],

07	io_pending_ms_ticks [个别IO 在队列(Pending queue)等待的总时间]

08	from sys.dm_io_virtual_file_stats(NULL, NULL) i

09	join sys.dm_io_pending_io_requests as p

10	on i.file_handle = p.io_handle

11	join sys.master_files m

12	On m.database_id=i.database_id and m.file_id=i.file_id

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

01	select --top 5

02	(total_logical_reads/execution_count) as [平均逻辑读取次数],

03	(total_logical_writes/execution_count) as [平均逻辑写入次数],

04	(total_physical_reads/execution_count) as [平均对象读取次数],

05	Execution_count 运行次数,

06	substring(qt.text,r.statement_start_offset/2+1,

07	(case when r.statement_end_offset = -1

08	then datalength(qt.text)

09	else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法]

10	from sys.dm_exec_query_stats  as r

11	cross apply sys.dm_exec_sql_text(r.sql_handle) as qt

12

order by

13	(total_logical_reads + total_logical_writes) Desc