首页 > 代码库 > 人人都是 DBA(IX)服务器信息收集脚本汇编
人人都是 DBA(IX)服务器信息收集脚本汇编
什么?有个 SQL 执行了 8 秒!
哪里出了问题?臣妾不知道啊,得找 DBA 啊。
DBA 人呢?离职了!!擦!!!
程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA"。
索引
- SQL Server 安装的是什么版本
- Windows 操作系统是什么版本
- SQL Server 是什么时候安装的
- 服务器主机名是什么
- 硬件服务器是谁制造的
- 服务器硬件是什么配置
- 服务器的 CPU 有几个核
- 服务器的 CPU 是什么型号
- SQL Server 启动了哪些服务
- 查看指定服务运行状态
- SQL Server 的 IP 地址信息
- SQL Server 监听了哪些 TCP 端口
- SQL Server 配置项信息
- 当前连接的 Session 有多少
- 每个数据库上的 Session 数量是多少
- 按主机查询 Session 数量
- 哪个 IP 地址上建立的连接最多
- 查看 Table 的基本属性
- 查看 Table 的数据行数
SQL Server 安装的是什么版本
SELECT @@VERSION AS [SQL Server Version];
版本号说明:
缩略语说明:
比如,下面查到的版本号:
- Build Version : 11.00.3153
- File Version : 2011.110.3153.0
可以对应到 2977326 MS14-044: Description of the security update for SQL Server 2012 Service Pack 1 (GDR) August 12, 2014
Windows 操作系统是什么版本
SELECT windows_release ,windows_service_pack_level ,windows_sku ,os_language_versionFROM sys.dm_os_windows_info WITH (NOLOCK)OPTION (RECOMPILE);
其中 windows_release 中的版本号代表着:
其中 windows_sku 代表着:
SQL Server 2008 中没有 sys.dm_os_windows_info,可以使用 @@VERSION 来看了。
SELECT @@VERSION AS [SQL Server Version];
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_descFROM 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_timeFROM sys.dm_tcp_listener_states WITH (NOLOCK)ORDER BY listener_idOPTION (RECOMPILE);
SQL Server 配置项信息
SELECT [name] ,value ,value_in_use ,minimum ,maximum ,[description] ,is_dynamic ,is_advancedFROM 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) DESCOPTION (RECOMPILE);
每个数据库上的 Session 数量是多少
SELECT DB_NAME(dbid) AS DBName ,COUNT(dbid) AS NumberOfConnections ,loginame AS LoginNameFROM sys.sysprocessesWHERE dbid > 0GROUP BY dbid ,loginameORDER 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) )GOINSERT INTO #tblEXEC sp_who;SELECT COUNT(0) AS CountByHostName ,hostnameFROM #tblGROUP BY hostname;SELECT COUNT(0) AS CountByDBName ,dbnameFROM #tblGROUP BY dbname;DROP TABLE #tblGO
哪个 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_idGROUP BY ec.client_net_address ,es.[program_name] ,es.[host_name] ,es.login_nameORDER 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_descFROM 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_descORDER BY SUM(Rows) DESCOPTION (RECOMPILE);
本文《人人都是 DBA》由 Dennis Gao 发表自博客园,未经作者本人同意禁止任何形式的转载,任何自动或人为的爬虫转载行为均为耍流氓。
人人都是 DBA(IX)服务器信息收集脚本汇编
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。