一个SQL语句的优化,操作系统等信息查询

我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(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)

最早的写法:

今天需要在本地建个数据库,就下载安装sql,第一次弄,遇到了一些问题,环境添加到
sql的bin目录,要用管理员命令运行cm,cd到sql/bin的目录,输入 net start mysql

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

WITH T AS
(SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag FROM YM  WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%'),
D AS (SELECT Code,province,city  FROM Adds)
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM T INNER JOIN TB AS A ON A.DR_203=T.Code WHERE T.Flag=0
 UNION ALL
 SELECT D.province,D.city FROM T INNER JOIN D ON D.Code=T.Code WHERE T.Flag=1
 UNION ALL
 SELECT '' AS province,'' AS city FROM T WHERE Code IS NULL OR Code=N'') AS S 
GROUP BY province,city;

运行sql,可能会出现密码错误,到配置文件,在[mysqld]加入skip-grant-tables 
可以不需要密码,mysql -u root -p 登录,密码空即可

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

SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag into #T FROM YM WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%';
SELECT Code,province,city into #D  FROM Adds;
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM #T INNER JOIN TB AS A ON A.DR_203=#T.Code WHERE #T.Flag=0
UNION ALL
SELECT #D.province,#D.city FROM #T INNER JOIN #D ON #D.Code=#T.Code WHERE #T.Flag=1
UNION ALL
SELECT '' AS province,'' AS city FROM #T WHERE Code IS NULL OR Code=N'') AS S GROUP BY province,city;
DROP table #T;
DROP table #D;

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

新的写法比原始写法性能高出太多(原语句执行会超时),最大的原因是对with语句理解有误!!!

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

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

发表评论

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

标签:
网站地图xml地图