SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息

这篇文章主要介绍了SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息的相关资料,需要的朋友可以参考下

在SQL SERVER中如何通过SQL语句获取服务器硬件和系统信息呢?下面介绍一下如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息。如有不足和遗漏,敬请补充。谢谢!

一:查看数据库服务器CPU的信息

—SQL 1:获取数据库服务器的CPU型号

?

1

2

3

4

EXEC xp_instance_regread

'HKEY_LOCAL_MACHINE',

'HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0',

'ProcessorNameString';

—SQL 2:获取数据库服务器CPU核数等信息(只适用于SQL 2005以及以上版本数据库)

?

1

2

3

4

5

6

7

8

9

10

/*************************************************************************************

–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核数(适用于所有版本)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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,不过虚拟机计算有点小问题,我修改了一下。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

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函数。

SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息

–SQL 1:获取数据库服务器物理内存数(适用于所有版本)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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

?

1

2

3

4

5

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

?

1

2

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以及以上的版本:查看物理内存大小,已经使用的物理内存以及还剩下的物理内存。

?

1

2

3

4

5

6

7

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

三:查看数据库服务器硬盘的信息

如下所示,我们可以通过下面脚本获取服务器的各个磁盘的使用情况。但是无法获取磁盘的型号、转速之类的信息。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

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语句,我们可以查看操作系统版本、补丁、语言等信息

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

–创建临时表保存语言版本信息

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这类数据)。

SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息

ps:使用SQL语句获得服务器名称和IP地址

使用SQL语句获得服务器名称和IP地址 获取服务器名称:

?

1

2

3

SELECT SERVERPROPERTY('MachineName')

select @@SERVERNAME

select HOST_NAME()

获取IP地址可以使用xp_cmdshell执行ipconfig命令:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

–开启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 :

?

1

2

3

4

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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26891/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-28 8:22:41
下一篇 2022-09-28 8:22:53

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!