Power BI – mein leichter Einstieg

14. Januar 2016 Björn Peters

Ich habe mich heute das erste mal mit einem ernsthaften Ansatz mit dem Power BI Desktop auseinander gesetzt.
Nein, nichts kompliziertes, aber für mich ausreichend und ein Einstieg in die Materie… also erwartet bitte (noch) keine komplexen Erläuterungen 😉

Power BI Desktop runtergeladen und installiert, dass war recht einfach und bekommen Sie garantiert hin.

Das Tool das erste Mal gestartet und ein simpler und selbsterklärender Dialog erscheint … natürlich will ich Daten laden 😉

Im Rahmen eines Kunden-Projektes sollten wir einfache Performance-Daten ermitteln (alle 5 Minuten die Laufzeit eines definierten Insert-Statements ermitteln), diese Daten kann man ja auch historisch sammeln und als „Trendbarometer“ für die Performance Auslastung des SQL Servers nutzen. Dazu müssen die Daten aber entsprechend gesammelt und grafisch aufbereitet werden… was liegt da näher sich mit dem neuen Power BI Desktop zu beschäftigen.

Da die Daten im Kundennetz liegen, musste ich den Zwischenschritt über eine Excel-Datei als Datenquelle machen… also Excel ausgewählt, Datei geöffnet, Tabelle gewählt => die Vorschau hatte mir genau meine Daten gezeigt, ich war erstmal zufrieden, abschließend „Daten laden“.

Nun wählt man einen Diagramm-Typen aus, in meinem Fall ein „Liniendiagramm“.
Jetzt braucht man nur noch die Datenfelder, Measures oder berechnete Spalten dem Diagramm hinzufügen, je nach Bedarf eben in die dafür vorgesehenen Felder.

Für mein Beispiel:

Achse => Check_Timestamp
Legende => bleibt vorerst einmal leer
Werte => Runtime

Damit haben einen ersten „Wurf“ und auch die erste grafische Darstellung meiner Werte.

Da es sich um viel zu viele Werte handelt (daher das kleine Warndreieck oben links im Diagramm) muss eine Unterteilung her, hierzu füge ich eine zusätzlich berechnete Spalte hinzu, die mir nur Werte für bestimmte Tage ausgibt.

Mittels rechte Maustaste in der Felderspalte oder die Menüleiste erhalten wir eine neue berechnete Spalte

Datum = LEFT(Tabelle1[Check_Timestamp];10)

Diese neue Spalte hänge ich als zusätzlichen Filter ein, dadurch kann ich mir immer nur einen bestimmten Tag oder mehrere Tage auswählen und analysieren.

Sicherlich mag es elegantere und saubere Lösungswege geben, dies ist aber mein erster Versuch und an diesem wollte ich euch/Sie teilhaben lassen. In Zukunft werde ich noch mehr über dieses grandiose Tool Power BI berichten, dann sicherlich mit „hübscheren“ Lösungen und Analysen.

Björn Peters

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.

Windows Cluster patchen und automatisch Verteilung wieder herstellen

21. Dezember 201519. Dezember 2015 Björn Peters

Jeder kennt es, Server und deren Betriebssysteme in produktiven Umgebungen (in Testumgebungen natürlich auch) müssen regelmäßig gepatcht werden. Solange es sich um Standalone-Systeme handelt gibt es selten Probleme…
Wenn man aber Ressourcen auf einem Cluster verteilt hat, dann sind so manche OS-Kollegen leider so uneinsichtig oder engstirnig, dass man sich ganz die Termine merken muss, wann diese Kollegen wieder das SQL Server Cluster patchen wollen.

Ich habe leider immer wieder die Erfahrung machen müssen, dass die Kollegen das Cluster nur als Aktiv-Passiv-System (=> alles läuft auf einem Knoten und schwenkt im Fehlerfall auf den Ersatzknoten) ansehen, ein Cluster kann aber eben auch als Aktiv-Aktiv-System betrieben werden. Ok, dann muss man darauf achten, dass im Fehlerfall alle Ressourcen auf einer Seite gemeinsam Lauffähig sind. Man muss sich also im Vorweg Gedanken machen zur optimalen Konfiguration (beispielhaft Min./Max Memory) um alle SQL-Server auf einer Cluster-Knoten betreiben zu können.

Aber was passiert mit den Cluster-Ressourcen nach einem Patch-Durchgang? Die oben bereits genannten Kollegen haben nur ihren Part im Kopf und patchen einfach nur die Systeme, heißt sie machen einen Knoten des Cluster frei und patchen diesen freie Knoten. Um natürlich auch Ressourcen und Kosten zu sparen, werden solche Dinge automatisiert in der Nacht durchgeführt.

