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.
Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.
