SQL Performance-Messung optimieren

[Ghost_Rider_R]

Hallo zusammen,

wie könnte ich dieses Skript optimieren, sodass am Ende nur das ResultSet am Ende angezeigt wird,
nicht jedoch die 10 Durchläufte der Query, welche gemessen werden soll? (-> SELECT * FROM Artikel)

DECLARE @QueryZaehler INT = 1;
DECLARE @MinZeit INT = 999999999;
DECLARE @MaxZeit INT = 0;
DECLARE @GesamtZeit INT = 0;
DECLARE @StartZeit DATETIME
DECLARE @EndZeit DATETIME
DECLARE @AusfuehrungsZeit INT
DECLARE @AnzahlDurchlaeufe INT

SET @AnzahlDurchlaeufe = 10

WHILE @QueryZaehler <= @AnzahlDurchlaeufe
BEGIN

SET @StartZeit = GETDATE();

--########## Abfrage ##########
--SELECT *
--FROM Artikel
--########## Ende #############

SET @EndZeit = GETDATE();
SET @AusfuehrungsZeit = DATEDIFF(ms, @StartZeit, @EndZeit);
SET @GesamtZeit = @GesamtZeit + @AusfuehrungsZeit;
IF @AusfuehrungsZeit < @MinZeit SET @MinZeit = @AusfuehrungsZeit;
IF @AusfuehrungsZeit > @MaxZeit SET @MaxZeit = @AusfuehrungsZeit;
SET @QueryZaehler = @QueryZaehler + 1;

END

SELECT
@GesamtZeit / @AnzahlDurchlaeufe AS 'Durchschnittliche Ausführungszeit (ms)',
@MinZeit AS 'Minimale Ausführungszeit (ms)',
@MaxZeit AS 'Maximale Ausführungszeit (ms)',
@AnzahlDurchlaeufe AS 'Anzahl Durchläufe',
@GesamtZeit AS 'GesamtZeit'

Vielen Dank schon mal für eure Hilfe!

LG Ghost Rider

 

[floq0r]

Mein erster Ansatz wäre gewesen den Output der "Benchmark-Queries" in eine table variable umzuleiten, das wird dir aber dein Ergebnis verfälschen weil der INSERT im exec plan auch noch dazukäme. Das, was SQL Server Management Studio an Gesamtausführungsdauer für eine Query benötigt beinhaltet aber auch die Bearbeitung durch den Client (siehe Client Statistics => SHIFT+ALT+S), die ja dann wegfiele wenn du das Ergebnis nicht anzeigst. Kurzum: Das ist eine schlechte Methode um Performance Messungen vorzunehmen. Schon deswegen, weil du hier nur Table-Scans ausführst und wenn die für dein Produktionsumfeld aussagekräftig wären hättest du ein mieses DB-Design.
Worum geht es hier überhaupt?

 

[Ghost_Rider_R]

Es geht mir um ein Skript in dem ich die Performance der fiktiven Abfrage SELECT * FROM Artikel messen kann. Die Abfrage wird später dann beliebig ausgetauscht. Das ist mein Vorhaben und die Parameter am Ende sind die, welche mich dann am Ende interessieren.

 

[floq0r]

Wie gesagt, du musst dir im Klaren sein was du hier misst. Wenn du das query result in dieser "Messung" nicht haben willst dann wird es nicht transmitted und wird nicht die selben Ressourcen binden wie ein Durchlauf in dem du die Daten dann tatsächlich produktiv abrufst. Wenn du den kompletten Ablauf einer Query simulieren willst dann verpack deine Logik in ein Script (PHP, nodeJS,...) und miss dort die exec time rundherum.
Wenn dir das alles egal ist dann leite den Output deiner Dummy query in einen table, temp table oder eine table variable um und setze am Beginn deines Scripts SET NOCOUNT ON; damit du keine affected row messages bekommst. Dann hast du zwar INSERTS die du nicht mesen willst aber bekommst dein gewünschten Ergebnis.

 

[andy_m4]

Normalerweise bieten die Datenbanken Möglichkeiten an, die Performance von Queries zu messen (EXPLAIN und Co).
Könnte für den Threadverlauf daher gut weiterhelfen, in dem Du mal sagt, über welche Datenbank wir hier konkret reden.

 

