sql测试及性能分析方法

超、凢脫俗 2022-07-15 03:44 251阅读 0赞

sql测试及性能分析方法  
  
  
**一、sql缓存的处理**  
1.清除缓存  
  
  
\--清除所有缓存  
DBCC DROPCLEANBUFFERS  
  
  
\--清除存储过程缓存  
DBCC FREEPROCCACHE  
  
  
\--清除会话缓存  
DBCC FREESESSIONCACHE  
  
\--清除系统缓存  
DBCC FREESYSTEMCACHE  
  
  
\---清空特定缓存存储区中的执行计划  
DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool\_name, 'Object Plans', 'SQL Plans', 'Bound Trees'  
GO  
  
  
\--将当前数据库的全部脏页写入磁盘。  
CHECKPOINT  
  
  
\--查看数据库打开事务状态等  
DBCC OPENTRAN  
  
  
\--查看全部游标  
sp\_cursor\_list

示例：

CHECKPOINT
    
    
    DBCC DROPCLEANBUFFERS
    
    
    DBCC FREEPROCCACHE
    
    
    DBCC FREESYSTEMCACHE ( 'ALL' ) 
    
    --要接着执行你的查询,不然SQLServer会时刻的自动往缓存里读入最有可能需要的数据页.

2.查看缓存里当前的内容

SELECT * FROM sys.dm_os_buffer_descriptors where page_type IN ('DATA_Page','INDEX_Page') ORDER BY row_count DESC

**二、查看查询计划**

文本方式查看查询计划

a、只开启执行计划，不包括详细的评估值

SET SHOWPLAN\_TEXT ON

b、开启所有的执行计划明细，包括各个属性的评估值  
SET SHOWPLAN\_ALL ON  
  
  
XML方式展现查询计划

SET STATISTICS XML ON

三、查看执行语句花费的时间  
  
  
1.手工添加语句，计算执行时间来查看执行语句花费了的时间

declare @d datetime
    set @d=getdate()
    /*你的SQL脚本开始*/
    SELECT [TestCase] FROM [TestCaseSelect] 
    /*你的SQL脚本结束*/
    select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2.通过设置STATISTICS来查看时间  
SET STATISTICS PROFILE ON：显示分析、编译和执行查询所需的时间（以毫秒为单位）。  
SET STATISTICS IO ON：报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。  
SET STATISTICS TIME ON：显示每个查询执行后的结果集，代表查询执行的配置文件。  
  
  
使用方法：打开SQL SERVER 查询分析器，输入以下语句：

SET STATISTICS PROFILE ON 
    SET STATISTICS IO ON 
    SET STATISTICS TIME ON 
    GO /*--你的SQL脚本开始*/
    SELECT [TestCase] FROM [TestCaseSelect] 
    GO /*--你的SQL脚本结束*/
    SET STATISTICS PROFILE OFF 
    SET STATISTICS IO OFF 
    SET STATISTICS TIME OFF

**四、内存**

--内存使用情况
    SELECT * FROM sys.dm_os_performance_counters  
    WHERE counter_name IN('Target Server Memory (KB)','Total Server Memory (KB)')  
    
    -- 内存状态  
    DBCC MemoryStatus  
    
    --查看最小最大内存  
    SELECT configuration_idas id,name as 名称,minimum as配置最小值, maximumas 最大值,  
    
    is_dynamic as 是否动态值, is_advanced as 是否优先, value_in_use AS 运行值,  
    
    description as 描述 FROM sys.configurations 
    
    
    --分类查看缓存所占用的内存
    select * from sys.dm_os_memory_cache_counters
    
    
    
    
    --示例：通过动态调整 Sql Server可用的物理内存设置来强制释放内存 
    
    CREATE procedure [dbo].ClearMemory   
    as  
    
    begin 
    
    --清除所有缓存 
    
    DBCC DROPCLEANBUFFERS 
    
    --打开高级配置 
    
    exec sp_configure'show advanced options', 1 
    
    --设置最大内存值，清除现有缓存空间 
    
    exec sp_configure'max server memory', 256 
    
    EXEC ('RECONFIGURE') 
    
    --设置等待时间 
    
    WAITFOR DELAY '00:00:01' 
    
    --重新设置最大内存值 
    
    EXEC  sp_configure'max server memory',4096 
    
    EXEC ('RECONFIGURE') 
    
    --关闭高级配置 
    
    exec sp_configure'show advanced options',0 
    
    GO

**五、执行计划**  
  
  
执行计划相关系统视图  
\--1.缓存的每一个对象返回一行，包括缓存计划的类型、缓存引用的对象、缓存计划占用的空间、被使用次数、以及创建时间等  
SELECT \* FROM sys.syscacheobjects;  
\--2.缓存的每个查询计划返回一行，包括执行计划被使用的次数、执行计划的大小、内存地址、执行计划的类型、语句等  
SELECT \* FROM sys.dm\_exec\_cached\_plans;  
GO  
\---3.返回由指定的 sql\_handle 标识的 SQL 批处理的文本  
/\*其中sql\_handle来自:  
sys.dm\_exec\_query\_stats  
sys.dm\_exec\_requests  
sys.dm\_exec\_cursors  
sys.dm\_exec\_xml\_handles  
sys.dm\_exec\_query\_memory\_grants  
sys.dm\_exec\_connections  
plan\_handle来自:sys.dm\_exec\_cached\_plans  
\*/  
SELECT \* FROM sys.dm\_exec\_sql\_text(sql\_handle | plan\_handle);  
GO  
\--4.以 XML 格式返回计划句柄指定的批查询的显示计划，主要接受来自sys.dm\_exec\_cached\_plans的plan\_handle句柄  
SELECT \* FROM sys.dm\_exec\_query\_plan(plan\_handle);  
GO  
\--5.每个计划属性返回一行，主要接受来自sys.dm\_exec\_cached\_plans的plan\_handle句柄  
SELECT \* FROM sys.dm\_exec\_plan\_attributes(plan\_handle);  
GO  
\--6.针对每个 Transact-SQL 执行计划、公共语言运行时 (CLR) 执行计划和与计划关联的游标返回一行,，主要接受来自sys.dm\_exec\_cached\_plans的plan\_handle句柄  
SELECT \* FROM sys.dm\_exec\_cached\_plan\_dependent\_objects(plan\_handle);  
  
