jeudi 8 septembre 2011

What are the Characteristics of my instances ?



As an Oracle DBA moving to MSSQL, I had to get an overview of the already installed SQL Server environments.
Therefore, I looked after the Catalog views to get as much information as possible on Instances and Databases.


1] Windows Server

CPU & Total physical memory of the SQL Server machine         sys.dm_os_sys_info & sys.dm_os_process_memory


SELECT Physical_memory_in_bytes/1024/1024 AS Server_Physical_Memory_MB FROM sys.dm_os_sys_info

[2012]
SELECT CPU_COUNT , Physical_memory_kb/1024 AS Server_Physical_Memory_MB , physical_memory_in_use_kb/1024 as phys_mem_in_use_MB
FROM sys.dm_os_sys_info , sys.dm_os_process_memory


CPU_COUNT    Server_Physical_Memory_MB  phys_mem_in_use_MB
2             8195                       3164

Drives & Free Space                                                    sys.xp_fixeddrives


-- drive / MB Free
EXEC master.sys.xp_fixeddrives


How do I check the NTFS Drive Blocksize
fsutil fsinfo ntfsinfo [your drive] Bytes Per Cluster


2] Instance Level

2.0 SQL Server Version and more

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

Instance
Version
INTANCE
SQL Server 8.00.760 - SP3 (Enterprise Edition)
INTANCE_1
SQL Server 9.00.5000.00 - SP4 (Express Edition)
INTANCE_2 (PROD)
SQL Server 9.00.4053.00 - SP3 (Enterprise Edition (64-bit))
INTANCE_3 (PROD)
SQL Server 10.0.4064.0 - SP2 (Standard Edition (64-bit))

-- get machine , instance name and version
SELECT CAST(SERVERPROPERTY('ComputerNamePhysicalNetBSHP') AS VARCHAR) + ' / '
+ CAST(SERVERPROPERTY('servername') AS VARCHAR) + ' - SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

A query I did to get the Information Nagios needed :

-- Server Name : MS Studio Name
-- ComputerNamePhysicalNetBSHP : physical server
-- servername : instance name
-- InstanceName : Returns NULL if the instance name is the default instance
-- IsClustered : 1 = Clustered. 0 = Not Clustered.
-- IsIntegratedSecurityOnly : 1 = Integrated Security. 0 = Not Integrated Security.

-- Query Catalog Views
SELECT
( SELECT SERVERPROPERTY('servername') ) AS [Instance_name] ,
( SELECT SERVERPROPERTY('ComputerNamePhysicalNetBSHP') ) AS [Node] ,
-- freetds format
case when SERVERPROPERTY('InstanceName') IS not null then
   ( SELECT LOWER(CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + '_'
   + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)) )
   else
   ( SELECT LOWER(CAST(SERVERPROPERTY('MachineName') AS VARCHAR)) )
   end AS "[instance_MSSQL_freetds]" ,
( SELECT LOWER(CAST(SERVERPROPERTY('MachineName') AS VARCHAR) ) ) AS "host" ,
case when SERVERPROPERTY('InstanceName') IS not null then
   ( SELECT LOWER(CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) ) )
   else ' '
   end AS "instance" ,
( SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
                WHEN 1 THEN 'N' -- 'Windows Authentication'
                WHEN 0 THEN 'O' -- 'Windows and SQL Server Authentication'
                END ) as pre_requ_Nagios , -- [Authentication Mode] ,
( SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')') Version
order by 1

Instance
Nodename
instance_MSSQL_freetds
Host
instance
Mixte authen
version
W-INTANCE-1\NAMED
W-SERVER-1
w-instance-1_named
w-instance-1
named
O
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))

SELECT
SERVERPROPERTY('servername') AS [Instance_name] ,
SERVERPROPERTY('ComputerNamePhysicalNetbios') AS [Node] ,
LOWER(CAST(SERVERPROPERTY('MachineName') AS VARCHAR)) [Machine] ,
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) [Named_Instance] ,
( SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
                WHEN 1 THEN 'N' -- 'Windows Authentication'
                WHEN 0 THEN 'O' -- 'Windows and SQL Server Authentication'
                END ) [Authentication Mode] ,
( SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')') Version
order by 1

Instance_name      Node                       Machine  Named_Instance    Authentication Mode             version
SRV11\INST1         SRV11                     w-iss-11  INST1                      O                                            SQL Server 11.0.3000.0 - SP1 (Enterprise Edition (64-bit))

2.1 Error Log lookup


exec sp_readerrorlog

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBSHP') AS [CurrentNodeName],
( SELECT SERVERPROPERTY('ErrorLogFileName') )

Instance
Nodename
Errorlog file path
SRV\INS
W-SERVER-4
U:\MSSQL10.INS\MSSQL\Log\ERRORLOG

2.2 Total Allocated MB Data + Log                          sys.master_files


( SELECT SUM(size*8)/1024. Total_mdf_ldf_MB_sz FROM sys.master_files )


2.3 Overall instance specifications
sys.master_files , sys.xp_fixeddrives , msdb.dbo.backupmediafamily , sys.dm_os_sys_info , sys.servers

Pre-requisite to get full list of parameters ( 70 ) – can be executed Online
EXEC sp_configure 'show advanced options', 1 -- Changing to 1 turns it on
GO
RECONFIGURE WITH OVERRIDE

Run this query on multiple instances when selecting SQL Server Group in Management Studio.


-- 1 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL, [MB free] int NOT NULL)

CREATE TABLE #sp_configure
(name varchar(35) NOT NULL, minimum int NOT NULL, maximum int NOT NULL, config_value int NOT NULL, run_value int NOT NULL)

-- 2 - Populate temp tables
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC sys.xp_fixeddrives
go

INSERT INTO #sp_configure(name,minimum,maximum,config_value,run_value)
EXEC sp_configure
go

-- 3 – Query Catalog Views
with TAFMB as ( select substring(physical_name,1,1) drive , SUM(size*8)/1024 Total_Allocated_Files_MB FROM sys.master_files group by substring(physical_name,1,1) )
SELECT distinct -- total_physical_memory_kb/1024 Phys_Mem_MB , available_physical_memory_kb/1024 Avail_Phys_Mem_MB ,
( SELECT SERVERPROPERTY('ComputerNamePhysicalNetBSHP') ) AS [CurrentNodeName] , d.cpu_count , -- b.Name ,
x.run_value Max_Server_Mem_MB , x2.run_value Max_Worker_Threads , x3.run_value Max_Parall_Degree ,
( select physical_memory_in_use_kb/1024 as phys_mem_in_use_MB from sys.dm_os_process_memory ) Mem_in_Use_MB ,
substring(physical_name,1,2) Drive , e.Total_Allocated_Files_MB ,
y.[MB free] Drive_Free_MB ,
( select top 1 substring(physical_device_name,1,15) from msdb.dbo.backupmediafamily where device_type = 2 ) Backup_Path ,
d.sqlserver_start_time , -- SQL 2008 only
(SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')') Version
FROM sys.master_files a , #tbl_xp_fixeddrives y , #sp_configure x , #sp_configure x2 , #sp_configure x3 ,
sys.servers b , -- sys.dm_os_sys_memory c
sys.dm_os_sys_info d , TAFMB e
where substring(physical_name,1,1) = y.Drive and e.drive = y.Drive
and x.name = 'max server memory (MB)' and x2.name = 'max worker threads' and x3.name = 'max degree of parallelism'
order by 1


-- 4 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

DROP TABLE #sp_configure


Output

Instance
NodeName
cpu
count
Max_Server
Mem_MB
Max_Worker
Threads
Max_Parall
Degree
Mem_in_Use
MB
Drive
Total_Alloc
Files_MB
Drive
Free_MB
W-INSTANCE-1
W-SERVER-2
24
8192
0
0
9351
Z:
42881
7618
W-INSTANCE-2\NAMED2
W-SERVER-2
24
4096
0
0
4873
W:
37077
5478
W-INSTANCE-3\NAMED3
W-SERVER-2
24
4096
0
0
4937
V:
6777
58247
W-INSTANCE-4\NAMED4
W-SERVER-2
24
4096
0
0
3607
Y:
2861
47817
W-INSTANCE-5\NAMED5
W-SERVER-2
24
1024
0
0
1200
T:
7299
4233

Backup Device path
sqlserver
start_time
Version
\\w-inst-1\NAMED1
2011-08-07 10:08:45.093
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))
\\w-inst-1\NAMED2
2011-08-07 10:08:43.330
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))
\\w-inst-1\NAMED3
2011-08-26 14:58:32.853
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))
\\w-inst-1\IFE\
2011-08-31 18:15:15.863
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))
\\w-inst-1\INST\
2011-08-17 11:40:54.323
SQL Server 10.50.1600.1 - RTM (Enterprise Edition (64-bit))

