Verschätzt – AdHoc Abfragen Probleme

Ich kenne nicht viele Systeme, deren SQL Logik nicht in Prozeduren verpackt worden wäre, aber dennoch.. es gibt sie. Sofern sie ebenfalls vorhaben, mehrfach AdHoc Abfragen an den Server zu stellen, in dem Sie beispielsweise auf Prozeduren in Anwendungen verzichten, dann sollten sie sich folgenden Umsatnd überlegen.

Autoparametrisierung

SQL Server versucht Abfragen zu parametrisieren, um Ausführungspläne wiederverwenden zu können. Gründe dafür sind natürlich Performance. Ein Plan muss erst erstellt werden, in dem Statistiken zu Rate gezogen werden und Entscheidungen über die Wahl von Indizes getroffen werden. Eine Autoparametrisierung erkennen Sie beispielsweise sehr leicht im tatsächlichen Ausführungsplan:

Leider hält sich diese Möglichkeit in Grenzen. Verwenden Sie beispielsweise einen Join, so verliert der SQL Server diese Fähigkeit.

Das bedeutet die Pläne werde so wie sind im Speicher abgelegt:

Nehmen wir mal an, wir würden bei dieser Abfrage 100 verschiedene Werte abfragen wollen, so ergeben sich im Arbeitsspeicher 100 Pläne!

Selbst bei einfachen Fragen kann man auf Probleme stoßen:

Führen sie mal folgende Abfrage einzeln aus:

So ergeben sich folgende Pläne im Speicher:

SQL Server schätzt bei Übergaben eines Parameters den nächst günstigen Datentyp ein. Im Falle von orderid=50 auf einen tinyint. Blöderweise passt der allerdings nicht mehr für orderid=500, Dazu muss also schon ein smallint her. Doch auch der paßt nicht mehr für die 50000. Somit müssen 3 Plne angelegt werden.

Die Lösung für die obigen Probleme können durchaus Prozeduren sein, da hier von Anfang an mit vordefinierten Datentypen parametrisiert wird. Die Allroundlösung für dynamisches SQL sind Prozeduren allerdings auch nicht unbedingt, aber das steht auf einen Blatt.

Tests

Wer hier diese Beispiele nachvollziehen möchte, der sollte vorher mal den Prozedurencache löschen, sonst kann das eine gute Weile dauern, bis man den kompletten Cache ausgelesen hat  

Verwenden Sie dazu, wenn möglich nicht dbb freeproccache sondern dbcc flushprocindb(7) wobei die 7 für die Datenbank ID steht. Damit verwerfen sie den eigtl. wertvollen Cache nur für die jeweilige Datenbank.

Um die Pläne zu sehen können Sie folgende Abfrage verwenden:

select usecounts, cacheobjtype,[TEXT] from
    sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    where cacheobjtype ='Compiled PLan'
        AND [TEXT] not like '%dm_exec_cached_plans%'
        AND [TEXT]Not like '%filetable%'

 

 

Also passen sie gut auf.. !