打开官方网站,网站开发系统调研目的,百度广告竞价排名,证件照片制作软件免费下载CPU使用率过高有许多可能原因#xff0c;但以下原因最为常见#xff1a;
1.由于以下情况#xff0c;表或索引扫描导致的高逻辑读取#xff1a; 过期统计信息 缺少索引 参数敏感计划 (PSP) 问题 设计不佳的查询 2.工作负荷增加
对于安装了sqlserver的服务器#xff0c;可…CPU使用率过高有许多可能原因但以下原因最为常见
1.由于以下情况表或索引扫描导致的高逻辑读取 ·过期统计信息 ·缺少索引 ·参数敏感计划 (PSP) 问题 ·设计不佳的查询 2.工作负荷增加
对于安装了sqlserver的服务器可以先看下任务管理器中sqlserver对cpu的占用情况确定是否是sqlserver导致cpu消耗过高。 如果sqlserver进程导致CPU使用率过高则最常见的原因是执行表或索引扫描的查询其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。
查询sqlserver当前在总CPU使用率中的占比
DECLARE init_sum_cpu_time int,utilizedCpuCount int--get CPU count used by SQL ServerSELECT utilizedCpuCount COUNT( * )FROM sys.dm_os_schedulersWHERE status VISIBLE ONLINE--calculate the CPU usage by queries OVER a 5 sec intervalSELECT init_sum_cpu_time SUM(cpu_time)FROM sys.dm_exec_requests WAITFOR DELAY 00:00:05SELECT CONVERT(DECIMAL(5,2),((SUM(cpu_time) - init_sum_cpu_time) / (utilizedCpuCount * 5000.00)) * 100) AS [CPU FROM Queries AS Percent of Total CPU Capacity]FROM sys.dm_exec_requests 查看当前占用CPU高的查询
SELECT TOP 100 s.session_id,r.status,r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time / (1000 * 60) Elaps M,SUBSTRING(st.TEXT, (r.statement_start_offset / 2) 1,((CASE r.statement_end_offsetWHEN -1 THEN DATALENGTH(st.TEXT)ELSE r.statement_end_offsetEND - r.statement_start_offset) / 2) 1) AS statement_text,COALESCE(QUOTENAME(DB_NAME(st.dbid)) N. QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) N. QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ) AS command_text,r.command,s.login_name,s.host_name,c.client_net_address,s.program_name,s.last_request_end_time,s.login_time,r.open_transaction_countFROM sys.dm_exec_connections cJOIN sys.dm_exec_sessions s ON c.session_id s.session_idJOIN sys.dm_exec_requests r ON r.session_id s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st-- WHERE r.session_id ! SPIDORDER BY r.cpu_time DESC 查找历史当前占用CPU高的查询
SELECT TOP 100 st.text AS batch_text,SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) 1,((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS statement_text,qs.execution_count,qs.total_worker_time,qs.last_execution_time,(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,qs.total_logical_reads / qs.execution_count AS avg_logical_reads,(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_msFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) stORDER BY(qs.total_worker_time / qs.execution_count) DESC 查询识别导致CPU使用率高且在查询计划中至少包含一个缺失索引的查询
-- Captures the Total CPU time spent by a query along with the query plan and total executionsSELECTqs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,q.[text],p.query_plan,qs_cpu.execution_count,q.dbid,d.name,q.objectid,q.encrypted AS text_encryptedFROM(SELECT TOP 500 qs.plan_handle,qs.total_worker_time,qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpuCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qJOIN master.dbo.sysdatabases d on q.dbidd.dbidCROSS APPLY sys.dm_exec_query_plan(plan_handle) pWHERE p.query_plan.exist(declare namespaceqplan http://schemas.microsoft.com/sqlserver/2004/07/showplan;//qplan:MissingIndexes)1 使用以下查询检查是否缺少索引并应用具有高改进度量值的任何建议索引。 从输出中具有最高improvement_measure值的前5或10条建议开始。这些索引对性能有最显著的积极影响。
SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,mig.index_group_handle,mid.index_handle,CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks migs.user_scans)) AS improvement_measure,CREATE INDEX missing_index_ CONVERT(VARCHAR, mig.index_group_handle) _ CONVERT(VARCHAR, mid.index_handle) ON mid.statement ( ISNULL(mid.equality_columns,) CASE WHEN mid.equality_columns IS NOT NULLAND mid.inequality_columns IS NOT NULL THEN ,ELSE END ISNULL(mid.inequality_columns,) ) ISNULL( INCLUDE ( mid.included_columns ),) AS create_index_statement,migs.*,mid.database_id,mid.[object_id]FROM sys.dm_db_missing_index_groups migINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle mid.index_handleWHERE CONVERT (DECIMAL (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks migs.user_scans)) 10ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks migs.user_scans) DESC
