???????SQL Server??CPU???
???????????? ???????[ 2012/12/21 10:07:57 ] ????????
????????
???????????????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??????????????
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11