查询某个数据库内各对象使用内存缓存区资源的统计
01 | CREATE SCHEMA myScript |
03 | CREATE PROC myScript.spBufferUsed @db sysname |
05 | DECLARE @sql varchar (8000) |
08 | ,OBJECT_SCHEMA_NAME(object_id, database_id) as SchemaName |
09 | ,object_name(p.object_id,b.database_id) as objname |
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' |
23 | EXEC myScript.spBufferUsed 'Northwind' |
清空缓存区信息并重新查询后,观察缓存区的使用情况
5 | select * from adventureWorks.Person.Contact |
6 | select * from adventureWorks.HumanResources.Employee |
9 | exec myScript.spBufferUsed 'AdventureWorks' |
显示累计最消耗 CPU 时间的前50个运行计划
01 | CREATE PROC myScript.spHighestCPUTime |
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 |
14 | FROM sys.dm_exec_query_stats qs |
15 | cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt |
17 | total_worker_time DESC |
清空运行计划缓存后,重复运行语法,观察缓存运行计划被重复使用与消耗时间的纪录
02 | EXEC myScript.spHighestCPUTime |
10 | SELECT * FROM AdventureWorks.Person.Contact |
12 | EXEC myScript.spHighestCPUTime |
显示累计最常重新编译的25个运行计划
01 | CREATE PROC myScript.spListRecompile |
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) |
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 |
查询某个数据库内各对象使用内存缓存区资源的统计
显示锁定与被锁定之间的链状关系
01 | create proc myScript.spBlockInfo |
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 [锁定者的语法] |
24 | sys.dm_tran_locks as t1, |
25 | sys.dm_os_waiting_tasks as t2 |
27 | t1.lock_owner_address = t2.resource_address |
通过sys.dm_io_pending_io_requests系统查看和sys.dm_io_virtual_file_stats 动态管理函数监控是否有 IO 延迟的状况
02 | db_name(i.database_id) db, |
05 | io_stall [用户等待文件完成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 语法
02 | (total_logical_reads/execution_count) as [平均逻辑读取次数], |
03 | (total_logical_writes/execution_count) as [平均逻辑写入次数], |
04 | (total_physical_reads/execution_count) as [平均对象读取次数], |
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 |
13 | (total_logical_reads + total_logical_writes) Desc |