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.
Wird nun für die Abfrage ein Sortieren über eine Spalte angegeben, dann kommt es plötzlich zu folgendem Verhalten:
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:
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:
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
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_kb from sys.dm_exec_query_memory_grants where 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)
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öhen
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 Warnings
.
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 
Also Aufpassen, liebe Pedanten!
