Πώς βρίσκουμε τι χαρακτηριστικά έχει το κάθε SQL Server instance

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024

Σε αυτό το άρθρο θα δούμε ένα query που έχω φτιάξει ώστε να μπορούμε να βρούμε εύκολα τα specs του κάθε SQL Server instance. To query αυτό μπορεί να τρέξει σε οποιαδήποτε έκδοση χωρίς να χρειάζεται κάποια μετατροπή καθώς είναι δυναμικό.

Τι μπορούμε να δούμε μέσα από αυτό το query

• Το Server name με την πόρτα
• Τι έκδοση είναι
• Ποιο patch είναι εγκατεστημένο
• To server collation
• Τον αριθμό CPU
• Την Physical RAM που έχει το μηχάνημα και το Max-Min memory που έχουμε ρυθμίσει για το instance
• To average CPU load της τελευταίας ώρας μέσα από το default extended event που έχει εγκατεστημένο ο SQL Server το System Health
• Το συνολικό μέγεθος των βάσεων (Data και Log files)
• Πότε έγινε τελευταία φορά start-up το instance
• Αν είναι ενεργοποιημένο το DAC
• Την τιμή που έχουμε ορίσει για Max DOP, blocked process threshold και cost threshold for parallelism
• Performance counters όπως το Page Life Expectancy, το Buffer cache hit ratio και άλλα.

Υπάρχει όμως ένα πρόβλημα

Καθώς το πεδίο για να βρούμε το μέγεθος της μνήμης RAM στο μηχάνημα άλλαξε στις νεότερες εκδόσεις το όνομα του από physical_memory_in_bytes σε physical_memory_kb στο view sys.configurations πρέπει το query να γραφτεί δυναμικά. Δηλαδή να τρέxει διαφορετικό query ανάλογα την έκδοση που έχουμε.

Μαζί με την αλλαγή αυτή ήρθε και η προσθήκη των availability groups. Οπότε απλά κοιτάω αν υπάρχει το object αυτό και αναλόγος τρέχει ένα από τα δύο queries. Εναλλάκτικα βέβαια μπορούμε να κοιτάμε το ResourceVersion αν είναι πριν από την έκδοση 11 (SQL Server 2012).

Το query

set nocount on
SET QUOTED_IDENTIFIER ON;
GO
declare
@query1 varchar(max),
@query2 varchar(max),
@final varchar(max)

set @query1 = 'SELECT
 @@servername AS [ServerName],
 (select distinct top 1 local_tcp_port from sys.dm_exec_connections where local_tcp_port is not null) as Port,
   CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''8%'' THEN ''SQL2000''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''9%'' THEN ''SQL2005''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''10.0%'' THEN ''SQL2008''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''10.5%'' THEN ''SQL2008 R2''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''11%'' THEN ''SQL2012''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''12%'' THEN ''SQL2014''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''13%'' THEN ''SQL2016''    
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''14%'' THEN ''SQL2017'' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''15%'' THEN ''SQL2019'' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''16%'' THEN ''SQL2022'' 
     ELSE ''unknown''
  END AS MajorVersion,
 SERVERPROPERTY(''Edition'') AS [Edition],SERVERPROPERTY(''ProductVersion'') AS ProductVersion,SERVERPROPERTY(''ProductLevel'') AS ProductLevel,SERVERPROPERTY(''ProductUpdateLevel'') AS ProductUpdateLevel
,SERVERPROPERTY(''Collation'') as [Server Collation] 
,cpu_count AS [Number of Logical CPU]
--,hyperthread_ratio
--,cpu_count/hyperthread_ratio AS [Number of Physical CPU]
,physical_memory_in_bytes/1024/1024/1024  as [Physical Memory (GB)]
,(SELECT cast(value_in_use as integer)/1024
FROM sys.configurations
WHERE name = ''max server memory (MB)'') as [Max Instance memory GB]
,(SELECT cast(value_in_use as integer)/1024
FROM sys.configurations
WHERE name = ''min server memory (MB)'') as [Min Instance memory GB]
,(select CAST( ((SUM(cast(size as bigint))* 8) / 1024.0 / 1024.0) as DECIMAL(18,2)) as [Total Data Size (GB)] from sys.master_files where type_desc = ''ROWS'') as [Total Data Size (GB)]
,(select CAST( ((SUM(cast(size as bigint))* 8) / 1024.0 / 1024.0) as DECIMAL(18,2)) as [Total Log Size (GB)] from sys.master_files where type_desc = ''LOG'') as [Total Log Size (GB)]
,(SELECT 
                        100-AVG(record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''INT'') )
          FROM ( 
                        SELECT  top 60 CONVERT(xml, record) AS [record] 
                        FROM sys.dm_os_ring_buffers 
                        WHERE ring_buffer_type = N''RING_BUFFER_SCHEDULER_MONITOR''
                        AND record LIKE ''%<SystemHealth>%''
			order by timestamp desc) AS x )[AVG CPU Load Last Hour],
