TSQL – MaxDOP und NUMA – Empfehlungen

Letzte Woche habe ich einen Beitrag verfasst zum Thema, wie ermittelt man ein paar wichtige Eckpunkte des SQL Servers wie Anzahl CPU, Anzahl Schedulers, Min.- und Max Memory oder maxDOP über den SQL Server mit einem kleinen TSQL Skript…

Irgendwie hat mir die Empfehlung für den „optimalen maxDOP“ keine Ruhe gelassen und ich habe nochmal zahlreiche Blogbeiträge und Artikel in der MSDN dazu gelesen, anscheinend hat sich in den letzten Jahren ein wenig was dazu geändert. Vielleicht liegt es an der fortschreitenden CPU-Architektur oder an der zunehmenden Virtualisierung von SQL Servern, je nach dem auf welcher Seite man liest (im Vergleich zu vor 2-3 Jahren) werden andere Herangehensweisen beschrieben.

Laut Microsoft ( und auch die Darstellung in anderen Blogs/Foren lauten hier gleich ) wird folgendes empfohlen:

SQL Server 2005 and later versions

Server with single NUMA node Less than 8 logical processors Keep MAXDOP at or below # of logical processors
Server with single NUMA node Greater than 8 logical processors Keep MAXDOP at 8
Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

Quelle:https://support.microsoft.com/en-us/kb/2806535

Wie kann man diese Werte aber nun in mein Skript von gestern einpflegen, dass eine Empfehlung für den optimalen maxDOP („Maximum Degree of Parallelism“) dabei herauskommt?

Vorraussetzungen für die Ermittlung eines optimalen maxDOP Wertes

Welche Parameter bzw Werte muss man beachten, wenn man gemäß obiger Tabelle Empfehlungen für einen optimalen Wert für den Maximum Degree of Parallelism geben möchte?
Der optimale maxDOP sollte maximal 8 betragen, hierzu ist es aber entscheidend zu wissen, wieviele logische Prozessoren pro NUMA Node dem SQL Server zugewiesen sind. Bei Servern, die mehrere NUMA Nodes haben und NUMA aktiviert wurde und auch noch Hyper-Threading nutzt, sollte der Wert für maxDOP die Anzahl der physischen Prozessoren pro NUMA-Knoten nicht überschreiten. Somit ergibt sich Notwendigkeit die folgenden Werte zu ermitteln, um damit weitere Berechnungen durchzuführen, um sich dem optimalen Wert anzunähern.

  • Anzahl der NUMA-Nodes für den jeweiligen SQL Server
  • Anzahl der logischen Prozessoren bzw Kerne
  • Ist Hyperthreading aktiviert

Wieviele NUMA – Nodes hat der SQL Server?

Kann man diese Werte auch direkt aus dem SQL ermitteln? Steht das irgendwo drin? Vielleicht über eine DMV zu ermitteln?

Zumindest steht es beim Starten des SQL Servers im Log wieviele NUMA Nodes der Server hat, dann wird es doch sicherlich auch im laufenden Betrieb verfügbar sein…

NUMA Nodes im SQL Server Log

Und tatsächlich konnte ich folgende Abfrage der System-Views ermitteln, die hier behilflich ist:

select * from sys.dm_os_memory_nodes where memory_node_id < 64
idealer noch 

select count(*) from sys.dm_os_memory_nodes where memory_node_id < 64

TSQL Abfrage NUMA Nodes

Wie wir sehen können, handelt es sich bei meiner Testmaschine um einen SQL Server mit nur einer NUMA-Node, wenn wir uns jetzt die obige Tabelle anschauen, dann bleiben uns nur noch zwei Möglickeiten für den „optimalen“ Max Degree of Parallism (maxDOP). Aber halt STOP, welche Zeile muss ich nun auswählen? Wieviele logische Prozessoren hat mein SQL Server aber bzw wie viele kann er nutzen?

Also wieder eine weitere einfache Abfrage ausgeführt:

SELECT cpu_count FROM sys.dm_os_sys_info

Wie nicht anders zu erwarten, erhalte ich für meinen Test SQL Server einen CPU Count von 4 logischen Prozessoren. Mit diesen beiden Werten, also Anzahl logischer Prozessoren und Anzahl NUMA Nodes kann man eine Entscheidung treffen für einen „optimalen“ Wert für den Konfigurationsparameter „maxDOP“. Laut obiger Tabelle muss der Wert genau der Anzahl der logischen Prozessoren oder unterhalb davon liegen.

