识别性能不佳的查询语句
SQL Server为所有执行的查询生成一个优化过的查询计划。这使得SQL Server的优化器可以在同样或者类似的查询执行的时候重用查询计划，从而在最短的时间内接收数据。一旦数据发生变化，数据列上的统计信息会使得查询计划变得过期和低效。为了获得应用程序的最佳性能以及一致的用户体验，识别并且调试这些语句非常的重要。之前列出的DMV可以直接帮助识别那些有问题的查询。
下面的是一些用于标识这些有问题的查询的基本查询语句：
过多的重新编译：
- 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
|
不够高效的查询计划
- select
- highest_cpu_queries.plan_handle,
- highest_cpu_queries.total_worker_time,
- q.dbid,
- q.objectid,
- q.number,
- q.encrypted,
- q.[text]
- from
- (select top 50
- qs.plan_handle,
- qs.total_worker_time
- from
- sys.dm_exec_query_stats qs
- order by qs.total_worker_time desc) as highest_cpu_queries
- cross apply sys.dm_exec_sql_text(plan_handle) as q
- order by highest_cpu_queries.total_worker_time desc
-
- I/O瓶颈
- select top 25
- (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
|