(select cntr_value from sys.dm_os_performance_counters
where counter_name =''Page life expectancy'' and object_name like ''%Buffer Manager%'') as [Page Life Expectancy],
(select cntr_value from sys.dm_os_performance_counters
where counter_name =''Memory Grants Pending'' and object_name like ''%Buffer Manager%'') as [Memory Grants Pending],
(SELECT ((a.cntr_value * 100) / b.cntr_value) as Value FROM sys.dm_os_performance_counters  a
JOIN (SELECT cntr_value, OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = ''Buffer cache hit ratio base'' AND OBJECT_NAME LIKE ''%:Buffer Manager%'') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = ''Buffer cache hit ratio'' AND a.OBJECT_NAME LIKE ''%:Buffer Manager%'') as [Buffer cache hit ratio],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2)) as value  from sys.dm_os_performance_counters
where counter_name = ''Batch Requests/sec'') as [AVG BatchRequests/sec], 
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2)) as Value from sys.dm_os_performance_counters
where counter_name = ''SQL Compilations/sec'') as [AVG SQL Compilations/sec],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2))  as Value from sys.dm_os_performance_counters
where counter_name = ''SQL Re-Compilations/sec'') as [AVG SQL Re-Compilations/sec],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2))  as Value from sys.dm_os_performance_counters
where counter_name = ''Page Splits/sec'') as [AVG Page Splits/sec],
sqlserver_start_time as [SQL Server Start Time]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''remote admin connections'') as [remote admin connections]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''blocked process threshold (s)'') as [blocked proccess threshold]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''max degree of parallelism'') as [max degree of parallelism]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''cost threshold for parallelism'') as [cost threshold for parallelism]
FROM sys.dm_os_sys_info  OPTION (RECOMPILE)'




set @query2 = 'SELECT
 @@servername AS [ServerName],
 (select distinct top 1 local_tcp_port from sys.dm_exec_connections where local_tcp_port is not null) as Port,
   CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''8%'' THEN ''SQL2000''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''9%'' THEN ''SQL2005''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''10.0%'' THEN ''SQL2008''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''10.5%'' THEN ''SQL2008 R2''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''11%'' THEN ''SQL2012''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''12%'' THEN ''SQL2014''
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''13%'' THEN ''SQL2016''    
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''14%'' THEN ''SQL2017'' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''15%'' THEN ''SQL2019'' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (''productversion'')) like ''16%'' THEN ''SQL2022'' 
     ELSE ''unknown''
  END AS MajorVersion,
 SERVERPROPERTY(''Edition'') AS [Edition],SERVERPROPERTY(''ProductVersion'') AS ProductVersion,SERVERPROPERTY(''ProductLevel'') AS ProductLevel,SERVERPROPERTY(''ProductUpdateLevel'') AS ProductUpdateLevel
,SERVERPROPERTY(''Collation'') as [Server Collation] 
,cpu_count AS [Number of Logical CPU]
--,hyperthread_ratio
--,cpu_count/hyperthread_ratio AS [Number of Physical CPU]
,physical_memory_kb/1024/1024 as [Physical Memory (GB)]
,(SELECT cast(value_in_use as integer)/1024
FROM sys.configurations
WHERE name = ''max server memory (MB)'') as [Max Instance memory GB]
,(SELECT cast(value_in_use as integer)/1024
FROM sys.configurations
WHERE name = ''min server memory (MB)'') as [Min Instance memory GB]
,(select CAST( ((SUM(cast(size as bigint))* 8) / 1024.0 / 1024.0) as DECIMAL(18,2)) as [Total Data Size (GB)] from sys.master_files where type_desc = ''ROWS'') as [Total Data Size (GB)]
,(select CAST( ((SUM(cast(size as bigint))* 8) / 1024.0 / 1024.0) as DECIMAL(18,2)) as [Total Log Size (GB)] from sys.master_files where type_desc = ''LOG'') as [Total Log Size (GB)]
,
   (SELECT 
                        100-AVG(record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''INT'') )
          FROM ( 
                        SELECT top 60 CONVERT(xml, record) AS [record] 
                        FROM sys.dm_os_ring_buffers 
                        WHERE ring_buffer_type = N''RING_BUFFER_SCHEDULER_MONITOR''
                        AND record LIKE ''%<SystemHealth>%''
			order by timestamp desc) AS x )[AVG CPU Load Last Hour],