Aber auch hier haben schlaue MVPs sich zahlreiche Gedanken gemacht, so dass ich diese nur aufgreifen muss. Bzw eigentlich nur sagen kann, es handelt sich bei diesen Empfehlungen ganz stark um Aussagen, die jeder eigentlich nur für seinen eigenen Server für seine ganz eigene Applikation ermitteln kann. Denn gerade der Wert „maxDOP“ und der dazugehörige Parameter „Cost Threshold for Parallelism“ sind sehr relevant für die Performance des SQL Servers. Man kann zwar eine Aussage/Empfehlung treffen => „Stellt alle eure SQL Server auf einen Wert von maxDOP von 6, das passt schon…“ Meine Vorschläge oder die der anderen SQL Schreiber sind nur grobe Vorgaben/Best Practises, die man als Anhaltspunkt oder Startpunkt für weitergehende Tests verwenden kann.

Aber weiter im Kontext… zahlreiche SQL Server Cracks haben sich tagelang wenn nicht wochenlang mit dem „optimalen“ Wert für maxDOP auseinander gesetzt und getestet und getestet und getestet. 😉

Herauskam, dass man bei einer Vielzahl von Tests festgestellt hat, dass die Hälfte der obigen Annahme für viele Systeme die beste Voraussetzung ist. Also um bei meinem Test SQL Server zu bleiben => Anzahl der logischen Prozessoren zu Anzahl NUMA Nodes ergab bei mir : 1 NUMA Node und 4 logische Prozessoren, daraus ergäbe sich dann ein „optimaler“ Wert für maxDOP von 2. In einigen Blogs habe ich den Verdacht gelesen, dass es Zusammenhänge mit Hyperthreading geben könnte. Man kann es nicht genug sagen, testet diesen Wert auf eurem System… testen, testen, testen.

In diesem Zusammenhang – wie auch bereits weiter oben angemerkt – gibt es noch einen weiteren Wert in der Konfiguration des SQL Server, der in direktem Zusammenhang mit maxDOP steht => „Cost Threshold for Parallelism“
Dieser Wert ist auf jeden Fall auf aktuelleren Systemen mit aktuellen Prozessoren weit wichtiger für die Performance eines SQL Servers, aber dazu gibt es demnächst einen eigenen Blogbeitrag 😉

Fazit:

Wenn man unbedingt eine grundlegende Formel zur Konfiguration des Wertes maxDOP erstellen möchte, so sollte diese doch heißen (neben testen, testen und nochmals testen) :

Wenn die Anzahl der NUMA Nodes = 1 ist und die Anzahl der logischen Prozessoren  <= 8 dann setze maxDOP auf 4, ansonsten wenn die Anzahl der logischen Prozessoren größer 8 ist, dann setze maxDOP auf 8.

Für mehr als eine NUMA Node gilt die Berechnung ebenfalls nur dann eben für die Anzahl der logischen Prozessoren pro NUMA Node.

Wenn das alles nichts hilft und die Anzahl der logischen Prozessoren <= 8 liegt, dann Anzahl der logischen Prozessoren / 2.

Genau so habe ich das (hoffentlich) auch in meinem Statement dargestellt.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

TSQL – wichtige Informationen über die SQL Server Instanz ermitteln

Wir alle kennen es, wir sollen Informationen zu unseren SQL Servern liefern… entweder der Kunde oder ein Kollege will schnell einen Überblick über die wichtigsten Parameter des SQL Servers haben… Was liegt da näher als mal eben schnell ein TSQL-Statement auf dem angefragten Server auszuführen?

Die letzten solcher Anfragen wollte immer die selben Informationen zusammengefasst bzw ermittelt haben… als habe ich mir mittels TSQL ein „kurzes“ Skript zusammengestellt und nach meinen Bedürfnissen angepasst. Ich bzw wir verwenden dieses Skript nun auch für unsere eigene Dokumentation 😉

  • Server- / Instanznamen
  • letzter SQL Server Engine Start
  • welche SQL Server Edition / Version ist im Einsatz
  • Welche Werte für CPU / Hyperthreads / maxDOP werden genutzt
  • Konfiguration der RAM Nutzung
  • Anzahl / Namen / Größen der User-Datenbanken
SET NOCOUNT ON;
Go
-- Setting value of 'show advanced options' to 1
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- DECLARATION
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, @scheduler_count int, @hyperthread_ratio int
DECLARE @CM Table ( [Index] int, Name nvarchar(1000) NOT NULL, Internal_Value int, Character_Value nvarchar(1000) )

