4g就开3gb开关,找cpu占用高的语句调优 用sqlprofile跟踪,或动态视图 SELECT total_cpu_time, total_execution_count, number_of_statements, s2.text, (SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text FROM (SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, COUNT(*) AS number_of_statements, qs.sql_handle, MIN(statement_start_offset) AS statement_start_offset, MAX(statement_end_offset) AS statement_end_offset FROM sys.dm_exec_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC) AS stats CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
用sqlprofile跟踪,或动态视图
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text,
(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle,
MIN(statement_start_offset) AS statement_start_offset,
MAX(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
第 6 行: 'MAX' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 18
在关键字 'ORDER' 附近有语法错误。运行代码,提示以上错误
sqlserver 2005 及以上的版本 的mssm中: 右键点击 用户数据库 ——报表——,有关于近期sql引擎活动的报表,一目了然!
第一种方法,在'事件选择'面板做列筛选,选择CPU,填写大于等于值, 多做几次调整适当的值.
第二种方法,将所有跟踪数据保存到表('常规' 下有个保存到表的项), 录制足够长的时间, 然后用sql语句去查看;
还有其它一些分析工具做.
jinsenhome 说的报表也是好办法.