(select cntr_value from sys.dm_os_performance_counters
where counter_name =''Page life expectancy'' and object_name like ''%Buffer Manager%'') as [Page Life Expectancy],
(select cntr_value from sys.dm_os_performance_counters
where counter_name =''Memory Grants Pending'' and object_name like ''%Buffer Manager%'') as [Memory Grants Pending],
(SELECT ((a.cntr_value * 100) / b.cntr_value) as Value FROM sys.dm_os_performance_counters  a
JOIN (SELECT cntr_value, OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = ''Buffer cache hit ratio base'' AND OBJECT_NAME LIKE ''%:Buffer Manager%'') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = ''Buffer cache hit ratio'' AND a.OBJECT_NAME LIKE ''%:Buffer Manager%'') as [Buffer cache hit ratio],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2)) as value  from sys.dm_os_performance_counters
where counter_name = ''Batch Requests/sec'') as [AVG BatchRequests/sec], 
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2)) as Value from sys.dm_os_performance_counters
where counter_name = ''SQL Compilations/sec'') as [AVG SQL Compilations/sec],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2))  as Value from sys.dm_os_performance_counters
where counter_name = ''SQL Re-Compilations/sec'') as [AVG SQL Re-Compilations/sec],
(select Cast(cast(cntr_value as float)/(SELECT cast (DATEDIFF(s, sqlserver_start_time, GETDATE()) as float) FROM sys.dm_os_sys_info) as decimal (10,2))  as Value from sys.dm_os_performance_counters
where counter_name = ''Page Splits/sec'') as [AVG Page Splits/sec],
sqlserver_start_time as [SQL Server Start Time]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''remote admin connections'') as [remote admin connections]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''blocked process threshold (s)'') as [blocked proccess threshold]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''max degree of parallelism'') as [max degree of parallelism]
,(SELECT value_in_use
FROM sys.configurations
WHERE name = ''cost threshold for parallelism'') as [cost threshold for parallelism]
FROM sys.dm_os_sys_info  OPTION (RECOMPILE)'


IF NOT EXISTS (SELECT 1
           FROM sys.system_objects WITH (NOLOCK)
           WHERE NAME='dm_hadr_database_replica_states') 
begin set @final = @query1 end
else 
begin set @final = @query2 end


exec (@final)

Το αποτέλεσμα εμφανίζεται όπως βλέπουμε παρακάτω:

Πώς μπορούμε να το τρέξουμε μαζικά σε όλα τα instances που έχουμε

Ο ποιο εύκολος τρόπος είναι να έχουμε ρυθμίσει στο SQL Server Management Studio όλες τις συνδέσεις μας προς τα instances και να τρέξει το query σε όλα μαζί.

Για να το κάνουμε αυτό πάμε στο View, Registered Servers:

Εκεί επιλέγουμε κάτω απο το Database Engine, δεξί κλικ στο Local Server Groups και πατάμε New Server Registration…:

Έτσι αρχίζουμε να συμπληρώνουμε όλους τους servers που έχουμε. Χρειαζόμαστε μόνο το ServerName με την πόρτα του και τα credentials που συνδεόμαστε:

Αφού προσθέσουμε όσους θέλουμε απλά επιλέγουμε δεξί κλικ στον φάκελο που τους προσθέσαμε (Local Server Groups) και New Query:

Προσθέτοντας το query στο καινούργιο αυτό παράθυρο και εκτελώντας το, θα μας τρέξει σε ένα result set όλη την πληροφορία που θέλουμε:

*Επειδή στο μηχάνημα που έτρεξα το query δεν είχε πρόσβαση σε άλλα instances, απλά πέρασα στα registered servers τρεις φορές το ίδιο για το παράδειγμα.