Langsom update i temp tabel

Hvor er TEMPDB placeret i forhold til Windows pagefile?

En temporær tabel findes kun i ram - så hvis du har et problem med fri ram, kan det simpelthen være et spørgsmål om swap. Typisk vil du have problemer med fri ram hvis der på samme maskine kører en webserver eller exchange.

Hvis TEMPDB er placeret sammen med pagefile eller med log til øvrige databaser, så kan du også komme ud i en situation med diskcontention - dvs. at der er for mange I/O operationer til at disken kan følge med.

Vi snakker multicpu 4 gb ram servere der ikke laver andet end at være SQL server! Så jeg tror ikke det er et ram problem eller disk io...  Har du nogen andre forslag fordi det er da for underligt :)

Har du parallel query slået til? 

Parallel query kan i nogen tilfælde være problematisk - man kommer i situationer hvor en query ender med at blokere sig selv - al CPU kraft ryger på administration af parallismen....

For sjovs skyld - prøv lige at køre en DBCC CHECKDB() i TEMPDB.

En anden ting; Kontroller at der ikke er slået Auto-Shrink eller Auto-close til på nogen af de baser du benytter.

Hvis du besøger www.quest.com så har de et værktøj der hedder Spotlight for SQL Server. Demo udgaven er begrænset, men den kan give dig et hurtigt indtryk af hvad der sker på serveren...

Parallel query bruges kun på servere med mere end 1 cpu. Problemet sker også på enkelt cpu servere, så det kan heller ikke være det.

DBCC CHECKDB i TEMPDB viser intet unormalt. Jeg har prøvet KEEPPLAN option på selects og updates i temptabellerne. Det ændrer intet heller.

Auto-Shrink er slået på men ikke Auto-close. Skulle det give lige dette problem? Tror jeg ikke...

Help!

Autoshrink bør være slået fra.  SQL Server ender med at stå og shrinke databasen for kort tid efter at udvide den igen. Det er ekstremt bekostelige affærer hvor SQL Serveren faktisk ikke kan svare på forespørgsler imens.

Så hvis du har autoshrink på din TEMPDB, så kan det godt være det der generer.

Jeg ville sætte TEMPDB til fx 2 GB med mulighed for at vokse i 50 MB blokke, og en Log fil på fx 500 MB med samme vokseværk.

Du kan ikke have autoshrink og close på tempdb...  Jeg snakkede om vores applications database

tempdb er 8 mb og vokser med 10 % både i data og log. loggen er 1 mb.

Hvis problemet var at den stod og voksede ville det jo kun være et problem første gang, så jeg tror heller ikke dette er problemet :(

HELP!

Kunne også skyldes manglende statistik .. tyder lidt i den retning ..

Alt statistik er slået til...

Hmmm... vil du publicere en sp_configure så vi kan se hvordan serveren er sat op.

Dette er vores udviklingsserver...  Kundens har 2 cpu'er og 4 gb ram...

name                                minimum    maximum    config_value run_value 
----------------------------------- ----------- ----------- ------------ -----------
affinity mask                      -2147483648 2147483647  0            0
allow updates                      0          1          0            0
awe enabled                        0          1          0            0
c2 audit mode                      0          1          0            0
cost threshold for parallelism      0          32767      5            5
Cross DB Ownership Chaining        0          1          0            0
cursor threshold                    -1          2147483647  -1          -1
default full-text language          0          2147483647  1033        1033
default language                    0          9999        0            0
fill factor (%)                    0          100        0            0
index create memory (KB)            704        2147483647  0            0
lightweight pooling                0          1          0            0
locks                              5000        2147483647  0            0
max degree of parallelism          0          32          0            0
max server memory (MB)              4          2147483647  348          348
max text repl size (B)              0          2147483647  65536        65536
max worker threads                  32          32767      255          255
media retention                    0          365        0            0
min memory per query (KB)          512        2147483647  1024        1024
min server memory (MB)              0          2147483647  348          348
nested triggers                    0          1          1            1
network packet size (B)            512        65536      4096        4096
open objects                        0          2147483647  0            0
priority boost                      0          1          0            0
query governor cost limit          0          2147483647  0            0
query wait (s)                      -1          2147483647  -1          -1
recovery interval (min)            0          32767      0            0
remote access                      0          1          1            1
remote login timeout (s)            0          2147483647  20          20
remote proc trans                  0          1          0            0
remote query timeout (s)            0          2147483647  600          600
scan for startup procs              0          1          0            0
set working set size                0          1          1            1
show advanced options              0          1          1            1
two digit year cutoff              1753        9999        2049        2049
user connections                    0          32767      0            0
user options                        0          32767      0            0

Eneste "unormale" jeg kan se, er at du har sat Set Working Size, men jeg kan ikke tro, at det har den her virkning.

Jeg ville til gengæld nok lige prøve at udvide tempdb så den var min. 100 MB i både log og data.

2 ideer;
Prøv at køre en stats_date(tabelid,indeksid) når du har indsat første række

Prøv at lave temp tabellen og dens indeks + første insert via query analyzer med Show Execution Plan slået til. Så vil du kunne se hvad pokker den har gang i når du kører.

Problemet sker ikke hvis man gør det via query analyser.. Kun gennem en stored proc.

Kan ikke lige hitte ud af at køre stats_date på en temp tabel

Temp tabellen burde være registeret i sysobjects i TEMPDB, ikke i din egen db. Navnet vil være en let forvansket udgave af det navn du har givet (bruges til at sikre unikt navn pr session).

Det lugter for mig af, at du har enten dynamisk sql liggende i din SP, eller har blandet DML og DDL udtryk så du starter med en ekstrem bunke rekompileringer af proceduren.

Det er sådan, at for hver gang du skifter mellem fx CREATE (DDL) og INSERT (DML) der bliver din SP rekompileret - og når statistikkerne i en tabel opdateres, så rekompileres din SP igen.

Du kan altså ikke undgå rekompileringer når du har temp tabeller i en SP, men du kan minske antallet ved at bytte rundt på hvor når de forskellige udtryk bruges.

Option KEEP_PLAN bruges også til at forhindre agressive statistik opdateringer (første opdat på en temp-tabel kommer nemlig efter 6 indsatte rækker).

Evt. prøv at køre en profiletrace med recompile events slået til mens du afvikler din SP - så vil du kunne se hvordan det ser ud.

I øvrigt, hvis du bruger parametre til at styre hvilken kode der afvikles i en SP, så får du også et kompileringsmæssigt overhead + tungere query plan oprettelse.  En typisk struktur der virker tilforladlig men giver dette problem er en sådan

create procedure dbo.vismig(@flag int)
as
begin
  if @flag=1 begin
    select * from mytable order by 1
  end else
  if @flag=2 begin
    select * from yourtable order by 1
  end else
  if @flag=3 begin
    select * from mytable
    union all
    select * from yourtable
  end
end
go

I ovenstående vil Query Optimizeren altså kompilere hele querien, ikke kun den del som @flag egentlig gør nødvendig - og så vidt jeg ved vil den også generere query planer for hver select...

Det er korrekt at jeg har parametre der styrer hvad der sker i proceduren. Den er på over 900 linier og der sker en masse.

Jeg har sat debug text ind i den for at benchmarke og ALT tager stort set ingen tid, undtagen den første tilgang til den ene temp tabel. De efterfølgende måske 2000 opdateringer af temp tabeller og mange opdateringer af rigtige tabeller tager ingen tid. Kun første tilgang af temp tabel. 5-11 sekunder!  Jeg synes stadig det virker skummelt og intet forklarer det. Ihvertfald ikke recompile.
Jeg kan også være heldig at den tager .5 sekund hvilket er underligt selv om jeg ændrer på parametre.

Jeg er på nippet til at give fortabt, men...

hvis du vil scripte sp'en og de nødvendige tabeller, så kan du maile det til mig på trer.fjerndette@mail.dk - så vil jeg godt lige prøve at kigge på det.

Det er flink af dig men det vil kræve en masse data i tabellerne også så det vil blive lidt omstændigt...  Jeg må finde på noget andet

Ok, så held og lykke - jeg kan vist ikke hjælpe.

Prøv dog lige at kalde fn_virtualfilestats() på filerne i tempdb - måske kan du se noget vedr. ventetid der.

Fik lige en ide. Prøv at ændre dine temp tabeller i proceduren til permanente.

Check så performance - hvis det fungerer bedre, så skal du blot tilføje spid'en (@@SPID) til dine data og til dine where/join-betingelser for at kunne få det unikt pr bruger.

Hmmm det kunne være en idé. Det havde jeg ikke overvejet...  Checker det ud...

Endnu et forslag (ganske desperat efterhånden :-)

Du kan også prøve at ændre fra temp tabeller til tabel-variabler. Du kan se et eksempel her http://www.eksperten.dk/artikler/120

Ok det ændrede ikke noget at jeg gik fra temp. tabel til statisk tabel. Samme delay sker. Dette må betyde at det har noget med recompile at gøre selv om det ikke giver mening for mig at det sker lige der.  Jeg kan ikke umiddelbart ændre på strukturen, det vil kræve en total rewrite og det kan ikke lade sig gøre.

Jeg har prøvet tabel variabler efter jeg læste den artikkel. Hjalp heller ikke noget :(

Jeg giver op

problemet var at jeg brugte et view som åbenbart pga størrelse eller noget gav dette underlig delay. jeg skrev et nyt og mindre view istedet og nu tager det ingen tid