SQL-Server kein index in der gespeicherten Prozedur
Ich habe nicht dieses Problem gelöst, indem Sie die gespeicherte Prozedur, doch wir haben beschlossen, zu übertreffen, die SP und führen nur die plain ol' SQL
Bitte sehen Sie sich die erweiterte Tabelle Schema unten
Edit 2: Aktualisiert den index (nicht für die Verwendung der actieGroep mehr)
NB. SQL Server 2005 Enterprise 9.00.4035.00
NB2. Scheint im Zusammenhang mit http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx
Habe ich zwei Indizes auf eine Tabelle:
- Ein clustered PK-index auf statistiekId
- Einen nicht-gruppierten index auf foreignId
Und ich habe das folgende Stück code:
DECLARE @fid BIGINT
SET @fid = 873926
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
Diese führt genau so, wie es sollte; es verweist auf den richtigen index, und alle es tut, ist das Scannen der index.
Nun bin ich durch das erstellen einer gespeicherten Prozedur:
ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
END
Läuft die Sache:
EXEC MyProcedure @fid = 873926
Nun läuft es ein clustered index scan auf meinem PK-index! Wtf ist da Los?
Also änderte ich die SP zu
SELECT foreignId
FROM STAT_Statistieken
WITH (INDEX(IX_STAT_Statistieken_2))
WHERE foreignId = @fid
Und jetzt gibt es: Abfrage-Prozessor konnte keinen Abfrage-plan erzeugen, weil die Hinweise in dieser Abfrage. Übermitteln Sie die Abfrage ohne Angabe von hinweisen und ohne SET FORCEPLAN. Während die gleiche Funktion ausgeführt wird, wie sollte es bei der Ausführung dieses direkt.
Extra-info: die vollständige Regelung, die sich reproduzieren können dieses Verhalten (englische Namen im Kommentar)
Tabelle
CREATE TABLE [dbo].[STAT_Statistieken](
[statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
[foreignId] [bigint] NOT NULL,
[datum] [datetime] NOT NULL, --date
[websiteId] [int] NOT NULL,
[actieId] [int] NOT NULL, --actionId
[objectSoortId] [int] NOT NULL, --kindOfObjectId
[aantal] [bigint] NOT NULL, --count
[secondaryId] [int] NOT NULL DEFAULT ((0)),
[dagnummer] AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
[actieGroep] AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
(
[statistiekId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Index
CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId] ON [dbo].[STAT_Statistieken]
(
[foreignId] ASC,
[dagnummer] ASC,
[actieId] ASC,
[secondaryId] ASC
)WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]
Ausführung
SET NOCOUNT ON;
DECLARE @maand INT, @jaar INT, @foreignId BIGINT
SET @maand = 9
SET @jaar = 2009
SET @foreignId = 828319
DECLARE @startDate datetime, @endDate datetime
SET @startDate = DATEADD(month, -1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
SET @endDate = DATEADD(month, 1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
DECLARE @firstDayDezeMaand datetime
SET @firstDayDezeMaand = CONVERT(datetime, CAST(@jaar AS VARCHAR(4)) + '/' + CAST(@maand AS VARCHAR(2)) + '/1')
DECLARE @daynumberFirst int
set @daynumberFirst = DATEDIFF(day, '2009/01/01', @firstDayDezeMaand)
DECLARE @startDiff int
SET @startDiff = DATEDIFF(day, '2009/01/01', @startDate)
DECLARE @endDiff int
SET @endDiff = DATEDIFF(day, '2009/01/01', @endDate)
SELECT @foreignId AS foreignId,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige
FROM STAT_Statistieken
WHERE
dagnummer >= @startDiff
AND dagnummer < @endDiff
AND foreignId = @foreignId
OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300))
DBCC Statistik
Name | Updated | Rows | Rows smpl | Steps | Density | Avg. key | String index
IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId Oct 6 2009 3:46PM 1245058 1245058 92 0,2492834 28 NO
All Density | Avg. Length | Columns
3,227035E-06 8 foreignId
2,905271E-06 12 foreignId, dagnummer
2,623274E-06 16 foreignId, dagnummer, actieId
2,623205E-06 20 foreignId, dagnummer, actieId, secondaryId
8,031755E-07 28 foreignId, dagnummer, actieId, secondaryId, statistiekId
RANGE HI | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE ROWS
-1 0 2 0 1
1356 3563 38 1297 2,747109
8455 14300 29 6761 2,115072
Und der index wird verwendet, wie gezeigt in der Ausführung plan. Wenn ich wickeln Sie diese in eine Prozedur mit diesem params:
@foreignId bigint,
@maand int, --month
@jaar int --year
Und führen Sie es mit _SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009
Macht es einen clustered index scan!
- Ist der Punkt der sproc, um herauszufinden, ob ein link vorhanden ist der anderen Tabelle, aus STAT_Statistieken, oder wie viele solcher links vorhanden?
- Nein, das ist nur eine abstrakte version des Problems. Ich will einfach nur einen SELECT auf die Tabelle mit dem index. Die Tabelle enthält nur eine Reihe von bigints
- was die Laufzeit der Abfrage bei der Verwendung der gruppierten und die normale index?
- Cluster: 28 Sek. Non-clustered: < 1 Sek.
- vermutlich ein strittiger Punkt, aber haben Sie auch aktualisieren Sie Ihre Statistiken, defragmentiert Ihre Indizes (vor allem Ihre clustered-Indizes), ... ?
- Wenn ich imitieren das schema mit den wenigen Informationen, die ich bekommen keine Probleme, es soll, wie erwartet. Die Fragen über den Abfrage-cache / query plan Treffer / parameter sniffing sind alreayd geschrieben, so ist dies nicht zu lösen, können Sie eine weniger abstrakte version und schema, Sie kann gut sein, das Ausschneiden der erforderlichen Informationen, um zu helfen, wenn und abstrahiert es.
- Hinzugefügt das vollständige schema in der post.
- Hallo Jan - irgendwelche Neuigkeiten zu diesem Thema? Ich glaube, ich habe herausgefunden, was war das problem verursacht (eine SQL Server-Optimierer-bug mit permanenten berechneten Spalten). Siehe meine Antwort unten. Lassen Sie mich wissen, wenn es funktioniert!
- Editiert die index
- Sind
-1
,1356
und8455
die nächsten Werte zu873924
? - Und könnten Sie bitte post die
XML
Pläne generiert durch die Abfrage und die von der gespeicherten Prozedur? Führen Sie einfachSET SHOWPLAN_XML ON GO SELECT …
undSET SHOWPLAN_XML ON GO EXECUTE _SP_TEMP …
- Hallo Jan - danke für die zusätzliche info... sehr nützlich. Ich gerade erneut meine Antwort mit einem zusätzlichen Vorschlag für das, was kann die Ursache sein und wie es zu lösen. Siehe unten. Auch, @Quassnoi Vorschlag zu post eine XML-plan ist ein guter. Wird definitiv helfen uns bei der diagnose Ihres Problems besser.
Du musst angemeldet sein, um einen Kommentar abzugeben.
[BEARBEITEN]
PERMANENTEN-nicht-sein-verwendet, die unten genannte Problem tritt nur mit der actieGroep/actieId auf meinem system (SQL 2008). Aber es ist möglich, dass das gleiche problem sein könnte, passiert auf Ihrem SQL 2005 system mit der dagnummer/datum Spalten als gut. Wenn tatsächlich das passiert ist, würde es erklären, das Verhalten, das Sie beobachten, da ein clustered index-scan notwendig wäre, um filter für die Werte des Datums. Um zu diagnostizieren, ob dies tatsächlich geschieht, fügen Sie einfach die datum-Spalte als INCLUDE-d-Spalte der index, wie hier:
Wenn das problem verschwindet mit diesem index-revision, dann wissen Sie, dass dagnummer ist die Frage-- kann man wohl auch entfernen dagnummer aus dem index, da SQL nicht verwenden es trotzdem.
Auch, die überarbeitung der index hinzufügen actieId ist eine gute Idee, denn es hatte die Frage unten. Aber in dem Prozess, den Sie auch verlassen müssen aantal Spalte im index, so dass Ihr index wird ein abdeckenden index für diese Abfrage. Ansonsten SQL-Lesen und Ihren clustered-index, um den Wert dieser Spalte. Dies verlangsamt deine Abfrage da-lookups in den gruppierten index sind ziemlich langsam.
[ENDE EDIT]
Hier sind ein paar Ideen, die Ihnen helfen können, dieses Problem zu lösen, mit am ehesten/einfachsten Dinge zuerst:
Als ich versuchte, repro Ihrem schema verwenden und Abfragen (mit fake-Daten generiert), sehe ich, dass Ihre permanente berechnete Spalte actieGroep wieder copmputed zur Laufzeit anstelle des permanenten Wert verwendet wird. Das sieht wie ein Fehler in der SQL Server-Abfrageoptimierer. Da die zu Grunde liegenden Spalte Wert actieGroep ist nicht vorhanden in Ihrer abdeckenden index
IX_STAT_Statistieken_1
index (nur die berechnete Spalte vorhanden ist), wenn SQL Server entscheidet, dass es abrufen muss, dass eine zusätzliche Spalte, SQL berücksichtigen kann, die einen gruppierten index günstiger, als mit Ihrem nicht-gruppierten index, und dann suchen bis actieId für jede übereinstimmende Zeile in der cluster-index. Dies ist, weil clustered-index-lookups sind sehr teuer im Vergleich zu sequenziellen I/O, so dass jeder plan erfordert mehr als ein paar Prozent der Zeilen, die gesucht werden ist wahrscheinlich billiger zu tun, mit einem scan. In jedem Fall, wenn dies tatsächlich das problem, das Sie sehen, dann hinzufügen actieGroep als INCLUDE-d-Spalte in IhrerIX_STAT_Statistieken_1
index sollte das Problem umgehen. Wie diese:CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken]
(
[foreignId] DESC,
[secondaryId] ASC,
[actieGroep] ASC,
[dagnummer] DESC,
[aantal] ASC
) INCLUDE (actieId) ON [PRIMARY]
den Datentyp der berechneten Spalte actieGroep ist ein string, aber du bist zu vergleichen mit Ganzzahlen (z.B. IN (1,2,3)) in der WHERE-Klausel und CASE-Anweisungen. Wenn SQL beschließt, konvertieren Sie die Spalte anstelle der Konstante, es verletzt Abfrage perf und kann die berechnete Spalte-ausbau-problem (oben beschrieben) wahrscheinlicher. Ich würde stark vorschlagen, ändern Sie Ihre berechnete Spalte definition mit einem integralen Typ, z.B.
CASE WHEN actieId BETWEEN 0 AND 9 THEN actieId
WHEN actieId BETWEEN 10 AND 99 THEN actieId/10
WHEN actieId BETWEEN 100 AND 999 THEN actieId/100
WHEN actieId BETWEEN 1000 AND 9999 THEN actieId/1000
WHEN actieId BETWEEN 10000 AND 99999 THEN actieId/10000
WHEN actieId BETWEEN 100000 AND 999999 THEN actieId/100000
WHEN actieId BETWEEN 1000000 AND 9999999 THEN actieId/1000000
ELSE actieId/10000000 END
du machst eine GRUPPE, die DURCH eine Spalte, die nur einen möglichen Wert. Also, die GRUPPE ist unnötig. Hoffentlich würde die Optimierung klug genug, dies zu wissen, aber Sie können nie sicher sein.
Versuchen Sie es mit einer OPTIMIZE FOR-Hinweis, anstatt direkt zwingen Indizes, die können den Fehler umgehen, die Sie mit Ihrem Hinweis
Craig Freedman ' s post http://blogs.msdn.com/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx beschreibt häufige Ursachen der hint-verbundene Fehlermeldung erscheinen, dass Sie immer beim KOMPILIEREN verwendet wird. Möchten Sie vielleicht zu prüfen, die post und stellen Sie sicher, dass Sie immer die neuesten updates zu SQL Server.
Ich bin mir sicher, dass Sie dies bereits getan haben, aber Sie können bauen wollen, eine "clean-room" - version Ihrer Daten, indem wir tun, was wir tun: erstellen einer neuen DB, verwenden Sie die DDL, die in Ihrer Frage um die Tabellen zu erstellen und füllen der Tabellen mit Daten. Wenn die Ergebnisse erhalten Sie sind anders, schauen Sie closley auf das schema in der realen Tabelle und die Indizes, und sehen, ob Sie sich unterscheiden.
Wenn keines dieser Werke, Kommentar und kann ich schlage vor, einige mehr wilde Ideen. 🙂
Außerdem fügen Sie bitte die genaue version und den update-level von SQL Server auf deine Frage!
Welcher Datentyp ist foreignId in der Tabelle? Wenn es int ist, dann sind Sie wahrscheinlich immer eine implizite Konvertierung verhindert index sucht. Wenn der Datentyp in der Tabelle ist vom Datentyp int und dann definieren Sie den parameter auf int als gut und man sollte einen index seek (kein index-scan) für diese Abfrage.
könnte es sein, die parameter sniffing, also versuchen Sie so etwas wie dieses:
Lesen Sie mehr anout parameter sniffing:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Zuerst sollte ich sagen, dass die Indizes, die Sie erstellt haben, sind nicht optimal, da Sie nur verwendet werden, um filter auf
foreignId
.SQL Server
ist nicht in der Lage zu tunSKIP SCAN
und Sie haben einesecondaryId
im index, die nicht gefiltert wird mit einem range-Bedingung.Daher Ihren Zustand auf
foreignId, actieGroep, dagNummer
nicht Ertrag einer begrenzten Anzahl von Bereichen und ist nicht ganz sargable. Es können filter nur aufforeignID
, nicht auf das ganze set.Nun mit Ihrem aktuellen index.
Ich gerade erstellt Ihren Tischen und füllten Sie mit zufälligen Daten mit diesem script:
und es verwendet
INDEX SEEK
egal was, was bedeutet wahrscheinlich, dass das problem mit der Datenverteilung.Würd ich dir empfehlen zu einem zusätzlichen index mit
secondaryID
entfernt, wie diese:WENN Sie trotzdem möchten, verwenden Sie Ihren aktuellen index, könnten Sie bitte diese Befehle ausführen:
Jeder Befehl drei resultsets.
Könnten Sie bitte post resultsets
1
und2
von jedem Befehl, und drei Zeilen aus resultset3
mit WertRANGE_HI
nur oben, nur unten und gleich873926
?Die Fehlermeldung, die Sie erhalten haben, können erzeugt werden, wenn es zu widersprüchlichen abfragehinweise in Ihrer Abfrage.
Können Sie die Abfrage ausführen, einschließlich dem Hinweis, außerhalb der gespeicherten Prozedur?
Einen alternativen Gedankengang, haben Sie schon Tests/Ausführung der gespeicherten Prozedur mit einem anderen parameter-Wert? Es ist möglich, dass der Wert des Parameters verwendet, um das ursprüngliche Ausführungsplan ist nicht ein geeigneter Kandidat für alle Aktivitäten. Möchten Sie vielleicht zu prüfen erneutes kompilieren der gespeicherten Prozedur, um zu sehen, wenn ein anderer Ausführungsplan hergestellt wird zwischen verschiedene Durchläufe mit verschiedenen Parametern.
Sollten Sie sicherstellen möchten, dass ein neuer Abfrageplan ist berechnet für jede Ausführung der gespeicherten Prozedur dann können Sie Gebrauch machen von die WITH RECOMPILE-Klausel. Dies sollte die Ausnahme und NICHT die Regel. Überprüfen Sie das Verhalten der Prozedur ist und es plangenerierung durch die Prüfung.
Versuchen, diese und sagt uns das Ergebnis:
DBCC FLUSHPROCINDB: dient zum löschen der gespeicherten Prozedur-cache für eine bestimmte Datenbank auf einem SQL-Server, nicht die gesamte SQL-Server. Die Datenbank-ID-Nummer betroffen sein müssen eingegeben werden als Teil des Befehls.
Können Sie diesen Befehl verwenden möchten, vor der Prüfung, um sicherzustellen, dass die vorherigen gespeicherten Prozedur Pläne nicht negativ beeinflussen-Test-Ergebnisse.
Beispiel:
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'Datenbankname')
DBCC FLUSHPROCINDB (@intDBID)
Ich gesehen habe, ein ähnliches Verhalten vor, wo es tatsächlich in der index-Hinweis und tun etwas Schlimmeres mit ihm (ungefiltert index-scan mit bookmark lookup).
Einer dieser vier sollte helfen:
1) Anhängen ;-T4102;-T4118 zu SQL Server 2005-Start-Parameter (möglicherweise gelten für SQL 2008). Hinweis: dieses holt Sie die SQL 2000-schlechte Handhabung, NICHT IN Abfragen, die in SQL 2005.
2) UPDATE STATISTICS [dbo].[STAT_Statistieken] MIT FULLSCAN
3) OPTION (MAXDOP 1) - manchmal ist die Parallelität Ursachen wirklich dumm Abfragen generiert werden
4) zur Sicherstellung der index ist online.
Beachten Sie auch, dass, wenn Sie erstellen einen index für eine Tabelle in einer gespeicherten Prozedur erstellte, dass der index nicht vorhanden ist, beim kompilieren der gespeicherten Prozedur Abfragen, damit es nicht verwendet werden. Da Ihre Tabelle erstellt wird, Global in " dbo " - ich nehme an, dass ist hier NICHT der Fall.
EDIT: manchmal wünschte ich, es war eine wahre forceplan, wo man Schlüssel in die direkte Planung und mögliche plan wird ausgeführt: eine Art Assembler-ähnliche Sprache, die für die DB.
Wenn Sie übergeben die parameter an, wie viele Zeilen in der Tabelle mit dem JOIN-bezogen auf die gesamte Anzahl der Zeilen in der Tabelle? SQL Server wählt einen index mit unter anderem einem Verhältnis von übereinstimmenden Zeilen zurückgegeben, die von der "JOIN", um die Gesamtzahl der Zeilen in der Tabelle. Wenn es eine große Anzahl von zurückgegebenen Zeilen, bezogen auf die gesamte Anzahl in der Tabelle der index kann ignoriert werden, da SQL Server-Einstellungen-Indizes, in denen die Anzahl der übereinstimmenden Zeilen niedriger ist, bezogen auf die gesamte.
Also, wenn Ihr WÄHLEN und Ihre Gespeicherte Prozedur-Aufruf unterschiedliche Werte für @fid, dann haben Sie vielleicht manchmal den index und das andere mal nicht. Wenn das klingt wie Ihr Problem werfen Sie einen Blick auf "Selektivität-Verhältnis" in google.
Glück!
Versuchen Sie dies und prüfen Sie, ob Sie den NICHT GRUPPIERTEN Index Hat mehr Seiten als die CLUSTERED-INDEX (DIES WÜRDE BEDEUTEN, DASS BILLIGER IST, LESEN Sie DEN GRUPPIERTEN INDEX)
Versuchen Sie, erstellen Ihren index so:
Neu erstellen und Ihr Vorgehen danach