Erheben von statistischen Informationen für einen SQL Server Teil 2

In Ergänzung zum Teil 1 und Nachfragen von Lesern möchte ich hier noch eine kurze Konkretisierung des Themas nachreichen: Natürlich gibt es diverse Monitoringtools, mit denen man in die Lage versetzt wird, diverse Informationen über Lastzustände oder ähnliches zu ermitteln. Mit meinen Artikel hierzu möchte ich mich allerdings auf die Möglichkeiten beschränken, welche sich mit den Bordmitteln des MS SQL Servers bieten.

In Teil 2 meines Artikels zum Erheben von statistischen Informationen für einen SQL Server möchte ich mit dem Thema der offenen Verbindungen und der angemeldeten Benutzer auseinandersetzen. Für allgemeine Informationen gibt es verschiedene Möglichkeiten, diese mittels SQL-Statements zu ermitteln.

Wie im letzten Artikel beginnen wir mit einem kurzen, einfachen Überblick über offene Verbindungen zu unserem SQL Server. Hierzu ist eine Stored Procedure vorgesehen:

sp_who

Das Resultset davon sieht in etwa so aus:

sp_whoDie hiermit erhobenen Informationen  sind allerdings noch nicht sehr vielsagend. Mehr Aussagekraft besitzt dafür die komplette Aufstellung aller Prozesse, die auf dem SQL Server gerade aktiv sind. Diese können über die Tabelle SysProcesses in der Master-Datenbank abgefragt werden.

Select * from Master..SysProcesses

Im Resultset finden wir schon detaillierter Informationen über laufende Prozesse. Allerdings haben wir hier auch alle Systemprozesse, unter anderem auch dem Vaterprozess mit der SPID 1, welcher uns im letzten Artikel schonmal untergekommen ist. Wollen wir nun nur eine Aufstellung aller Prozesse haben, welche nicht zu den reinen Systemprozessen gehören, sprich alle die, welche von Diensten oder Benutzern geöffnet wurden, können wir das Statement ein wenig modifizieren:

Select * from master..sysprocesses where IsNull(HostName, “) <> “ and IsNull(program_name, “) <> “ and isnull(hostprocess, “) <> “

Wie wir anhand dieses Statements feststellen, werden für Systemprozesse weder ein Hostname, ein Programmname noch ein entsprechender Hostprozess abgebildet, was bei Dienstprozessen oder Benutzerprozessen durchaus der Fall ist. Dies ist hier auch unser Unterscheidungskriterium. Wie wir im Resultset feststellen werden, ist es auf den ersten Blick ohne das Auswendiglernen sämtlicher Datenbank-ID’s nicht möglich zu erkennen, wer auf welcher Datenbank angemeldet ist. Dazu müssen wir das Statement noch ein wenig weiter ausbauen:

Select p.spid, p.login_time, p.last_batch, p.open_tran, p.status, p.nt_domain, p.nt_username, p.loginame,
p.hostname, p.program_name,
isnull(d.name, ’n/a‘) as DBName,
datediff(mi, p.login_time, getdate()) as ConnectionTime
from master..sysprocesses p left outer join master..sysdatabases d
on p.dbid = d.dbid
where IsNull(p.HostName, “) <> “ and IsNull(p.program_name, “) <> “ and isnull(p.hostprocess, “) <> “

Ich habe mich hier auf die meiner Meinung nach relevantesten Datenspalten beschränkt. Weitere können bei Bedarf gerne wieder hinzugefügt werden. Zusätzlich finden wir in diesem Statement noch eine Auflistung, welche Verbindung bereits wie lange in Minuten geöffnet ist. Somit ist es uns möglich, einen Überblick über angemeldete Benutzer und / oder Dienste sowie der entsprechenden Verbindungszeiten auf schnellem Wege zu liefern.

Und für alle die es noch nicht wissen: Eine geöffnete Verbindung kann man in der Rolle des sysadmin auch ohne die Mitwirkung des Anwenders manuell trennen: Hierzu wird das Kommando kill in Verbindung mit der (möglicherweise mittels eines Statements aus diesem Artikel) ermittelten SPID verwendet. Mehr dazu bei MSDN: KILL (Transact-SQL)

Erheben von statistischen Informationen für einen SQL Server Teil 1

