Förord

Updates hur de utförs i SQL 6.5 av Alexander Chigrik.

Innehåll

  » Direct Update
  » In-Place Update Metod
  » On-Page Delete/Insert Metod
  » Full Delete/Insert
  » Deferred Updates

SQL server 6.5 kan använda två olika metoder för att uppdatera poster, beroende på omständigheterna. De inkluderar:
Direct Update
Deferred Update
Varje metod påverkar prestandan på olika sätt, som vi kommer att se i den här artikeln.

En Direct Update inkluderar tre olika metoder av modifiering.

In-place update
On-page delete/insert
Full delete/insert
Tänk på dessa tre ovanstående metoder som olika sätt att implementera en Direct Update. Metoden som väljs av SQL server beror på ett antal faktorer, som kommer att diskuteras senare. Å andra sidan, en Deferred Update (diskuteras mer i detalj senare) använder alltid Full delete/insert metoden för modifiering.

Om du vill se hur SQL server genomför en detaljerad uppdatering, så kan du, om du sätter på SET SHOWPLAN ON i frågan i ISQL/W och sen kör följande kod för att se vilka kommandon som skrivs till transaktions loggen.

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

WHERE op - is the transaction log operation.


Här är några op värden:

op = 0 - is "BEGIN TRANSACTION"
op = 4 - is "Insert Row"
op = 5 - is "Delete Row"
op = 6 - is "Deferred Update step 2 insert record"
op = 9 - is "Modify Row"

p = 11 - is "Deferred Update step 1 insert record"
op = 12 - is "Deferred Update step 1 delete record"
op = 30 - is "COMMIT TRANSACTION"

Som tillägg, du kan också använda Status flagga 3604 och 323 för att få en mer detaljerad beskrivning av vilken Update metod som används av SQL server 6.5

Statusflagga 3604 skickar Status utdata till klienten. Den här Statusflaggan används bara när man sätter Statusflagga med DBCC TRACEON och DBCC TRACEOFF. Statusflagga 323 är en odokumenterad statusflagga. Du kan använda den för att se en detaljerad beskrivning av olika uppdaterings metoder som används av SQL Server 6.5

Direct Update

En Direct Update är snabbare än en Deferred Update eftersom mindre SQL server overhead är orsakad, som vi kommer att se. Om det är möjligt försök att skapa kod som använder Direct Update istället för Deferred Update för att optimera prestandan.

För att en Direct Update ska ske, så måste följande regler följas:

• Uppdateringen kan inte påverka kolumn (er) som medverkar i ett klustrat index och för flerraders uppdateringar.


• Uppdateringen kan inte påverka null tillåtna kolumner


• Uppdateringen kan inte påverka kolumner med variabel längd.


• Tabellen kan inte inkludera en kolumn med Timestamp datatypen


• Den uppdaterade kolumnen inte medverka i en unik oklustrat index.


• Den uppdaterade kolumnen inte medverka i en icke unik oklustrat index om index används för att hitta rader som innehåller uppdaterad kolumn.

Som vi redan har diskuterat, en Direct Update kan implementeras genom tre olika
uppdaterings metoder.

In-Place Update Metod

Av de tre uppdateringsmetoder som finns i en Direct Update, en In-Place Update använder
minst overhead och är den snabbaste att utföras. När den används, datan är modifierad på dess
original plats, och endast en rad är skriven till transaktionsloggen med MODIFY frågan.

För att en In-Place Update ska ske, så måste följande regler följas:

• Uppdateringen kan inte påverka några kolumner som ingår i ett klustrat index.


• Tabellen kan inte ha en UPDATE trigger


• Tabellen kan inte ingå i en replikering.

För enstaka rad uppdateringar:

• De uppdaterade kolumnerna kan vara av variabel längd, men det nya antalet rader måste få plats på samma sida som de gamla raderna.


• De uppdaterade kolumnerna kan ingå i en icke-unik oklustrat index bara om index kolumnen har en fast kolumn bredd.


• De uppdaterade kolumnerna kan ingå i en unik oklustrad index om indexnyckeln är av fast bredd och att WHERE frågans kriterier har en exakt matchning.

Den nya raden kan inte innehålla skillnader på mer än 50 % på originalets radstorlek och det totala antalet numret på en inte intilliggande skillnad får inte vara mer än 24.

För flerraders uppdateringar:

• De uppdaterade kolumnerna måste ha en fast längd.


• De uppdaterade kolumnerna kan inte ingå i ett unikt oklustrat index.


• De uppdaterade kolumnerna kan ingå i en icke-unik oklustrat index bara om kolumnerna är av fast bredd (indexet som används för att hitta rader kan inte vara samma som den uppdaterade kolumnen).


• Tabellen kan inte innehålla en kolumn med timestamp som datatyp.

Här är ett exempel som använder pubs databasen som demonstrerar hur du kan visa en typ av Uppdatering som har hänt.

USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE stores SET state = 'UT' WHERE stor_id = '6380'
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO

Ovanstående kod resulterar i följande:

STEG 1
Typen av frågan är en UPDATE
Uppdateringsmetoden är direkt
FROM TABLE
sparar.

Nästlad upprepning
Använder ett klustrat index
TO TABLE
sparar
Update: in-place, clust, safeind[0]=0x1

(1 row(s) affected)

STEG 1
Typen av frågan är en SETOFF
TRAN_ID LOG_RECORD
-------------- ----------
...

0x380300000000 0
0x380300000000 9
0x380300000000 30

