????????

???????????????CPU????????μ?SQLServer崻???????????????????Щ????????

?????????????

????????CPU???SQLServer?????????????к??????????????????????????????CPU??????????????????????????????????????????????????????????????????????????????????????????Щ???ù????????CPU????????

???????????????Performance Monitor????

?????????????????????????CPU?????????Processor:% Processor Time ?????????????????????80%/????????????п?????????CPU????????

????CPU????????????????????????????????SQL Statistics???????????????????????????????????????????????????????SQL Recompilations/sec ?е?BatchRequests/sec?????????????????????

???????ú???????????????

?????? SQL Server: SQL Statistics: SQL Compilations/sec

?????? SQL Server: SQL Statistics: SQL Recompilations/sec

?????? SQL Server: SQL Statistics: Batch Requests/sec

??????????????????CPU????????????????SQL Server: Cursor Manager By Type – CursorRequests/Sec ????????????????????α?????????????????????????????α?????????п??????????Ч???α???ú?С????????????small fetch size??????????????

??????????в???????????CPU?????????飺

????SQL Statistics:Batch Requests/sec counter ??????????CPU?????????У?????????????ú?С???????????ζ????????ò??м?????С?

????????????????DMVs????

??????????????CPU????????DMVs??????????sys.dm_exec_query_stats??????????????????????CPU?????????????????????CPU????м????


select plan_handle??
      sum(total_worker_time) as total_worker_time??
      sum(execution_count) as total_execution_count??
      count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order bysum(total_worker_time)?? sum(execution_count) desc


????SQLServer2008??????????????????????hash??????????query_hash????????????????????????????????????????????query_hash????????? Showplan/Statistics XML QueryHash?????п??????

????Plan_generation_num????????????????????????

????SQLServer???????????????????????????м??????????????????CPU???á???Ч?????????????CPU???????????????????sys.dm_exec_query_stats ??????

??????????????????SQLServer???????????????????????飺

????sys.dm_exec_query_optimizer_info ?????е???????????????????á?

???????????????DMVs???????????в???????????????м?????????

?????? sys.dm_exec_cached_plan: Shows the cached query plans.
?????? sys.dm_exec_requests: Shows each executing request in the SQL Server instance.
?????? sys.dm_exec_sessions: Shows all active user connections and internal tasks.
?????? sys.dm_exec_sql_text: Shows the text of the SQL batches.
?????? sys.dm_os_tasks: Shows each active task within SQL Server.

????SQL Server Profiler:

????????????????????????????????????SQLServer Profiler?????????????????????SQLServer Profiler ??????????????????????洢??????????????????????

????SP:Recompile?? CursorRecompile?? SQL:StmtRecompile: ???????????SQLServer???????SP:Recompile????е?EventSubClass ??????????????

?????? Showplan XML For Query Compile: ???????????T-SQL?????????????????????????????ID.??????????????????????????????????????????????????????????????????SQL Compilations/sec ????????????????????????

??????Ч???α???????RPC:Completed????????????sp_cursorfetch????????????????????????????fetch??????????????