TSQL – Getting important information about SQL Server instance

We all know it, we are to provide information on our SQL servers … either the customer or a colleague will quickly have an overview of the most important parameters of the SQL Server … What is closer than times since just quickly a TSQL statement on performing the requested server?

The last of such requests would have always found the same information grouped together or … when I’ve compiled using TSQL a “short” script and adapted to my needs. I or we use this script now also for our own documentation;-)

• Server- / Instance name
• last SQL Server Engine Start
• which SQL Server Edition / Version
• which values for CPU / Hyper thread / maxDOP are configured
• Configuration of Memory Restriction/Usage
• Count / Names / Sizes of User-Databases

SET NOCOUNT ON;
Go
-- Setting value of 'show advanced options' to 1
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- DECLARATION
SET NOCOUNT ON
DECLARE @date datetime, @start int, @ver varchar(13), @config_value varchar(20), @run_value varchar(20), @sqlstart datetime, @Mem int, @optimal_maxdop int, @cpu_count int, @scheduler_count int, @hyperthread_ratio int
DECLARE @CM Table ( [Index] int, Name nvarchar(1000) NOT NULL, Internal_Value int, Character_Value nvarchar(1000) )

-- SET Current Date/Time
SELECT @date = getdate()

-- Get SQL Server Version
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,13))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,30,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2012',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2014',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2016',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

-- Get Informations and Calculation on MaxDOP
CREATE TABLE #MDP (
name nvarchar(1000),
minimun int NOT NULL,
maximun int NOT NULL,
config_value int NOT NULL,
run_value int NOT NULL
)
Insert into #MDP exec sp_configure 'max degree of parallelism'
SELECT @config_value=rtrim(convert(varchar(8),config_value)) ,@run_value=rtrim(convert(varchar(8),run_value)) from #MDP
DROP TABLE #MDP

-- Last SQL Server Start Date/Time
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- Get Informations on CPU, Schedulers and Memory
Insert into @CM exec xp_msver select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@scheduler_count=(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255)
,@cpu_count=cpu_count
,@hyperthread_ratio=hyperthread_ratio
,@optimal_maxdop=case
when @scheduler_count >= 8 then 4
when @scheduler_count > 8 then 8
else CEILING(@scheduler_count*.5) end
from sys.dm_os_sys_info;

-- Main Statement
SELECT
CONVERT(CHAR(50), SERVERPROPERTY('MachineName')) AS [Hostname]
,isnull(CONVERT(CHAR(50), SERVERPROPERTY('InstanceName')), 'Default') [InstanceName]
,@@servername as [Servername]
,getdate() as [Current Date/Time]
,@sqlstart as [last SQL Srv Start]
,serverproperty('Edition') as [SQL Edition]
,@ver as [SQL Version]
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as [IsCluster]
,@cpu_count/@hyperthread_ratio as [CPU Count]
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) as [No of Scheduler]
,@optimal_maxdop as [Optimal MDP]
,@Mem as [Memory (MB)]
,(SELECT value_in_use FROM sys.configurations WHERE name like 'min server memory (MB)') as [actual min memory]
,(SELECT value_in_use FROM sys.configurations WHERE name like 'max server memory (MB)') as [actual max memory]
,(select COUNT(name) AS MB from master..sysdatabases) AS [No Of DBs]
,(select SUM(size*8/1024) AS MB from master..sysaltfiles where fileid = 1 and dbid > 4) AS [Overall Database Size (MB)]
Go

-- Adding Informations for all User Databases
-- Declaration
DECLARE @SumDBSize VARCHAR(2)
DECLARE @temp INT
DECLARE @DBSize INT
DECLARE @Servername varchar(100)

-- Get/Set Servername
Set @Servername = ''
If @Servername = '' Set @Servername = @@SERVERNAME

-- Calculating DBSize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[master].[dbo].[DatabaseFiles]'))
Set @temp = (SELECT round(SUM(db.size)/1024/1024,0) FROM [master].[dbo].[DatabaseFiles] as db where DATEDIFF(day, db.CreationDate, GETDATE()) = 0)
else Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)
Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)

Select
@Servername as 'Servername',
DB_NAME(sys.master_files.database_id) as 'DBName',
sum(sys.master_files.size * 8 / 1024 ) as 'DB-Size/MB',
sum(sys.master_files.size * 8 / 1024 / 1024 ) as 'DB-Size/GB'
from sys.master_files INNER JOIN sys.databases ON DB_NAME(sys.master_files.database_id) = DB_NAME(sys.databases.database_id) where DB_NAME(sys.master_files.database_id) not in ('master','model','msdb','tempdb') group by sys.master_files.database_id;
go

-- Reset value of 'show advanced options' to 0
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

Thanks to Matteo Lorini for its contribution to “Best Practices Recommendation for MaxDOP”

This TSQL, of course, can be used freely but should be tested in advance on a test system, hazard/risk borne by the performer.