“Optimize for ad-hoc workloads” – warum diesen Parameter aktivieren?

Heute wurde mir die Frage gestellt, ob ich den Parameter “Optimize for ad-hoc workloads” kenne bzw ob ich wüsste wofür dieser da ist.
Ja, ich kenne diesen Parameter und ich weiß auch, dass wir ihn in unseren Best-Practises aktivieren. Also muss ich mich schon einmal damit beschäftigt haben… scheint aber schon lange her zu sein, denn ich brachte die Fakten einfach nicht mehr zusammen. Dieser Parameter ist auch schon recht alt, in den Dokumentation des SQL Server fand ich den Parameter erstmalig für den SQL Server 2008, da kann man mal die Details “vergessen”.

“Optimize for ad-hoc workloads”

Aber vielleicht sollte ich erst einmal klären, was ad-hoc in diesem Zusammenhang bedeutet… Man findet diesen Begriff in Verbindung mit dem SQL Server relativ häufig, aber macht man sich dann Gedanken was im Detail damit gemeint sein könnte, dabei ist es relativ einfach.

Ad-hoc Queries sind einfache Abfragen, also zum Beispiel Abfragen die ein Benutzer “einfach mal so” in seinem Abfrage-Tool erstellt und ausführt. Also Abfragen die irgendwie immer anders aussehen, auch wenn sich vielleicht nur Werte oder die Sortierung geändert haben, somit kann der Ausführungsplan für diese Query erst bei Ausführung ermittelt bzw erstellt werden.
Als Gegensatz zu ad-hoc Queries sind Stored-Procedures und Prepared-Statements zu nennen, da die Abfrage bereits zum Zeitpunkt der Erstellung bekannt ist und entsprechend frühzeitig in Ausführungsplan erstellt wurde. Man hat hier eine fest definierte Abfrage/Funktion erstellt, welche nur (grob gesagt) einem verallgemeinerten Zweck dient, um zum Beispiel aus dieser Tabelle eine Auswahl erstellt und an welche nur die ID als Variable übergeben wird. Bei Ausführung der Stored Procedure wird dann immer der bereits bekannte Ausführungsplan herangezogen.

Was passiert bei der Ausführung von ad-hoc Queries?

Jedesmal wenn eine ad-hoc Query ausgeführt wird, sucht der SQL Server (die Datenbank Engine – der Optimizer) in seinem Plan Cache ob es zu dieser Query bereits einen Ausführungsplan gibt. Gibt es keinen gespeicherten Ausführungsplan so wird ein neuer für diese Abfrage berechnet und im Plan Cache gespeichert. Wenn nun eine Applikation immer wieder ein und dieselbe Abfrage ausführt, zum Beispiel für jede Kategorie in einem Kategorienbaum alle zugehörigen Produkte sucht:

Select * from Products where category_id = 1 and stock_value > 0
Select * from Products where category_id = 2 and stock_value > 0
Select * from Products where category_id = 3 and stock_value > 0
...

Da die Anfrage immer “anders” ist, als die vorherige Abfrage, wird für jedes dieser Statements ein eigener Ausführungsplan im Plan Cache erstellt und belegt dort Platz. Im Idealfall kann man die Applikation anpassen und die Verarbeitung anpassen bzw optimieren, was aber tun wenn man dies nicht kann?

Beispiel eines Autoteile-Händlers: Gewisse Produkte gibt es für gewisse Marken => Ermittel alle Teile aller Kategorien für die Marke XYZ.
Jede Ergebniszeile nimmt einen gewissen Bereich im Speicher des SQL Servers ein, bei ~100.000 Kategorien/Teilen pro Automarke…
Irgendwann ist der Plan Cache voll und der SQL Server benötigt neuen Platz. Da der Plan Cache aber begrenzt ist, bleibt nur die Möglichkeit des “Aufräumens”. Aber woran orientiert sich der SQL Server beim Aufräumen im Plan Cache?

Wie optimiert der Parameter die Ausführung der ad-hoc Queries?

Durch die Aktivierung des Parameters “Optimize for ad-hoc workloads” (von 0 nach 1) ändert sich die Art der Kompilierung eben dieser ad-hoc Queries bzw deren Speicherung im Plan Cache. Während vorher für jede Abfrage ein Ausführungplan kompiliert wurde und anschließend gespeichert wurde, wird jetzt nur noch ein sogenannter Plan Stub gespeichert, dadurch reduziert sich der Speicherbedarf enorm. In Beispielen die ich hierzu gefunden habe, werden Reduzierungen von ~90% erreicht. (Variiert von System/Abfrage – kann mit Statements ermittelt bzw überprüft werden) Diese Einsparung wird dadurch erreicht, dass nur noch ein Hash-Wert für diese Ad-hoc Queries ermittelt / gespeichert wird und kein vollständiger Ausführungsplan. Wird jetzt eine Abfrage ein zweites Mal ausgeführt, so wird auch hier erst einmal nur der Hash-Wert für die Ausführung berechnet und dann im Plan Cache gesucht. Ist ein gesuchter Ausführungsplan bereits vorhanden so wird nun der endgültige Ausführungsplan vollständig kompiliert und gespeichert. Durch diese optimierte Speicherung und Verarbeitung von Abfragen bzw Ausführungsplänen wird a.) Speicher im Plan Cache für relevantere Abfragen frei gehalten und gleichzeitig die Kompellierungslast von den Prozessoren genommen.

Fazit:

Wenn eine (oder mehrere) Applikation immer wieder eine Vielzahl von ad-hoc Queries ausführt, dann sollte man diesen Parameter “Optimize for ad-hoc workloads” definitiv aktivieren. Im Grunde sollte dieser Parameter bei allen SQL Servern (es gibt auch Ausnahmen 😉 ) aktivieren, da die meisten Applikationen eine Vielzahl von immer wieder minimal andere Abfragen ausführen. Hier unterstützt diese Speicher-Optimierung den SQL Server bzw die Performance erheblich.

Wer sich selber mal ein Bild machen möchte, sollte sich die Abfragen von Kimberly Tripp anschauen, um sich einen Überblick über die jeweilige Auslastung bzw Mengenverhältnisse seines SQL Servers zu machen (Beispiel das Beitragsbild – hier ist der Unterschied zwischen ad-hoc und prepared Statements nicht so sonderlich – ich habe aber auch keinen Vorher/Nachher-Vergleich)

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO
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.