See http://support.microsoft.com/kb/321185 for Release & Product Version.

PS : query on a single instance, add b.Name


3] DB Level

Instance wise

3.1 DB specs
sys.databases , sys.master_files , sys.dm_os_performance_counters , msdb.dbo.backupset


select a.name , database_id, SUSER_SNAME(owner_sid) as Owner, convert(varchar(10),create_date,101) Create_Date , state_desc State,
recovery_model_desc Recovery , snapshot_isolation_state_desc , compatibility_level ,
( SELECT ceiling(SUM(size*8)/1024.) Total_mdf_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id ) Total_sz_MB ,
( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '0' ) Total_Data_sz_MB ,
( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '1' ) Total_Log_sz_MB ,
( select cntr_value from sys.dm_os_performance_counters b where counter_name like 'Percent Log Used%' and instance_name = a.name ) Log_Pct_Used ,
( SELECT max(backup_finish_date) FROM msdb.dbo.backupset b WHERE a.name = b.database_name and type = 'D' ) Last_Full_Backup ,
( SELECT top 1 ceiling(backup_size/1024.0/1024.0)
FROM msdb.dbo.backupset b WHERE a.name = b.database_name and type = 'D' ORDER BY backup_finish_date DESC ) BackupSize_MB
from sys.databases a where a.name not in ('model','tempdb')
order by 2


Output

Instance
DB
db_id
Owner
Creation Date
State
Recovery
W-INSTANCE-1
master
1
sa
04/06/2011
ONLINE
SIMPLE
W-INSTANCE-1
msdb
4
sa
04/06/2011
ONLINE
SIMPLE
W-INSTANCE-1
SUSDB
5
SA
04/06/2011
ONLINE
SIMPLE
W-INSTANCE-1
SMS
6
SA
03/07/2011
ONLINE
SIMPLE
W-INSTANCE-1
FEPDB
7
SA
03/08/2011
ONLINE
SIMPLE

snapshot
isolation
Compatibility
DB
Size MB
Data
Size MB
Log
Size MB
% Log
used
Last Full DB Backup
Backup
Size MB
ON
100
6
4
2
41
2011-09-05 23:54:31.000
3
ON
100
15
14
2
53
2011-09-05 23:54:34.000
14
OFF
90
2055
1770
286
5
2011-09-05 23:57:51.000
1756
OFF
100
19197
13311
5887
0
2011-09-06 00:24:12.000
12905
ON
100
1320
1031
289
7
2011-09-06 00:29:12.000
17

Backup Size is the UnCompressed size, even if Compressed backup was done ( see Backup post ).

For monitoring purpose, remove the following columns :


select a.name , -- database_id, SUSER_SNAME(owner_sid) as Owner, convert(varchar(10),create_date,101) Create_Date , state_desc State,
-- recovery_model_desc Recovery , snapshot_isolation_state_desc , compatibility_level ,
( SELECT ceiling(SUM(size*8)/1024.) Total_mdf_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id ) Total_sz_MB ,


Tuning :

alter database '+@DATABASE+' set AUTO_CLOSE off,AUTO_CREATE_STATISTICS on,
AUTO_UPDATE_STATISTICS on,AUTO_SHRINK off,RECOVERY SIMPLE,AUTO_CLOSE off,ALLOW_SNAPSHOT_ISOLATION ON'+char(13)+
'alter database '+@DATABASE+' set READ_COMMITTED_SNAPSHOT ON'+char(13)+'

3.2 More DB/Files specs       sys.master_files [ DB sys.database_files ] , sys.databases , sys.dm_os_performance_counters


SELECT distinct DB_NAME(a.database_id) DB_name,recovery_model_desc Recovery,
type_desc Type,a.Name Logical_Name,Physical_Name,(size*8)/1024 Size_MB,
CASE WHEN is_percent_growth = 1 THEN ceiling(((size*8.0)/1024.0)*(growth/100.0))
                 WHEN is_percent_growth = 0 THEN ceiling((growth)*8.0/1024.0) ELSE 0 END as Growth_MB,