Aber was passiert nach dem Patchen mit dem Cluster? Hat sich jemand die ursprüngliche Verteilung gemerkt? Werden die Ressourcen wieder auf die ursprünglichen Knoten verteilt?
Aus leidlicher Erfahrung muss ich leider „Nein“ sagen…

Auswirkungen dieser Nicht-Beachtung

Auch wenn man sich im Vorwege ausreichend Gedanken gemacht hat, dann ist es (zumindest bei uns) so, dass die einzelnen Cluster-Ressourcegruppen nicht alle die volle RAM-Ausstattung nutzen können, da es hier oft zu Engpässen kommt.

Wir konfigurieren unsere geclusterten SQL-Server meist so, dass der Parameter „Min.Memory“ die Hälfte des Parameters „Max.Memory“ erhält. Sollten dann tatsächlich alle Instanzen auf einem Knoten laufen, kommt es immer wieder zu RAM-Engpässen da alle SQL Server Instanzen versuchen unter Last ihren konfigurierten Wert für „Max.Memory“ erreicht. Da dies dann nicht passt, „prügeln“ sich die einzelnen Instanzen eben um die knappen System-Ressourcen.

Also wäre es das Idealste nach dem Patchen eben genau die vorgesehen Verteilung wieder herzustellen, damit alle Ressourcen möglichst optimal arbeiten können. Aber wie erreicht man dies?
Auf jedem System wird Powershell mitgeliefert, mittels Powershell werden immer mehr Skripte geschrieben, so dass man sein ganzes System oder sein ganzen Windows-Cluster sehr gut mit Powershell administrieren kann.

In der Theorie klingt das alles so einfach:

Systemzustand ermitteln
Cluster-Ressource-Gruppen ermitteln
durch die einzelnen Gruppen durchlaufen
- Prefered-Owner der jeweiligen Ressource-Gruppe ermitteln
- mit dem aktuellen Owner vergleichen
- bei Abweichungen auf den Prefered-Owner schwenken
Fertig

Da ich diese Aktivität in der Hauptsache bei meinen Betriebssystem-Kollegen sehe, hatte ich dort einmal nachgefragt und erhielt Antworten, die mich nicht wirklich hoffen ließen. (ansatzweise habe ich das weiter oben schon angedeutet)
Also musste ich mir selber Gedanken machen… aber erst einmal googlen… warum sollte ich das Rad neu erfinden… 😉

hier die Lösung für die Cluster Umverteilung

Und siehe da, es hatte mir jemand die Arbeit abgenommen… 😉
Fermin Sanchez von der fsis GmbH hatte sich zu diesem Thema schon einmal Gedanken gemacht und (für mich) glücklicherweise in seinem Blog veröffentlicht.

Import-Module FailoverClusters
 
$clustergroups = Get-ClusterGroup | Where-Object {$_.IsCoreGroup -eq $false}
foreach ($cg in $clustergroups)
{
    $CGName = $cg.Name
    Write-Host "`nWorking on $CGName"
    $CurrentOwner = $cg.OwnerNode.Name
    $POCount = (($cg | Get-ClusterOwnerNode).OwnerNodes).Count
    if ($POCount -eq 0)
    {
        Write-Host "Info: $CGName doesn't have a preferred owner!" -ForegroundColor Magenta
    }
    else
    {
        $PreferredOwner = ($cg | Get-ClusterOwnerNode).Ownernodes[0].Name
        if ($CurrentOwner -ne $PreferredOwner)
        {
            Write-Host "Moving resource to $PreferredOwner, please wait..."
            $cg | Move-ClusterGroup -Node $PreferredOwner
        }
        else
        {
            write-host "Resource is already on preferred owner! ($PreferredOwner)"
        }
    }
}
Write-Host "`n`nFinished. Current distribution: "
Get-ClusterGroup | Where-Object {$_.IsCoreGroup -eq $false}

Da Fermin das Skript in seinem Blog „as-is“ veröffentlicht hat, musste ich das Skript natürlich erst einmal testen, hierzu habe ich die eigentliche „Move-Zeile“ auskommentiert. Das Ergebnis dieses Testlauf brachte genau das Ergebnis, was ich mir erhofft hatte. Im Rahmen des letzten Patchdays habe ich das Skript nach dem Patchen erfolgreich eingesetzt und konnte so Kundenbeschwerden über mangelhafte Performance des SQL-Server vermeiden.

