一、问题描述
反馈SQLServer中出现CPU使用率过高有许多可能原因，但常见的原因如下最为常见：
- 由于以下情况，表或索引扫描导致的高逻辑读取：
- 过期统计信息
- 缺少索引
- 设计不佳的查询
- 工作负载增加
针对此类问题，微软有一套的排查套路，整理记录在此。
近阶段程序运行过程中，发现CPU占用特别高，导致程序运行速度很慢，当时监测的CPU运行情况如下，该版本的SqlServer数据库最多可以使用40个CPU逻辑处理单元，全部100%
二、处理步骤
步骤1，验证 SQL Server 是否导致 CPU 使用率过高
使用以下工具之一检查 SQL Server 进程是否确实导致 CPU 使用率过高：任务管理器：在“进程”选项卡上，检查“64 位版本的 SQL Server Windows NT”的“CPU”列的值是否接近 100%。
性能和资源监视器
计数器：Process/%User Time, % Privileged Time
实例：sqlservr
可以使用以下 PowerShell 脚本在 60 秒的跨度内收集计数器数据：
-
$serverName
= $env:COMPUTERNAME
-
$Counters
= @(
-
(
"\\$serverName"
+
"\Process(sqlservr*)\% User Time"), (
"\\$serverName"
+
"\Process(sqlservr*)\% Privileged Time")
-
)
-
Get-Counter -Counter $Counters -MaxSamples
30 | ForEach {
-
$_.CounterSamples | ForEach {
-
[pscustomobject]@{
-
TimeStamp
= $_.TimeStamp
-
Path
= $_.Path
-
Value
= ([Math]
::Round($_.CookedValue,
3))
-
}
-
Start-Sleep -s
2
-
}
-
}
如果 % User Time 始终大于 90%，则是 SQL Server 进程导致 CPU 使用率过高。 但是，如果 % Privileged time 始终大于 90%，则是防病毒软件、其他驱动程序或计算机上的其他操作系统组件导致 CPU 使用率过高。
步骤2，确定影响 CPU 使用率的查询
如果 Sqlservr.exe 进程导致 CPU 使用率过高，则最常见的原因是执行表或索引扫描的 SQL Server 查询，其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。 要了解查询当前在总 CPU 使用率中的占比，请运行以下语句：
-
DECLARE @init_
sum_cpu_
time int,
-
@utilizedCpuCount int
-
--
get CPU
count used
by SQL Server
-
SELECT @utilizedCpuCount
=
COUNT(
* )
-
FROM sys.dm_os_schedulers
-
WHERE
status
=
'VISIBLE ONLINE'
-
--calculate the CPU
usage
by queries OVER a
5 sec interval
-
SELECT @init_
sum_cpu_
time
=
SUM(cpu_
time)
-
FROM sys.dm_exec_requests WAITFOR DELAY
'00:00:05'
SELECT 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 活动的查询，请运行以下语句：
如果查询目前未驱动 CPU，可以运行以下语句来查找历史占用大量 CPU 的查询：
步骤 3：更新统计信息
在确定 CPU 占用最高的查询后，请更新这些查询使用的表的“更新统计信息” 。 可以使用 sp_updatestats 系统存储过程更新当前数据库中所有用户定义表和内部表的统计信息。
exec sp_updatestats
sp_updatestats系统存储过程针对当前数据库中的所有用户定义表和内部表运行UPDATE STATISTICS。
步骤 4：添加缺失索引
缺少索引可能导致运行速度较慢的查询和 CPU 使用率过高。 可以识别缺失的索引并创建这些索引，以改善这种性能影响。
-
-- Captures the Total CPU
time spent
by a query along
with the query plan
and total executions
-
SELECT
-
qs_cpu.total_worker_
time
/
1000
AS total_cpu_
time_ms,
-
q.[text],
-
p.query_plan,
-
qs_cpu.execution_
count,
-
q.dbid,
-
q.objectid,
-
q.encrypted
AS text_encrypted
-
FROM
-
(
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_cpu
-
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
AS q
-
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
-
WHERE p.query_plan.exist(
'declare namespace
-
qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
-
//qplan:MissingIndexes')
=
1
2、查看已标识查询的执行计划，并通过进行所需的更改来优化查询。 以下屏幕截图显示了一个示例，其中 SQL Server 将指出查询的缺失索引。 右键单击查询计划的“缺失索引”部分，然后选择“缺少索引详细信息”，在 SQL Server Management Studio 的另一个窗口中创建索引。
3. 使用以下查询检查是否缺少索引，并应用具有高改进度量值的任何建议索引。 从输出中具有最高 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
NULL
-
AND 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 mig
-
INNER JOIN sys.dm_db_missing_
index_
group_stats migs
ON migs.
group_handle
= mig.
index_
group_handle
-
INNER JOIN sys.dm_db_missing_
index_details mid
ON mig.
index_handle
= mid.
index_handle
-
WHERE CONVERT (DECIMAL (
28,
1),
-
migs.avg_total_user_cost
* migs.avg_user_impact
* (migs.user_seeks
+ migs.user_scans))
>
10
-
ORDER
BY migs.avg_total_user_cost
* migs.avg_user_impact
* (migs.user_seeks
+ migs.user_scans) DESC
步骤 5：调查并解决参数敏感问题
可以使用 DBCC FREEPROCCACHE 命令释放计划缓存，并检查这是否解决了 CPU 使用率过高的问题。 如果问题已修复，则表示是参数敏感问题（PSP，也称为“参数探查问题”）。
使用不带参数的 DBCC FREEPROCCACHE 将从计划缓存中删除所有已编译的计划。 这将导致再次编译新的查询执行，从而导致每个新查询的持续时间一次性延长。 最佳方法是使用 DBCC FREEPROCCACHE ( plan_handle | sql_handle ) 来识别导致问题的查询，然后解决单个查询或有问题的查询。
将 DBCC FREEPROCCACHE 命令用作临时解决方案，直到应用程序代码修复为止。 可以使用 DBCC FREEPROCCACHE (plan_handle) 命令仅删除导致问题的计划。 例如，若要查找引用 AdventureWorks 中 Person.Person 表的查询计划，可以使用此查询查找查询句柄。 然后，可以使用查询结果第二列中生成的 DBCC FREEPROCCACHE (plan_handle)，从缓存中释放特定查询计划。
-
SELECT text,
'DBCC FREEPROCCACHE (0x'
+
CONVERT(
VARCHAR (
512), plan_handle,
2)
+
')'
AS dbcc_freeproc_command
FROM sys.dm_exec_cached_plans
-
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
-
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
-
WHERE text
LIKE
'%person.person%'
步骤 6：调查并解决 SARGability 问题
当 SQL Server 引擎可以使用索引查找来加快查询的执行速度时，查询中的谓词将被视为 SARGable (Search ARGument-able)。 许多查询设计会阻止 SARGability，并导致表或索引扫描和 CPU 使用率过高。 请考虑 AdventureWorks 数据库的以下查询，其中必须检索每个 ProductNumber 并向其应用 SUBSTRING() 函数，然后再将其与字符串文本值进行比较。 如你所见，必须先提取表的所有行，然后应用函数，然后才能进行比较。 从表中提取所有行意味着表或索引扫描，这会导致 CPU 使用率较高。
-
# 函数查询方法
-
SELECT ProductID, Name, ProductNumber
-
FROM [Production].[Product]
-
WHERE SUBSTRING(ProductNumber,
0,
4)
=
'HN-'
-
# 谓词模糊方法
-
SELECT ProductID, Name, ProductNumber
-
FROM [Production].[Product]
-
WHERE Name LIKE
'Hex%'
-
# 谓词计算方法
-
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice
*
0.10 [
10% Commission]
-
FROM [Sales].[SalesOrderDetail]
-
WHERE UnitPrice
*
0.10
>
300
步骤 7：禁用重度跟踪
检查 SQL 跟踪或 XEvent 跟踪，这些跟踪会影响 SQL Server 性能并导致 CPU 使用率过高。 例如，如果跟踪重度 SQL Server 活动，使用以下事件可能会导致 CPU 使用率过高：
- 查询计划 XML 事件 (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile,
- query_post_execution_showplan, query_pre_execution_showplan)
- 语句级事件 (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
- 登录和注销事件 (login, process_login_finish, login_event, logout)
- 锁定事件 (lock_acquired, lock_cancel, lock_released)
- 等待事件 (wait_info, wait_info_external)
- SQL 审核事件（取决于该组中审核的组和 SQL Server 活动）
运行以下查询以确定活动的 XEvent 或 Server 跟踪：
-
PRINT
'--Profiler trace summary--'
-
SELECT traceid,
property, CONVERT(VARCHAR(
1024),
value)
AS
value
FROM
::fn_trace_getinfo(
-
default)
-
GO
-
PRINT
'--Trace event details--'
-
SELECT trace_id,
-
status,
-
CASE
WHEN row_
number
=
1
THEN path
ELSE
NULL
end
AS path,
-
CASE
WHEN row_
number
=
1
THEN max_
size
ELSE
NULL
end
AS max_
size,
-
CASE
WHEN row_
number
=
1
THEN
start_
time
ELSE
NULL
end
AS
start_
time,
-
CASE
WHEN row_
number
=
1
THEN
stop_
time
ELSE
NULL
end
AS
stop_
time,
-
max_files,
-
is_rowset,
-
is_rollover,
-
is_shutdown,
-
is_
default,
-
buffer_
count,
-
buffer_
size,
-
last_event_
time,
-
event_
count,
-
trace_event_id,
-
trace_event_name,
-
trace_
column_id,
-
trace_
column_name,
-
expensive_event
-
FROM
-
(
SELECT t.id
AS trace_id,
-
row_
number() over(PARTITION
BY t.id
order
by te.trace_event_id, tc.trace_
column_id)
AS row_
number,
-
t.
status,
-
t.path,
-
t.max_
size,
-
t.
start_
time,
-
t.
stop_
time,
-
t.max_files,
-
t.
is_rowset,
-
t.
is_rollover,
-
t.
is_shutdown,
-
t.
is_
default,
-
t.buffer_
count,
-
t.buffer_
size,
-
t.
last_event_
time,
-
t.event_
count,
-
te.trace_event_id,
-
te.name
AS trace_event_name,
-
tc.trace_
column_id,
-
tc.name
AS trace_
column_name,
-
CASE
WHEN te.trace_event_id
in (
23,
24,
40,
41,
44,
45,
51,
52,
54,
68,
96,
97,
98,
113,
114,
122,
146,
180)
THEN CAST(
1
as
bit)
ELSE CAST(
0
AS
BIT)
END
AS expensive_event
FROM sys.traces t CROSS APPLY
::fn_trace_geteventinfo(t.id)
AS e JOIN sys.trace_events te
ON te.trace_event_id
= e.eventid JOIN sys.trace_
columns tc
ON e.columnid
= trace_
column_id)
AS x
-
GO
-
PRINT
'--XEvent Session Details--'
-
SELECT sess.NAME
'session_name', event_name, xe_event_name, trace_event_id,
-
CASE
WHEN xemap.trace_event_id
IN(
23,
24,
40,
41,
44,
45,
51,
52,
54,
68,
96,
97,
98,
113,
114,
122,
146,
180)
-
THEN Cast(
1
AS
BIT)
-
ELSE Cast(
0
AS
BIT)
-
END
AS expensive_event
-
FROM sys.dm_xe_sessions sess
-
JOIN sys.dm_xe_session_events evt
-
ON sess.
address
= evt.event_session_
address
-
INNER JOIN sys.trace_xe_event_map xemap
-
ON evt.event_name
= xemap.xe_event_name
-
GO
步骤 8：配置虚拟机
如果使用的是虚拟机，请确保不会过度预配 CPU 并正确配置它们。
解决 ESX/ESXi 虚拟机性能问题 (2001003)
步骤 9：纵向扩展系统以使用更多 CPU
如果单个查询实例的 CPU 占用很低，但所有查询的总体工作负载共同导致 CPU 占用较高，请考虑通过添加更多 CPU 来纵向扩展计算机。 使用以下查询找出超过单个执行的平均 CPU 占用和最大 CPU 占用的特定阈值且已在系统上多次运行的查询数：
-
-- Shows queries where Max
and average CPU
time exceeds
200 ms
and executed more
than
1000
times
-
DECLARE @cputime_threshold_microsec INT
=
200
*
1000
-
DECLARE @execution_
count INT
=
1000
-
SELECT qs.total_worker_
time
/
1000 total_cpu_
time_ms,
-
qs.max_worker_
time
/
1000 max_cpu_
time_ms,
-
(qs.total_worker_
time
/
1000)
/execution_
count average_cpu_
time_ms,
-
qs.execution_
count,
-
q.[text]
-
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle)
AS q
-
WHERE (qs.total_worker_
time
/execution_
count
> @cputime_threshold_microsec
-
OR qs.max_worker_
time
> @cputime_threshold_microsec )
-
AND execution_
count
> @execution_
count
-
ORDER
BY qs.total_worker_
time DESC
三、常用sql检查语句：
1、排查连接对象
此脚本可以查看到主机名和连接对象，如果连接对象不属于已知的软件，在防火墙中将此IP禁止掉即可
-
--如果想要指定查询某个数据库，将后面的注释去掉即可
-
SELECT
*
FROM sys.[sysprocesses] WHERE [spid]
>
50 --
AND DB_NAME([dbid])
=
'hisdb'
2、查询sql阻塞
然后使用下面语句看一下各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空。
-
SELECT [session_id],
-
[request_id],
-
[
start_
time]
AS
'开始时间',
-
[
status]
AS
'状态',
-
[command]
AS
'命令',
-
dest.[text]
AS
'sql语句',
-
DB_NAME([database_id])
AS
'数据库名',
-
[blocking_session_id]
AS
'正在阻塞其他的ID',
-
[wait_
type]
AS
'等待资源类型',
-
[wait_
time]
AS
'等待时间',
-
[wait_resource]
AS
'等待的资源',
-
[reads]
AS
'物理读次数',
-
[writes]
AS
'写次数',
-
[logical_reads]
AS
'逻辑读次数',
-
[row_
count]
AS
'返回结果行数'
-
FROM sys.[dm_exec_requests]
AS der
-
CROSS APPLY
-
sys.[dm_exec_sql_text](der.[sql_handle])
AS dest
-
WHERE [session_id]
>
50
-
ORDER
BY [cpu_
time] DESC
-
---查看是哪些SQL语句占用较大可以使用下面代码
-
SELECT
TOP
10
-
dest.[text]
AS
'sql语句'
-
FROM sys.[dm_exec_requests]
AS der
-
CROSS APPLY
-
sys.[dm_exec_sql_text](der.[sql_handle])
AS dest
-
WHERE [session_id]
>
50
-
ORDER
BY [cpu_
time] DESC
3、查询worker等待
如果SQLSERVER存在要等待的资源，那么执行下面语句就会显示出会话中有多少个worker在等待
-
SELECT [session_id],
-
[request_id],
-
[
start_
time]
AS
'开始时间',
-
[
status]
AS
'状态',
-
[command]
AS
'命令',
-
dest.[text]
AS
'sql语句',
-
DB_NAME([database_id])
AS
'数据库名',
-
[blocking_session_id]
AS
'正在阻塞其他的ID',
-
der.[wait_
type]
AS
'等待资源类型',
-
[wait_
time]
AS
'等待时间',
-
[wait_resource]
AS
'等待的资源',
-
[dows].[waiting_tasks_
count]
AS
'当前等待任务数',
-
[reads]
AS
'物理读次数',
-
[writes]
AS
'写次数',
-
[logical_reads]
AS
'逻辑读次数',
-
[row_
count]
AS
'返回结果行数'
-
FROM sys.[dm_exec_requests]
AS der
-
INNER JOIN [sys].[dm_os_wait_stats]
AS dows
-
ON der.[wait_
type]
=[dows].[wait_
type]
-
CROSS APPLY
-
sys.[dm_exec_sql_text](der.[sql_handle])
AS dest
-
WHERE [session_id]
>
50
-
ORDER
BY [cpu_
time] DESC
查询CPU占用最高的SQL语句
-
SELECT total_worker_
time
/execution_
count
AS avg_cpu_cost, plan_handle,
-
execution_
count,
-
(
SELECT SUBSTRING(text, statement_
start_offset
/
2
+
1,
-
(CASE
WHEN statement_
end_offset
= -
1
-
THEN LEN(CONVERT(nvarchar(max), text))
*
2
-
ELSE statement_
end_offset
-
END
- statement_
start_offset)
/
2)
-
FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
-
FROM sys.dm_exec_query_stats
-
ORDER
BY [avg_cpu_cost] DESC
4、索引缺失查询
如果索引缺失的话，需要根据查询结果中的关键信息逐一添加
-
SELECT DatabaseName
= DB_NAME(database_id)
-
,[
Number Indexes Missing]
=
count(
*)
-
FROM sys.dm_db_missing_
index_details
-
GROUP
BY DB_NAME(database_id)
-
ORDER
BY
2 DESC;
-
SELECT [Total Cost]
= ROUND(avg_total_user_cost
* avg_user_impact
* (user_seeks
+ user_scans),
0)
-
, avg_user_impact
-
, TableName
= statement
-
, [EqualityUsage]
= equality_
columns
-
, [InequalityUsage]
= inequality_
columns
-
, [Include Cloumns]
= included_
columns
-
FROM sys.dm_db_missing_
index_groups g
-
INNER JOIN sys.dm_db_missing_
index_
group_stats s
ON s.
group_handle
= g.
index_
group_handle
-
INNER JOIN sys.dm_db_missing_
index_details d
ON d.
index_handle
= g.
index_handle
-
ORDER
BY [Total Cost] DESC;
5、占用cpu高100%排查
sqlserver占用cpu高100%排查，可根据下面语句查询当前耗时最高的sql语句，以及读写次数。
-
SELECT
TOP
50
-
[session_id],
-
[request_id],
-
[cpu_
time],
-
[
start_
time]
AS
'开始时间',
-
[
status]
AS
'状态',
-
[command]
AS
'命令',
-
dest.[text]
AS
'sql语句',
-
DB_NAME([database_id])
AS
'数据库名',
-
[blocking_session_id]
AS
'正在阻塞其他会话的会话ID',
-
der.[wait_
type]
AS
'等待资源类型',
-
[wait_
time]
AS
'等待时间',
-
[wait_resource]
AS
'等待的资源',
-
[dows].[waiting_tasks_
count]
AS
'当前正在进行等待的任务数',
-
[reads]
AS
'物理读次数',
-
[writes]
AS
'写次数',
-
[logical_reads]
AS
'逻辑读次数',
-
[row_
count]
AS
'返回结果行数'
-
FROM sys.[dm_exec_requests]
AS der
-
INNER JOIN [sys].[dm_os_wait_stats]
AS dows
-
ON der.[wait_
type]
=[dows].[wait_
type]
-
CROSS APPLY
-
sys.[dm_exec_sql_text](der.[sql_handle])
AS dest
-
WHERE [session_id]
>
50
-
ORDER
BY [cpu_
time] DESC
四、其他常用sql
-
--------------------SQL Server启动时间
-
SELECT sqlserver_
start_
time
FROM sys.dm_os_sys_info;
-
-
-
--------------------SQL Server版本
-
select @@version;
-
-
-
--------------------数据库正在执行的sql
-
select text,wait_resource,wait_
time,wait_
type,session_id,blocking_session_id
-
from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle);
-
-
select request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
-
from sys.dm_tran_locks where resource_
type
=
'OBJECT';
-
-
--------------------死锁
-
select a.text seesion_text,a.wait_
type,a.wait_
time,a.session_id,a.blocking_session_id,b.text blocking_session_text
from
-
(
select text,wait_resource,wait_
type,wait_
time,session_id,blocking_session_id
from sys.dm_exec_requests
-
cross apply sys.dm_exec_sql_text(sql_handle) where wait_
time
>
1000) a
left join
-
(
select c.session_id,t.text
from sys.dm_exec_connections
as c cross apply sys.dm_exec_sql_text(most_recent_sql_handle)
as t) b
-
on b.session_id
=a.blocking_session_id;
-
-
-
--------------------获取一次性缓存计划的数量
-
SELECT objtype, cacheobjtype,
-
AVG(usecounts)
AS Avg_UseCount,
-
SUM(refcounts)
AS AllRefObjects,
-
SUM(CAST(
size_
in_bytes
AS bigint))
/
1024
/
1024
AS
Size_MB
-
FROM sys.dm_exec_cached_plans
-
WHERE objtype
=
'Adhoc'
AND usecounts
=
1
-
GROUP
BY objtype, cacheobjtype;
-
-
SELECT objtype, cacheobjtype,
-
AVG(usecounts)
AS Avg_UseCount,
-
SUM(refcounts)
AS AllRefObjects,
-
SUM(CAST(
size_
in_bytes
AS bigint))
/
1024
/
1024
AS
Size_MB
-
FROM sys.dm_exec_cached_plans
-
WHERE objtype
=
'Adhoc'
AND usecounts
>
1
-
GROUP
BY objtype, cacheobjtype;
-
-
-
--------------------操作系统内存建议
-
-- You want
to see
"Available physical memory is high"
-
SELECT total_physical_memory_kb
/
1024
AS [Physical Memory (MB)],
-
available_physical_memory_kb
/
1024
AS [Available Memory (MB)],
-
total_
page_
file_kb
/
1024
AS [Total
Page
File (MB)],
-
available_
page_
file_kb
/
1024
AS [Available
Page
File (MB)],
-
system_cache_kb
/
1024
AS [System Cache (MB)],
-
system_memory_state_desc
AS [System Memory State]
-
FROM sys.dm_os_sys_memory
WITH (NOLOCK) OPTION (RECOMPILE);
-
-
-
--------------------操作系统内存情况
-
SELECT
-
physical_memory_
in_
use_kb
/
1024 Physical_memory_
in_
use_MB,
-
large_
page_allocations_kb
/
1024 Large_
page_allocations_MB,
-
locked_
page_allocations_kb
/
1024 Locked_
page_allocations_MB,
-
virtual_
address_
space_reserved_kb
/
1024 VAS_reserved_MB,
-
virtual_
address_
space_committed_kb
/
1024 VAS_committed_MB,
-
virtual_
address_
space_available_kb
/
1024 VAS_available_MB,
-
page_fault_
count
Page_fault_
count,
-
memory_utilization_percentage Memory_utilization_percentage,
-
process_physical_memory_low Process_physical_memory_low,
-
process_virtual_memory_low Process_virtual_memory_low
-
FROM sys.dm_os_process_memory;
-
-
-
--------------------恢复日志描述模型，复用等，日志大小和日志文件大小
-
SELECT db.[name]
AS [Database Name], SUSER_SNAME(db.owner_sid)
AS [Database Owner], db.recovery_model_desc
AS [Recovery Model],
-
db.log_reuse_wait_desc
AS [Log Reuse Wait Description],
-
CAST((CAST(ls.cntr_
value
AS FLOAT)
/
1024)
AS DECIMAL(
18,2))
AS [Log
Size (MB)],
-
CAST((CAST(lu.cntr_
value
AS FLOAT)
/
1024)
AS DECIMAL(
18,2))
AS [Log Used (MB)],
-
CAST(CAST(lu.cntr_
value
AS FLOAT)
/ CAST(ls.cntr_
value
AS FLOAT)
AS DECIMAL(
18,2))
*
100
AS [Log Used %],
-
db.[compatibility_level]
AS [DB Compatibility Level],
-
db.
page_verify_option_desc
AS [
Page Verify Option],db.
is_auto_update_stats_
on,
-
db.
is_auto_update_stats_async_
on,
-
db.
is_auto_
close_
on, db.
is_auto_shrink_
on
-
FROM sys.databases
AS db
WITH (NOLOCK)
-
INNER JOIN sys.dm_os_performance_counters
AS lu
WITH (NOLOCK)
-
ON db.name
= lu.instance_name
-
INNER JOIN sys.dm_os_performance_counters
AS ls
WITH (NOLOCK)
-
ON db.name
= ls.instance_name
-
WHERE lu.counter_name LIKE N
'Log File(s) Used Size (KB)%'
-
AND ls.counter_name LIKE N
'Log File(s) Size (KB)%'
-
AND ls.cntr_
value
>
0
-
ORDER
BY db.[name] OPTION (RECOMPILE);
-
-
-
-
-
--------------------按索引优势列出的所有数据库缺少索引
-
SELECT CONVERT(decimal(
18,2), user_seeks
* avg_total_user_cost
* (avg_user_impact
*
0.01))
AS [
index_advantage],
-
migs.
last_user_seek, mid.[statement]
AS [Database.Schema.
Table],
-
mid.equality_
columns, mid.inequality_
columns, mid.included_
columns,
-
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
-
FROM sys.dm_db_missing_
index_
group_stats
AS migs
WITH (NOLOCK)
-
INNER JOIN sys.dm_db_missing_
index_groups
AS mig
WITH (NOLOCK)
-
ON migs.
group_handle
= mig.
index_
group_handle
-
INNER JOIN sys.dm_db_missing_
index_details
AS mid
WITH (NOLOCK)
-
ON mig.
index_handle
= mid.
index_handle
-
ORDER
BY
index_advantage DESC OPTION (RECOMPILE);
-
-
-
--------------------查看CPU占用量最高的会话及SQL语句
-
select spid,cmd,cpu,physical_io,memusage,
-
(
select
top
1 [text]
from
::fn_
get_sql(sql_handle)) sql_text
-
from master..sysprocesses
order
by cpu desc,physical_io desc
-
-
-
--------------------查看缓存重用次数少，内存占用大的SQL语句
-
SELECT
TOP
100 usecounts, objtype, p.
size_
in_bytes,[sql].[text]
-
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
-
ORDER
BY usecounts,p.
size_
in_bytes desc
-
-
-
--------------------查看哪些进程在使用
-
select
*
from sysprocesses
6369
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
