人人都是 DBA（IX）服务器信息收集脚本汇编

SQL Server 是什么时候安装的

SELECT @@SERVERNAME AS [Server Name]
    ,create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE NAME = N‘NT AUTHORITY\SYSTEM‘
    OR NAME = N‘NT AUTHORITY\NETWORK SERVICE‘
OPTION (RECOMPILE);

服务器主机名是什么

SELECT SERVERPROPERTY(‘MachineName‘) AS [MachineName]
    ,SERVERPROPERTY(‘ServerName‘) AS [ServerName]
    ,SERVERPROPERTY(‘InstanceName‘) AS [Instance]
    ,SERVERPROPERTY(‘IsClustered‘) AS [IsClustered]
    ,SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS‘) AS [ComputerNamePhysicalNetBIOS]
    ,SERVERPROPERTY(‘Edition‘) AS [Edition]
    ,SERVERPROPERTY(‘ProductLevel‘) AS [ProductLevel]
    ,SERVERPROPERTY(‘ProductVersion‘) AS [ProductVersion]
    ,SERVERPROPERTY(‘ProcessID‘) AS [ProcessID]
    ,SERVERPROPERTY(‘Collation‘) AS [Collation]
    ,SERVERPROPERTY(‘IsFullTextInstalled‘) AS [IsFullTextInstalled]
    ,SERVERPROPERTY(‘IsIntegratedSecurityOnly‘) AS [IsIntegratedSecurityOnly];

硬件服务器是谁制造的

EXEC xp_readerrorlog 0, 1, N‘Manufacturer‘; 

服务器硬件是什么配置

SELECT cpu_count AS [Logical CPU Count]
    ,scheduler_count
    ,hyperthread_ratio AS [Hyperthread Ratio]
    ,cpu_count / hyperthread_ratio AS [Physical CPU Count]
    ,physical_memory_kb / 1024 AS [Physical Memory (MB)]
    ,committed_kb / 1024 AS [Committed Memory (MB)]
    ,committed_target_kb / 1024 AS [Committed Target Memory (MB)]
    ,max_workers_count AS [Max Workers Count]
    ,affinity_type_desc AS [Affinity Type]
    ,sqlserver_start_time AS [SQL Server Start Time]
    ,virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK)
OPTION (RECOMPILE);

SQL Server 2008 R2 可以使用：

SELECT cpu_count AS [Logical CPU Count]
    ,hyperthread_ratio AS [Hyperthread Ratio]
    ,cpu_count / hyperthread_ratio AS [Physical CPU Count]
    ,physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)]
    ,sqlserver_start_time
    ,affinity_type_desc
FROM sys.dm_os_sys_info WITH (NOLOCK)
OPTION (RECOMPILE);

服务器的 CPU 有几个核

EXEC sys.xp_readerrorlog 0
    ,1
    ,N‘detected‘
    ,N‘socket‘;

服务器的 CPU 是什么型号

EXEC xp_instance_regread N‘HKEY_LOCAL_MACHINE‘
    ,N‘HARDWARE\DESCRIPTION\System\CentralProcessor\0‘
    ,N‘ProcessorNameString‘;

SQL Server 启动了哪些服务

SELECT servicename
    ,process_id
    ,startup_type_desc
    ,status_desc
    ,last_startup_time
    ,service_account
    ,is_clustered
    ,cluster_nodename
    ,[filename]
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);

查看指定服务运行状态

EXEC master.dbo.xp_servicecontrol ‘QUERYSTATE‘
    ,‘MSSQLServer‘

EXEC master.dbo.xp_servicecontrol ‘QUERYSTATE‘
    ,‘SQLServerAgent‘

EXEC master.dbo.xp_servicecontrol ‘QUERYSTATE‘
    ,‘SQLBrowser‘

SQL Server 的 IP 地址信息

