SqlServer如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息
作者：潇湘隐者
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了SqlServer如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息的相关资料,需要的朋友可以参考下
在SQL SERVER中如何通过SQL语句获取服务器硬件和系统信息呢？下面介绍一下如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息。如有不足和遗漏，敬请补充。谢谢！
一：查看数据库服务器CPU的信息
---SQL 1:获取数据库服务器的CPU型号
EXEC xp_instance_regread
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';
---SQL 2:获取数据库服务器CPU核数等信息(只适用于SQL 2005以及以上版本数据库)
/*************************************************************************************
--cpu_count
:指定系统中的逻辑 CPU 数
--hyperthread_ratio
:指定一个物理处理器包公开的逻辑内核数与物理内核数的比.虚拟机
中可以表示每个虚拟插槽的核数。虚拟中[Physical CPU Count]其实
表示虚拟插槽数
*************************************************************************************/
SELECT s.cpu_count
AS [Loggic CPU Count]
,s.hyperthread_ratio
AS [Hyperthread Ratio]
,s.cpu_count/s.hyperthread_ratio AS [Physical CPU Count]
FROM sys.dm_os_sys_info s OPTION (RECOMPILE);
---SQL 3:获取数据库服务器CPU核数(适用于所有版本)
CREATE TABLE #TempTable
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
INSERT INTO #TempTable
SELECT Internal_Value AS VirtualCPUCount
#TempTable
Name = 'ProcessorCount';
DROP TABLE #TempT
---SQL 4：在老外博客中看到一个计算CPU相关信息的SQL，不过虚拟机计算有点小问题，我修改了一下。
DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [float] NULL
,[c_val] [varchar](128) NULL
INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [number_of_cores_per_cpu]
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [total_number_of_cores]
,[cpu_count] AS [number_of_virtual_cpus]
SELECT [c_val]
FROM @xp_msver
WHERE [c_name] = 'Platform'
) AS [cpu_category]
FROM [sys].[dm_os_sys_info]
SELECT [number_of_physical_cpus]
,[number_of_cores_per_cpu]
,[total_number_of_cores]
,[number_of_virtual_cpus]
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]
---查看虚拟机CPU信息
DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [float] NULL
,[c_val] [varchar](128) NULL
INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
,[hyperthread_ratio] AS [number_of_cores_per_cpu]
,[cpu_count] AS [total_number_of_cores]
,[cpu_count] AS [number_of_virtual_cpus]
SELECT [c_val]
FROM @xp_msver
WHERE [c_name] = 'Platform'
) AS [cpu_category]
FROM [sys].[dm_os_sys_info]
SELECT [number_of_physical_cpus]
,[number_of_cores_per_cpu]
,[total_number_of_cores]
,[number_of_virtual_cpus]
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]
二：查看数据库服务器内存的信息
能否通过SQL语句获取服务器的物理内存大小？内存条型号？虚拟内存大小？内存使用情况？ 目前我所知道的只能通过SQL语句获取服务器物理内存大小，内存的使用情况。 至于内存条型号，系统虚拟内存大小，暂时好像还无法通过SQL语句获取。
查看服务器的物理内存情况
&&& 如下所示，从sys.dm_os_sys_info里面获取的physical_memory_in_bytes 或physical_memory_kb 的值总是低于实际物理内存。暂时不清楚具体原因（还未查到相关资料），所以计算大小有出入，要获取实际的物理内存，就必须借助CEILING函数。
--SQL 1：获取数据库服务器物理内存数(适用于所有版本)
CREATE TABLE #TempTable
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
INSERT INTO #TempTable
SELECT Internal_Value/1024 AS PhysicalMemory
#TempTable
Name = 'PhysicalMemory';
DROP TABLE #TempT
---SQL 2：适用于SQL Server 2005、SQL Server 2008
SELECT CEILING(physical_memory_in_bytes*1.0/24)
AS [Physical Memory Size]
FROM sys.dm_os_sys_info OPTION (RECOMPILE)
SELECT physical_memory_in_bytes*1.0/24
physical_memory_in_bytes AS [Physical Memory Size]
FROM sys.dm_os_sys_info OPTION (RECOMPILE)
---SQL 3：适用于SQL Server 2012 到 SQL Server 2014
SELECT CEILING(physical_memory_kb*1.0/) AS [Physical Memory Size]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);
---SQL 4：适用于SQL Server 2008以及以上的版本:查看物理内存大小，已经使用的物理内存以及还剩下的物理内存。
SELECT CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024)
AS [Physical Memory Size]
,CAST(available_physical_memory_kb * 1.0 / 1024 / 1024
AS DECIMAL(8, 4)) AS [Unused Physical Memory]
,CAST(( total_physical_memory_kb - available_physical_memory_kb ) * 1.0
/ 1024 / 1024 AS DECIMAL(8, 4))
AS [Used Physical Memory]
,CAST(system_cache_kb*1.0 /
AS DECIMAL(8, 4)) AS [System Cache Size]
sys.dm_os_sys_memory
三：查看数据库服务器硬盘的信息
如下所示，我们可以通过下面脚本获取服务器的各个磁盘的使用情况。但是无法获取磁盘的型号、转速之类的信息。
SET NOCOUNT ON
DECLARE @Result
DECLARE @objectInfo
DECLARE @DriveInfo
DECLARE @TotalSize
VARCHAR(20);
DECLARE @OutDrive
DECLARE @UnitMB
DECLARE @FreeRat
SET @UnitMB = 1048576;
--创建临时表保存服务器磁盘容量信息
CREATE TABLE #DiskCapacity
INSERT #DiskCapacity([DiskCD], FreeSize )
EXEC master.dbo.xp_
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE;
EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT;
DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD
SELECT DiskCD FROM #DiskCapacity
ORDER by DiskCD
OPEN CR_DiskI
FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
WHILE @@FETCH_STATUS=0
EXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfo
EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUT
UPDATE #DiskCapacity
SET TotalSize=@TotalSize/@UnitMB
WHERE DiskCD=@DriveInfo
FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
CLOSE CR_DiskInfo
DEALLOCATE CR_DiskI
EXEC @Result=sp_OADestroy @objectInfo
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE;
SELECT DiskCD
AS [Drive CD]
STR(TotalSize*1.0/)
AS [Total Size(GB)] ,
STR((TotalSize - FreeSize)*1.0/)
AS [Used Space(GB)] ,
STR(FreeSize*1.0/)
AS [Free Space(GB)] ,
STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2) AS [Used Rate(%)]
STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2)
AS [Free Rate(%)]
FROM #DiskC
DROP TABLE #DiskC
四：查看操作系统信息
& 通过下面SQL语句，我们可以查看操作系统版本、补丁、语言等信息
--创建临时表保存语言版本信息
CREATE TABLE #Language
[LanguageDtl]
NVARCHAR(64) ,
[os_language_version]
INSERT INTO #Language
SELECT 'English - United States'
,1033 UNION ALL
SELECT 'English - United Kingdom'
,2057 UNION ALL
SELECT 'Chinese - People''s Republic of China',2052 UNION ALL
SELECT 'Chinese - Singapore'
,4100 UNION ALL
SELECT 'Chinese - Taiwan'
,1028 UNION ALL
SELECT 'Chinese - Hong Kong SAR'
,3076 UNION ALL
SELECT 'Chinese - Macao SAR'
WITH SystemVersion(SystemInfo,ReleaseNo)
SELECT 'Windows 10' ,
SELECT 'Windows Server 2016 Technical Preview' ,
SELECT 'Windows 8.1' ,
SELECT 'Windows Server 2012 R2' ,
SELECT 'Windows 8' ,
SELECT 'Windows Server 2012' ,
SELECT 'Windows 7' ,
SELECT 'Windows Server 2008 R2' ,
SELECT 'Windows Server 2008' ,
SELECT 'Windows Vista' ,
SELECT 'Windows Server 2003 R2' ,
SELECT 'Windows Server 2003' ,
SELECT 'Windows XP 64-Bit Edition' ,
SELECT 'Windows XP' ,
SELECT 'Windows 2000' ,
SELECT s.SystemInfo
,w.windows_service_pack_level
,l.LanguageDtl
FROM sys.dm_os_windows_info w
INNER JOIN SystemVersion s ON w.windows_release=s.ReleaseNo
INNER JOIN #Language l ON l.os_language_version = w.os_language_
DROP TABLE #L
& 1：如上所示，临时表#Language的数据此处只列了几条常用的数据，如需全部数据，参考自行补充。
& 2：操作系统的版本信息的数据来源于
有可能出现不同操作系统具有相同Version number值，例如Windows 7 和Windows Server 2008 R2的Version numberd都为6.1。导致下面查询结果出现多条记录（如下所示）。一般要酌情判断（如果生产服务器都为Windows服务器，可以剔除Windows XP、Windows 7这类数据）。
ps:使用SQL语句获得服务器名称和IP地址
使用SQL语句获得服务器名称和IP地址&& 获取服务器名称：&
SELECT SERVERPROPERTY('MachineName')
select @@SERVERNAME
select HOST_NAME()
获取IP地址可以使用xp_cmdshell执行ipconfig命令：
--开启xp_cmdshell
exec sp_configure'show advanced options', 1
reconfigure with override
exec sp_configure'xp_cmdshell', 1
reconfigure with override
exec sp_configure'show advanced options', 0
reconfigure with override
declare @ipline varchar(200)
declare @pos int
declare @ip varchar(40)
set nocount on
set @ip = null
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp(ipline varchar(200))
insert #temp exec master..xp_cmdshell'ipconfig'
select @ipline = ipline
from #temp
where upper(ipline) like '%IPv4 地址%'--这里需要注意一下，系统不同这里的匹配值就不同
if @ipline is not null
set @pos = charindex(':',@ipline,1);
set @ip = rtrim(ltrim(substring(@ipline ,
@pos + 1 ,
len(@ipline) - @pos)))
select distinct(rtrim(ltrim(substring(@ipline ,
@pos + 1 ,
len(@ipline) - @pos)))) as ipaddress from #temp
drop table #temp
set nocount off
但是很多情况下由于安全问题是不允许使用xp_cmdshell，可以通过查询SYS.DM_EXEC_CONNECTIONS ：&
SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
,CLIENT_NET_ADDRESS AS 'ClientIPAddress'
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具SQLServer CPU瓶颈问题的判定和解决
title: SQLServer · CASE分析 · CPU瓶颈问题的判定和解决
author: 天铭
数据库出现无法登陆的告警
活跃连接堆积
实例CPU持续99%+
实例总连接数超过规格活跃链接堆积是结果，能堆积到500+可想对业务的影响已经
title: SQLServer · CASE分析 · CPU瓶颈问题的判定和解决
author: 天铭
数据库出现无法登陆的告警
活跃连接堆积
实例CPU持续99%+
实例总连接数超过规格活跃链接堆积是结果，能堆积到500+可想对业务的影响已经非常严重了连接数超过的原因跟业务上的限制策略有关
实例正常连接已经无法建立，只能利用DAC协助诊断
select lastwaittype,COUNT(*) from sys.sysprocesses
where spid&50
and lastwaittype!='MISCELLANEOUS'
group by lastwaittype
WITH [Waits] AS
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ( [wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE',
N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE',
N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK',
N'SLEEP_SYSTEMTASK',
N'WAITFOR',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT',
N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT',
N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE',
N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH',
N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
[W1]. [wait_type] AS [WaitType],
CAST ([W1]. [WaitS] AS DECIMAL( 14, 2 )) AS [Wait_S],
CAST ([W1]. [ResourceS] AS DECIMAL( 14, 2 )) AS [Resource_S],
CAST ([W1]. [SignalS] AS DECIMAL( 14, 2 )) AS [Signal_S],
[W1]. [WaitCount] AS [WaitCount],
CAST ([W1]. [Percentage] AS DECIMAL( 4, 2 )) AS [Percentage],
CAST (([W1]. [WaitS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1]. [ResourceS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1]. [SignalS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] &= [W1].[RowNum]
GROUP BY [W1]. [RowNum], [W1].[wait_type] , [W1] .[WaitS],
[W1]. [ResourceS], [W1].[SignalS] , [W1] .[WaitCount], [W1].[Percentage]
HAVING SUM ([W2] .[Percentage]) - [W1].[Percentage] & 95 ;
CPU 开销大的SQL
实例在无法连接前的一个诊断报告也和我们的检查结果一致
实例CPU使用率
活跃连接都在等CPU调度，spid已经复用到1.6K+
为了让实例快速恢复，首先要做的是适当放大调整CPU affinity mask，并且让用户应用做适当降级不要再次压垮实例
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 1 TO 2,6,9 TO 10,14 TO 16,19 TO 23
长期需要优化SQL逐步从根本上解决问题，当然也有的时候SQL的执行计划已经很好，只是业务的并发和RT达不到用户要求，这就需要考虑升级或做业务调整这个CASE通过类似几个SQL优化达到了不错的效果
set statistics profile on
set statistics io on
set statistics time on
select top 50 *** from *** where ***='***' order by id desc
set statistics profile off
set statistics io off
set statistics time off
执行计划的Bookmark可以进一步优化
注意一般的情况下都要加online参数避免锁表时间过长，尤其是这个CASE中1kw+的大表；但也要清楚相应代价，具体可以看下这篇
优化后的 SQL开销
set statistics profile on
set statistics io on
set statistics time on
select top 50 *** from *** where ***='***' order by id desc
set statistics profile off
set statistics io off
set statistics time off
优化后的SQL执行计划
逻辑读从5k降到6，CPU从31降到0 ms，且从执行计划来看已经最优
实例整体优化后CPU开销变化CPU开销明显已经下降
本文为云栖社区原创内容，未经允许不得转载，如需转载请发送邮件至yqeditor@list.；如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@ 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
用云栖社区APP，舒服~
【云栖快讯】阿里云免费套餐放量周：每天100个免费邀请码，更有坚果pro手机等你来抢&&
是将源站内容分发至全国所有的节点，缩短用户查看对象的延迟，提高用户访问网站的响应速度与网站的可用性，解决网络带宽...
全球领先的SaaS性能测试平台，具有强大的分布式压测能力，可模拟海量用户真实的业务场景，让应用性能问题无所遁形。
通过机器学习和数据建模发现潜在的入侵和攻击威胁，帮助客户建设自己的安全监控和防御体系，从而解决因网络攻击导致企业...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效率，降低 IT 成本...
Loading...2727人阅读
MSSql2005及新版本（57）
--检测CPU压力的一个方法是计算运行状态下的工作进程数量，
--通过执行如下的DMV查询可以得到这个信息
SELECT COUNT(*) AS workers_waiting_for_cpu,t2.scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state='RUNNABLE' AND&
t1.scheduler_address = t2.scheduler_address AND
&t2.scheduler_id&255
GROUP BY t2.scheduler_id
--也可以执行如下的查询得到工作进程在可运行状态下花费的时间
SELECT SUM(signal_wait_time_ms) FROM sys.dm_os_wait_stats
--下面是一个DMV查询，它可以用来找出每次执行中占用CPU最多的钱10为查询，
--也列出了SQL语句的查询计划及计划被执行的次数。如果一个查询大家虽高，
--但执行次数少，那也可以采纳。
SELECT TOP 10
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
--以上DMV只显示当前被缓存的查询合计统计信息
--为了找出工作负荷中运行最频繁的查询，就需要执行下面的DMV查询。
SELECT TOP 10 total_worker_time ,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 execution_count
--SQL Server在优化查询计划上花费的时间可以用下面的DMV查询
SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter='optimizations'
SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter='elapsed time'
SELECT TOP 10 plan_generation_num ,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
WHERE plan_generation_num&1
&ORDER BY execution_count
&--检查高速缓存内存
&DBCC memorystatus
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：4108981次
积分：40940
积分：40940
排名：第88名
原创：388篇
转载：590篇
评论：214条
(2)(7)(2)(9)(11)(9)(7)(19)(17)(22)(8)(1)(5)(3)(14)(4)(1)(7)(8)(1)(4)(4)(3)(2)(4)(3)(2)(5)(11)(8)(2)(11)(2)(14)(3)(4)(6)(9)(4)(9)(15)(4)(3)(8)(10)(9)(7)(6)(14)(3)(15)(37)(13)(14)(14)(14)(17)(14)(12)(9)(2)(22)(8)(7)(16)(16)(4)(30)(13)(11)(3)(2)(19)(12)(8)(8)(14)(2)(10)(32)(8)(16)(34)(10)(51)(63)(38)(11)(3)(1)