[Ghost_Rider_R]

Es geht um einen Microsoft SQL Server in der Version 2012.

 

[abcddcba]

Kannst du fuer das Profiling von allem in SQL Server nicht auch den SQL Server Profiler nehmen? Der sollte doch Bestandteil sein?

Ansonsten bieten viele DB Engines eigentlich auch EXPLAIN ANALYZE an. Ob SQL Server 2012, keine Ahnung. Aber damit bekommst du halt Query Plan + die Stats (size, execution time), auch der einzelnen Schritte im Query Plan. Evtl. reicht dir sowas auch schon?

 

[Ghost_Rider_R]

Ich hatte da eher den Ansatz, Abfrage etwas umbauen und dann Vorher Nachher-Vergleiche machen. Es geht vor alle um kleine Sachen, bei denen ich mal eben schauen wollte, ob es sich positiv oder negativ auf die Leistung auswirkt.

 

[floq0r]

Dann probier mal SET statistics ON; und schau dir die Menge der reads an.

 

[marcOcram]

Hat der SQL Server 2012 schon einen Query Store? Damit kann man Abfragen gut im Nachhinein analysieren weil da viele Statistiken zu den Abfragen gespeichert werden (rückwirkend). Das hat uns im Produktiveinsatz schon sehr geholfen. Musste man nur aktivieren.

 

[Ghost_Rider_R]

Query Store höre ich eben das erste Mal. Weiß jemand, ob das der SQL Server 2012 schon hat? Könnte aber eine Alternative darstellen.

 

[marcOcram]

Nach einer kurzen Recherche, ist der wohl erst mit 2016 dazu gekommen.
Da SQL Server 2012 schon EOL ist, sollte man sowieso mal über ein Update nachdenken.

 

[Ghost_Rider_R]

Wir haben schon seit 3 Jahren den SQL Server 2019 in der Ecke liegen, leider wurde mir von der GF noch kein Zeitfenster für ein Upgrade gegeben und das seit 3 Jahren.

 

[errno]

Hi,

WHILE @QueryZaehler <= @AnzahlDurchlaeufe

Ich habe nicht ganz verstanden, was mit diesem Code erreicht werden soll. Sie können jedoch auch LIMIT, TOP oder etc. anstelle der Code Teile in dieser Zeile verwenden.

select TOP 2
    (@GesamtZeit / @AnzahlDurchlaeufe) as 'Durchschnittliche Ausführungszeit (ms)', --@GesamtZeit / @AnzahlDurchlaeufe AS 'Durchschnittliche Ausführungszeit (ms)',
    (iif(datediff(ms,@StartZeit, @EndZeit) < @MinZeit, @MinZeit, '1753-01-01')) as 'Minimale Ausführungszeit (ms)', --IF @AusfuehrungsZeit < @MinZeit SET @MinZeit = @AusfuehrungsZeit;
    (iif(datediff(ms,@StartZeit, @EndZeit) > @MaxZeit, E@MaxZeit, '1753-01-01')) as 'Maximale Ausführungszeit (ms)', --IF @AusfuehrungsZeit > @MaxZeit SET @MaxZeit = @AusfuehrungsZeit;
    (@AnzahlDurchlaeufe) as 'Anzahl Durchläufe', --@AnzahlDurchlaeufe AS 'Anzahl Durchläufe',
    (@GesamtZeit + @AusfuehrungsZeit) as 'GesamtZeit' -- (@GesamtZeit + @AusfuehrungsZeit) as 'GesamtZeit'
from Ihre_Table
where 'Minimale Ausführungszeit (ms)' != '1753-01-01' and 'Maximale Ausführungszeit (ms)' != '1753-01-01' --Sie können Zeilen ignorieren, die keine Daten enthalten

Für Analysierung :
Wählen Sie Ihre eigenen benutzerdefinierten Abfragen mit der Maus oder der Tastatur aus und wählen Sie "Display Executed Estimate Plan" aus den Rechtsklick-Optionen. In diesem Bereich können Sie die notwendigen Analysen finden und Änderungen vornehmen. Sie brauchen mindestens SQL Server 2016 für das Option.

Sollte ein Fehler auftreten, würde ich mich über eine Antwort freuen.

MfG / Best Regards