-- SET Current Date/Time
SELECT @date = getdate()

-- Get SQL Server Version
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,13))

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,30,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2012',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2014',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2016',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

-- Get Informations and Calculation on MaxDOP
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

-- Last SQL Server Start Date/Time
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- Get Informations on CPU, Schedulers and Memory
Insert into @CM exec xp_msver select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@scheduler_count=(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) ,@cpu_count=cpu_count ,@hyperthread_ratio=hyperthread_ratio ,@optimal_maxdop=case when @scheduler_count >= 8 then 4
when @scheduler_count > 8 then 8
else CEILING(@scheduler_count*.5) end
from sys.dm_os_sys_info;

-- Main Statement
SELECT
CONVERT(CHAR(50), SERVERPROPERTY('MachineName')) AS [Hostname]
,isnull(CONVERT(CHAR(50), SERVERPROPERTY('InstanceName')), 'Default') [InstanceName]
,@@servername as [Servername]
,getdate() as [Current Date/Time]
,@sqlstart as [last SQL Srv Start]
,serverproperty('Edition') as [SQL Edition]
,@ver as [SQL Version]
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as [IsCluster]
,@cpu_count/@hyperthread_ratio as [CPU Count]
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) as [No of Scheduler] ,@optimal_maxdop as [Optimal MDP] ,@Mem as [Memory (MB)] ,(SELECT value_in_use FROM sys.configurations WHERE name like 'min server memory (MB)') as [actual min memory] ,(SELECT value_in_use FROM sys.configurations WHERE name like 'max server memory (MB)') as [actual max memory] ,(select COUNT(name) AS MB from master..sysdatabases) AS [No Of DBs] ,(select SUM(size*8/1024) AS MB from master..sysaltfiles where fileid = 1 and dbid > 4) AS [Overall Database Size (MB)]
Go

-- Adding Informations for all User Databases
-- Declaration
DECLARE @SumDBSize VARCHAR(2)
DECLARE @temp INT
DECLARE @DBSize INT
DECLARE @Servername varchar(100)

-- Get/Set Servername
Set @Servername = ''
If @Servername = '' Set @Servername = @@SERVERNAME

-- Calculating DBSize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[master].[dbo].[DatabaseFiles]'))
Set @temp = (SELECT round(SUM(db.size)/1024/1024,0) FROM [master].[dbo].[DatabaseFiles] as db where DATEDIFF(day, db.CreationDate, GETDATE()) = 0)
else Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)
Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)

Select
@Servername as 'Servername',
DB_NAME(sys.master_files.database_id) as 'DBName',
sum(sys.master_files.size * 8 / 1024 ) as 'DB-Size/MB',
sum(sys.master_files.size * 8 / 1024 / 1024 ) as 'DB-Size/GB'
from sys.master_files INNER JOIN sys.databases ON DB_NAME(sys.master_files.database_id) = DB_NAME(sys.databases.database_id) where DB_NAME(sys.master_files.database_id) not in ('master','model','msdb','tempdb') group by sys.master_files.database_id;
go

-- Reset value of 'show advanced options' to 0
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

Vielen Dank an  für seinen Beitrag zu „Best Practices Empfehlungen für MaxDOP

Dieses TSQL kann natürlich jederzeit frei genutzt werden, vorab in einem Testsystem getestet werden, Gefahr/Risiko trägt der Ausführende.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

T-SQL Tuesday #68: Just Say No to Defaults – Optimierung der SQL Server Defaults

Ein weitere TSQL2sDay, erneut schreibt sich die SQL Server Community die Finger zu einem bestimmten Thema wund, diesmal dreht sich alles um das Thema „Just say No to Defaults“ (Optimierung der SQL Server Defaults).

Einen der wichtigsten Parameter (aus meiner Betriebssicht) sind die Einstellungen rund um die Nutzung des Arbeitsspeichers (Server Memory Options) => Minimum und Maximum Server Memory (in MB).

TSQL2sDay #68 Defaults of SQL Server Memory Options Configuration

Im Default werden diese Parameter von Microsoft suboptimal ausgelegt, der Parameter „Minimum Server Memory“ ist dabei gar nicht so dramatisch, denn dieser wird auf 0 gesetzt. Aber der Parameter „Maximum Server Memory“ wird auf 2147483647MB gesetzt (das entspricht einer maximalen Speichernutzung von 2048 Terabyte (!!!)).
Ok, man könnte das so interpretieren, dass der SQL Server gerne den vollen zur Verfügung stehenden Arbeitsspeicher ausnutzen darf, aber ist das sinnvoll???

