3
SQL Index-Nutzung
Guten Morgen,
ich verstehe da was nicht und frage mal in die Runde.
Ich habe eine SQL Funktion geschrieben die zu einem Objekt Unterobjekte sucht und alle Unterpfade rekursiv ausließt und in einer Tabelle ausgibt. Relativ schlichtes CTE. Das ganze läuft auf einem eigenen SQL Server der per Verbindungsserver auf das Produktiv-System lesend zugreift. Im Produktivsystem möchte ich möglichst wenig verändern.
Jetzt kann ich dabei entweder nur die Haupttabelle verwenden die eine Parent-ID mit liefert oder eine eigene Relations-Tabelle. Die Haupttabelle hat keinen passenden Index. Eine Testabfrage mit 386 Pfaden läuft dementsprechend lahm. Witziger Weise läuft die Funktion in der Verbundenen Datenbank (MSSQL 12, 14 Sekunden) immernoch deutlich schneller als in der Produktivdatenbank (MSSQL 11, 51 Sekunden). Mit (selbst erstelltem) Index läuft es in beiden Datenbanken unter 1 Sekunde, der Index würde also genutzt werden. Die Versionsunterschiede interessieren mich also nicht wirklich.
Dann wollte ich es über die Relationstabelle machen, um keinen zusätzlichen Index im Produktivsystem führen zu müssen. Jetzt läuft die selbe Funktion hier aber nur im Produktivsystem schnell (0 Sekunden), im verbundenen System dauert die Selbige 18 Sekunden. Der Index scheint aber auch in diesem Fall genutzt zu werden. Dazu beobachte ich (etwas umständlich) die Seeks in diesem Select:
Im Execution Plan steht leider nicht welche Indexe zum Einsatz kommen (oder ich bin zu blöd), kann ich das irgendwie ermitteln?
Hat jemand noch eine Idee woran das liegen könnte?
Ich kann das Problem mit einem eigenen neuen Index erstmal sinnvoll lösen aber mich lässt das nicht ganz los.
ich verstehe da was nicht und frage mal in die Runde.
Ich habe eine SQL Funktion geschrieben die zu einem Objekt Unterobjekte sucht und alle Unterpfade rekursiv ausließt und in einer Tabelle ausgibt. Relativ schlichtes CTE. Das ganze läuft auf einem eigenen SQL Server der per Verbindungsserver auf das Produktiv-System lesend zugreift. Im Produktivsystem möchte ich möglichst wenig verändern.
Jetzt kann ich dabei entweder nur die Haupttabelle verwenden die eine Parent-ID mit liefert oder eine eigene Relations-Tabelle. Die Haupttabelle hat keinen passenden Index. Eine Testabfrage mit 386 Pfaden läuft dementsprechend lahm. Witziger Weise läuft die Funktion in der Verbundenen Datenbank (MSSQL 12, 14 Sekunden) immernoch deutlich schneller als in der Produktivdatenbank (MSSQL 11, 51 Sekunden). Mit (selbst erstelltem) Index läuft es in beiden Datenbanken unter 1 Sekunde, der Index würde also genutzt werden. Die Versionsunterschiede interessieren mich also nicht wirklich.
Dann wollte ich es über die Relationstabelle machen, um keinen zusätzlichen Index im Produktivsystem führen zu müssen. Jetzt läuft die selbe Funktion hier aber nur im Produktivsystem schnell (0 Sekunden), im verbundenen System dauert die Selbige 18 Sekunden. Der Index scheint aber auch in diesem Fall genutzt zu werden. Dazu beobachte ich (etwas umständlich) die Seeks in diesem Select:
SELECT Db_name(database_id) db,
Object_name(object_id) [table],
si.NAME,
index_id, user_seeks, user_scans, user_lookups,
user_updates, system_seeks, system_scans,
system_lookups, last_user_seek,
last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats istat,
sys.sysindexes si
WHERE istat.object_id = si.id
AND istat.index_id = si.indidIm Execution Plan steht leider nicht welche Indexe zum Einsatz kommen (oder ich bin zu blöd), kann ich das irgendwie ermitteln?
Hat jemand noch eine Idee woran das liegen könnte?
Ich kann das Problem mit einem eigenen neuen Index erstmal sinnvoll lösen aber mich lässt das nicht ganz los.
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 1614321030
Url: https://administrator.de/contentid/1614321030
Ausgedruckt am: 25.11.2024 um 10:11 Uhr
3 Kommentare
Neuester Kommentar
Moin,
eine Optimierung der Dauer einer Abfrage bedeutet nicht, dass dies Abfrage effizienter im Bezug auf z.B. die Anzahl der Lesevorgänge ist. Hier kommt dann ins Spiel, wie schnell das jeweilige System die Ressourcen bereitstellen kann.
Zusätzlich stellt sich die Frage, welcher Plan bei der Ausführung verwendet wird.
Eine Anlaufstelle sind die Tools von BrentOzar (first Aid kit, kostenlos):
BrentOzar BlitzCache
BrentOzar BlitzWho
BrentOzar BlitzIndex
Falls du das Verhalten in einer Testumgebung nachstellen kannst, empfehle ich, zunächst den Plan Cache über DBCC FREEPROCCACHE leeren.
Im Execution Plan steht zu jedem Objekt mit der Überschrift Index Seek oder Index Scan in der zweiten Zeile der verwendete Index soweit ich mich erinnere (SSMS Version> 18).
Gruß
Grinskeks
eine Optimierung der Dauer einer Abfrage bedeutet nicht, dass dies Abfrage effizienter im Bezug auf z.B. die Anzahl der Lesevorgänge ist. Hier kommt dann ins Spiel, wie schnell das jeweilige System die Ressourcen bereitstellen kann.
Zusätzlich stellt sich die Frage, welcher Plan bei der Ausführung verwendet wird.
Eine Anlaufstelle sind die Tools von BrentOzar (first Aid kit, kostenlos):
EXEC sp_BlitzCache @SortOrder = 'duration'; BrentOzar BlitzCache
BrentOzar BlitzWho
BrentOzar BlitzIndex
Falls du das Verhalten in einer Testumgebung nachstellen kannst, empfehle ich, zunächst den Plan Cache über DBCC FREEPROCCACHE leeren.
Im Execution Plan steht zu jedem Objekt mit der Überschrift Index Seek oder Index Scan in der zweiten Zeile der verwendete Index soweit ich mich erinnere (SSMS Version> 18).
Gruß
Grinskeks
1
Okay ich habe bisher wohl den Fehler gemacht den Execution plan nur für den Funktionsaufruf zu betrachten, da steht nämlich nirgendwo was von Index. Wenn ich das Statement direkt ausführe bekomme ich sehr viel mehr Information. Dort steht dann was von Index Spool.
Die teuerste Operation ist aber ein INNER JOIN der auf eine Sortierung Bezug nimmt... etwas irritierend warum er da sortieren sollte ist mir nicht klar. Aber ich habe eine andere Stelle gefunden die ursächlich scheint. Ich werde mal noch etwas testen.
Die teuerste Operation ist aber ein INNER JOIN der auf eine Sortierung Bezug nimmt... etwas irritierend warum er da sortieren sollte ist mir nicht klar. Aber ich habe eine andere Stelle gefunden die ursächlich scheint. Ich werde mal noch etwas testen.
Also ich habe mein Hauptproblem gefunden (Ziel ist natürlich nicht allein eine Optimierung sondern eine Laufzeit die in der Praxis nicht meinen Prozess killt):
Ich habe mit einer Einschränkung im CTE versucht die Pfade schon vorab zu filtern. Das hat am Anfang (ohne jeglichen Index) die Anzahl der Ergebnisse wie gewünscht reduziert und damit auch die Laufzeit verkürzt. Jetzt läuft es mit Index aber sehr viel schneller wenn ich das erst später filtere.
Beispiel:
Ich habe eine Struktur
1
1/1
1/2
2
2/1
2/2
Und möchte die auf 1 vorfiltern, dann habe ich im CTE bereits eine Bedingung gebaut, etwa so:
Wohingegen ich jetzt erst später eingrenze:
Das läuft jetzt in ca. 0,1 Sekunde durch und ist damit schnell genug das ein User auch sofort bei Klick ein Ergebnis bekommt.
Ich habe mit einer Einschränkung im CTE versucht die Pfade schon vorab zu filtern. Das hat am Anfang (ohne jeglichen Index) die Anzahl der Ergebnisse wie gewünscht reduziert und damit auch die Laufzeit verkürzt. Jetzt läuft es mit Index aber sehr viel schneller wenn ich das erst später filtere.
Beispiel:
Ich habe eine Struktur
1
1/1
1/2
2
2/1
2/2
Und möchte die auf 1 vorfiltern, dann habe ich im CTE bereits eine Bedingung gebaut, etwa so:
WITH cte AS (
SELECT ... FROM tabelle
UNION ALL
SELECT ... FROM cte
INNER JOIN tabelle
ON ...
WHERE tabelle.wert LIKE left(@filter_wert,datalength(tabelle.wert) + '%'
)
SELECT *
FROM cteWITH cte AS (
SELECT ... FROM tabelle
UNION ALL
SELECT ... FROM cte
INNER JOIN tabelle
ON ...
)
SELECT *
FROM cte
WHERE cte.wert LIKE @filter_wert + '%' 
