SqlServer如何通過SQL語句獲取處理器、內(nèi)存以及操作系統(tǒng)相關(guān)信息
在SQL SERVER中如何通過SQL語句獲取服務(wù)器硬件和系統(tǒng)信息呢？下面介紹一下如何通過SQL語句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息。如有不足和遺漏，敬請(qǐng)補(bǔ)充。謝謝！
一：查看數(shù)據(jù)庫服務(wù)器CPU的信息
---SQL 1:獲取數(shù)據(jù)庫服務(wù)器的CPU型號(hào)
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString';
---SQL 2:獲取數(shù)據(jù)庫服務(wù)器CPU核數(shù)等信息(只適用于SQL 2005以及以上版本數(shù)據(jù)庫)
/************************************************************************************* --cpu_count :指定系統(tǒng)中的邏輯 CPU 數(shù) --hyperthread_ratio :指定一個(gè)物理處理器包公開的邏輯內(nèi)核數(shù)與物理內(nèi)核數(shù)的比.虛擬機(jī) -- 中可以表示每個(gè)虛擬插槽的核數(shù)。虛擬中[Physical CPU Count]其實(shí) -- 表示虛擬插槽數(shù) *************************************************************************************/ 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:獲取數(shù)據(jù)庫服務(wù)器CPU核數(shù)(適用于所有版本)
CREATE TABLE #TempTable
(
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
);
INSERT INTO #TempTable
EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount
FROM #TempTable
WHERE Name = 'ProcessorCount';
DROP TABLE #TempTable;
GO
---SQL 4：在老外博客中看到一個(gè)計(jì)算CPU相關(guān)信息的SQL，不過虛擬機(jī)計(jì)算有點(diǎn)小問題，我修改了一下。
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]
AS (
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [number_of_cores_per_cpu]
,CASE
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]
---查看虛擬機(jī)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]
AS (
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]
二：查看數(shù)據(jù)庫服務(wù)器內(nèi)存的信息
能否通過SQL語句獲取服務(wù)器的物理內(nèi)存大?。績?nèi)存條型號(hào)？虛擬內(nèi)存大?。績?nèi)存使用情況？ 目前我所知道的只能通過SQL語句獲取服務(wù)器物理內(nèi)存大小，內(nèi)存的使用情況。 至于內(nèi)存條型號(hào)，系統(tǒng)虛擬內(nèi)存大小，暫時(shí)好像還無法通過SQL語句獲取。
查看服務(wù)器的物理內(nèi)存情況
如下所示，從sys.dm_os_sys_info里面獲取的physical_memory_in_bytes 或physical_memory_kb 的值總是低于實(shí)際物理內(nèi)存。暫時(shí)不清楚具體原因（還未查到相關(guān)資料），所以計(jì)算大小有出入，要獲取實(shí)際的物理內(nèi)存，就必須借助CEILING函數(shù)。
--SQL 1：獲取數(shù)據(jù)庫服務(wù)器物理內(nèi)存數(shù)(適用于所有版本)
CREATE TABLE #TempTable
(
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
);
INSERT INTO #TempTable
EXEC xp_msver;
SELECT Internal_Value/1024 AS PhysicalMemory
FROM #TempTable
WHERE Name = 'PhysicalMemory';
DROP TABLE #TempTable;
GO
---SQL 2：適用于SQL Server 2005、SQL Server 2008
SELECT CEILING(physical_memory_in_bytes*1.0/1024/1024/1024) AS [Physical Memory Size] FROM sys.dm_os_sys_info OPTION (RECOMPILE) SELECT physical_memory_in_bytes*1.0/1024/1024/1024 , 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/1024/1024) AS [Physical Memory Size] FROM sys.dm_os_sys_info OPTION (RECOMPILE);
---SQL 4：適用于SQL Server 2008以及以上的版本:查看物理內(nèi)存大小，已經(jīng)使用的物理內(nèi)存以及還剩下的物理內(nèi)存。
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 / 1024/1024 AS DECIMAL(8, 4)) AS [System Cache Size]
FROM sys.dm_os_sys_memory
三：查看數(shù)據(jù)庫服務(wù)器硬盤的信息
如下所示，我們可以通過下面腳本獲取服務(wù)器的各個(gè)磁盤的使用情況。但是無法獲取磁盤的型號(hào)、轉(zhuǎn)速之類的信息。
SET NOCOUNT ON DECLARE @Result INT; DECLARE @objectInfo INT; DECLARE @DriveInfo CHAR(1); DECLARE @TotalSize VARCHAR(20); DECLARE @OutDrive INT; DECLARE @UnitMB BIGINT; DECLARE @FreeRat FLOAT; SET @UnitMB = 1048576; --創(chuàng)建臨時(shí)表保存服務(wù)器磁盤容量信息 CREATE TABLE #DiskCapacity ( [DiskCD] CHAR(1) , FreeSize INT , TotalSize INT ); INSERT #DiskCapacity([DiskCD], FreeSize ) EXEC master.dbo.xp_fixeddrives; 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 FOR SELECT DiskCD FROM #DiskCapacity ORDER by DiskCD OPEN CR_DiskInfo; FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo WHILE @@FETCH_STATUS=0 BEGIN 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 END CLOSE CR_DiskInfo DEALLOCATE CR_DiskInfo; 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/1024,6,2) AS [Total Size(GB)] , STR((TotalSize - FreeSize)*1.0/1024,6,2) AS [Used Space(GB)] , STR(FreeSize*1.0/1024,6,2) 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 #DiskCapacity; DROP TABLE #DiskCapacity;
四：查看操作系統(tǒng)信息
通過下面SQL語句，我們可以查看操作系統(tǒng)版本、補(bǔ)丁、語言等信息
--創(chuàng)建臨時(shí)表保存語言版本信息
CREATE TABLE #Language
(
[LanguageDtl] NVARCHAR(64) ,
[os_language_version] INT
);
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' ,5124;
WITH SystemVersion(SystemInfo,ReleaseNo)
AS
(
SELECT 'Windows 10' ,
'10.0*'
UNION ALL
SELECT 'Windows Server 2016 Technical Preview' ,
'10.0*'
UNION ALL
SELECT 'Windows 8.1' ,
'6.3*'
UNION ALL
SELECT 'Windows Server 2012 R2' ,
'6.3'
UNION ALL
SELECT 'Windows 8' ,
'6.2'
UNION ALL
SELECT 'Windows Server 2012' ,
'6.2'
UNION ALL
SELECT 'Windows 7' ,
'6.1'
UNION ALL
SELECT 'Windows Server 2008 R2' ,
'6.1'
UNION ALL
SELECT 'Windows Server 2008' ,
'6.0'
UNION ALL
SELECT 'Windows Vista' ,
'6.0'
UNION ALL
SELECT 'Windows Server 2003 R2' ,
'5.2'
UNION ALL
SELECT 'Windows Server 2003' ,
'5.2'
UNION ALL
SELECT 'Windows XP 64-Bit Edition' ,
'5.2'
UNION ALL
SELECT 'Windows XP' ,
'5.1'
UNION ALL
SELECT 'Windows 2000' ,
'5.0'
)
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_version;
DROP TABLE #Language;
注意：
1：如上所示，臨時(shí)表#Language的數(shù)據(jù)此處只列了幾條常用的數(shù)據(jù)，如需全部數(shù)據(jù)，參考https://msdn.microsoft.com/zh-CN/goglobal/bb964664.aspx自行補(bǔ)充。
2：操作系統(tǒng)的版本信息的數(shù)據(jù)來源于https://msdn.microsoft.com/zh-CN/library/ms724832(VS.85).aspx
有可能出現(xiàn)不同操作系統(tǒng)具有相同Version number值，例如Windows 7 和Windows Server 2008 R2的Version numberd都為6.1。導(dǎo)致下面查詢結(jié)果出現(xiàn)多條記錄（如下所示）。一般要酌情判斷（如果生產(chǎn)服務(wù)器都為Windows服務(wù)器，可以剔除Windows XP、Windows 7這類數(shù)據(jù)）。
ps:使用SQL語句獲得服務(wù)器名稱和IP地址
使用SQL語句獲得服務(wù)器名稱和IP地址 獲取服務(wù)器名稱：
SELECT SERVERPROPERTY('MachineName')
select @@SERVERNAME
select HOST_NAME()
獲取IP地址可以使用xp_cmdshell執(zhí)行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
go
begin
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 地址%'--這里需要注意一下，系統(tǒng)不同這里的匹配值就不同
if @ipline is not null
begin
set @pos = charindex(':',@ipline,1);
set @ip = rtrim(ltrim(substring(@ipline ,
@pos + 1 ,
len(@ipline) - @pos)))
end
select distinct(rtrim(ltrim(substring(@ipline ,
@pos + 1 ,
len(@ipline) - @pos)))) as ipaddress from #temp
drop table #temp
set nocount off
end
go
但是很多情況下由于安全問題是不允許使用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
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信