Dynamic Management Views in SQL Server 2005 Teil 1: Statement Cache
Eines meiner Liebligsfeatures in SQL Server 2005 sind Dynamic Management Views, kurz DMVs. DMVs liefern Zustandsinformationen über bestimmte Funktionen der relationalen Datenbank direkt aus dem Hauptspeicher von SQL Server. Dazu gehören Prozessinformationen, Hauptspeicherauslastung, Disk I/O, Locking & Blocking und viele andere Informationen. Wichtig ist: Nahezu alle Informationen stammen aus dem Hauptspeicher, werden also bei Nustart der SQL Server Instanz zurückgesetzt.
Als erstes möchte ich zeigen, wie man auf den Statement Cache von SQL Server zugreift, der alle bisher ausgeführten SQL Anweisungen mit ihren Ausführungsstatistiken, dem Ausführungsplan und dem SQL-Text enthält. "Alle" ist hier allerdings mit Vorsicht zu genießen, da Anweisungen bei knappem Speicher aus aus dem Statement Cache herausfallen können; außerdem werden triviale Ad Hoch Anweisungen nie in den Cache aufgenommen, es sei denn, die Auto-Parametrisierung schlägt zu. (wie im Beispiel unten)
Den Statement Cache kann man sich mit
select
* from sys.dm_exec_query_stats
ansehen. Das Ergebnis sieht in etwa so aus:
sql_handle ... plan_handle ... last_execution_time execution_count total_worker_time ... total_physical_reads ...
--------------------------------------------------------------------------------------------------------------------------------------
0x0300FF7F92... ... 0x0500FF7F9286... 2005-10-18 09:37:35.623 1647 448560 ... 1 ...
...
Das heißt, es werden die internen Handles für SQL-Text und Ausführungsplan zusammen mit den Statistiken ausgegeben. Wie macht man das jetzt lesbar?
zuerst kann man den SQL-Text mit sys.dm_exec_sql_text(sql_handle) und den Ausführungsplan mit sys.dm_exec_query_plan(plan_handle) ausgeben. Das ergibt dann:
select text from sys.dm_exec_sql_text(0x020000009EB2B834894D14B535C6D3C3224151972E3A3D62)
(@1 int,@2 varchar(8000),@3 varchar(8000))INSERT INTO [DasReplikat]..[AllEmps]([empid],[fname],[lname]) values(@1,@2,@3)
select query_plan from sys.dm_exec_query_plan(0x060001009EB2B834B8014C09000000000000000000000000)
<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1314.06"><BatchSequence><Batch><Statements><StmtSimple ...
Das ist schon ganz nett, aber noch nicht wirklich übersichtlich. Das Schöne daran, dass DMVs und DM-Functions Tabellen zurückgeben ist aber, dass man das ganze joinen kann:
Mit
select
text, query_plan, st.* from Sys.dm_exec_query_stats st
cross apply sys.dm_exec_sql_text(sql_handle) s
cross apply sys.dm_exec_query_plan(plan_handle)
erhält man den gesamten Statement Cache mit Text, Ausführungsplan und Statistiken!
Der Ausführungsplan hat übrigens noch eine Besonderheit: Der Query Optimizer beschwert sich in ihm über fehlende Indizes, nach dem Motto "Wenn ich diesen Index gehabt hätte wäre ich viel schneller gewesen". Darauf kann man abfragen:
select
text, query_plan, st.* from Sys.dm_exec_query_stats st
cross apply sys.dm_exec_sql_text(sql_handle) s
cross apply sys.dm_exec_query_plan(plan_handle)
where convert(nvarchar(max), query_plan) like '%MissingIndex%'
ergibt alle Abfragen, denen ein Index fehlt. Als Beispiel für AdventureWorks: Auf LastName in Person.Contact ist kein Index. Führt man diese Abfrage aus:
use AdventureWorks
select count(*) from Person.Contact where LastName like 'K%'
und schaut sich dann mit obiger Anweisung den Statement Cache an ergibt sich folgender Abschnitt im Query Plan:
<
MissingIndexes>
<MissingIndexGroup Impact="95.6825">
<MissingIndex Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]">
<ColumnGroup Usage="INEQUALITY">
<Column Name="[LastName]" ColumnId="6" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
und das bedeutet, die Abfrage wäre ca. 95% schneller gewesen, wenn es einen Index auf LastName in Person.Contact gegeben hätte. Achtung! Das gilt natürlich nur für die eine Anweisung; es kann sein, dass dieser Index andere Anweisungen negativ beeinflussen würde.
In Summe bietet sys.dm_exec_query_stats hochinteressante Einblicke in den Cache der ausgeführten Anweisungen und ihrer Ausführugnspläne und macht damit den Server transparenter.
Gruß,
Steffen