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) GOEXEC 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 50total_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) ObjectNameFROM 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=1SELECT * FROM AdventureWorks.Person.Contact GO EXEC myScript.spHighestCPUTime

 

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

CREATE PROC myScript.spListRecompile AS select top 25plan_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 qsCross 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 intselect @single=sum(case(usecounts)when 1 then 1else 0end),@reused=sum(case(usecounts)when 1 then 0else 1end),@total=count(usecounts) from sys.dm_exec_cached_plansselect '只使用过一次的运行计划的数量(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) GOEXEC 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 rcross apply sys.dm_exec_sql_text(r.sql_handle) as qtwhere 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 rcross apply sys.dm_exec_sql_text(r.sql_handle) as qt order by (total_logical_reads + total_logical_writes) Desc

转载于:https://www.cnblogs.com/50614090/archive/2010/11/18/1881209.html

总结

以上是生活随笔为你收集整理的sql2005 性能调校 观察各种资源的使用情况的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。