SELECT CONNECTIONPROPERTY(‘net_transport‘) AS net_transport
    ,CONNECTIONPROPERTY(‘protocol_type‘) AS protocol_type
    ,CONNECTIONPROPERTY(‘auth_scheme‘) AS auth_scheme
    ,CONNECTIONPROPERTY(‘local_net_address‘) AS local_net_address
    ,CONNECTIONPROPERTY(‘local_tcp_port‘) AS local_tcp_port
    ,CONNECTIONPROPERTY(‘client_net_address‘) AS client_net_address;

SELECT SERVERPROPERTY(N‘MachineName‘) AS MachineName;

SQL Server 监听了哪些 TCP 端口

SELECT listener_id
    ,ip_address
    ,is_ipv4
    ,port
    ,type_desc
    ,state_desc
    ,start_time
FROM sys.dm_tcp_listener_states WITH (NOLOCK)
ORDER BY listener_id
OPTION (RECOMPILE);

SQL Server 配置项信息

SELECT [name]
    ,value
    ,value_in_use
    ,minimum
    ,maximum
    ,[description]
    ,is_dynamic
    ,is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY [name]
OPTION (RECOMPILE);

通常会关注：

• backup compression default ：通常为 1；
• cost threshold for parallelism 
• clr enabled 
• lightweight pooling ：通常为 0；
• max degree of parallelism
• max server memory (MB)
• optimize for ad hoc workloads ：通常为 1；
• priority boost ：通常为 0；

当前连接的 Session 有多少

SELECT login_name
    ,[program_name]
    ,COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
    ,[program_name]
ORDER BY COUNT(session_id) DESC
OPTION (RECOMPILE);

每个数据库上的 Session 数量是多少

SELECT DB_NAME(dbid) AS DBName
    ,COUNT(dbid) AS NumberOfConnections
    ,loginame AS LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid
    ,loginame
ORDER BY 1, 2, 3;

按主机查询 Session 数量

CREATE TABLE #tbl (
    spid INT
    ,ecid INT
    ,[status] VARCHAR(50)
    ,loginame VARCHAR(255)
    ,hostname VARCHAR(255)
    ,blk VARCHAR(50)
    ,dbname VARCHAR(255)
    ,cmd VARCHAR(255)
    ,request_id VARCHAR(255)
    )
GO

INSERT INTO #tbl
EXEC sp_who;

SELECT COUNT(0) AS CountByHostName
    ,hostname
FROM #tbl
GROUP BY hostname;

SELECT COUNT(0) AS CountByDBName
    ,dbname
FROM #tbl
GROUP BY dbname;

DROP TABLE #tbl
GO

哪个 IP 地址上建立的连接最多

SELECT ec.client_net_address
    ,es.[program_name]
    ,es.[host_name]
    ,es.login_name
    ,COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) ON es.session_id = ec.session_id
GROUP BY ec.client_net_address
    ,es.[program_name]
    ,es.[host_name]
    ,es.login_name
ORDER BY ec.client_net_address
    ,es.[program_name]
OPTION (RECOMPILE);

查看 Table 的基本属性

SELECT [name]
    ,create_date
    ,lock_on_bulk_load
    ,is_replicated
    ,has_replication_filter
    ,is_tracked_by_cdc
    ,lock_escalation_desc
FROM sys.tables WITH (NOLOCK)
ORDER BY [name]
OPTION (RECOMPILE);

查看 Table 的数据行数

SELECT OBJECT_NAME(object_id) AS [ObjectName]
    ,SUM(Rows) AS [RowCount]
    ,data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
    AND OBJECT_NAME(object_id) NOT LIKE N‘sys%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘queue_%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘filestream_tombstone%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘fulltext%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘ifts_comp_fragment%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘filetable_updates%‘
    AND OBJECT_NAME(object_id) NOT LIKE N‘xml_index_nodes%‘
GROUP BY object_id
    ,data_compression_desc
ORDER BY SUM(Rows) DESC
OPTION (RECOMPILE);

《人人都是 DBA》系列文章索引：

本系列文章《》由  发表自，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。