Når man skal tune en SQL Server - specielt en med mange databaser - så er serverens metadata uvurderlige til at identificere problemet.
Ofte kan man benytte Performance monitor og profileren til at fine problemet, men jo flere databaser og jo mere aktivitet, jo sværrere er det at skyde sig ind på den eller de queries der er det egentlige problem.
Metadata kan altså hjælpe med at finde den eller de enkelte SQL udtryk man med fordel kan starte med at tune på. Disse data ligger i forskellige interne hukommelsesstrukturer i SQL Serveren, men Microsoft har været så flink at lave funktioner, virtuelle tabeller og globale variabler til at give læse adgang til dem.
Da det både er virtuelle tabeller og systemtabeller så er både fortolkning af data og tabeludseende absolut afhængigt af SQL Server version og aktuelle servicepack. Der er ingen garenti for, at Microsoft ikke vil ændre noget på et tilfældigt tidspunkt.
I artiklen her går jeg ud fra mulighederne på en SQL Server 2000 med Servicepack 3a.
Historik
24.02.2004 - artikel frigivet.
16.08.2004 - layout fejl fixet i script
Interessante Interne tællere
En del forskellige tællere (counters) tillader SQL Server at man ser direkte fra Serveren fremfor via Performance monitor. Data fra disse er kun SQL Server relevante data, dvs. at hvad andre services / applikationer har brugt af CPU, netværkstraffik etc, indgår ikke.
Af speciel interesse ved tuning er følgende (den fulde liste og forklaring til hver enkelt kan findes i Microsoft Books Online):
@@CONNECTIONS
Aktuelt antal sessioner mod SQL Serveren.
@@CPU_BUSY
Antal milisekunder SQL Serveren har brugt CPUerne siden SQL Serveren startede. Optællingen sker pr CPU således at en dual-cpu maskine kan have været aktiv i 120.000 milisekunder efter 1 minut.
@@IDLE
Antal milisekunder CPU'erne har været inaktive siden SQL Serveren startede. Igen, det er kun SQL Servers forbrug af CPU'er der registeres.
@@IO_BUSY
Antal milisekunder SQL Server har brugt på Input/Output operationer siden SQL Serveren startede.
@@PACK_RECEIVED
Antal netværks-datapakker SQL Server har modtaget siden start.
@@PACK_SENT
Antal netværks-datapakker SQL Server har sendt siden start.
@@PACKET_ERRORS
Antal netværks-datapakker med fejl.
@@TOTAL_READ
Antal læsninger SQL Server har lavet fra disk.
@@TOTAL_WRITE
Antal skrivninger SQL Server har lavet til disk.
@@TOTAL_ERRORS
Antal læse/skrive fejl SQL Server har haft på disksystemet.
Hvordan man vælger at benytte data er så op til den enkelte, men det er nu ganske simpelt fx at skrive en stored procedure der beregner netværkstrafik henover et kort tidsrum:
create procedure dbo.VisNettrafik
as
begin
set nocount on
-- Opret variabler
declare @pr1 float, @ps1 float, @pe1 float,
declare @pr2 float, @ps2 float, @pe2 float
declare @nio float
-- Aflæs trafik niveau - vi aflæser som én operation
select @pr1=@@pack_received, @ps1=@@pack_sent, @pe1=@@packet_errors
-- Vent 1 sekund
waitfor delay '00:00:01'
-- Aflæs trafik niveau igen
select @pr2=@@pack_received, @ps2=@@pack_sent, @pe2=@@packet_errors
-- Omregn så vi kan få procent ud
set @nio = (@pr2-@pr1)+(@ps2-@pr1)
-- Vis procentvis fordeling af trafik
select 'Modtaget' as [Traffik], round(100/@nio*(@pr2-@pr1),2) as Procent
union all
select 'Sendt', round(100/@nio*(@ps2-@ps1),2)
union all
select 'Fejl', round(100/@nio*(@pe2-@pe1),2)
end
go
Tilsvarende er det nu en simpel opgave at lave sine egne udtræk til at fortolke diskbelastning og cpuforbrug.
Diskbelastning
Faktisk har SQL Server en indbygget procedure man kan benytte hvis man ikke vil bruge tid på at designe præcis til egne behov.
Funktionen er sp_monitor og den giver en opsummering på de ovenstående data. Dvs afvikler man sp_monitor 2 gange vil anden afvikling give forskellen i fht første.
Fortolkningen af data og hvornår man skal skride ind afhænger af hvad man bruger serveren til.
Det siger sig selv, at hvis @@PACKET_ERRORS eller @@TOTAL_ERRORS begynder at stige, så har man problemer - et eller andet sted. @@TOTAL_ERRORS er jo for alle diske og @@PACKET_ERRORS kan være både fra klienternes netkort og fra serverens.
Begynder antal læsninger fra disk (@@TOTAL_READS) pludselig at stige kraftigt er det heller ikke godt. I den bedste af alle verdener vil SQL Server jo læse de mest brugte data ind i sin cache og så vil diskreads ikke længere stige.
Stiger tælleren pludselig kraftigt er det forbrugsmønsteret på serveren der er ændret - eller SQL Server har ikke længere mulighed for at cache data, måske for lidt ram?
Ofte vil man sige, at spørgsmålet om for lidt ram altid er et JA! - Men da Windows har en ubehagelig lav grænse for hvor meget hukommelse man kan sætte i den (de færreste bruger jo Windows Datacenter Edition), så er det nødvendigt at tune udnyttelsen af den eksisterende hukommelse så den udnyttes bedre.
Generelt kan du kun vurdere om en given aflæsning antyder et problem hvis du ved hvordan serveren normalt ser ud - for en given værdi af normal :-) Det er det Microsoft mener, når de anbefaler at du laver en baseline til performancesammenligning.
Man kan ret nemt opbygge en stored procedure der læser samtlige data fra ovennævnte variabler og gemmer dem i en tabel sammen med et tidsstempel.
Proceduren schedulerer man så til at køre f.eks. hvert minut og så glemmer man alt om den. Efter et par dage kan man så stoppe den schedulerede procedurer, og trække sine data over i Excel eller lignende, hvor man kan lave en række smukke grafer over hver tæller. Nu har man et visuelt sammenligningsgrundlag til senere aflæsninger.
Man skal blot huske, at hver gang man sætter en ny database i produktion, laver større ændringer, så skal man genstarte sin procedure og få de nye data ud.
Men når man nu f.eks. har fundet ud af, at det er disksystemet der er belastet, hvordan finder man så ud af hvilken database der er den skyldige?
Til det formål har vi en funktion der kan fortælle os hvor mange operationer der sker i den enkelte fil i en database.
Funktionen hedder
fn_virtualfilestats(dbid,fileid)
og skal som vist kaldes med database id og fil id for den enkelte database.
Data som den returnerer er antal læsninger og skrivninger i filen (inkl. datamængder i bytes) samt antal milisekunder brugere har ventet på I/O. Det eneste der mangler er, at få et samlet overblik over hver enkelt fil i hver enkelt database. Det kan nemt gøres ved en procedure som nedenfor.
create procedure dbo.VisFilStatistik
as
begin
set nocount on
-- opret midlertidig tabel til info
create table #vfs (
dbnavn varchar(128),
filnavn varchar(500),
dbid int,
filid int,
timestamp int,
reads int,
writes int,
bytes_read int,
bytes_written int,
waittime_ms int
)
create table #dbfiler(
dbid int,
dbnavn varchar(128),
filid int,
filnavn varchar(400)
)
-- opret lidt variabler og en cursor
declare @dbid int, @filid int, @dbnavn varchar(128)
declare @filnavn varchar(500), @sql varchar(500)
declare crsr cursor local fast_forward for
select dbid, name
from master.dbo.sysdatabases
open crsr
fetch next from crsr
into @dbid, @dbnavn
-- loop gennem cursoren, og opsaml data om filer
while @@fetch_status=0 begin
set @sql = 'insert into #dbfiler '+
'select '+cast(@dbid as varchar)+','+
''''+@dbnavn+''',fileid, name from '+
'['+@dbnavn+'].dbo.sysfiles'
exec (@sql)
fetch next from crsr
into @dbid, @dbnavn
end
close crsr
deallocate crsr
-- cursoren er nu lukket - lad os se på hvordan filerne har det.
-- opret cursor til at opsamle fil data
declare crsr cursor local fast_forward for
select dbnavn, filnavn, dbid, filid
from #dbfiler
open crsr
fetch next from crsr
into @dbnavn, @filnavn, @dbid, @filid
-- opsaml data fra hver fil i hver database og gem data.
while @@fetch_status=0 begin
insert into #vfs
select @dbnavn, @filnavn, *
from :: fn_virtualfilestats(@dbid, @filid)
fetch next from crsr
into @dbnavn, @filnavn, @dbid, @filid
end
close crsr
deallocate crsr
-- Vis opsamplede data
select *
from #vfs
end
go
I outputtet har man nu en total oversigt over antal operationer og antal bytes overført i de enkelte filer i den enkelte database - samt hvor længe brugeren venter på data.
Det er nu en smal sag at finde ud af hvilken database man f.eks. skal flytte til nye diske for at glæde brugerne. En anden mulighed for at afhjælpe høj ventetid i en enkelt database fil kan være at splitte den op i flere og fordele data mellem dem.
Når man så har identificeret en database som et problembarn, så kan man begynde at køre en profiler mod den, og den alene. Profileren kan sættes op til at filtrere så det kun er aktivitet mod en enkelt database der opfanges.
En god ting at holde øje med er tablescan. Et tablescan er, når SQL Server beslutter sig for at søge en tabel igennem fra A til Z efter matchende data.
En af grundene til at SQL Server vælger tablescan kan f.eks. være at der mangler indeks der dækker forespørgslen eller at de indeks der er, ikke er ordentligt opdaterede (Normalt opdateres indeksstatistikkerne automatisk når 20% af tabellen er blevet opdateret/ændret).
SQL Server har en funktion man kan kalde som giver informationen om hvornår et givent indeks sidst er blevet opdateret.
funktionen har følgende syntaks:
stats_date(tableid, indexid)
Id-værdierne som skal bruges som parametre kan findes i systemtabellerne dbo.sysobjects og dbo.sysindexes i den enkelte database.
Kaldet ser således ud:
select *
from :: stats_date(1,1)
Jeg vil undlade at opbygge en procedure til at liste alle indeks opdateringer i en database - skelettet til en sådan kan jo ses ovenfor, i proceduren VisFilStatistik().
Hvis man finder, at et eller flere indeks trænger til en ekstra opdatering, så kan man tvinge en sådan igennem ved kommandoen:
update statistics [dbo].[mytable]
with all
Man kan begrænse en update statistics til et enkelt indeks på en tabel hvis ønsket og man kan ændre det udsnit af tabellen den sampler. WITH ALL som angivet ovenfor, fortæller SQL Server at samtlige statistikker skal opdateres for tabellen dbo.mytable. Se Books Online for flere detaljer.
Buffercache og rekompileringer
Men det er jo ikke altid, at det er diskene der er problemet. Det meste af tiden kan man vende øjet mod den SQL der afvikles på boksen, og ved at optimere den kan man få væsentligt mere ud af sin server.
Al SQL der fyres af mod en SQL Server lagres i dens buffer cache. Her ligger både SQL-teksten, den kompilerede SQL og planen for hvordan data skal hentes sammen med oplysninger om hvor dyrt det har været at kompilere og finde på planen. Behøver jeg nævne at det også her kan ses hvor meget ram den enkelte query tager samt hvor mange der har brugt det enkelte objekt?
Med andre ord - det er lidt af en guldgrube at komme ind og kigge i buffer cachens data. Og heldigvis, det kan vi komme til.
Microsoft har lavet en virtuel tabel kaldet dbo.SysCacheObjects som findes i MASTER databasen. Den er udførligt beskrevent i Books Online, så det vil jeg ikke bruge tid på her, i stedet vil jeg give et par små SQL udtræk som kan hjælpe:
RAM forbrug per database:
select db_name(dbid) as DB, sum(pagesused)*8 as 'KBytes'
from master.dbo.syscacheobjects (nolock)
group by dbid
order by 2
Antal objekter der skal rekompileres ved kald:
select db_name(dbid) as DB, count(*) as Objects
from master.dbo.syscacheobjects (nolock)
where uid<>-2
group by dbid
order by 2
Og lidt mere avanceret, de forskellige objekttyper fordelt per database og deres hukommelsesforbrug.
select isnull(db_name(dbid),'All DBs') as DB,
isnull(objtype,'Totals ') as Types,
count(*) as objects,
sum(pagesused)*8 as KBytes
from master.dbo.syscacheobjects (nolock)
group by dbid, objtype
with cube
Der er meget andet at hente fra dbo.SysCacheObjects - igen vil jeg anbefale at kigge i Microsoft Books Online.
Parameterisering af queries
Når man laver et udtræk af tabellen dbo.SysCacheObjects, så finder man en række forskellige objekt-typer i kolonnen ObjType. Den kolonne er ikke helt uinteressant, for den fortæller faktisk lidt om hvad SQL Server foretager sig med en given query.
Når man fyrer en query eller en stored procedure sker der nemlig en hel masse. Først søger SQL Server SysCacheObjects gennem for om queryen findes med den aktuelle objektejer.
Gør den ikke det starter Query Rewriteren med at optimere ens forespørgsel - den ændrer simpelthen ikke-optimale SQL udtryk til mere optimale. Derefter kompilerer den ens query og laver en Query Plan for den.
Query Planen er en plan der fortæller i hvilken rækkefølge data skal hives ud af de enkelte tabeller, og hvilke join typer der skal benyttes. SQL Server gør simpelthen det, at den laver et utal af planer og måler deres effektivitet (cost). Til disse valg benytter SQL Server de statistikker som ligger på indeks og kolonner. Disse informationer kan faktisk ses direkte i tabellen dbo.SysIndexes i den enkelte database.
Efter et stykke tid vælger den så den plan der har den laveste omkostning at udføre. Og så, langt om længe, hiver den data ud og præsenterer dem for brugeren.
Selvom vi taler om milisekunder for alt det overstående, så tager det alligevel tid. Ofte er omkostningen ved at gøre klar til en query lige så stor som det at finde de aktuelle data.
Ovenstående forklarer også hvorfor det er vigtigt at skrive SQL der ikke skal rekompileres ved hvert kald (se også mine artikler om grundlæggende performancetuning, part 1 - 3) samt hvorfor man skal parameterisere.
Hvis man f.eks. har en query som nedenfor:
select navn, adresse
from dbo.minevenner
where navn = 'Jens'
Så skal SQL Server først igennem hele ovenstående cirkus før den kan finde alle Jens'er. Og når man bagefter vil finde Torben, så skal den til det igen.
Hvis man istedet får en parameter (variabel) lagt ind på navnet, så vil SQL Server kunne genbruge den foregående plan, og vupti, omkostningerne ved at finde Torben og efterfølgende er absolut minimale.
Det "ved" SQL Server naturligvis godt, så den forsøger efter bedste evne at parameterisere - de auto-parameteriserede queries kan ses - det er dem hvor SQL'en ser således ud:
(@1=varchar(20))select navn, adresse
from dbo.minevenner
where navn=@1
Et hurtigt kig i dbo.SysCacheObjects, og man vil konstatere at der er nogle stykker - men man vil også konstatere at der er rigtig mange simple forespørgsler a la den ovenstående der ikke er parameteriseret.
Med andre ord - viljen til at autoparameterisere er der, men evnen er ikke så god! Det er altså op til udvikleren at sikre at alle queries bliver parameteriseret.
Hvordan man reelt gør det afhænger af det enkelte udviklingsprog og valgte forbindelsesstype (ODBC/JDBC/ADO) men lidt ide kan man få i Microsoft Books Online. Start med artiklerne "Parameter Queries" og "How to prepare and execute a statement (ODBC)" og check referencerne derfra.
At finde de mest brugte queries
Ok, nu har vi fundet en række interessante queries man kan tune på. Det er også fint nok, men hvorfor "spilde" tiden på at skære 5 sekunder af en query som måske kun kaldes hver 14. dag? At skære 10 milisekunder af en query som kaldes hvert sekund giver jo reelt set meget mere luft på serveren.
For at finde de queries skal vi rette blikket mod dbo.SysProcesses. Den indeholder aktuelle aktive sessioner - og har, efter SP3, fået et par ekstra kolonner, heriblandt den vi skal bruge, nemlig sql_handle.
Når sql_handle indeholder en værdi har man adgang til kilden for den aktuelle query der er under afvikling. Adgangen sker via funktionen fn_get_sql(handle). Lige så snart SQL Server er færdig med at udføre opgaven sættes sql_handle igen til 0x0.
Nedenstående cursor-query trækker alle informationer om aktuelt kørende query ud, at udbygge den med indsættelse i en log-tabel og skedulering til - f.eks. 4 gange i sekundet - at hente data lader jeg stå som en øvelse for læseren :-)
declare @handle binary(20)
declare crsr cursor local fast_forward for
select sql_handle
from master.dbo.sysprocesses
where sql_handle<>0x0
open crsr
fetch next from crsr
into @handle
while @@fetch_status=0 begin
select *
from ::fn_get_sql(@handle)
fetch next from crsr
into @handle
end
close crsr
deallocate crsr
Når man så har ladet sit job køre noget tid (timer eller dage - det afhænger af hvad man bruger serveren til) så laver man et opslag i loggen og tæller antal forekomster af hver SQL. De SQL'er der har flest forekomster, det er dem der enten kører tit eller køre meget længe - og det er dem der er værd at tune først.
I dbo.SysProcesses finder man også information om forbrugt CPU per session, I/O og hvad netprotokol klienterne benytter - altsammen noget man kan have glæde af.
En anden interessant ting ved dbo.SysProcesses er, at man her kan finde information om ventetider. Det hjælper jo ikke meget at tune SQL hvis det reelt set er netkortet der ikke kan følge med.
Kolonnerne med ventetidsinformation er waittime, waittype, lastwaittype og waitresource. Nærmere information om dem kan man finde i Books Online.
Med andre ord - det er bare om at komme igang med at se hvad der egentlig sker på ens server, og få tunet det ordentligt.
God fornøjelse
Troels