CASE WHEN max_size != -1 THEN ceiling(max_size*8.0/1024.0)
ELSE max_size END MaxSize_MB,
case when type_desc = 'LOG' then cntr_value else NULL end as Instant_Log_Space_Used_Pct
FROM sys.master_files a , sys.dm_os_performance_counters b , sys.databases c
where DB_NAME(a.database_id)=b.instance_name and counter_name like 'Percent Log Used%' and a.database_id = c.database_id
order by 3,1


Output

DB
Recovery
Type
Logical Name
Physical Name
master
SIMPLE
LOG
mastlog
U:\MSSQL10_50.RSG\MSSQL\DATA\mastlog.ldf
model
FULL
LOG
modellog
U:\MSSQL10_50.RSG\MSSQL\DATA\modellog.ldf
msdb
SIMPLE
LOG
MSDBLog
U:\MSSQL10_50.RSG\MSSQL\DATA\MSDBLog.ldf
ReportServer
FULL
LOG
ReportServer_log
U:\MSSQL10_50.RSG\MSSQL\DATA\ReportServer_log.LDF
tempdb
SIMPLE
LOG
templog
U:\MSSQL10_50.RSG\MSSQL\DATA\templog.ldf
master
SIMPLE
ROWS
master
U:\MSSQL10_50.RSG\MSSQL\DATA\master.mdf
model
FULL
ROWS
modeldev
U:\MSSQL10_50.RSG\MSSQL\DATA\model.mdf
msdb
SIMPLE
ROWS
MSDBData
U:\MSSQL10_50.RSG\MSSQL\DATA\MSDBData.mdf
ReportServer
FULL
ROWS
ReportServer
U:\MSSQL10_50.RSG\MSSQL\DATA\ReportServer.mdf
tempdb
SIMPLE
ROWS
tempdev
U:\MSSQL10_50.RSG\MSSQL\DATA\tempdb.mdf

Size_MB
Growth_MB
MaxSize_MB
Log pct
used
1
1
-1
53
6
1
-1
97
1
1
2097152
47
128
32
2097152
47
31
4
2097152
9
0
1
-1
64
4
1
-1
NULL
1
1
-1
NULL
13
2
-1
NULL
60
32
-1
NULL
20
1
-1
NULL
8
1
-1
NULL

DB File modification to increase size or autogrowth :

ALTER DATABASE ReportServer MODIFY FILE (NAME = ReportServer, FILEGROWTH = 32 MB);

ALTER DATABASE ReportServer MODIFY FILE (NAME = ReportServer_log, SIZE = 64 MB);
ALTER DATABASE ReportServer MODIFY FILE (NAME = ReportServer_log, FILEGROWTH = 32 MB);

3.3 Other info

Get Node , allocated size , free on drive

-- 1 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL, [MB free] int NOT NULL)

-- 2 - Populate temp tables
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC sys.xp_fixeddrives
go

-- 3 – Query Catalog Views
with TAFMB as ( select substring(physical_name,1,1) drive , SUM(size*8)/1024 Total_Allocated_Files_MB FROM sys.master_files group by substring(physical_name,1,1) )
SELECT distinct -- total_physical_memory_kb/1024 Phys_Mem_MB , available_physical_memory_kb/1024 Avail_Phys_Mem_MB ,
( SELECT SERVERPROPERTY('ComputerNamePhysicalNetBSHP') ) AS [CurrentNodeName] , e.Total_Allocated_Files_MB , y.[MB free] Drive_Free_MB ,
(SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')') Version
FROM sys.master_files a , #tbl_xp_fixeddrives y ,
sys.servers b , -- sys.dm_os_sys_memory c
sys.dm_os_sys_info d , TAFMB e
where substring(physical_name,1,1) = y.Drive and e.drive = y.Drive
order by 1

-- 4 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

CurrentNodeName
Total_Allocated_Files_MB
Drive_Free_MB
Version
NULL
26902
55668
SQL Server 11.0.3000.0 - SP1 (Enterprise Edition (64-bit))

Get Database Volume

select a.name ,
( SELECT ceiling(SUM(size*8)/1024.) Total_mdf_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id ) Total_sz_MB ,
( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '0' ) Total_Data_sz_MB ,
( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '1' ) Total_Log_sz_MB
from sys.databases a where a.name not in ('model','tempdb')
order by 2

name
Total_sz_MB
Total_Data_sz_MB
Total_Log_sz_MB
master
7
5
2
msdb
38
19
20
VirtualManagerDB
26844
7129
19716


Publié par orACsd à 08:38
Libellés :