强制重新编译的演示代码 |
|
CPU负载统计的演示代码 |
--运行以下脚本,复制输出结果 --然后启动多个运行时间较长的脚本 --再次运行以下脚本,复制输出结果 --比较输出结果间的差异,主要关注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
--注意服务器统计输出 DBCC DROPCLEANBUFFERS --清除相关缓存 DBCC FREESYSTEMCACHE ('ALL') --清除相关缓存 SELECT c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
--注意服务器统计输出 DBCC DROPCLEANBUFFERS --清除相关缓存 DBCC FREESYSTEMCACHE ('ALL') --清除相关缓存 SELECT c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID OPTION (MERGE JOIN)
|
|
|
Tempdb负载统计的演示代码 |
|