SQL Server nicht verwenden, meine index
Habe ich eine relativ einfache Abfrage:
SELECT
col1,
col2…
FROM
dbo.My_Table
WHERE
col1 = @col1 AND
col2 = @col2 AND
col3 <= @col3
Es wurde die Durchführung entsetzlich, so dass ich fügte hinzu, ein index über col1, col2, col3 (int, bit, und datetime). Wenn ich überprüft die Abfrage-plan wurde ignoriert meine index. Ich habe versucht, Neuanordnung der Spalten im index in jeder möglichen Konfiguration und es immer ignoriert den index. Wenn ich die Abfrage ausführen, es macht einen clustered-index-scan (Tabellen-Größe ist zwischen 700K und 800K Zeilen) und dauert 10-12 Sekunden. Wenn ich es erzwingen, um meine index gibt es sofort. Ich war vorsichtig und löschen Sie den cache und-Puffer zwischen den tests.
Andere Dinge, die ich versucht habe:
UPDATE STATISTICS dbo.My_Table
CREATE STATISTICS tmp_stats ON dbo.My_Table (col1, col2, col3) WITH FULLSCAN
Bin ich etwas fehlt hier? Ich hasse es, um ein index-Hinweis in einer gespeicherten Prozedur, SQL-Server kann einfach nicht scheinen, um eine Ahnung auf diese. Wer weiß, andere Dinge, die möglicherweise verhindern, dass SQL Server von der Erkenntnis, dass die Verwendung des index ist eine gute Idee?
EDIT: Einer der Spalten, die zurückgegeben wird, eine Spalte TEXT, so mit einem abdeckenden index oder ein INCLUDE funktioniert nicht 🙁
- Sie stellen eine Ellipse in die Spalte der Liste, welche anderen Spalten sind Sie bei der Auswahl? Was ist, wenn es nur war, col1, col2 und col3?
- Chris, guter Punkt, aber "Wenn ich die Kraft, es zu verwenden, meinem index, es gibt sofort" Abdeckungen.
- Die Ellipsen sind, weil die Abfrage gibt alle Spalten in der Tabelle. Ich glaube, ich hätte mit SELECT *
Du musst angemeldet sein, um einen Kommentar abzugeben.
Haben Sie 800k Zeilen indiziert werden, die von col1, col2, col3. Col2 ist ein bisschen, so dass Ihre Selektivität bei 50%. Col3 ist geprüft auf eine Auswahl (<=), es ist also die Selektivität wird grob auf etwa 50% zu. Die Blätter col1. Die Abfrage wurde für die generischen, parametrisierten plan, so hat es zu berücksichtigen, für den Allgemeinen Fall. Wenn Sie 10 unterschiedlichen Werte col1, dann ist dein index zurück etwa 800k /10 * 25%, das ist etwa ~20k keys nachschlagen im clustered-index zum abrufen der '...' Teil. Wenn Sie 10k distinct spalte1 Werte dann den index zurückgeben, nur 20 Schlüsseln zu suchen. Wie Sie sehen können, was zählt, ist nicht, wie Sie bauen Sie Ihre index-in diesem Fall, aber die eigentlichen Daten. Basierend auf der Selektivität von col1, der Optimierer einen plan wählen, basierend auf einen clustered index scan (besser als 20k Schlüssel suchen, jede Suche zu einem Preis von mindestens 3-5 Seite liest) oder ein auf der Grundlage der non-clustered-index (falls col1 ist selektiv genug). Im wirklichen Leben, die Verteilung von col1 spielt auch eine Rolle, aber gehen in, dass, erschweren die Erklärung zu viel.
Können Sie kommen im Nachhinein und behaupten, der plan ist falsch, aber der plan ist die beste Kalkulation basiert auf den zur Verfügung stehenden Daten zur compile-Zeit. Sie können es beeinflussen mit hinweisen (index-Hinweis, wie Sie andeutet, oder eine Optimierung für die Hinweise Quassnoi andeutet), aber dann Ihre Abfrage durchführen kann, um besser für Ihr test-set, und weit schlimmer für einen anderen Satz von Daten, also für den Fall, wenn @col1 =
<the value that matches 500k records>
. Sie können auch den index abdecken, wodurch die '...' in die Projektions-Liste, die erfordern, dass der gruppierte index-lookup erforderlich, in welchem Fall die non-clustered index ist immer eine bessere Kosten entsprechen, als die clustered-scan.Kimberley Tripp hat ein blog-Artikel über dieses Thema, Sie nennt es die 'index tipping point", die erklärt, wie Sie kommen, eine scheinbar perfekte Kandidat index ignoriert wird: einen nicht gruppierten index, erstreckt sich nicht auf die projektionsliste und hat eine schlechte Selektivität wird gesehen als teurer als ein clustered-scan.
SQL Server
optimizer ist nicht gut im optimieren von Abfragen, die Variablen verwenden.Wenn Sie sicher sind, Sie profitieren immer von der Nutzung des index, nur ein Hinweis.
Wenn Sie die literal-Werte die Abfrage statt der Variablen, es wird bei der Auswahl der richtigen Statistiken und verwenden Sie den index.
Können Sie auch versuchen, mehr Licht Hinweis:
die Berechnung der optimalen Ausführungsplan für diese Werte von den Variablen, mit Statistik, und nicht bleiben, um mit index, egal was.
Die Reihenfolge des index wichtig ist für diese Abfrage:
Es ist nicht so sehr die ASC/DESC -, wie beim sql-server geht zu entsprechen, die where-Klausel, kann es passen auf col3 ersten und Fuss der index zusammen, dass Wert.
Haben Sie versucht, tossing aus der bit aus dem index?
Auch, Sie habe sich den rest der Spalten aus der select-Liste. Möchten Sie vielleicht in Betracht ziehen, wenn es nicht viele, entweder im index oder als GEHÖREN - Anweisung zum erstellen einer abdeckenden index für die Abfrage.
Versuchen Maskierung Ihre Parameter zu verhindern, dass Parameter sniffing:
Klingt dumm, aber ich habe gesehen, SQL-server tun einige seltsame Dinge, weil der parameter sniffing.
Ich Wette, SQL Server denkt, dass der Preis zu bekommen, den rest der Spalten (gekennzeichnet durch ... in deinem Beispiel) aus dem gruppierten index, überwiegt der Vorteil der index-so ist es nur scans der clustered key. Wenn ja, sehen Sie, wenn Sie können, machen dies zu einem abdeckenden index.
Oder verwendet es einen anderen index ersetzen?
Sind die Spalten null-Werte zulässt? Manchmal Sql Server denkt, es ist zum Scannen der Tabelle NULL-Werte.
Versuchen Sie, "und col1 ist nicht null", um die Abfrage, es mgiht machen sqlserver verwenden Sie die index-wtihout Hinweis.
Überprüfen Sie auch, ob die Statistik wirklich up to date:
COL1
istNULL
Wenn Ihr WÄHLEN Sie ist wieder Spalten, die nicht in Ihrem index SQL meinen finden, die einen effizienteren scan des gruppierten index, anstatt einen Schlüssel-lookup zu finden, die anderen Werte, die Sie anfordern.
Wenn Sie eine TEXT-Spalte schalten Sie den Datentyp VARCHAR(MAX) - dann einschließlich der Werte in den nicht gruppierten index.