Du kan se på resultatet att i det här fallet är frågan en “UPDATE”, uppdateringsmetoden är
direct” och att “In-Place” metoden för modifieringen användes.
Titta på de tre sista raderna från transaktionsloggen nedanför. Notera att när en In-Place
uppdateringsmetod används, att bara en rad är uppdaterad och att endast tre rader skrivs till
ransaktionsloggen:

- BEGIN TRANSACTION
- Modify Row
- COMMIT TRANSACTION

On-Page Delete/Insert Metod

Den här metoden för ändringar används när en In-Place update normalt skulle användas, men
en eller fler av följande villkor ingår som hindrar den från att användas:

Det finns en update trigger på den uppdaterade tabellen.
Den uppdaterade tabellen ingår i en replikeringsprocess.
Storleken på frågan blev ändrad.

Här är ett exempel:

USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE jobs SET job_desc = 'Updated row' WHERE job_id = 1
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO

Det här är resultatet av ovanstående kod:

STEG 1
Typen av frågan är en UPDATE
Uppdateringsmetoden är direkt
FROM TABLE
jobs
Nästlad upprepning
Using Clustered Index
TO TABLE
jobs
Update: on-page delete/insert, clust, safeind[0]=0x1

(1 row(s) affected)

STEG 1
The type of query is SETOFF
TRAN_ID LOG_RECORD
-------------- ----------
...

0x3b0300001800 0
0x3b0300001800 5
0x3b0300001800 4
0x3b0300001800 30

Du kan se på ovanstående kod att den skapar en “update” fråga, att uppdateringsmetoden är
direct” och att “On-page delete/insert” metoden för modifiering används.
Titta på de sista fyra raderna från transaktionsloggen nedanför. I det här fallet, när en enkel
rad är uppdaterad, fyra rader skrivs till transaktionsloggen. Jämfört med föregående
uppdateringsmetod, så behövs mer resurser för On-page delete/insert metoden.

- BEGIN TRANSACTION
- Delete Row
- Insert Row
- COMMIT TRANSACTION

Full Delete/Insert

Den här metoden för modifiering används med Direct Update när det inte finns plats för att
sätta in en ny rad på den uppdaterade sidan. För att göra detta, SQL server 6.5 måste då skapa
n ny rad på en ny sida, vilket skapar mest overhead av alla tre uppdateringsmetoderna som
stöds av Direct Update. Den här metoden för modifiering används alltid med Deferred
updates, som diskuteras nedanför.

Deferred Updates

När en Deferred Update används, så skrivs raderna till transaktions loggen med lämplig no-op
angiven ("Deferred Update steg 1 delete record" och "Deferred Update step 1 insert record"
angiven), SQL Servern går sedan tillbaka till starten på transaktionen och startar med att utföra delete operationen. När den är klar med delete operationen, då kör den insert operationen, och endast efter det är raderna placerade på en data sida.

Regler för hur en Deferred Update inträffar, inkluderar:

• UPDATE påverkar de kolumner som ingår i ett klustrat index.

För flerraders uppdateringar:

• UPDATE påverkar nolltillåtna kolumner.


• UPDATE påverkar kolumner med variabel längd.


• Tabellen innehåller en kolumn med timestamp som datatyp.


• De uppdaterade kolumnerna ingår i en unik oklustrat index.


• De uppdaterade kolumnerna ingår i en icke-unik, oklustrat index, om index används för att
  hitta rader i den uppdaterade kolumnen.

Här är ett exempel:

USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE discounts SET lowqty = 100
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO

Här är resultatet av ovanstående kod:

STEG 1
Frågetypen är en UPDATE
Uppdateringsmetoden är uppskjuten(deferred)
FROM TABLE
discounts
Nästlad upprepning
Table Scan
TO TABLE
discounts
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe

(3 row(s) affected)

STEG 1
Frågetypen är en SETOFF
TRAN_ID LOG_RECORD
-------------- ----------
...

0x140300000f00 0
0x140300000f00 12
0x140300000f00 11
0x140300000f00 12
0x140300000f00 11
0x140300000f00 12
0x140300000f00 11
0x140300000f00 5
0x140300000f00 5
0x140300000f00 5
0x140300000f00 6
0x140300000f00 6
0x140300000f00 6
0x140300000f00 30

Du kan se på resultatet ovanför, att i det här fallet så är frågetypen en “UPDATE”,
uppdateringsmetoden är “deferred” och "full delete/insert" metoden för modifiering används.
Ovanstående exempel skriver 14 rader till transaktionsloggen, vilket gör den här
uppdateringsmetoden till den som tar mest resurser i anspråk.

- BEGIN TRANSACTION
- Deferred Update step 1 delete record
- Deferred Update step 1 insert record
- Deferred Update step 1 delete record
- Deferred Update step 1 insert record
- Deferred Update step 1 delete record
- Deferred Update step 1 insert record
- Delete Row
- Delete Row
- Delete Row
- Deferred Update step 2 insert record
- Deferred Update step 2 insert record
- Deferred Update step 2 insert record
-COMMIT TRANSACTION

Som du kan se, de olika metoderna som används i SQL Server kan markant påverka SQL serverns overhead och prestanda. Sensmoralen i den här artikeln är att om det är möjligt, försök att skriva din kod så att du använder den uppdateringsmetod som använder minst resurser, men ju mer du vet om hur SQL Server 6.5 uppdaterar rader, desto mer förberedd är du för att kunna skriva kod som tar fördelar av hur den arbetar internt.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: