 --运行以下脚本,复制输出结果
--然后启动多个运行时间较长的脚本
--再次运行以下脚本,复制输出结果
--比较输出结果间的差异,主要关注runnable_tasks_count
SELECT scheduler_id, cpu_id,
parent_node_id, current_tasks_count,
runnable_tasks_count, current_workers_count,
active_workers_count, work_queue_count
FROM sys.dm_os_schedulers
--统计查询优化器的相关信息
SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations' OR counter = 'elapsed time'
--查找Query Plan的常规统计信息
SELECT plan_generation_num, creation_time,
last_execution_time, execution_count,
total_worker_time, total_physical_reads,
total_logical_reads, total_logical_writes,
total_elapsed_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
--查找Query Plan的CPU相关统计
--总计耗费CPU时间最长的查询计划
SELECT TOP 5 total_worker_time, last_worker_time,
max_worker_time, min_worker_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_worker_time DESC
--单次执行耗费CPU时间最长的查询计划
SELECT TOP 5 total_worker_time, last_worker_time,
max_worker_time, min_worker_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC
--利用次数最多的查询计划
SELECT TOP 5 creation_time, last_execution_time,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY execution_count DESC
GO
|
|
|
--显示服务器的内存配置
--开启高级配置
sp_configure 'show advanced options', '1'
RECONFIGURE
--显示各项内存相关运行值
sp_configure 'awe_enabled'
sp_configure 'min server memory'
sp_configure 'max server memory'
sp_configure 'min memory per query'
sp_configure 'query wait'
--关闭高级配置
sp_configure 'show advanced options', '0'
RECONFIGURE
--显示系统相关信息
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
--显示SQL Server的内存分配情况
DBCC MEMORYSTATUS
--显示各种对象占用内存的数量
SELECT type, SUM (pages_allocated_count * page_size_in_bytes) as 'Bytes Used'
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 2 DESC;
GO
--由多页分配器分配的内存总量
select sum(multi_pages_kb) / 1024.00 AS multi_pages_mb
from sys.dm_os_memory_clerks
--统计各种类型Memory_Clerk由多页分配器分配的内存总量
select type, sum(multi_pages_kb) / 1024.00 AS multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb != 0
group by type
order by 2 desc
--如果rounds_count和remove_rounds_count不断增长，代表内存面临压力
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
--各种由于I/O Latch申请而导致等待的信息
select wait_type, waiting_tasks_count, wait_time_ms,
signal_wait_time_ms, wait_time_ms / waiting_tasks_count AS 'avg_task_wait_time'
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' and waiting_tasks_count > 0
order by wait_type
--查看有挂起IO的数据库或文件
--运行Tough Taks 01.sql和Tough Tasks 02.sql
select database_id, file_id, io_stall, scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL) t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
--寻找IO最频繁的5个查询
--其中的Query Plan可以到处为sqlplan文件
--USE AdventureWorksDW
--SELECT * INOT dbo.TestProduct FROM dbo.DimProduct
--DELETE FROM dbo.TestProduct
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc
--Query Option对执行计划及系统负载的影响
SET STATISTICS IO ON
SET STATISTICS TIME ON
--注意服务器统计输出
| | |