SQL Server查询优化(细化到进程级别)

细化到进程级别

使用SQL Server自带的工具SQL Server Profiler。但有几点需要特别注意：

1. 不要把数据写入到表中，这样会严重影响性能。把数据写入到磁盘文件中是最快的选择，加载时我们可以使用fn_trace_gettable函数，用SELECT…INTO语句批量导入。

2. 追踪会产生大量I/O操作。不要把追踪文件访问和数据库文件相同的文件的磁盘上，理想情况是为追踪文件单独配置一个硬盘。

3. 慎重选择事件类和数据列，只跟踪需要的信息，移除所有默认的和不必要的对象。

4. 使用跟踪筛选功能，只选择相关的事件。

 

至于事件类，如果系统中的大部分操作都是由调用存储过程而引起的，而且每个存储过程只调用较少或有限的操作，则可以跟踪SP:Completed的事件类。然后可以根据存储过程对数据进行聚合。类似地，如果大部分操作是由调用批处理而引起的，而且每个批处理只有少量的操作，则可以跟踪SQL:BatchCompleted事件类。然而，如果每个过程调用了许多操作，就应该跟踪SP:StmtComplete事件类，以捕获每个存储过程调用的独立语句。如果操作是作为即席批处理（ad-hoc batch)而提交的（就像我们的例子一样），则应该跟踪SQL:StmtComplete事件类。不过要记住，跟踪每个语句的事件类时，可能会产生大量跟踪信息。对被跟踪的SQL Server实例有影响，所以要尽可能将这样的跟踪限制在较短的周期内，只收集具有代表性的工作负荷。最后，如果操作是作为远程过程调用(remote procedure call)而提交的，则应该跟综RPC:Completed事件类。注意，跟踪的所有亊件类都 是Completed事件类，而不是Starting事件类。只有Completed事件类才会携带性能信息，如Duration、 CPU、Reads和Writes,因为这些值在每个事件开始时都是未知的。

至于数据列，主要需要包含实际T-SQL代码的TexData列，以及一些与性能有关的计数器（例如，Duration列）。用户通常把等待认为是性能问题，而Duration代表该事件持续的时间（elapsed < time)。如果正在专门定位与I/O 相关的问題，则可以分析Reads和Writes列，我还喜欢跟踪 RowCounts列，特别是当査找与网络相关的问题时。对于把结果集返回给客户端的杳询，如果该查询在这个计数器上的数值比较大，则表明可能存在网络压力。

SQL Server 2008提供了一个名为sys.dm_exec_query_stats的DMV，返回有关缓存的查询计划中的聚合性能统计信息 SQL Server。 缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。 在从缓存删除计划时，也将从该视图中删除对应行。

-- 返回数据库中最高总时间的5个查询模式

SELECT TOP (5)

  MAX(query) AS sample_query,

  SUM(execution_count) AS cnt,

  SUM(total_worker_time) AS cpu,

  SUM(total_physical_reads) AS reads,

  SUM(total_logical_reads) AS logical_reads,

  SUM(total_elapsed_time) AS duration

FROM (SELECT

        QS.*,

        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 query

      FROM sys.dm_exec_query_stats AS QS

        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST

        CROSS APPLY sys.dm_exec_plan_attributes(QS.plan_handle) AS PA

      WHERE PA.attribute = 'dbid'

        AND PA.value = DB_ID('Performance')) AS D

GROUP BY query_hash

ORDER BY duration DESC;