MSSQL Configuration
Overview Script
This script provides high level configuration information for the SQL Server, including:
(logical) cpu allocation
physical memory (MB)
Edition
Version
Last User Activity timestamp
Database Count
Windows Version
On newer versions of SQL (SQL2012+) use this SQL...
SELECT @@servername AS [Server],
instance = case @@servicename
when 'MSSQLSERVER' then 'DEFAULT'
else @@servicename
end,
(SELECT DISTINCT local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL) AS [Port],
cpu_count AS [Logical CPU Count],
hyperthread_ratio,
cpu_count/hyperthread_ratio as [Physical_CPU_Count],
physical_memory_kb/1024 AS [Physical Memory (MB)],
VM_type = case virtual_machine_type
when 0 then 'None'
when 1 then 'Hypervisor'
when 2 then 'Other'
else 'Error'
end,
SERVERPROPERTY('edition') AS [Edition],
SERVERPROPERTY('productversion') AS [Version],
(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]
FROM sys.dm_db_index_usage_stats
WHERE database_id > 4) AS [Last User Activity],
(SELECT COUNT(*)
FROM sys.databases) AS [Database Count],
(SELECT os_version = case windows_release
when '10.0' then 'Windows 10/2016/2019'
when '6.3' then 'Windows 8.1/2012R2'
when '6.2' then 'Windows 8/2012'
when '6.1' then 'Windows 7/2008R2'
when '6.0' then 'Windows Vista/2008'
else 'Uknown version'
end
FROM sys.dm_os_windows_info) As [OS_Version]
FROM sys.dm_os_sys_info;
GO
On SQL2008R2 use this slight variation...
SELECT @@servername AS [Server],
instance = case @@servicename
when 'MSSQLSERVER' then 'DEFAULT'
else @@servicename
end,
(SELECT DISTINCT local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL) AS [Port],
cpu_count AS [Logical CPU],
hyperthread_ratio as [Hyperthread Ratio],
cpu_count/hyperthread_ratio as [Physical_CPU],
physical_memory_in_bytes/1024/1024 AS [Physical Memory (MB)],
VM_type = case virtual_machine_type
when 0 then 'None'
when 1 then 'Hypervisor'
when 2 then 'Other'
else 'Error'
end,
SERVERPROPERTY('edition') AS [Edition],
SERVERPROPERTY('productversion') AS [Version],
(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]
FROM sys.dm_db_index_usage_stats
WHERE database_id > 4) AS [Last User Activity],
(SELECT COUNT(*)
FROM sys.databases) AS [Database Count],
(SELECT os_version = case windows_release
when '10.0' then 'Windows 10/2016/2019'
when '6.3' then 'Windows 8.1/2012R2'
when '6.2' then 'Windows 8/2012'
when '6.1' then 'Windows 7/2008R2'
when '6.0' then 'Windows Vista/2008'
else 'Uknown version'
end
FROM sys.dm_os_windows_info) As [OS_Version]
FROM sys.dm_os_sys_info;
GO
On older versions of SQL (SQL2005, SQL2008) use this cut down alternate version...
SELECT @@servername AS [Server],
instance = case @@servicename
when 'MSSQLSERVER' then 'DEFAULT'
else @@servicename
end,
(SELECT DISTINCT local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL) AS [Port],
cpu_count AS [Logical CPU Count],
hyperthread_ratio as [Hyperthread Ratio],
cpu_count/hyperthread_ratio as [Physical_CPU],
physical_memory_in_bytes/1024/1024 AS [Physical Memory (MB)],
SERVERPROPERTY('edition') AS [Edition],
SERVERPROPERTY('productversion') AS [Version],
(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]
FROM sys.dm_db_index_usage_stats
WHERE database_id > 4) AS [Last User Activity],
(SELECT COUNT(*)
FROM sys.databases) AS [Database Count]
FROM sys.dm_os_sys_info;
GO