Vielen Dank an Fermin und seine Leistung!

Björn Peters

Login Migration von SQL Server 2005 auf 2008R2 inklusive Passwörtern

9. Oktober 2015 Björn Peters

Gestern stand ich mal wieder vor der Aufgabe einen älteren SQL Server (SQL Server 2005) auf einen „neueren“ SQL Server (SQL Server 2008 R2) zu migirieren.

Auftrag:
Kopiere bitte die Datenbanken 1-10 auf den neuen Server
Kopiere bitte alle User/Logins auf den neuen Server inkl. aller Berechtigungen

Nun kennen wir eigentlich alle diese Login-Migrations-Skripte von Microsoft => Übertragen von Benutzernamen und Kennwörtern zwischen Instanzen von SQL Server oder aus dem KB-Artikel 246133

Aber bringt der uns wirklich weiter, wenn wir funktionierende Passwörter brauchen?
Kann man mit diesen Skripten erfolgreich die Berechtigungen kopieren?

Aus meiner Erfahrung… Leider NEIN.
Also bevor ich mich wieder „schwarz“ ärgere und hinterher mehr Arbeit mit dem Aufräumen und Passwörter korrigieren habe, habe ich erstmal fleißig gegoogelt, denn eigentlich müssten doch viel mehr Leute Probleme mit der Login Migration haben.

Beim Suchen bin ich über einen Beitrag „Transferring Logins to a Database Mirror“ und habe es ausprobiert.
Das Skript bzw die Stored Procedure macht genau das was ich gesucht habe bzw was ich gebraucht habe.

Allerdings fand ich keine Anleitung dazu, daher schreibe ich mein Vorgehen bis zur erfolgreichen Umsetzung auf. Es ist nicht wirklich kompliziert, aber man muss sich erst einmal durch den Skript Code wühlen, um zu verstehen was erwartet wird bzw wie vorzugehen ist.

mit dem Ziel-SQL-Server verbinden
den Quell-SQL Server als Linked-Server einrichten
das Skript ausführen, um die Stored Procedure anzulegen

die Stored Procedure mit Angabe des Quell-Servers ausführen

EXEC dba_CopyLogins @PartnerServer=LinkedServer, @Debug = 0 (Default = Ausführen) / 1 (Print-Ausgabe)

ggf. Fehlermeldungen / Hinweise überprüfen
Datenbanken und User/Logins auf Funktion prüfen

Fertig!

Das Skript lässt sich mit dem Parameter @Debug = 1 auch auf die reine Textausgabe umstellen, so dass man sich das Ergebnis erst einmal anschauen kann.
Die Stored Procedure legt die Logins mit der gleichen SID an und kopiert auch die Passwörter in korrekter Weise mit, somit dürfte es später auch keinerlei Schwierigkeiten mit „Orphaned Users“ geben.

Die aktuelle Version des Skriptes findet ihr natürlich beim Autoren, hier ist der Stand vom 08.Oktober 2015 veröffentlicht.

Use master;
Go

If Exists (Select 1 From INFORMATION_SCHEMA.ROUTINES
			Where ROUTINE_NAME = 'dba_CopyLogins'
			And ROUTINE_SCHEMA = 'dbo')
	Drop Procedure dbo.dba_CopyLogins
Go

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

Create Procedure dbo.dba_CopyLogins
	@PartnerServer sysname,
	@Debug bit = 0
As

Declare @MaxID int,
	@CurrID int,
	@SQL nvarchar(max),
	@LoginName sysname,
	@IsDisabled int,
	@Type char(1),
	@SID varbinary(85),
	@SIDString nvarchar(100),
	@PasswordHash varbinary(256),
	@PasswordHashString nvarchar(300),
	@RoleName sysname,
	@Machine sysname,
	@PermState nvarchar(60),
	@PermName sysname,
	@Class tinyint,
	@MajorID int,
	@ErrNumber int,
	@ErrSeverity int,
	@ErrState int,
	@ErrProcedure sysname,
	@ErrLine int,
	@ErrMsg nvarchar(2048)
Declare @Logins Table (LoginID int identity(1, 1) not null primary key,
						[Name] sysname not null,
						[SID] varbinary(85) not null,
						IsDisabled int not null,
						[Type] char(1) not null,
						PasswordHash varbinary(256) null)
Declare @Roles Table (RoleID int identity(1, 1) not null primary key,
					RoleName sysname not null,
					LoginName sysname not null)
