这个服务器的查询主要是通过调用2个存储过程实现的,通过下面的语句检查发现CPU主要就被那两个存储过程占用了: select highest_cpu_queries.sql_handle, 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.sql_handle, 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然后做的就是优化那两个存储过程等等,具体操作: 1、历史数据转移走 2、去掉存储过程里面的isnull 等等函数判断,去掉不必要的order by 和group by以及一些变态的运算 3、设置数据库选项参数化为“强制参数化” 4、重新调整了索引
这个服务器就装了一个SQL Server数据库,其他任何应用都没装,也没有病毒(内网机,已检查),通过SQL Server 的《活动监视器》查看,数据库使用的CPU和任务管理器里面的CPU曲线是一样的,可以判断CPU主要是耗在了数据库上面
cpu使用率不高啊,另外,你的server没做虚拟化的吧?
谢版主,谢诸位前辈,CPU不高,小弟结贴了
周期性检查还是有必要的,加上CPU这东西波动很大,仅一个图不能说明什么: SELECT TOP ( 10 ) wait_type , waiting_tasks_count , ( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time , max_wait_time_ms , CASE waiting_tasks_count WHEN 0 THEN 0 ELSE wait_time_ms / waiting_tasks_count END AS avg_wait_time FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and -- LAZYWRITER_SLEEP waits AND wait_type NOT LIKE 'XE%' AND wait_type NOT IN -- remove system waits ( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' ) ORDER BY wait_time_ms DESC
select
highest_cpu_queries.sql_handle,
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.sql_handle,
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然后做的就是优化那两个存储过程等等,具体操作:
1、历史数据转移走
2、去掉存储过程里面的isnull 等等函数判断,去掉不必要的order by 和group by以及一些变态的运算
3、设置数据库选项参数化为“强制参数化”
4、重新调整了索引
SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
-- LAZYWRITER_SLEEP waits
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN -- remove system waits
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC
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 statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC