我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,
下面讲的就是如何查询它们的相关信息。
1、(1)获取数据库服务器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)

–缓冲区池消耗内存总量
SELECTSUM(multi_pages_kb +
virtual_memory_reserved_kb +
shared_memory_reserved_kb
+awe_allocated_kb) AS[Used by
BPool,kb]
FROM sys.dm_os_memory_clerks
WHERE type=’MEMORYCLERK_SQLBUFFERPOOL’

下面介绍一下关于mssql
server中使用递归的sql查询语哦,有需要的朋友可以参考一下。

(2) 获取数据库服务器CPU核数(适用于所有版本

–查询确认哪些内部组件窃取了缓冲区池中大部分的页面
SELECTTOP10 type,
SUM(single_pages_kb) AS
stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(single_pages_kb) DESC

张表的结构如下图所示

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

–缓冲区池外分配了内存的内部组件
SELECTTOP10 type,
SUM(multi_pages_kb) AS
menory_allocated_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(multi_pages_kb) DESC

当前需要实现的功能:通过Number的值为67来获取当前的节点ID、父节点ID递归实现SQL语句:

2:在老外博客中看到一个计算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]

 代码如下复制代码 with znieyu as(select c.Id,c.FatherId,0 as lv1 from ColumnTable c where c.Number=67union allselect c.Id,c.FatherId,lv1-1 from znieyu zinner join ColumnTable c on z.FatherId=c.Id)select znieyu.Id ,znieyu.FatherId,znieyu.lv1 as lv from znieyu

二:查看数据库服务器内存的信息
能否通过SQL语句获取服务器的物理内存大小?内存条型号?虚拟内存大小?内存使用情况?
目前我所知道的只能通过SQL语句获取服务器物理内存大小,内存的使用情况。
至于内存条型号,
系统虚拟内存大小,暂时好像还无法通过SQL语句获取。
查看服务器的物理内存情况
如下所示,从sys.dm_os_sys_info里面获取的physical_memory_in_bytes
或physical_memory_kb 的值总是低于实际物理内存。
暂时不清楚具体原因(还未查到相关资料),所以计算大小有出入,要获取实际的物理内存,就必须借助CEILING函数。

实现的效果:

1、(1)适用于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

满足条件67ID为6的递归–6–2(LV0)–1(LV-1)–0(LV-2)满足条件67ID为8的递归–8–5(LV0)–2(LV-1)–1(LV-2)–0(LV-3)

(2)获取数据库服务器物理内存数(适用于所有版本)
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

(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);

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

如下所示,我们可以通过下面脚本获取服务器的各个磁盘的使用情况。但是无法获取磁盘的型号、转速之类的信息。
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的数据此处只列了几条常用的数据,如需全部数据,参考

2:操作系统的版本信息的数据来源于

有可能出现不同操作系统具有相同Version number值,例如Windows 7 和Windows
Server 2008 R2的Version
numberd都为6.1。导致下面查询结果出现多条记录(如下所示)。一般要酌情判断(如果生产服务器都为Windows服务器,可以剔除Windows
XP、Windows 7这类数据)。

图片 1

使用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


上面是通过sql语句查询,可以通过(这里是2014版)点击数据库-属性–可以看到很多信息,但是看不到物理内存和虚拟内存,一般在属性-内存-最大内存设置:物理内存的80%


 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图