Declare @Perms Table (PermID int identity(1, 1) not null primary key,
					LoginName sysname not null,
					PermState nvarchar(60) not null,
					PermName sysname not null,
					Class tinyint not null,
					ClassDesc nvarchar(60) not null,
					MajorID int not null,
					SubLoginName sysname null,
					SubEndPointName sysname null)

Set NoCount On;

If CharIndex('\', @PartnerServer) > 0
  Begin
	Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
  End
Else
  Begin
	Set @Machine = @PartnerServer;
  End

-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
		'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
		'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
		'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
		'And P.name <> ''sa''' + CHAR(10) +
		'And P.name Not Like ''##%''' + CHAR(10) +
		'And CharIndex(''' + @Machine + '\'', P.name) = 0;';

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;

-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
		'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_role_members RM' + CHAR(10) +
		'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals RoleP' +
		CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
		'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals LoginP' +
		CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
		'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
		'And LoginP.name <> ''sa''' + CHAR(10) +
		'And LoginP.name Not Like ''##%''' + CHAR(10) +
		'And RoleP.type = ''R''' + CHAR(10) +
		'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';

Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;

-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
		'	SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
		'	SubP.name Collate database_default,' + CHAR(10) +
		'	SubEP.name Collate database_default' + CHAR(10) +
		'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
		'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_permissions SP' + CHAR(10) +
		CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
		'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals SubP' + CHAR(10) +
		CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
		'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.endpoints SubEP' + CHAR(10) +
		CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
		'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
		'And P.name <> ''sa''' + CHAR(10) +
		'And P.name Not Like ''##%''' + CHAR(10) +
		'And CharIndex(''' + @Machine + '\'', P.name) = 0;'

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;

Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;

While @CurrID <= @MaxID
  Begin
	Select @LoginName = Name,
		@IsDisabled = IsDisabled,
		@Type = [Type],
		@SID = [SID],
		@PasswordHash = PasswordHash
	From @Logins
	Where LoginID = @CurrID;
	
	If Not Exists (Select 1 From sys.server_principals
				Where name = @LoginName)
	  Begin
		Set @SQL = 'Create Login ' + quotename(@LoginName)
		If @Type In ('U', 'G')
		  Begin
			Set @SQL = @SQL + ' From Windows;'
		  End
		Else
		  Begin
			Set @PasswordHashString = '0x' +
				Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
			
			Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, ';
			
			Set @SIDString = '0x' +
				Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
			Set @SQL = @SQL + 'SID = ' + @SIDString + ';';
		  End

		If @Debug = 0
		  Begin
			Begin Try
				Exec sp_executesql @SQL;
			End Try
			Begin Catch
				Set @ErrNumber = ERROR_NUMBER();
				Set @ErrSeverity = ERROR_SEVERITY();
				Set @ErrState = ERROR_STATE();
				Set @ErrProcedure = ERROR_PROCEDURE();
				Set @ErrLine = ERROR_LINE();
				Set @ErrMsg = ERROR_MESSAGE();
				RaisError(@ErrMsg, 1, 1);
			End Catch
		  End
		Else
		  Begin
			Print @SQL;
		  End
		
		If @IsDisabled = 1
		  Begin
			Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
			If @Debug = 0
			  Begin
				Begin Try
					Exec sp_executesql @SQL;
				End Try
				Begin Catch
					Set @ErrNumber = ERROR_NUMBER();
					Set @ErrSeverity = ERROR_SEVERITY();
					Set @ErrState = ERROR_STATE();
					Set @ErrProcedure = ERROR_PROCEDURE();
					Set @ErrLine = ERROR_LINE();
					Set @ErrMsg = ERROR_MESSAGE();
					RaisError(@ErrMsg, 1, 1);
				End Catch
			  End
			Else
			  Begin
				Print @SQL;
			  End
		  End
		End
	Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;

