Som
dba ser jag ofta att kunder råkar utför den så kallade paramater sniffingen.
Applikationer och frågor i SQL tar helt plötsligt och oförklarligt mycket
längre tid än vad dom brukar. När något går trögt så är det till DBA-n man vänder
sig och frågar vad som är fel. Som jag ser det är det kanske inte en DBA-s roll
att lösa det då det är mer en utvecklar sak men man måste så klart vara
behjälplig med att lösa problemet. Så hur man än gör så är det på ens bord. I
denna artikel skall jag kort beskriva vad det är, hur man identifiera och sen
löser problemet.
Förklaring
Från min erfarenhet är problemet oftast orsakat av en query
som har en kolumn i where klausulen med stor skillnad på data.
Som i mitt exempel att man tex har ett värde av Kiruna och 1 miljon av värdet
Halmstad. När det är sån stor skillnad så kommer SQL förmodligen välja olika
query plans beroende på hur man skall hämta datat, för att göra det på effektivaste
sättet. Tex så gör vi en select stjärna och respektive värde I where klausulen.
För den enstaka raden väljer SQL en Key Lookup medans det är mer effektivt att
göra en Clustred Index Scan för en miljon rader.
SELECT
* FROM dbo.customer WHERE City = 'Kiruna'
SELECT
* FROM dbo.customer WHERE City = 'Halmstad'
Vad händer nu när vi skapar en stored procedure för denna
query istället? Den kommer med stor sannolikhet kunna ha problem med parameter
sniffing. Så låt oss testa.
CREATE
PROCEDURE USP_GetCustomerByCity @City VARCHAR(50)
AS
SELECT
* FROM dbo.customer WHERE City = @City
Om vi nu kör denna procedur med den höga selektiviteten,
alltså värdet ’Kiruna’ kommer proceduren att kompileras för detta värde. Detta
är helt i sin ordning för det är så SQL fungerar. Om vi nu kör den med det
andra värdet ’Hamlstad’ så är den ju fortfarande optimerad för värdet ’Kiruna’.
SQL vet att det bara finns en rad med detta värde och väljer således den query
plan som är bäst för hög selektivitet. Den gör en Key Lookup.
EXEC
USP_GetCustomerByCity 'Halmstad'
Hade SQL valt en
Clustred Index Scan istället hade det blivit en dramatisk skillnad i antal
reads. Titta vi på outputen från Statistics IO ser det ut så här.
Table
'customer'. Scan count 1, logical reads 7316
Table
'customer'. Scan count 1, logical reads 3002730
Hur kan man hitta problem procedurerna
Det finns tyvärr inget enkelt sätt att hitta dessa problem. En
möjlig väg är att titta i Plan cachen efter querys som har stora skillnader i
logical reads. DMV-en sys.dm_exec_query_stats har information om tex hur många
gånger en query har körts, hur mycket read, cpu mm den tagit. Om vi kör denna
fråga nedan så få vi lite intressant information.
WITH
CTE as (
SELECT qs.execution_count,
SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset
= -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1
) AS
[Individual Query]
,qt.text AS [Parent
Query],
qs.last_logical_reads,
qs.max_logical_reads,
qs.min_logical_reads,
qs.total_logical_reads
FROM sys.dm_exec_query_stats AS
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 28
), CTE2 as (
select *
,
max_logical_reads - min_logical_reads as diff
FROM
CTE)
SELECT * FROM CTE2
ORDER BY diff DESC;
Frågan returnera information från Plan Cachen för vår procedur.
Den säger att den blivit kört sju gånger och som mest har den genererat 3002734
reads jämfört med sex som minst. Rätt stor skillnad här så i detta läge så skulle
man kunna misstänka att denna procedur har eller kan drabbas av parameter
sniffing problem.
Åtgärder
En åtgärd som jag kommit på mig själv att göra är att bara
köra et sp_upstestats. En uppdatering av statistiken triggar nämligen en omkompilering.
Men detta är väl inte en bra lösning i längden utan bara något man oftast gör
för att det är bråttom. Det finns riktiga lösningar på det. Nedan följer några
med kort förklaring.
Recompile
Skapa proceduren med optionen recompile.
CREATE PROCEDURE
[dbo].[USP_GetCustomerByCity] @City VARCHAR(50)
WITH
RECOMPILE
AS
SELECT
* FROM dbo.customer WHERE City = @City
Optimize For
Optimera proceduren för ett värde som du vet är representant
för alla värde med optionen Optimize for.
CREATE PROCEDURE
[dbo].[USP_GetCustomerByCity] @City VARCHAR(50)
AS
SELECT
* FROM dbo.customer WHERE City = @City
OPTION
(OPTIMIZE FOR
(@City =
'Halmstad'))
Plan Guide
Denna option kan vara ett sätta att lösa det om man inte har
möjlighet att ändra i koden för tex en tredjeparts applikation.
Local Variable
En annan variant är att man använder en lokal variabel i sin
procedur. Gör man detta vet inte SQL riktig vad den skall använda för värde och
titta då i statistiken och optimera utefter det. Skall inte beskriva hur här
men att göra det kan bli både bättre och sämre.
Sammanfatting
Problemet är relativt vanligt enligt min uppfattning men jag
tycker att förhållandevis många utvecklare är dålig koll på vad det är. Hoppas
att man i kommande SQL versioner enklare kan hitta problemen. Har tittat lite
på Query Storen i SQL 2016. Där finns en del statistik över frågor så man
enklare skulle kunna jämföra hur frågor körts. Lite som att använda DMV jag
nämnde tidigare men den visa ju bara vad som finns i cachen, inte över tid.