Als Systembetreuer eines SQL Servers kann es immer wieder einmal vorkommen, das Fragen über das Nutzungsverhalten des SQL Servers gestellt werden. Mit einfachen  Bordmitteln des SQL Servers ist es durchaus möglich, einen recht umfangreichen Satz an Informationen zusammenzustellen. Das ganze werde ich die folgenden Teile splitten:

  • allgemeine Informationen
  • Überblick über offene Verbindungen und angemeldete User
  • Auswertung von DB- und Tabellengrößen

Ein erster Überblick mittels sp_monitor

Einen ersten Überblick liefert die Stored Procedure sp_monitor, welche in etwa den folgenden Output liefert:

sp_monitor_output

Was lässt sich aus diesen Informationen herauslesen?

Alle Informationen sind bezogen auf die Zeitspanne seit dem letzten Start des SQL Servers. In der Spalte last_run findet sich die Ausführungszeit des letzten Monitorings (der letzten Ausführung von sp_monitor), während in der Spalte current_run die Ausführungszeit des aktuellen Laufes enthalten ist. In der Spalte seconds ist das Zeitdelta in Sekunden zwischen den beiden letzten Ausführungen der sp_monitor abgebildet.
Als nächstes erhalten wir Informationen über die Prozessornutzung des SQL Servers. Die Werte hier sind jeweils in Sekunden abgebildet, welche der SQL Server an CPU-Zeit belegt hat. Der erste Wert ist hierbei der Absolutwert seit dem letzten Start des SQL Servers, der in Klammern eingefasste Wert bildet den jeweiligen Anteil zwischen den beiden letzten Ausführungen von sp_monitor ab. Dabei wird unterschieden in cpu_busy, io_busy und idle, wobei cpu_busy für die reine Prozessorzeit steht, welche der SQL Server benötigt hat, io_busy für die Prozessorzeit, welche für I/O-Operationen benötigt wurde und idle natürlich für den Leerlauf. Die jeweils mit Bindestrich abgegrenzten Prozentangaben sollen den jeweiligen prozentualen Anteil an den Gesamtoperationen des Servers seit dem letzten Lauf von sp_monitor darstellen, aber wie hier deutlich erkennbar in der Spalte idle scheint dies (zumindest für die idle-time) ein paar Probleme zu bereiten.
Als nächstes werden die durch den SQL Server erhaltenen empfangenen sowie gesendeten Netzwerkpakete abgebildet, ebenfalls wieder mit Total- und Differenzwert. Solange die Spalte packet_errors den Wert 0(0) anzeigt, besteht hier auch kein Grund zur Sorge. Wohlgemerkt geht es hier nur um Pakete, die direkt an die abgefragte SQL Server Instanz gesendet wurden oder von dieser gesendet wurden. Der übrige Netzwerkverkehr des basierenden Servers ist hier nicht berücksichtigt.
Zu guter Letzt  erhalten wir Informationen über die Anzahl von Lese- und Schreibzugriffen auf die Datenbank, die Anzahl der dabei aufgetretenen Fehler (ohne Unterscheidung von Lese- oder Schreibfehlern) sowie die Anzahl von hergestellten Verbindungen, wieder mit absolutem sowie differentiellem Wert.

Erhebung von Einzelwerten aus sp_monitor

Neben der Gewinnung eines Überblickes kann es auch Situationen geben, in denen nur ein bestimmter absoluter Wert der in sp_monitor abgebildeten Werte benötigt wird. Diese können über die folgenden Statements gewonnen werden:

select @@cpu_busy
select @@io_busy
select @@idle
select @@pack_recieved
select @@pack_sent
select @@packet_errors
select @@total_read
select @@total_write
select @@total_errors
select @@connections

Ermitteln der uptime einer SQL Server Instanz

Für die Erfüllung von SLA’s etc. kann es notwendig sein, die uptime einer SQL Server Instanz zu ermitteln. Diese lässt sich nur über einen kleinen Umweg herausfinden: Die Loginzeit des intialen Prozesses eines SQL Servers, welcher immer die spid (Session ID eines Prozesses) 1 besitzt. Dazu kann der folgende Code verwendet werden:

SELECT datediff(mi, login_time, getdate()) FROM master..sysprocesses WHERE spid = 1

In diesem Fall wird die uptime der SQL Server Instanz in Minuten returniert. Eine Anpassung ist je nach Anforderung umsetzbar.