Vorsicht vor Ordnung - Sort Warnings


Andreas Rauch

So schön das Ergebnis einem gefällt, das Sortieren  in Abfragen kostet Performance.  Sortierarbeiten werden seitens des SQL Server folgendermaßen erledigt: Der Optimizer versucht den Bedarf an Arbeitsspeicher für die Sortierung zu schätzen. Dazu werden Datentypen untersucht und die Anzahl der geschätzten Ergebniszeilen. Daraus resultiert der Arbeitsspeicherbedarf für die Abfrage. Reicht der Arbeitsspeicher nicht aus, muss über die tempdb ausgelagert werden.

Wie gesagt: Sortieren kostet. Die Ausnahme: Daten sind via Clustered Index bereits sortiert, dann spielen Sortierangaben auf die führende Indexspalte keine Rolle (egal ob asc oder desc) und werden vom Optimizer schlichtweg ignoriert.   

Das Problem des Sortierens ist allerdings nicht, dass es grundsätzlich Ressourcen verbraucht, sondern dass es in verschiedensten Situationen deutlich mehr Ressourcen verbraucht, da die sonst im Speicher stattfindende Sortierung eben über die tempdb ausgelagert werden muss. Dieses verhalten wird mit dem Event Sort Warnings kenntlich gemacht:  HDD statt RAM!  

Bedingungen für Sort Warning

Unter welchen Umständen kann so eine Auslagerung stattfinden? Einerseits spielen hier Statistiken eine Rolle, der Einsatz von Variablen oder Parametersniffing.

 

Falsche Statistiken

Im folgenden Beispiel sind die Statistiken über die Verteilung der Daten nicht unbedingt alt, aber schlichtweg falsch. Man erkennt das ganz deutlich, wenn man den tatsächlichen Ausführungsplan sich mal genauer ansieht und dort die Diskrepanz zwischen geschätzter Anzahl an Zeilen und tatsächlich gelieferten betrachtet.

image

 
 

Wird nun für die Abfrage ein Sortieren über eine Spalte angegeben, dann kommt es plötzlich zu folgendem Verhalten:image

Die Sortierung kostet nun schon 63% der Abfrage und die Abfrage benötigt deutlich  mehr Zeit!

SQL Server schätzt aufgrund der einen geschätzten Zeile den Bedarf auf ca 1 MB ein. Der tatsächliche Bedarf liegt allerdings deutlich höher. Das Problem ist schlichtweg durch ein Aktualisieren der Statistiken zu lösen.

Nach einer Aktualisierung der Statistiken sieht die Sache schon anders aus:image

Die Abfrage ist deutlich schneller fertig. Der Arbeitsspeicherverbrauch liegt nun allerdings deutlich höher bei ca 780 MB. Zur Erinnerung: Dieser plötzliche Mehrbedarf des RAM wurde vorher über die tempdb gespoolt!

Problem Variablen

Jedem Entwickler werden sich nun die Haare aufstellen. Wenn wir eine Tabelle mit bspw. einer Spalte X und eine Datentyp varchar(50) haben, werden wir sicherlich bei der der Definition von Variablen, welche die Werte aus der Spalte X bekommen sollen, den gleichen Datentype für die Variable übergeben.  Tja und genau hier liegt das Problem. SQL schätzt auf der Basis von Datentypen den Arbeitsspeicher ein, wobei fixen Längen keine Problem bei der Einschätzung sind, allerdings Datentypen mit variabler Länge werden grundsätzlich mit der Hälfte des maximalen Wertes eingeschätzt! In diesem Fall also nur 25 byte.

Nehmen wir mal folgende Konstellation an:

create table tabdemo 
    (c1 int primary key clustered, c2 int, c3 char(2000))


Die Tabelle wird mit ca 200000 Datensätzen gefüllt und nun kommt folgendes Statement:

declare @c1 int, @c2 int,@c3 char(2000)
select @c1=c1, @c2=c2,@c3=c3    
    from tabdemo
    where c1<3057 
    order by c2

 

