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

--运行以下脚本,复制输出结果
--然后启动多个运行时间较长的脚本
--再次运行以下脚本,复制输出结果
--比较输出结果间的差异,主要关注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)