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

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.