Der Plan sieht dazu so aus:

image

Die Rückgabe sind 3057 Zeilen. Das Sortieren ist (noch) kein Problem.

Nun aber folgende Abfrage:

declare @c1 int, @c2 int,@c3 char(2000)
select @c1=c1, @c2=c2,@c3=c3    
    from tabdemo
    where c1<3058 
    order by c2


image

Die Abfrage benötigt nun ca die 10-fache Zeit und weist Sort Warnings auf, obwohl nur eine einzige Zeile mehr zurückgegeben wird! Problem: Der Arbeitsspeicher wurde zu gering eingeschätzt.

Nun wollen sehen wir uns mal genauer den Ressourcenverbrauch der Abfrage an…

Messung

Wir können gerne mal messen, wieviel in die Tempdb weggeschrieben wird und wie hoch die Arbeitsspeicherauslastung war. Dazu verwenden wir zwei Systemsichten, die uns diese Daten zurückgeben. Die Abfragen lasse ich in einer Endlosschleife immer wieder durchlaufen, um an diese flüchtigen Messwerte zu kommen:

--Rückgabe des geschätzten und des tatsächlichen Arbeitsspeichers

select granted_memory_kb,used_memory_kb,max_used_memory_kbfrom sys.dm_exec_query_memory_grantswhere session_id=71 –Session ID der Abfrage



--Schreiben in der tempdb

select
num_of_bytes_read, num_of_bytes_written, num_of_reads, num_of_writes
from sys.dm_io_virtual_file_stats(db_id('tempdb'),1)
image



Der Arbeitsspeicher wird voll ausgenutzt und das was nicht reinpasst kommt in die tempdb!

Vergleicht man nun folgende zeitlich wenige Sekunden nacheinander aufgenommen Screenshots, bemerkt man, dass die Anzahl der geschriebenen Bytes sich deutlich erhöhenimage

 


Wie könnte eine Problemlösung aussiehen?

Nun ja.. wir müssen die Variablen so anpassen, dass SQL Server zu einer großzügigeren Einschätzung des Arbeitsspeicher gezwungen wird.

declare @c1 int, @c2 int,@c3 varchar(4500)
begin
    select @c1=c1, @c2=c2,@c3=convert(varchar(4500),c3    )
    from tab7
    where c1<4000 --bei 4000, 3500?? 
    order by c2
end
 

Gedanklich würde so eine Programmierung nie einleuchten, aber in der Praxis kann aber eben dies passieren. Sort Warnings lassen sich übrigens gut im Profiler nachvollziehen. Dazu gibts ein eigenes Event im Bereich Error und Warningsimage.

 

Parametersniffing

Beim Parametersniffing passiert gelinde gesagt dasselbe, wie bei falschen Statistiken. Eine Prozedur wurde erstellt und beim ersten Aufruf für sehr selektive Daten optimiert. Wird diese nun mit Parametern aufgerufen, die sehr häufig vorkommen bzw nicht mehr selektiv sind, ist der gute Plan beim Teufel. SQL  verschätzt sich und das Ergebnis ein ist zu geringer Arbeitspeicher für das Statement und daher verursacht dies ein  Schreiben in die  temdb.

Wie kann man dieses Problem beheben? Einerseits wäre ein Rekompilieren der Prozedur sinnvoll, wenn grundsätzlich sehr häufig größere Ergebnismengen rauskommen würden. Alternativ wäre auch ein Ändern des Codes der Prozedur sinnvoll, wie zb die Angabe with Recompile.

…OPTION (RECOMPILE).

Allerdings wird die Prozedur zum ständigen Recompilieren gezwungen und der Performancevorteil der Prozedur ist wieder beim Teufel.

Beste Lösung

Muss der Order denn sein? Das kann durchaus mal die Anwendung übernehmen Zwinkerndes Smiley

 

Also Aufpassen, liebe Pedanten!

Kommentare sind geschlossen