Frage Wie entferne ich einen bestimmten fehlerhaften Plan aus dem SQL Server-Abfragecache?


Wir haben eine bestimmte SQL Server 2008-Abfrage (keine gespeicherte Prozedur, sondern dieselbe SQL-Zeichenfolge - wird alle 5 Minuten ausgeführt), die einen sehr schlechten Abfrageplan zwischenspeichert.

Diese Abfrage wird normalerweise in einigen Millisekunden ausgeführt, aber mit diesem fehlerhaften Abfrageplan dauert es mehr als 30 Sekunden.

Wie mache ich operativ? Entfernen Sie nur den einen schlecht gecachten Abfrageplan von SQL Server 2008, ohne den gesamten Abfragecache auf dem Produktionsdatenbankserver wegzublasen?


26
2017-12-05 06:16


Ursprung




Antworten:


Ich habe ein paar Dinge herausgefunden

select * from sys.dm_exec_query_stats

zeigt alle zwischengespeicherten Abfragepläne an. Leider wird dort kein SQL-Text angezeigt.

Sie können den SQL-Text jedoch wie folgt mit den Plänen verknüpfen:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

Von hier ist es ziemlich trivial, ein hinzuzufügen WHERE Klausel, um die SQL zu finden, die ich kenne, ist in der Abfrage, und dann kann ich ausführen:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

um jeden Abfrageplan aus dem Abfrageplancache zu entfernen. Nicht gerade einfach oder bequem, aber es erscheint arbeiten..

Bearbeiten: Dumping der ganz Query-Cache funktioniert auch und ist zumindest nach meiner Erfahrung weniger gefährlich als es klingt:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

30
2017-12-05 06:46



Der Hinweis, einen Planhinweis zu verwenden, steht trotzdem. - Remus Rusanu
Ich fand das, nachdem meine Anfrage magisch aufgefrischt wurde, es ist ein schlechter Plan, aber ich plane es beim nächsten Mal zu testen. Ein Planhinweis hilft nicht, wenn die Abfrage unter 'optional-itis' leidet - wo sie viele optionale Parameter hat und für einen Satz optimiert wurde, und dann für einen anderen Satz ausgeführt wird. Es gibt keinen optimalen Plan, der für diese Art von Abfrage hinzugefügt werden kann. Es gibt einen optimalen Plan für einen Parametersatz, der wiederum für einen anderen Parametersatz schrecklich ist. - Nick.McDermaid


Wenn Sie wissen, wie der gute Plan aussieht, verwenden Sie einfach ein Planhinweis.

Sie können einen bestimmten Cache-Eintrag nicht entfernen, aber Sie können einen gesamten Cache-Pool mit löschen DBCC FREESYSTEMCACHE(cachename/poolname).

Sie können den Cache-Namen eines fehlerhaften Abfrageplans abrufen, wenn Sie den Plan-Handle haben (aus sys.dm_exec_requests.plan_handle für die session_id in Schwierigkeiten während der Ausführung oder von sys.dm_exec_query_stats nach der Ausführung):

select ce.name
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce on cp.memory_object_address = ce.memory_object_address
where cp.plan_handle = @bad_plan

Jedoch haben alle SQL-Pläne den Namen "SQL-Pläne", was die Auswahl des richtigen für DBCC FREESYSTEMCACHE zu einer ... schwierigen Wahl macht.

Aktualisieren

Vergiss es, vergaß es DBCC FREEPROCCACHE(plan_handle)Ja, das wird funktionieren.


5
2017-12-05 06:36



Die Möglichkeit, einen Plan_Handle an DBCC FREEPROCCACHE zu übergeben, ist in SQL Server 2008 und nicht in SQL Server 2005 verfügbar. - Mario


Das FREISUCHE Lösung ist in Ordnung, aber ein direkterer Weg, dies zu tun ist zu verwenden OPTION (REKOMPIL) auf Ihrer SQL-Zeichenfolge (Sie erwähnten, dass es kein SP war), teilt dies der Engine einen Single-Use-Plan mit, weil Sie wahrscheinlich vermuten, dass Parameter Sniffing oder Ihre Statistiken sich drastisch von run to run unterscheiden und Sie vermuten, dass ein Bad Cache ist Planproblem.

DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = 'SELECT * FROM Table WHERE Column LIKE @NAME OPTION (RECOMPILE)'
EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName' 

1
2018-05-29 16:22