"Backup Compression" and "Optimized for AdHoc workloads" settings.
SQL Server Start Time
Number of CPUs and amount of Memory
Cluster
Database Mirroring
Server Collation
Server Collation
References
Backup Compression
Optimized for AdHoc workloads
(*) when new service packs or cumulative updates are available, you have to update the section "SQL Version", use http://sqlserverbuilds.blogspot.com.ar/ to the the build/version data
/*
-- !! Make sure you can see advanced options in sp_configure
sp_configure 'show advanced options',1
GO
reconfigure
GO
*/
-- MAIN SCRIPT
USE master
GO
SET NOCOUNT ON
DECLARE
@date datetime,
@start int,
@ver varchar(13),
@MDPconfig_value varchar(20),
@MDPrun_value varchar(20),
@Backup_Comp varchar(20),
@Adhoc varchar(20),
@sqlstart datetime,
@Mem int,
@optimal_maxdop int,
@cpu_count int,
@hyperthread_ratio int,
@IPAdd varchar(30),
@MirrorServer varchar(50),
@VersionNr varchar (200),
@SVCsecs INT,
@SVCTimeValue varchar(50)
DECLARE @CM Table
(
[Index] int,
Name nvarchar(1000) NOT NULL,
Internal_Value int,
Character_Value nvarchar(1000)
)
DECLARE @SP_CONFIGURE Table
(
name nvarchar(1000),
minimun int NOT NULL,
maximun int NOT NULL,
config_value int NOT NULL,
run_value int NOT NULL
)
SELECT @date = getdate()
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1
SELECT @ver = rtrim(substring(@@version,29,13))
if @start = 0
SELECT @ver = rtrim(substring(@@version,30,9))
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,38,12))
IF @@version like 'Microsoft SQL Server "Denali"%'
BEGIN
SELECT @ver = rtrim(substring(@@version,40,11))
SELECT rtrim(substring(@@version,40,11)),@@version
END
IF @@version like 'Microsoft SQL Server 2012%'
SELECT @ver = rtrim(substring(@@version,charindex(' - ',@@version)+3,12))
INSERT INTO @SP_CONFIGURE exec sp_configure
-- MAX DEGREE OF PARALLELISM
SELECT @MDPconfig_value=rtrim(convert(varchar(8),config_value)) ,@MDPrun_value=rtrim(convert(varchar(8),run_value))
FROM @SP_CONFIGURE
WHERE name ='max degree of parallelism'
--BACKUP COMPRESSION
SELECT @Backup_Comp=rtrim(convert(varchar(8),run_value))
FROM @SP_CONFIGURE
WHERE name ='backup compression default'
--OPTIMIZE FOR AD HOC Workload
SELECT @Adhoc =rtrim(convert(varchar(8),run_value))
FROM @SP_CONFIGURE
WHERE name ='optimize for ad hoc workloads'
--SQL Server Service Start time
SELECT @sqlstart = create_date from sys.databases where name = 'Tempdb'
SET @SVCsecs = DATEDIFF(SECOND,@sqlstart,GETDATE())
select @SVCTimeValue =
convert(varchar(10), (@SVCsecs/86400)) + ':' +
convert(varchar(10), ((@SVCsecs%86400)/3600)) + ':'+
convert(varchar(10), (((@SVCsecs%86400)%3600)/60)) + ':'+
convert(varchar(10), (((@SVCsecs%86400)%3600)%60))
-- CPU and Memory
Insert into @CM exec xp_msver
--//REPORT
select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@cpu_count=cpu_count,
@hyperthread_ratio=hyperthread_ratio,@optimal_maxdop=case
when cpu_count / hyperthread_ratio > 8 then 4
else CEILING((cpu_count / hyperthread_ratio)*.5)
end
from sys.dm_os_sys_info;
-- IP Address
SELECT TOP 1 @IPAdd = dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID
--Database Mirroring
SELECT TOP 1 @MirrorServer= mirroring_partner_instance from msdb.sys.database_mirroring (nolock)
where mirroring_partner_instance is not null
-- SQL Server Patches
/*
You can use below site to update below list
http://sqlserverbuilds.blogspot.com.ar/
*/
DECLARE @Builds TABLE ([Version] varchar(200),[Build] varchar(50))
INSERT INTO @Builds ([Version] ,[Build] )
VALUES
('SQL 2012 + Cumulative Update 1','11.0.2316.0'),
('SQL 2012 RTM','11.0.2100.60'),
('SQL 2008 R2 SP1 + Cumulative Update 5','10.50.2806.0'),
('SQL 2012 RC0','11.0.1750.32'),
('SQL 2012 CTP3 + QFE','11.0.1706.0'),
('SQL 2012 CTP3','11.0.1440.0'),
('SQL 2012 CTP1','11.0.1103.0'),
('SQL 2008 R2 SP1 + Cumulative Update 4','10.50.2796.0'),
('SQL 2008 R2 SP1 + Cumulative Update 3','10.50.2789.0'),
('SQL 2008 R2 SP1 + Cumulative Update 2','10.50.2772.0'),
('SQL 2008 R2 SP1 + Cumulative Update 1','10.50.2769.0'),
('SQL 2008 R2 + SP1','10.50.2500.0'),
('SQL 2008 R2 + SP1 CTP','10.50.2418.0'),
('SQL 2008 R2 + Cumulative Update 9','10.50.1804.0'),
('SQL 2008 R2 + Cumulative Update 8','10.50.1797.0'),
('SQL 2008 R2 + Q2494086','10.50.1790.0'),
('SQL 2008 R2 + Cumulative Update 7','10.50.1777.0'),
('SQL 2008 R2 + Cumulative Update 6','10.50.1765.0'),
('SQL 2008 R2 + Cumulative Update 5','10.50.1753.0'),
('SQL 2008 R2 + Cumulative Update 4','10.50.1746.0'),
('SQL 2008 R2 + Cumulative Update 3','10.50.1734.0'),
('SQL 2008 R2 + Cumulative Update 2','10.50.1720.0'),
('SQL 2008 R2 + Cumulative Update 1','10.50.1702.0'),
('SQL 2008 RTM','10.50.1600.1'),
('SQL 2008 RC 0','10.50.1450.3'),
('SQL 2008 + Service Pack 3','10.0.5500.0'),
('SQL 2008 SP2 + Cumulative Update 6','10.0.4321.0'),
('SQL 2008 SP2 + Cumulative Update 5','10.0.4316.0'),
('SQL 2008 SP2 + Q2494094','10.0.4311.0'),
('SQL 2008 + Cumulative Update 4 for SP2','10.0.4285.0'),
('SQL 2008 + Cumulative Update 3 for SP2','10.0.4279.0'),
('SQL 2008 + Cumulative Update 2 for SP2','10.0.4272.0'),
('SQL 2008 + Cumulative Update 1 for SP2','10.0.4266.0'),
('SQL 2008 SP2 + Q2494089','10.0.4064.0'),
('SQL 2008 + Service Pack 2','10.0.4000.0'),
('SQL 2008 + Cumulative Update 16 for SP1','10.0.2850.0'),
('SQL 2008 + Cumulative Update 15 for SP1','10.0.2847.0'),
('SQL 2008 SP1 + Q2494100','10.0.2841.0'),
('SQL 2008 + Cumulative Update 14 for SP1','10.0.2821.0'),
('SQL 2008 + Cumulative Update 13 for SP1','10.0.2816.0'),
('SQL 2008 + Cumulative Update 12 for SP1','10.0.2808.0'),
('SQL 2008 + Q2413738','10.0.2804.0'),
('SQL 2008 + Cumulative Update 10 for SP1','10.0.2799.0'),
('SQL 2008 + Cumulative Update 9 for SP1','10.0.2789.0'),
('SQL 2008 + Cumulative Update 8 for SP1','10.0.2775.0'),
('SQL 2008 + Cumulative Update 7 for SP1','10.0.2766.0'),
('SQL 2008 + Q978839','10.0.2760.0'),
('SQL 2008 SP1 + Q978791','10.0.2758.0'),
('SQL 2008 + Cumulative Update 6 for SP1','10.0.2757.0'),
('SQL 2008 + Cumulative Update 5 for SP1','10.0.2746.0'),
('SQL 2008 + Q976761','10.0.2740.0'),
('SQL 2008 + Cumulative Update 4 for SP1','10.0.2734.0'),
('SQL 2008 + Cumulative Update 3 for SP1','10.0.2723.0'),
('SQL 2008 + Cumulative Update 2 for SP1','10.0.2714.0'),
('SQL 2008 + Q970507','10.0.2712.0'),
('SQL 2008 + Cumulative Update 1 for SP1','10.0.2710.0'),
('SQL 2008 + Service Pack 1 GDR June 14, 2011','10.0.2573.0'),
('SQL 2008 + Service Pack 1','10.0.2531.0'),
('SQL 2008 + Cumulative Update 10','10.0.1835.0'),
('SQL 2008 + Cumulative Update 9','10.0.1828.0'),
('SQL 2008 + Cumulative Update 8','10.0.1823.0'),
('SQL 2008 + Q973601','10.0.1818.0'),
('SQL 2008 + Cumulative Update 6','10.0.1812.0'),
('SQL 2008 + Cumulative Update 5','10.0.1806.0'),
('SQL 2008 + Cumulative Update 4','10.0.1798.0'),
('SQL 2008 + Cumulative Update 3','10.0.1787.0'),
('SQL 2008 + Q958186 (Cumulative HF2, available byrequest.)','10.0.1779.0'),
('SQL 2008 + Q958611','10.0.1771.0'),
('SQL 2008 + Q956717','10.0.1763.0'),
('SQL 2008 + Q957387','10.0.1755.0'),
('SQL 2008 + Q956718','10.0.1750.0'),
('SQL 2008 RTM','10.0.1600.22.0'),
('SQL 2008 February CTP','10.0.1300.13'),
('SQL 2008 July CTP (requires Virtual Server 2005 R2),','10.0.1049.14'),
('SQL 2008 June CTP','10.0.1019.17')
SELECT @VersionNr = [Version] From @Builds where [Build] = @ver
---------------------------------------------------------------------------------------
--//REPORT
---------------------------------------------------------------------------------------
SELECT
@@servername as Servername
,@IPAdd as [IP Address]
,@ver as [SQL Build]
,@VersionNr as [SQL Version]
,getdate() as [Current Date/Time]
,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]
,cast(@MDPconfig_value as varchar(1))+' / '+ cast(@MDPrun_value as varchar(1)) +' / '+ cast(@optimal_maxdop as varchar(2))as [MDP Cfg/Run/Optimal]
,@hyperthread_ratio as [Hyperthread Ratio]
,@backup_comp as [Backup Compression]
,@Adhoc as [Opt for Ad hoc workload]
,convert(varchar(30),@sqlstart,120)+ ' / '+@SVCTimeValue as [SQL Svc Start / DD:HH:MM:SS]
,SERVERPROPERTY('collation') AS SQLServerCollation
,SERVERPROPERTY('collation') AS SQLServerCollation
,serverproperty('Edition') as [SQL Edition]
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as IsCluster
,@cpu_count as [CPU Count]
,@Mem as [Memory (MB)]
,@MirrorServer as [DBMirror Server]
