在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 EXEC xp_msver; SELECT Internal_Value AS VirtualCPUCount FROM #TempTable WHERE Name = 'ProcessorCount'; DROP TABLE #TempTable; GO
---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] 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] ---查看虛擬機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]
二:查看數據庫服務器內存的信息
能否通過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 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以及以上的版本:查看物理內存大小,已經使用的物理內存以及還剩下的物理內存。
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
三:查看數據庫服務器硬盤的信息
如下所示,我們可以通過下面腳本獲取服務器的各個磁盤的使用情況。但是無法獲取磁盤的型號、轉速之類的信息。
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; --創建臨時表保存服務器磁盤容量信息 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;
四:查看操作系統信息
通過下面SQL語句,我們可以查看操作系統版本、補丁、語言等信息
--創建臨時表保存語言版本信息 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:如上所示,臨時表#Language的數據此處只列了幾條常用的數據,如需全部數據,參考https://msdn.microsoft.com/zh-CN/goglobal/bb964664.aspx自行補充。
2:操作系統的版本信息的數據來源於https://msdn.microsoft.com/zh-CN/library/ms724832(VS.85).aspx
有可能出現不同操作系統具有相同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 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 地址%'--這裡需要注意一下,系統不同這裡的匹配值就不同 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