Um die jeweiligen optimalen Werte einstellen zu können, muss man einige wissen bzw beachten.
Grundsätzlich konfiguriert man den SQL Server mittels dieser beiden Werte die Höhe des vom SQL Server zu nutzenden Arbeitsspeicher (in Megabytes) pro Instance.

Eigentlichkeit kann der SQL Server die Speicher Nutzung recht gut dynamisch verwalten, heißt im Rahmen seiner Grenzen nimmt sich der SQL Server Engine Prozess immer soviel wie er gerade braucht und gibt diesen nach Beendigung seiner Tätigkeiten wieder frei.

Den Parameter „Minimum Server Memory“ betrachten wir erstmal nicht, sondern schauen uns den „Maximum Server Memory“ Wert genauer an.

Maximum Server Memory
Wieviel Arbeitsspeicher ist überhaupt in den Server installiert worden?
Welche Prozesse benötigen auf einem Server eigentlich Arbeitsspeicher, um performant laufen zu können?

  • das Betriebssystem (OS) auf jeden Fall
  • der SQL Server, in der Hauptsache die DB Engine
    • ggfs noch SSIS, SSAS oder SSRS, Fulltext-Daemon
  • im ungünstigsten Fall auch noch die Applikation

Wir empfehlen immer Applikation und Datenbank zu trennen, genauso empfehlen wir (je nach System-Größe und Anforderung) SSRS und SSAS auf eigene Server auszulagern. Ansonsten müsste man eben für diese Prozesse auch einen gewissen Anteil des Arbeitsspeichers reservieren, um hier keine Probleme zu bekommen.
Auf welchen Wert kann man nun diesen Parameter setzen damit der SQL Server ausreichend RAM zugewiesen bekommt, die anderen Applikationen und das Betriebssystem noch ausreichend Arbeitsspeicher erhält.

Betrachten wir hier also verschiedene Szenarien:

Szenario 1:
nur DB-Engine installiert
RAM-Verbraucher sind daher nur das Betriebssystem und die DB-Engine des SQL Servers
In der Regel reservieren wir immer 10 Prozent des RAM für das Betriebssystem mindestens aber 1 GB.
Damit ich nicht immer wieder rechnen muss habe ich mir folgendes kleines Hilfsskript „gebastelt“, um eine initiale Einrichtung des SQL Server zu ermöglichen. Im Laufe der „Einschwingphase“ sollte man sich das Nutzungsverhalten der Applikation bzw des SQL Servers anschauen, um hier eventuell nachbessern zu können. (Testen, Testen, Testen, Nachbessern, Testen… 😉 )

Declare @num_instances as integer, @total_RAM as float, @factor as float, @min_memory as float, @max_memory as float

-- Set Number of Instances
SELECT @num_instances = 1

-- Get Total RAM-Size
select @total_RAM = (mem.total_physical_memory_kb/1024) from sys.dm_os_sys_memory mem

-- Set Factor
if (@total_RAM <= 4096) set @factor = 0.75 else set @factor = 0.9

-- Set Total RAM for each Instance
Set @total_RAM = @total_RAM/@num_instances

-- Get max Memory for SQL-Server
select @min_memory = round((@total_RAM*@factor)/2,0)
select @max_memory = round((@total_RAM*@factor),0)

-- SET min & max memory for SQL Server
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'min server memory (MB)', @min_memory;
EXEC sys.sp_configure N'max server memory (MB)', @max_memory;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;

Szenario 2:
DB-Engine mit SSIS oder SSRS installiert

Hier kann man leider nicht so pauschal vorgehen, da SSIS und SSRS auch ihren Anteil am Arbeitsspeicher haben wollen, hier hilft nur erstmal einen Wert setzen und ausprobieren. Gibt es Engpässe in der DB-Engine während des normalen Tagesgeschäft, wenn die Verarbeitung über SSIS läuft oder Reports aktualisiert werden?

Grundsätzlich empfehle ich erstmal sowieso eine größere Ausstattung des Servers falls mehrere Services zusätzlich installiert werden sollen. Dadurch kommen wir auf einen annähernd gleichen Wert für die DB-Engine als wenn die Dienste nicht mitinstalliert wurden oder (je nach Anforderungen) erhält die SQL Server Engine mehr Arbeitsspeicher als zuvor.

Hier bleibt einem – abhängig von Ausgangslage, Ausstattung des Servers und Anforderungen leider nichts anderes übrig als Annahmen zu treffen und zu testen ggfs nachzubessern.

Auf kleinen Systemen kommt SSRS vielleicht mit 1 GB aus, SSIS vielleicht mit 3GB, so dass OS und DB Engine noch mit 3 bzw 1 GB auskommen.
Aber wie bereits mehrfach erwähnt, hängt dies von einer Vielzahl von Faktoren ab, daher kann man nur testen, anpassen, testen.

Szenario 3:
DB-Engine mit SSAS installiert

Per Default ist der SQL Server Analysis Service (SSAS) auf eine maximale Speichernutzung von 80% des Gesamt-RAM (HardMemoryLimit), wobei es auch einen Parameter gibt, der den unteren Wert (LowMemoryLimit – Default 65%) definiert.
Hier muss man also die Werte für die DB-Engine als auch für den Analysis Service anpassen.

Empfehlung hierzu:
Man verwende mein Skript von oben, setze aber die Anzahl der Instanzen auf 2 (um die maximale Auslastung der DB Engine auf annähernd 50% zu setzen) und begrenze den Wert für das HardMemoryLimit des Analysis Services auf 50%, LowMemoryLimit auf 30%. So kommen sich die drei beteiligten Prozesse nicht in die Quere und theoretisch haben alle drei Prozesse ausreichen Arbeitsspeicher.

Memory Auslastung überprüfen
Jonathan Kehayias hat einen sehr guten Artikel über das Thema „How much memory does my SQL Server actually need?“ geschrieben, in dem er sehr gut die Tiefen der DB Engine erläutert, wie der Arbeitsspeicher aufgeteilt wird und schreibt seine Erkenntnisse daraus nieder.

Sakthivel Chidambaram hat in seinem MSDN Blog auch einen schönen Beitrag zum Thema „Monitoring SQL Server Memory Usage“, das Statement welches er dort veröffentlicht hat, möchte ich euch nicht vorenthalten:

/*============================================================================
 Script to report Memory usage details of a SQL Server instance
 Author: Sakthivel Chidambaram, Microsoft http://blogs.msdn.com/b/sqlsakthi 

 Date: June 2012
 Version: V2
 
 V1: Initial Release
 V2: Added PLE, Memory grants pending, Checkpoint, Lazy write,Free list counters

 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
 ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
 TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
 PARTICULAR PURPOSE. 

============================================================================*/ 
-- We don't need the row count
SET NOCOUNT ON

-- Get size of SQL Server Page in bytes
DECLARE @pg_size INT, @Instancename varchar(50) 
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' 

-- Extract perfmon counters to a temporary table
IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters

-- Get SQL Server instance name
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio' 

-- Print Memory usage details
PRINT '----------------------------------------------------------------------------------------------------' 
PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' 
PRINT '----------------------------------------------------------------------------------------------------' 
SELECT 'Memory visible to the Operating System' 
SELECT CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB], CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB], CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] FROM sys.dm_os_sys_info 
SELECT 'Buffer Pool Usage at the Moment' 
SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB FROM sys.dm_os_sys_info 
SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Total Server Memory (KB)' 
SELECT 'Memory needed as per current Workload for SQL Server instance' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Target Server Memory (KB)' 
SELECT 'Total amount of dynamic memory the server is using for maintaining connections' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Connection Memory (KB)' 
SELECT 'Total amount of dynamic memory the server is using for locks' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Lock Memory (KB)' 
SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'SQL Cache Memory (KB)' 
SELECT 'Total amount of dynamic memory the server is using for query optimization' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Optimizer Memory (KB) ' 
SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Granted Workspace Memory (KB) ' 
SELECT 'Total Amount of memory consumed by cursors' 
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total' 
SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages' 
SELECT 'Number of Data pages in the buffer pool' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages' 
SELECT 'Number of Free pages in the buffer pool' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages' 
SELECT 'Number of Reserved pages in the buffer pool' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages' 
SELECT 'Number of Stolen pages in the buffer pool' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages' 
SELECT 'Number of Plan Cache pages in the buffer pool' 
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references' 
SELECT cntr_value as [Page Life in seconds],CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END as 'PLE Status' FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy'
SELECT 'Number of requests per second that had to wait for a free page' 
SELECT cntr_value as [Free list stalls/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free list stalls/sec'
SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed' 
SELECT cntr_value as [Checkpoint pages/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Checkpoint pages/sec'
SELECT 'Number of buffers written per second by the buffer manager"s lazy writer'
SELECT cntr_value as [Lazy writes/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Lazy writes/sec'
SELECT 'Total number of processes waiting for a workspace memory grant'
SELECT cntr_value as [Memory Grants Pending] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Pending'
SELECT 'Total number of processes that have successfully acquired a workspace memory grant'
SELECT cntr_value as [Memory Grants Outstanding] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

Anhand dieses Statements kann man sehr viele hilfreiche Informationen rund um die Memory Auslastung und Eckdaten zur Analyse ermitteln, um sich selber ein besseres Bild machen zu können wo der Engpass des SQL Server zu suchen ist.

TSQL2sDay150x150

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

TSQL #66 – Monitoring Backup Lösung – Backup Historie

Aus mehrfachem Anlass möchte auch ich etwas zum Thema Backup-Historie schreiben, erst einmal weil Catherine Wilhelmsen den TSQL Tuesday #66 zum Thema Monitoring gehostet hat und weil Thomas Larock gerade heute eine ähnliche Lösung vorgestellt hat.

Wir DBAs kennen das leidige Thema Backup-Überwachung… lief es oder lief es nicht, kann ich meinen SLA dem Kunden gegenüber noch halten?
Wir haben bei einer Vielzahl von SQL Servern vertraglich zugesagt täglich das Monitoring zu prüfen, es muss mindestens ein Full-Backup innerhalb der letzten 24 Stunden erstellt worden sein. Ist dies nicht der Fall wird automatisiert – aus dem Reporting Tool heraus – ein Ticket „Bitte Backup überprüfen“ erstellt.

Daher hat mein Statement auch diese Einschränkungen, welche sich natürlich individuell je nach Bedürfnissen anpassen lassen.
Idealerweise schreibt man das Ergebnis der Abfrage in eine Tabelle um historische Daten über das Backup zu sammeln, so kann man aus den jeweiligen Backup-Größen einen Trend ermitteln.

/*
Get all Backup History Data - used at Customer XYZ for Reporting 
*/

SELECT 
  s.name as [Database], 
  (select bmf.physical_device_name from msdb..backupmediafamily bmf where bmf.media_set_id = bs.media_set_id) as [Backup_Path_File],
  bs.backup_start_date as DATE_BEGIN, 
  bs.backup_finish_date as DATE_END, 
  CASE WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 0
    ELSE 1
  END as [Status],
  CASE WHEN bs.backup_start_date > DATEADD(dd,-1,getdate())
    THEN 'Backup is current within a day'
     WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 'Backup is current within a week'
    ELSE '*****CHECK BACKUP!!!*****'
  END as [Message],
  convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as [Backup_Volume],
  GETDATE() as [Date_Checked]
from 	master..sysdatabases s LEFT OUTER JOIN	msdb..backupset bs ON s.name = bs.database_name
  AND bs.backup_start_date = (SELECT MAX(backup_start_date) FROM msdb..backupset WHERE database_name = bs.database_name AND type = 'D')
WHERE	s.name <> 'tempdb'
ORDER BY s.name
GO

Mit dem Ergebnis der Abfrage kann man nun viele Dinge machen, zum Beispiel anhand der Spalte „Status“ die jeweilige Zeile farbig markieren, um die fehlerhaften Sicherungen besser kenntlich zu machen. Oder man passt die Betreffzeile einer Status-Mail entsprechend an.
Wir haben eine Kombination der SQL Server Alerts mit obigem Abfrage-Skript, welches uns dann darüber informiert, wo wir nachzubessern haben.

Backup History Monitoring

Zusätzlich empfehle ich die Einrichtung des SQL Alerts „SQL Server Alert System: ‚Severity 016‘ occurred on SERVER_XYZ“ mit Mail-Alarmierung (hierzu siehe auch diesen Blogbeitrag zu SQL Server Alerts).

Nachtrag:
Ich bin in der letzten Woche über ein Storage-Problem gestolpert, wo dieses Skript auch nicht wirklich sauber funktionierte, da das Backup zwar lief aber leider sehr sehr langsam, so dass es nicht innerhalb von 40 Minuten durch war (wie gewohnt) sondern leider rund 24 Stunden lief… dadurch war die Analyse mittels Skript nicht immer zu 100% aussagekräftig, aber hier wusste man ja um das Problem… 😉

Backup

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.