SELECT @@VERSION AS [SQL Server Version];

SELECT windows_release    ,windows_service_pack_level    ,windows_sku    ,os_language_versionFROM sys.dm_os_windows_info WITH (NOLOCK)OPTION (RECOMPILE);

SELECT @@VERSION AS [SQL Server Version];

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

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

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

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

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‘

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;

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

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

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

SELECT DB_NAME(dbid) AS DBName    ,COUNT(dbid) AS NumberOfConnections    ,loginame AS LoginNameFROM sys.sysprocessesWHERE dbid > 0GROUP BY dbid    ,loginameORDER BY 1, 2, 3;

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

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

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

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