SQL2005常见性能问题排错演示代码[收藏]

强制重新编译的演示代码

 

USE AdventureWorks
GO

--在SQL Profiler中监视Stored Procedures类别中的SP:Recompile事件
--注意SQL Profiler中没有出现SP:Recompile事件
--SQL Server在创建存储过程对象时不会编译,延迟编译
CREATE PROCEDURE dbo.DemoProc1
AS
CREATE TABLE #temp (IDA int, IDB int)
SELECT * FROM #temp
GO

--运行两次,注意
--第一次SQL Profiler中出现了SP:Recompile事件
--第二次SQL Profiler中没有出现SP:Recompile事件
EXEC dbo.DemoProc1

--注意每次运行都出现SP:Recompile事件
EXEC dbo.DemoProc1 WITH RECOMPILE

DBCC freeproccache --清除过程缓存

--重新启动SQL Profiler
CREATE PROCEDURE dbo.DemoProc2
WITH RECOMPILE
AS
CREATE TABLE #temp (IDA int, IDB int)
SELECT * FROM #temp
GO

--注意每次运行SQL Profiler都出现SP:Recompile事件
EXEC dbo.DemoProc2

 

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负载统计的演示代码

 

--统计Tempdb各部分的空间消耗
select
    sum(user_object_reserved_page_count)*8 as user_objects_kb, 
    sum(internal_object_reserved_page_count)*8 as internal_objects_kb, 
    sum(version_store_reserved_page_count)*8  as version_store_kb, 
    sum(unallocated_extent_page_count)*8 as freespace_kb 
from sys.dm_db_file_space_usage 
where database_id = 2

--统计Tempdb中用户对象消耗的空间
--由于演示系统空闲，在另一会话中创建一个全局临时表
use tempdb
declare userobj_cursor cursor for  
select  
     sys.schemas.name + '.' + sys.objects.name  
from sys.objects, sys.schemas 
where object_id > 100 and  
      type_desc = 'USER_TABLE'and  
      sys.objects.schema_id = sys.schemas.schema_id 
go 
 
open userobj_cursor 
go 
 
declare @name varchar(256) 
fetch userobj_cursor into @name 
while (@@FETCH_STATUS = 0)  
begin 
    exec sp_spaceused @objname = @name 
        fetch userobj_cursor into @name     
end 
close userobj_cursor
deallocate userobj_cursor

--统计每个会话创建的内部对象
select  
    session_id,  
    internal_objects_alloc_page_count,  
    internal_objects_dealloc_page_count 
from sys.dm_db_session_space_usage 
order by internal_objects_alloc_page_count DESC