SQLS各种性能统计（收藏）

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

----找到Blocking Process------------------------------------------------------

SELECT  s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
        s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid), 
        Definition = CAST(text AS VARCHAR(MAX))
 INTO   #Processes
 FROM   sys.sysprocesses s  CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE  s.spid > 50