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

3 comments:

  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
  2. Buenas Noches Javier,
    validando sobre el script, cuando indica el campo "Optimal MDP", significa que me esta recomendando poner el valor en el maxdop?

    saludos

    ReplyDelete
  3. Hola. El calculo de MDP optimo esta dado por la cantidad de procesadores y cores . tambien teniendo en cuenta si las CPUs tiene hyperthreading . El valor es mas bien de referencia, pero lo podes tomar como valor inicial

    ReplyDelete