torsdag 5 november 2015

Vanliga problem som DBA-n råkar utför – Parameter Sniffing

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.


onsdag 4 november 2015

Hur mycket resurser tar min databas?


Har en kund som frågat mig flera gånger om det enkelt går att se vilken eller vilka databaser som används i servern och hur mycket resurser dessa tar.

Tänkte här skriva kring hur man skulle kunna bygga ett chargeback system i SQL på ett sätt som jag tycker verka vara en enkel lösning för dom som vill ha lite koll på vilka databaser som används mest i SQL servern med information om resursnyttjande.

Första frågan är vad vill vi kolla på? För att göra det så enkelt som möjligt så tänkte jag CPU nyttjande och hur mycket MB som läses och skrivs mot databasen. För att lösa detta så är det Extended Event som gäller. Man skulle kunna göra det via resource Governor också men i en miljö med många databaser skulle det blir väl komplext att hantera. Dessutom skulle man behöva hantera när man lägger till och ta bort databaser.

En lösning med Extended Event skulle kunna vara så här:

Vi skapar en session i SQL som kollar, SQL_BATCH_COMPLETED samt RPC_COMPLETED. Sen tittar vi efter databaser som inte är systemdatabaser, och sessioner som inte är system sessioner samt där det är någon aktivitet (CPU). Eventuellt skulle man kunna filtrera med på tex applikation så man inte få med saker från SQL Agenten som kanske gör index maintenance. I skriptet nedan gör vi det.

CREATE EVENT SESSION [ResourceUsageStats] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(0)
    ACTION(sqlserver.client_app_name,sqlserver.database_id)
    WHERE ([sqlserver].[database_id]>(4) AND [sqlserver].[database_id]<>(32767) AND [sqlserver].[session_id]>(50) AND [cpu_time]>(0) AND NOT [sqlserver].[client_app_name] like N'%SQLAgent%')),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(0)
    ACTION(sqlserver.client_app_name,sqlserver.database_id)
    WHERE ([sqlserver].[database_id]>(4) AND [sqlserver].[database_id]<>(32767) AND [sqlserver].[session_id]>(50) AND [cpu_time]>(0) AND NOT [sqlserver].[client_app_name] like N'%SQLAgent%'))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\ResourceUsageStats.xel',max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Sessionen ovan skapar en fil i C:\Temp och den kan vi lätt läsa med följande.

SELECT CAST(event_data AS XML) AS event_data_XML
INTO #Events
FROM sys.fn_xe_file_target_read_file('C:\Temp\ResourceUsageStats_*.xel', null, null, null) AS F;
select * from #Events

Resultatet är kanske inte så lockande direkt. Bara rader med XML.










Vi väljer att läsa in det till en annan temptabell.


SELECT
  event_data_XML.value ('(/event/action [@name=''database_id'']/value)[1]', 'INT') AS DBID,
  event_data_XML.value ('(/event/data [@name=''cpu_time'']/value)[1]', 'BIGINT') AS cpu_time,
  event_data_XML.value ('(/event/data [@name=''logical_reads'']/value)[1]', 'BIGINT') AS logical_reads,
  event_data_XML.value ('(/event/data [@name=''writes'']/value)[1]', 'BIGINT') AS writes
INTO #Queries
FROM #Events;

Sen aggregera vi ihop det hela och joinar med sys.databases.

SELECT a.dbid,
b.name,
SUM(cpu_time) as CPU,
MBRead = ((SUM(logical_reads) * 8 ) / 1024),
MBWrite = ((SUM(writes) * 8 ) / 1024),
MBTotal = (((SUM(logical_reads) + SUM(writes)) * 8) / 1024)
FROM #Queries a
inner join sys.databases b
            on a.dbid = b.database_id
group by b.name, a.dbid;

För vår test databas få vi nu ett snyggt resultat, förutsatt att vi läst och skrivit lite mot databasen.








Mitt exempel kanske inte bli så imponerande men tänk om du har hundra databaser.