\--7.返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。\*/  
\--该系统视图针对每一个缓存中的执行计划统计其执行时间、物理、逻辑操作等信息  
SELECT \* FROM sys.dm\_exec\_query\_stats  
  
  
  
  
示例：根据被缓存的执行计划找到数据库中耗时最长的20个查询语句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
    SELECT TOP 20 
      CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) 
                                         AS [Total Duration (s)] 
      , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
                                   AS DECIMAL(28, 2)) AS [% CPU] 
      , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
            qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
      , qs.execution_count 
      , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 
                    AS DECIMAL(28, 2)) AS [Average Duration (s)] 
      , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
        ((CASE WHEN qs.statement_end_offset = -1 
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2) + 1) AS [Individual Query 
      , qt.text AS [Parent Query] 
      , DB_NAME(qt.dbid) AS DatabaseName 
      , qp.query_plan 
    FROM sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
    WHERE qs.total_elapsed_time > 0 
    ORDER BY qs.total_elapsed_time DESC

**六、常用监控性能DMV & DMF**  
  
DMV（Dynamic Management Views，动态管理视图）其实就是SQL Server内核的元数据，  
当查询在SQL Server中运行时，SQL Server会自动的将此次活动的相关信息记录下来，并且保存在内存之中，这些活动信息，就称之为：DMV。  
通过对内部的元数据的分析，我们快速而准确获取很多与SQL Server内部相关的信息，从而进行性能分析。  
  
  
DMF（Dynamic Management Functions），可以简单的将DMF理解为SQL Server内部的一系列函数。  
  
  
常规服务器动态管理对象包括：  
dm\_db\_\*：数据库和数据库对象  
dm\_exec\_\*：执行用户代码和关联的连接  
dm\_os\_\*：内存、锁定和时间安排  
dm\_tran\_\*：事务和隔离  
dm\_io\_\*：网络和磁盘的输入/输出

--查看性能记数器
    SELECT * FROM sys.dm_os_performance_counters
    
    
    --执行过的线程所遇到的所有等待(不是当前正在运行的线程, 为自上次重置统计信息或启动服务器以来累积的数据),可分析靠前的几个等待较高的事件。
    select * from sys.dm_os_wait_stats order by wait_time_ms desc
    
    
    --重置该动态视图
    DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
    GO
    -- 正在等待某些资源的任务的等待队列
    select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc
    
    内存使用：  
    --查看当前由 SQL Server 分配的内存对象(KB): 
    select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;
    --查看系统内存当前信息: 
    select * from sys.dm_os_sys_memory (这个动态视图只在sql 2008中才有)
    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使用的最小,最大内存(MB)：
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'min server memory', 300;
    GO
    sp_configure 'max server memory', 850;
    GO
    RECONFIGURE;
    GO
    
    
    --CPU使用情况:
    SELECT TOP 50
    total_worker_time/execution_count AS '每次执行占用CPU(微秒)',
    execution_count       as '执行次数',
    total_worker_time     as '总共占用CPU(微秒)',
    creation_time         as '创建时间',
    last_execution_time   as '最后执行时间',
    min_worker_time       as '最低每次占用CPU',
    max_worker_time       as '最高每次占用cpu',
    total_physical_reads  as '总共io物理读取次数',
    total_logical_reads   as '总共逻辑读取次数',
    total_logical_writes  as '总共逻辑写次数',
    total_elapsed_time    as '完成此计划的执行所占用的总时间(微秒)',
    (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS 'SQL内容'
    FROM sys.dm_exec_query_stats
    ORDER BY 1 DESC
    
    
    --下面的查询显示SQL 等待分析和前10 个等待的资源
    select top 10 *
    from sys.dm_os_wait_stats
    where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
    order by wait_time_ms desc;
    
    --SQL Server中的活动会话数:
    exec   sp_who   'active'
    print @@rowcount
    
    --SQL Server等待情况
    select * from sys.dm_os_waiting_tasks ; --当前等待事件
    select * from sys.dm_os_wait_stats  --历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空
    DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
    
    找出进程阻塞:
    --运行下面的查询可确定阻塞的会话
    select blocking_session_id, wait_duration_ms, session_id from
    sys.dm_os_waiting_tasks
    where blocking_session_id is not null
    
    --spid 正在阻塞另一个 spid，可在数据库中创建以下存储过程，然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid；@spid 是可选参数。
    
    create proc dbo.sp_block (@spid bigint=NULL)
    as
    select
       t1.resource_type,
       'database'=db_name(resource_database_id),
       'blk object' = t1.resource_associated_entity_id,
       t1.request_mode,
       t1.request_session_id,
       t2.blocking_session_id    
    from
       sys.dm_tran_locks as t1,
       sys.dm_os_waiting_tasks as t2
    where
       t1.lock_owner_address = t2.resource_address and
       t1.request_session_id = isnull(@spid,t1.request_session_id)
    
    --以下是使用此存储过程的示例。
    exec sp_block
    exec sp_block @spid = 7
    
    select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;
    select * from sys.dm_os_sys_info;
    select * from sys.dm_os_performance_counters
    
    
    A. 获取有关按平均 CPU 时间排在最前面的五个查询的信息
    --以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。
    SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
       SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
           ((CASE qs.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/execution_count DESC;
    
    
    B. 提供批处理执行统计信息
    --以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。
    SELECT s2.dbid, 
       s1.sql_handle,  
       (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
         ( (CASE WHEN statement_end_offset = -1
            THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
            ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
       execution_count,
       plan_generation_num,
       last_execution_time,  
       total_worker_time,
       last_worker_time,
       min_worker_time,
       max_worker_time,
       total_physical_reads,
       last_physical_reads,
       min_physical_reads,  
       max_physical_reads,  
       total_logical_writes,
       last_logical_writes,
       min_logical_writes,
       max_logical_writes  
    FROM sys.dm_exec_query_stats AS s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
    WHERE s2.objectid is null
    ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
    
    -- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器)
    select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads
    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
    ,io_stall_write_ms,num_of_writes
    ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
    ,io_stall_read_ms + io_stall_write_ms as io_stalls
    ,num_of_reads + num_of_writes as total_io
    ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
    from sys.dm_io_virtual_file_stats(null,null)
    order by avg_io_stall_ms desc;
    
    
    -- 查看分区表money，各个分区的行数和边界值.
    select partition = $partition.分区函数名(userid)
         ,rows      = count(*)
         ,minval    = min(userid)
         ,maxval    = max(userid)
    from dbo.money with(nolock)
    group by $partition.分区函数名(userid)
    order by partition;
    
    -- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器),
    SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
    mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
    vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
    vfs.NumberReads, vfs.NumberWrites,
    (Size*8)/1024 Size_MB
    FROM ::fn_virtualfilestats(NULL,NULL) vfs
    INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
    AND mf.FILE_ID = vfs.FileId
    GO
    
    -- 性能计数器动态视图,  和使用windows性能计数器效果一致。
    SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters
     WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (
            ([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN
             ('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR
            ([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN
             ('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR
            ([object_name] LIKE ('%General Statistics%') AND [counter_name] IN
             ('Active Temp Tables', 'User Connections')) OR
            ([object_name] LIKE ('%Databases%') AND [counter_name] IN
             ('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec',
               'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR
            ([object_name] LIKE ('%Access Methods%') AND [counter_name] IN
             ('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR
            ([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN
             ('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR
            ([object_name] LIKE ('%SQL Statistics%') AND [counter_name] IN
             ('SQL Compilations/sec', 'SQL Re-Compilations/sec'))
            )
    
    
    --查看IO状况
    SELECT TOP 20
     [Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
     , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
                                               qs.execution_count
     , qs.execution_count
     , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
     ((CASE WHEN qs.statement_end_offset = -1
       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
       ELSE qs.statement_end_offset
       END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
     , qt.text AS [Parent Query]
     , DB_NAME(qt.dbid) AS DatabaseName
     , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY [Total IO] DESC
    
    
    
    --CPU 平均占用率最高的前 50 个 SQL 语句。
    SELECT TOP 50
    total_worker_time/execution_count AS [Avg CPU Time],
    (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
    FROM sys.dm_exec_query_stats
    ORDER BY [Avg CPU Time] DESC
    
    
    --查询显示一些可能占用大量 CPU 使用率的运算符（例如 ‘%Hash Match%’、‘%Sort%’）以找出可疑对象。
    select *
    from
         sys.dm_exec_cached_plans
         cross apply sys.dm_exec_query_plan(plan_handle)
    where
         cast(query_plan as nvarchar(max)) like '%Sort%'
         or cast(query_plan as nvarchar(max)) like '%Hash Match%'
    
    
    --查看 CPU、计划程序内存和缓冲池信息。
    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
    
    
    --显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
    select top 25
    sql_text.text,
    sql_handle,
    plan_generation_num,
    execution_count,
    dbid,
    objectid
    from sys.dm_exec_query_stats a
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    where plan_generation_num > 1
    order by plan_generation_num desc
    
    --查找生成最多 I/O 的前五个请求/批处理。调整这些查询将提高系统性能。
    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_phys_reads,
        Execution_count,
       statement_start_offset as stmt_start_offset,
       sql_handle,
       plan_handle
    from sys.dm_exec_query_stats  
    order by  (total_logical_reads + total_logical_writes) Desc

--查看数据库数据文件以及磁盘的空间情况
    SELECT DB_NAME(df.database_id) as dbName,
     physical_name AS DataFile,
     size AS FileSize,
     volume_mount_point AS Drive,
     CAST(total_bytes/1024/1024/1024 AS VARCHAR) + ' GB' AS DriveSize,
     CAST(available_bytes/1024/1024/ 1024 AS VARCHAR) + ' GB'  AS SpaceAvailable
    FROM sys.master_files df
    CROSS APPLY sys.dm_os_volume_stats(df.database_id, df.file_id) ovs
    where DB_NAME(df.database_id)='DEMO'

参考：  
http://www.cnblogs.com/qanholas/archive/2011/05/06/2038543.html  
http://blog.csdn.net/dcx903170332/article/details/45917387  
http://www.jb51.net/article/19324.htm  
http://blog.csdn.net/dinglang\_2009/article/details/46480859  
http://www.sqlserver-dba.com/dmv/