While @CurrID <= @MaxID
  Begin
	Select @LoginName = LoginName,
		@RoleName = RoleName
	From @Roles
	Where RoleID = @CurrID;

	If Not Exists (Select 1 From sys.server_role_members RM
				Inner Join sys.server_principals RoleP
					On RoleP.principal_id = RM.role_principal_id
				Inner Join sys.server_principals LoginP
					On LoginP.principal_id = RM.member_principal_id
				Where LoginP.type In ('U', 'G', 'S')
				And RoleP.type = 'R'
				And RoleP.name = @RoleName
				And LoginP.name = @LoginName)
	  Begin
		If @Debug = 0
		  Begin
			Exec sp_addsrvrolemember @rolename = @RoleName,
							@loginame = @LoginName;
		  End
		Else
		  Begin
			Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
			Print '		@loginame = ''' + @LoginName + ''';';
		  End
	  End

	Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;

While @CurrID <= @MaxID
  Begin
	Select @PermState = PermState,
		@PermName = PermName,
		@Class = Class,
		@LoginName = LoginName,
		@MajorID = MajorID,
		@SQL = PermState + space(1) + PermName + SPACE(1) +
			Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
					When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
					Else '' End +
			' To ' + QUOTENAME(LoginName) + ';'
	From @Perms
	Where PermID = @CurrID;
	
	If Not Exists (Select 1 From sys.server_principals P
				Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
				Where SP.state_desc = @PermState
				And SP.permission_name = @PermName
				And SP.class = @Class
				And P.name = @LoginName
				And SP.major_id = @MajorID)
	  Begin
		If @Debug = 0
		  Begin
			Begin Try
				Exec sp_executesql @SQL;
			End Try
			Begin Catch
				Set @ErrNumber = ERROR_NUMBER();
				Set @ErrSeverity = ERROR_SEVERITY();
				Set @ErrState = ERROR_STATE();
				Set @ErrProcedure = ERROR_PROCEDURE();
				Set @ErrLine = ERROR_LINE();
				Set @ErrMsg = ERROR_MESSAGE();
				RaisError(@ErrMsg, 1, 1);
			End Catch
		  End
		Else
		  Begin
			Print @SQL;
		  End
	  End

	Set @CurrID = @CurrID + 1;
  End

Set NoCount Off;

Ich werde das Skript in Zukunft öfter nutzen, da es für mich und meinem Zwecke der Login Migration sehr gut funktioniert hat.
Vielen Dank an den SQLSoldier

Björn Peters

SQL Server – Security Bulletin MS15-058

15. Juli 2015 Björn Peters

Es war wieder Patch Tuesday (14. Juli 2015):

Der SQL Server hatte seit letztem August kein Sicherheitsupdate erhalten, nun war es soweit, der Download wurde auf den Downloadserver freigegeben.
Beide Fixes für GDR und QFE wurde veröffentlicht, weitere Details siehe Security Bulletin MS15-058.

Dieses Security Bulletin MS15-058 wurde veröffentlicht, um die Sicherheitsanfälligkeit in der Ausführung von Remote-Code zu beseitigen (für Details dazu finden Sie unter KB #3065718).Wenn Sie eine der folgenden Versionen verwenden, sollten Sie diesen Patch zeitnah installieren:

SQL Server 2014 SP1 – unaffected, but there is a GDR for a wrong results bug
SQL Server 2014 RTM – affected
SQL Server 2012 SP2 – affected
SQL Server 2012 SP1 – affected
SQL Server 2012 RTM – likely affected but you need to move to SP1 or SP2 for the fix
SQL Server 2008 R2 SP3 – affected
SQL Server 2008 R2 SP2 – affected
SQL Server 2008 R2 SP1 – likely affected but you need to move to SP2 or SP3 for the fix
SQL Server 2008 R2 RTM – likely affected but you need to move to SP2 or SP3 for the fix
SQL Server 2008 SP4 – affected
SQL Server 2008 SP3 – affected
SQL Server 2008 SP2 – likely affected but you need to move to SP3 or SP4 for the fix
SQL Server 2008 SP1 – likely affected but you need to move to SP3 or SP4 for the fix
SQL Server 2008 RTM – likely affected but you need to move to SP3 or SP4 for the fix

Sollten Sie unsicher sein, ob und welchen Patch Sie installieren sollten, hat Ihnen Aaron Bertrand auch eine Matrix zur Verdeutlichung erstellt.

Unter Umständen sind auch ältere Versionen von dem Bug / Loch betroffen, allerdings wird für diese kein Fix mehr (über öffentliche Server) zur Verfügung gestellt.

Björn Peters

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

14. Juli 201513. Juni 2016 Björn Peters

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.

Björn Peters

SQL aus Hamburg

unser täglich Brot mit MS SQL Servern

SQL Server

Power BI – mein leichter Einstieg

Windows Cluster patchen und automatisch Verteilung wieder herstellen

Auswirkungen dieser Nicht-Beachtung

hier die Lösung für die Cluster Umverteilung

Login Migration von SQL Server 2005 auf 2008R2 inklusive Passwörtern

SQL Server – Security Bulletin MS15-058

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

Related posts:

Auswirkungen dieser Nicht-Beachtung

hier die Lösung für die Cluster Umverteilung

Related posts:

Related posts:

Related posts:

Related posts: