下面讲的就是如何查询它们的相关信息【www.915

作者: www.9159.com  发布:2019-09-23

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

--查看内存信息
SELECT CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024)  AS [物理内存大小]
    ,CAST(available_physical_memory_kb * 1.0 / 1024 / 1024
                       AS DECIMAL(8, 4)) AS [未使用的内存大小]
    ,CAST(( total_physical_memory_kb - available_physical_memory_kb ) * 1.0
    / 1024 / 1024 AS DECIMAL(8, 4))              AS [已使用的内存大小]
    ,CAST(system_cache_kb*1.0 / 1024/1024 AS DECIMAL(8, 4)) AS [系统缓存大小]
FROM  sys.dm_os_sys_memory

Query #13 SQL Server Error Log(FC)

Query #13 SQL Server Error Log(FC)

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

--查看CPU的型号
EXEC xp_instance_regread
  'HKEY_LOCAL_MACHINE',
  'HARDWAREDESCRIPTIONSystemCentralProcessor',
  'ProcessorNameString';

-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured

-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured

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

--查询每个磁盘的信息,包括已经使用的和未使用的信息
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;

SELECT is_enabled, [path], max_size, max_files

SELECT is_enabled, [path], max_size, max_files

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]

FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

二:查看数据库服务器内存的信息
能否通过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

-- Knowing this information is important for troubleshooting purposes

-- Knowing this information is important for troubleshooting purposes

(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
www.9159.com ,FROM #TempTable
WHERE Name = 'PhysicalMemory';
DROP TABLE #TempTable;
GO

-- Also shows you the location of other error and diagnostic log files

-- Also shows you the location of other error and diagnostic log files

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

 

 

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

Query #14 Cluster Node Properties

Query #14 Cluster Node Properties

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

-- Get information about your cluster nodes and their status

-- Get information about your cluster nodes and their status

四:查看操作系统信息

-- (if your database server is in a failover cluster)

-- (if your database server is in a failover cluster)

通过下面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

SELECT NodeName, status_description, is_current_owner

SELECT NodeName, status_description, is_current_owner

注意:

FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);

FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);

1:如上所示,临时表#Language的数据此处只列了几条常用的数据,如需全部数据,参考

   

   

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

-- Knowing which node owns the cluster resources is critical

-- Knowing which node owns the cluster resources is critical

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

-- Especially when you are installing Windows or SQL Server updates

-- Especially when you are installing Windows or SQL Server updates

www.9159.com 1

 

 

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

Query #15 AlwaysOn AG Cluster

Query #15 AlwaysOn AG Cluster

SELECT SERVERPROPERTY('MachineName')
select @@SERVERNAME
select HOST_NAME()

-- Get information about any AlwaysOn AG cluster this instance is a part of

-- Get information about any AlwaysOn AG cluster this instance is a part of

获取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

SELECT cluster_name, quorum_type_desc, quorum_state_desc

SELECT cluster_name, quorum_type_desc, quorum_state_desc

但是很多情况下由于安全问题是不允许使用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

FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);


   

   

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

-- You will see no results if your instance is not using AlwaysOn AGs

-- You will see no results if your instance is not using AlwaysOn AGs


-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters

-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters

 

--

--

 

 

Query #16 Hardware Info(FOR 2016)

Query #16 Hardware Info(FOR 2016)

-- Hardware information from SQL Server 2016

-- Hardware information from SQL Server 2016

SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],

SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],

cpu_count/hyperthread_ratio AS [Physical CPU Count],

cpu_count/hyperthread_ratio AS [Physical CPU Count],

physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],

physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],

committed_target_kb/1024 AS [Committed Target Memory (MB)],

committed_target_kb/1024 AS [Committed Target Memory (MB)],

max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],

max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],

sqlserver_start_time AS [SQL Server StartTime], virtual_machine_type_desc AS [Virtual Machine Type],

sqlserver_start_time AS [SQL Server StartTime], virtual_machine_type_desc AS [Virtual Machine Type],

softnuma_configuration_desc AS [Soft NUMA Configuration]

softnuma_configuration_desc AS [Soft NUMA Configuration]

FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

   

   

-- Gives you some good basic hardware information about your database server

-- Gives you some good basic hardware information about your database server

-- Cannot distinguish between HT and multi-core

-- Cannot distinguish between HT and multi-core

-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM

-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM

-- It merely indicates that you have a hypervisor running on your host

-- It merely indicates that you have a hypervisor running on your host

-- Soft NUMA configuration is a newcolumnforSQL Server 2016

-- Soft NUMA configuration is a newcolumnforSQL Server 2016

 

 

Query #17 System Manufacturer

Query #17 System Manufacturer

-- Get System Manufacturer and model number from SQL Server Error log

-- Get System Manufacturer and model number from SQL Server Error log

     EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';

     EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';

-- This can help you determine the capabilities and capacities of your database server

-- This can help you determine the capabilities and capacities of your database server

-- Can also be used to confirm if you are running in a VM

-- Can also be used to confirm if you are running in a VM

-- This query might take a few seconds if you have not recycled your error log recently

-- This query might take a few seconds if you have not recycled your error log recently

-- This query will returnno results if your error log has been recycled since the instance was started

-- This query will returnno results if your error log has been recycled since the instance was started

 

 

Query #18 Processor Description

Query #18 Processor Description

-- Get processor description from Windows Registry

-- Get processor description from Windows Registry

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWAREDESCRIPTIONSystemCentralProcessor', N'ProcessorNameString';

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWAREDESCRIPTIONSystemCentralProcessor', N'ProcessorNameString';

-- Gives you the model number and rated clock speed of your processor(s)

-- Gives you the model number and rated clock speed of your processor(s)

-- Your processors may be running at less than the rated clock speed due

-- Your processors may be running at less than the rated clock speed due

    -- to the Windows Power Plan or hardware power management

    -- to the Windows Power Plan or hardware power management

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

--

--

   

   

-- You can learn more about processor selection for SQL Server by following this link

-- You can learn more about processor selection for SQL Server by following this link

--

--

 

 

Query #19 BPE Configuration

Query #19 BPE Configuration

-- See if buffer pool extensions (BPE) is enabled

-- See if buffer pool extensions (BPE) is enabled

SELECT [path], state_description, current_size_in_kb,

SELECT [path], state_description, current_size_in_kb,

CAST(current_size_in_kb/1048576.0 ASDECIMAL(10,2)) AS [Size (GB)]

CAST(current_size_in_kb/1048576.0 ASDECIMAL(10,2)) AS [Size (GB)]

    FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);

    FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);

-- BPE is available in both Standlard Edition and Enterprise Edition

-- BPE is available in both Standlard Edition and Enterprise Edition

-- It is a more interesting feature for Standard Edition

-- It is a more interesting feature for Standard Edition

   

   

-- Buffer Pool Extension to SSDs in SQL Server 2014

-- Buffer Pool Extension to SSDs in SQL Server 2014

--

--

 

 

Query #20 BPE Usage

Query #20 BPE Usage

-- Look at buffer descriptors to see BPE usage by database

-- Look at buffer descriptors to see BPE usage by database

SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],

SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],

CAST(COUNT(*)/128.0 ASDECIMAL(10, 2)) AS [Buffer size(MB)],

CAST(COUNT(*)/128.0 ASDECIMAL(10, 2)) AS [Buffer size(MB)],

AVG(read_microsec) AS [AvgReadTime (microseconds)]

AVG(read_microsec) AS [AvgReadTime (microseconds)]

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

WHERE database_id <> 32767

WHERE database_id <> 32767

AND is_in_bpool_extension = 1

AND is_in_bpool_extension = 1

GROUPBY DB_NAME(database_id)

GROUPBY DB_NAME(database_id)

ORDERBY [Buffer size(MB)] DESCOPTION (RECOMPILE);

ORDERBY [Buffer size(MB)] DESCOPTION (RECOMPILE);

 

 

Query #21 Memory Dump Info

Query #21 Memory Dump Info

-- Get information on location, time and size of any memory dumps from SQL Server

-- Get information on location, time and size of any memory dumps from SQL Server

SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]

SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]

FROM sys.dm_server_memory_dumps WITH (NOLOCK)

FROM sys.dm_server_memory_dumps WITH (NOLOCK)

ORDERBY creation_time DESCOPTION (RECOMPILE);

ORDERBY creation_time DESCOPTION (RECOMPILE);

   

   

-- This will not return any rows if you have

-- This will not return any rows if you have

-- not had any memory dumps (which is a good thing)

-- not had any memory dumps (which is a good thing)

 

 

Query #22 Database Filename and Paths

Query #22 Database Filename and Paths

   

   

  -- File names and paths for all user and system databases on instance

  -- File names and paths for all user and system databases on instance

   SELECT DB_NAME([database_id]) AS [Database Name], 

   SELECT DB_NAME([database_id]) AS [Database Name], 

          [file_id], name, physical_name, [type_desc], state_desc,

          [file_id], name, physical_name, [type_desc], state_desc,

          is_percent_growth, growth,

          is_percent_growth, growth,

          CONVERT(bigint, growth/128.0) AS [Growth in MB], 

          CONVERT(bigint, growth/128.0) AS [Growth in MB], 

          CONVERT(bigint, size/128.0) AS [Total Size in MB]

          CONVERT(bigint, size/128.0) AS [Total Size in MB]

   FROM sys.master_files WITH (NOLOCK)

   FROM sys.master_files WITH (NOLOCK)

   ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

   ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

   -- Things to look at:

   -- Things to look at:

   -- Are data files and log files on different drives?

   -- Are data files and log files on different drives?

   -- Is everything on the C: drive?

   -- Is everything on the C: drive?

   -- Is TempDB on dedicated drives?

   -- Is TempDB on dedicated drives?

   -- Is there only one TempDB data file?

   -- Is there only one TempDB data file?

   -- Are all of the TempDB data files the same size?

   -- Are all of the TempDB data files the same size?

   -- Are there multiple data files for user databases?

   -- Are there multiple data files for user databases?

   -- Is percent growth enabled for any files (which is bad)?

   -- Is percent growth enabled for any files (which is bad)?

 

 

Query #23 Volume Info

Query #23 Volume Info

-- Volume info for all LUNS that have database files on the current instance

-- Volume info for all LUNS that have database files on the current instance

SELECTDISTINCT vs.volume_mount_point, vs.file_system_type,

SELECTDISTINCT vs.volume_mount_point, vs.file_system_type,

vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],

vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],

CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],

CAST(CAST(vs.available_bytes ASFLOAT)/ CAST(vs.total_bytes ASFLOAT) ASDECIMAL(18,2)) * 100 AS [SpaceFree %]

CAST(CAST(vs.available_bytes ASFLOAT)/ CAST(vs.total_bytes ASFLOAT) ASDECIMAL(18,2)) * 100 AS [SpaceFree %]

FROM sys.master_files AS f WITH (NOLOCK)

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

ORDERBY vs.volume_mount_point OPTION (RECOMPILE);

ORDERBY vs.volume_mount_point OPTION (RECOMPILE);

   

   

-- Shows you the total and free space on the LUNs where you have database files

-- Shows you the total and free space on the LUNs where you have database files

-- Being low onfreespace can negatively affect performance

-- Being low onfreespace can negatively affect performance

 

 

Query #24 Drive Level Latency

Query #24 Drive Level Latency

   

   

 -- Drive level latency information

 -- Drive level latency information

SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point], 

SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point], 

      CASE 

      CASE 

          WHEN num_of_reads = 0 THEN 0 

          WHEN num_of_reads = 0 THEN 0 

          ELSE (io_stall_read_ms/num_of_reads) 

          ELSE (io_stall_read_ms/num_of_reads) 

      END AS [Read Latency],

      END AS [Read Latency],

      CASE 

      CASE 

          WHEN io_stall_write_ms = 0 THEN 0 

          WHEN io_stall_write_ms = 0 THEN 0 

          ELSE (io_stall_write_ms/num_of_writes) 

          ELSE (io_stall_write_ms/num_of_writes) 

      END AS [Write Latency],

      END AS [Write Latency],

      CASE 

      CASE 

          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 

          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 

          ELSE (io_stall/(num_of_reads + num_of_writes)) 

          ELSE (io_stall/(num_of_reads + num_of_writes)) 

      END AS [Overall Latency],

      END AS [Overall Latency],

      CASE 

      CASE 

          WHEN num_of_reads = 0 THEN 0 

          WHEN num_of_reads = 0 THEN 0 

          ELSE (num_of_bytes_read/num_of_reads) 

          ELSE (num_of_bytes_read/num_of_reads) 

      END AS [Avg Bytes/Read],

      END AS [Avg Bytes/Read],

      CASE 

      CASE 

          WHEN io_stall_write_ms = 0 THEN 0 

          WHEN io_stall_write_ms = 0 THEN 0 

          ELSE (num_of_bytes_written/num_of_writes) 

          ELSE (num_of_bytes_written/num_of_writes) 

      END AS [Avg Bytes/Write],

      END AS [Avg Bytes/Write],

      CASE 

      CASE 

          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 

          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 

          ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 

          ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 

      END AS [Avg Bytes/Transfer]

      END AS [Avg Bytes/Transfer]

  FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,

  FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,

               SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,

               SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,

               SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,

               SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,

               SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point 

               SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point 

        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

        INNER JOIN sys.master_files AS mf WITH (NOLOCK)

        INNER JOIN sys.master_files AS mf WITH (NOLOCK)

        ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

        ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs 

        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs 

        GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab

        GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab

  ORDER BY [Overall Latency] OPTION (RECOMPILE);

  ORDER BY [Overall Latency] OPTION (RECOMPILE);

  -- Shows you the drive-level latency for reads and writes, in milliseconds

  -- Shows you the drive-level latency for reads and writes, in milliseconds

  -- Latency above 20-25ms is usually a problem

  -- Latency above 20-25ms is usually a problem

 

 

Query #25 IO Stalls by File

Query #25 IO Stalls by File

-- Calculates average stalls per read, per write, and per total input/output for each database file

-- Calculates average stalls per read, per write, and per total input/output for each database file

SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) ASNUMERIC(10,1)) AS [avg_read_stall_ms],

SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) ASNUMERIC(10,1)) AS [avg_read_stall_ms],

CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) ASNUMERIC(10,1)) AS [avg_write_stall_ms],

CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) ASNUMERIC(10,1)) AS [avg_write_stall_ms],

CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) ASNUMERIC(10,1)) AS [avg_io_stall_ms],

CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) ASNUMERIC(10,1)) AS [avg_io_stall_ms],

CONVERT(DECIMAL(18,2), mf.size/128.0) AS [FileSize (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,

CONVERT(DECIMAL(18,2), mf.size/128.0) AS [FileSize (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,

fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],

fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],

io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]

io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]

FROM sys.dm_io_virtual_file_stats(null,null) AS fs

FROM sys.dm_io_virtual_file_stats(null,null) AS fs

INNERJOIN sys.master_files AS mf WITH (NOLOCK)

INNERJOIN sys.master_files AS mf WITH (NOLOCK)

ON fs.database_id = mf.database_id

ON fs.database_id = mf.database_id

AND fs.[file_id] = mf.[file_id]

AND fs.[file_id] = mf.[file_id]

ORDERBY avg_io_stall_ms DESCOPTION (RECOMPILE);

ORDERBY avg_io_stall_ms DESCOPTION (RECOMPILE);

   

   

-- Helps determine which database files on the entire instance have the most I/O bottlenecks

-- Helps determine which database files on the entire instance have the most I/O bottlenecks

-- This can help you decide whether certain LUNs are overloaded and whether you might

-- This can help you decide whether certain LUNs are overloaded and whether you might

-- want to move some files to a different location or perhaps improve your I/O performance

-- want to move some files to a different location or perhaps improve your I/O performance

 

 

Query #25 IO Warning

Query #25 IO Warning

   

   

-- Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs

-- Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs

CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

 

 

       INSERT INTO #IOWarningResults 

       INSERT INTO #IOWarningResults 

       EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

       EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

 

 

       INSERT INTO #IOWarningResults 

       INSERT INTO #IOWarningResults 

       EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';

       EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';

 

 

       INSERT INTO #IOWarningResults 

       INSERT INTO #IOWarningResults 

       EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';

       EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';

 

 

       INSERT INTO #IOWarningResults 

       INSERT INTO #IOWarningResults 

       EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';

       EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';

 

 

       INSERT INTO #IOWarningResults 

       INSERT INTO #IOWarningResults 

       EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';

       EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';

 

 

   SELECT LogDate, ProcessInfo, LogText

   SELECT LogDate, ProcessInfo, LogText

   FROM #IOWarningResults

   FROM #IOWarningResults

   ORDER BY LogDate DESC;

   ORDER BY LogDate DESC;

 

 

   DROP TABLE #IOWarningResults; 

   DROP TABLE #IOWarningResults; 

   -- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of

   -- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of

   -- poor I/O performance (which might have many different causes)

   -- poor I/O performance (which might have many different causes)

 

 

   -- Diagnostics in SQL Server help detect stalled and stuck I/O operations

   -- Diagnostics in SQL Server help detect stalled and stuck I/O operations

   -- 

   -- 

本文由9159.com发布于www.9159.com,转载请注明出处:下面讲的就是如何查询它们的相关信息【www.915

关键词: