sql server 運維時CPU，記憶體，操作系統等信息查詢（用sql語句）

来源:https://www.cnblogs.com/yuanmengruoxi/archive/2018/08/23/9523380.html
-Advertisement-
Play Games

我們只要用到資料庫，一般會遇到資料庫運維方面的事情，需要我們尋找原因，有很多是關乎處理器(CPU)、記憶體（Memory）、磁碟（Disk）以及操作系統的，這時我們就需要查詢他們的一些設置和內容，下麵講的就是如何查詢它們的相關信息。1、（1）獲取資料庫伺服器CPU核數等信息(只適用於SQL 2005以 ...


我們只要用到資料庫，一般會遇到資料庫運維方面的事情，需要我們尋找原因，有很多是關乎處理器(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)


（2） 獲取資料庫伺服器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


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]

二：查看資料庫伺服器記憶體的信息
能否通過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


（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的數據此處只列了幾條常用的數據，如需全部數據，參考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這類數據）。

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

----------------------

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • CSV文件導入導mysql資料庫
    1.導入 導入實例 說明： 第一行就是導入文件； 第二行參看語法就會發現有兩個詞：replace 和 ignore 。replace和ignore關鍵詞控制對現有的唯一鍵記錄的重覆的處理。如果你指定replace，新行將代替有相同的唯一鍵值的現有行。如果你指定ignore，跳過有唯一鍵的現有行的重覆 ...
  • scala 語法（一）
    1）基本語法 變數：val var(可變變數）； 數據類型:Byte,Char,Int,Short,Long,String(字元),Float,Double,Boolean(true,flase); 列子： val a = 3; or val a:Int = 3;//不可變,不能重新賦值 var b ...
  • Java KeyStore 用命令生成keystore文件
    1.生成keyStore文件 在命令行下執行以下命令： Shell代碼 收藏代碼 其中 -genkey表示生成密鑰 -validity指定證書有效期，這裡是36000天 -alias指定別名，這裡是www.zlex.org -keyalg指定演算法，這裡是RSA -keystore指定存儲位置，這裡是 ...
  • mysql 開發進階篇系列 28 資料庫二進位包安裝(centos系統準備)
    1. centos 7安裝工作 對於mysql二進位安裝，我這裡在使用一臺新的centos系統。準備好VMware，Xftp-6.0, Xshell-6.0。在VMware中網路使用橋接模式，分配20G硬碟，1個處理器4個內核。進入centos安裝界面後選擇中文，時區上海，配置分區/boot 200 ...
  • JanusGraph 圖資料庫安裝小記 ——以 JanusGraph 0.3.0 為例
    由於近期項目中有使用圖數據的需求，經過對比，我們選擇嘗試使用 JanusGraph。本篇小記記錄了我們安裝 JanusGraph 以及需要一起集成的 Cassandra + Elasticsearch 的過程。 根據官網的介紹，JanusGraph 是一個可擴展的圖資料庫，針對存儲和查詢包含分佈在多... ...
  • 快速刷新視圖
    DECLARE @tableNames varchar(500)SET @tableNames='xxx,DB2, ' -- 關鍵此處填寫需要刷新視圖的資料庫名稱 DECLARE @i_start intSET @i_start=1; DECLARE @i_end intSET @i_end = C ...
  • [mysql] Can't read from messagefile
    系統：windows 重啟mysql服務出現 Server] Can't read from messagefile 等錯誤時候， 應先執行 mysqld --initialize-insecure 進項初始化操作 然後重啟服務成功 ...
  • mybatis使用oracle的sequence
    oracle資料庫創建SEQUENCE 從0開始，不迴圈，自增1的SEQUENCE。 以上，關於SEQUENCE就不贅述了。 mybatis的mapper配置 這樣後，就能獲取自增序列後插入資料庫了。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類