Tuesday, October 4, 2011

Useful information about the SQL instance

This script gets useful information about the SQL instance, like:
- Name
- SQL Version
- Time Zone
- Max Degree of Parallelism (Configured , Running & Optimal)
- Number of Cores
- Memory
- Cluster ot Standalone
- SQL Server service start time



-- MAIN SCRIPT
--// SQL Version
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,
@hyperthread_ratio int
DECLARE @CM Table
(
[Index] int,
Name nvarchar(1000) NOT NULL,
Internal_Value int,
Character_Value nvarchar(1000)
)

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



-- MAX DEGREE OF PARALLELISM
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
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

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





--//REPORT
SELECT
@@servername as Servername
,getdate() as [Current Date/Time]
,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]
,@ver as SQL_version
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,@hyperthread_ratio as [Hyperthread Ratio]
,@optimal_maxdop as [Optimal MDP]
,@sqlstart as sql_srv_start
,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)]

1 comment:

  1. Hi Javier,

    Firstly (and once again), congratulations on the setup of your blog!

    Yes indeed this script is really useful, if I may add a couple of suggestions:

    1. Collation
    2. for your 2nd-last point, "cluster or standalone", can we check if it is mirrored configuration? i.e., "cluster or mirror or standalone".

    Best Regards,
    Duncan

    ReplyDelete