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'

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

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

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

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