Aspetta ! Aspetta ! True, questu ùn hè micca un altru articulu nantu à i tipi di copia di salvezza di SQL Server. Ùn vi mancu parlà di e diffirenzii trà i mudelli di ricuperazione è cumu si tratta di un logu overgrown.
Forsi (forsi solu), dopu avè lettu stu post, puderete assicurà chì a copia di salvezza chì hè stata sguassata da voi cù i mezi standard serà sguassata dumani sera, bè, 1.5 volte più veloce. È solu per u fattu chì utilizate un pocu di più paràmetri di BACKUP DATABASE.
Se u cuntenutu di u post era evidenti per voi, mi dispiace. Aghju lettu tuttu ciò chì Google hà avutu per a frasa "habr sql server backup", è in un solu articulu ùn aghju trovu alcuna menzione di u fattu chì u tempu di salvezza pò esse influenzatu in qualchì modu cù i paràmetri.
Subitu attiraraghju a vostra attenzione à u cummentariu di Alexander Gladchenko ():
Ùn mai cambià i paràmetri BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in a produzzione. Sò fatti solu per scrive tali articuli. In pratica, vi sbarazzate di prublemi di memoria in pocu tempu.
Saria, sicuru, esse cool per esse u più intelligente è postu cuntenutu esclusivu, ma, sfurtunatamenti, questu ùn hè micca u casu. Ci sò articuli / posti in lingua inglesa è in lingua russa (sò sempre cunfusu per ciò chì chjamanu currettamente) dedicati à stu tema. Eccu alcuni di quelli chì aghju trovu: , , .
Allora, per cumincià, aghjustà una sintassi di BACKUP ligeramente spogliata da (A propositu, aghju scrittu sopra nantu à BACKUP DATABASE, ma tuttu questu s'applica à a copia di salvezza di u logu di transazzione è à a copia di salvezza differenziale, ma forse cù un effettu menu evidenti):
BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
<...>
[ WITH { <...>
| <general_WITH_options> [ ,...n ] } ]
[;]
<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
<...>
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...><…> - significa chì ci era qualcosa quì, ma l'aghju cacciatu perchè avà ùn hè micca pertinente à u tema.
Cumu di solitu piglià una copia di salvezza? Cumu "insignanu" cumu piglià backups in miliardi d'articuli? In generale, se aghju bisognu di fà una copia di salvezza una volta di una basa di dati micca assai grande, scriveraghju automaticamente qualcosa cum'è questu:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееE, in generale, prubabilmente 75-90% di tutti i paràmetri chì sò generalmente citati in l'articuli nantu à i backups sò listati quì. Ebbè, ci hè ancu INIT, SKIP. Avete visitatu MSDN? Avete vistu chì ci sò opzioni per una schermu è mezu? Aghju vistu ancu ...
Probabilmente avete digià capitu chì più avanti parlemu di i trè paràmetri chì sò stati in u primu bloccu di codice - BLOCKSIZE, BUFFERCOUNT è MAXTRANSFERSIZE. Eccu e so descrizzioni da MSDN:
BLOCKSIZE = { bluccatu | @ blocksize_variable } - indica a dimensione di u bloccu fisicu in byte. E dimensioni supportate sò 512, 1024, 2048, 4096, 8192, 16, 384 è 32 bytes (768 KB). U valore predeterminatu hè 65 per i dispositi di cinta è 536 per altri dispositi. Di genere, stu paràmetru ùn hè micca necessariu perchè a dichjarazione BACKUP selezziunate automaticamente a dimensione di bloccu adattata per u dispusitivu. L'impostazione di a dimensione di u bloccu annulla esplicitamente a selezzione automatica di a dimensione di u bloccu.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - Definisce u numeru tutale di buffer I/O chì seranu utilizati per l'operazione di salvezza. Pudete specificà qualsiasi valore integer pusitivu, ma un gran numaru di buffer pò causà un errore di memoria fora di u spaziu di indirizzu virtuale eccessivu in u prucessu Sqlservr.exe.
A quantità totale di spaziu utilizatu da i buffers hè determinata da a seguente formula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { massima di trasferimentu | @ maxtransfersize_variable } specifica a più grande dimensione di u pacchettu di dati, in byte, per scambià trà SQL Server è i media di salvezza. Multipli di 65 bytes (536 KB) finu à 64 bytes (4 MB) sò supportati.
Ghjuru - aghju lettu questu prima, ma ùn m'hè mai venutu in mente quantu impattu puderanu avè nantu à a produtividade. Inoltre, apparentemente, aghju bisognu di fà una spezia di "esce" è ammette chì ancu avà ùn aghju micca capitu ciò chì esattamente facenu. Probabilmente bisognu di leghje più nantu à l'I / O buffered è u travagliu cù un discu duru. Qualchì ghjornu faraghju questu, ma per avà possu solu scrive un script chì verificarà cumu questi valori affettanu a velocità à a quale a copia di salvezza hè presa.
Aghju fattu una piccula basa di dati, circa 10 GB in grandezza, mette nantu à u SSD, è mette u repertoriu per backups in u HDD.
Creu una tavula temporale per almacenà i risultati (ùn l'aghju micca tempuranee, perchè possu scavà in i risultati in più detail, ma decide per voi stessu):
DROP TABLE IF EXISTS ##bt_results;
CREATE TABLE ##bt_results (
id int IDENTITY (1, 1) PRIMARY KEY,
start_date datetime NOT NULL,
finish_date datetime NOT NULL,
backup_size bigint NOT NULL,
compressed_size bigint,
block_size int,
buffer_count int,
transfer_size int
);U principiu di u script hè simplice - i loops nidificati, ognunu di i quali cambia u valore di un paràmetru, inserisce questi paràmetri in u cumandamentu BACKUP, salvà l'ultimu record cù a storia da msdb.dbo.backupset, sguassate u schedariu di salvezza è a prossima iterazione. . Siccomu i dati di l'esekzione di salvezza sò pigliati da a copia di salvezza, a precisione hè un pocu persa (ùn ci hè micca frazzioni di seconde), ma sopravvivemu questu.
Prima avete bisognu di attivà xp_cmdshell per sguassà e copia di salvezza (poi ùn vi scurdate di disattivà si ùn avete micca bisognu):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOEbbè, in realtà:
DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH
COMPRESSION,
BLOCKSIZE = {bs},
BUFFERCOUNT = {bc},
MAXTRANSFERSIZE = {ts}';
DECLARE @sql AS nvarchar(max);
/* BLOCKSIZE values */
DECLARE @bs int = 4096,
@max_bs int = 65536;
/* BUFFERCOUNT values */
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
/* MAXTRANSFERSIZE values */
DECLARE @ts int = 524288, --512KB, default = 1024KB
@min_ts int = 524288,
@max_ts int = 4194304; --4MB
SELECT TOP 1
@bs = COALESCE (block_size, 4096),
@bc = COALESCE (buffer_count, 7),
@ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;
WHILE (@bs <= @max_bs)
BEGIN
WHILE (@bc <= @max_bc)
BEGIN
WHILE (@ts <= @max_ts)
BEGIN
SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));
EXEC (@sql);
INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size, @bs, @bc, @ts
FROM msdb.dbo.backupset
ORDER BY backup_set_id DESC;
EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;
SET @ts += @ts;
END
SET @bc += @bc;
SET @ts = @min_ts;
WAITFOR DELAY '00:00:05';
END
SET @bs += @bs;
SET @bc = @min_bc;
SET @ts = @min_ts;
ENDS'è vo avete bisognu di clarificazione di ciò chì succede quì, scrivite in i cumenti o PM. Per avà, vi dicu solu nantu à i paràmetri chì aghju messu in BACKUP DATABASE.
Per BLOCKSIZE avemu una lista "chjusa" di valori, è ùn aghju micca realizatu una copia di salvezza cù BLOCKSIZE < 4KB. MAXTRANSFERSIZE ogni numeru chì hè un multiplu di 64KB - da 64KB à 4MB. U predeterminatu in u mo sistema hè 1024KB, aghju pigliatu 512 - 1024 - 2048 - 4096.
Era più difficiuli cù BUFFERCOUNT - pò esse qualsiasi numeru pusitivu, ma u ligame dice . Dice ancu cumu per uttene infurmazione nantu à quale BUFFERCOUNT a copia di salvezza hè in realtà fatta cù - per mè hè 7. Ùn ci era micca puntu di riduzzione, è u limitu superiore hè statu scupertu sperimentalmente - cù BUFFERCOUNT = 896 è MAXTRANSFERSIZE = 4194304 a copia di salvezza hè cascata cun un errore (circa chì hè scrittu in u ligame sopra):
Msg 3013, Livellu 16, Statu 1, Linea 7 BACKUP DATABASE hè terminatu anormalmente.
Msg 701, Livellu 17, Statu 123, Linea 7 Ci hè una memoria di sistema insufficiente in u pool di risorse "default" per eseguisce sta dumanda.
Per paragunà, vi mustraraghju prima i risultati di eseguisce una copia di salvezza senza specificà alcunu paràmetru:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Ebbè, copia di salvezza è salvezza:
Elaborate 1070072 pagine per a basa di dati "bt", u schedariu "bt" in u schedariu 1.
Elaborate 2 pagine per a basa di dati "bt", u schedariu "bt_log" in u schedariu 1.
BACKUP DATABASE hà trattatu cù successu 1070074 pagine in 53.171 seconde (157.227 MB/sec).
U script stessu, pruvà i paràmetri, hà travagliatu in un paru d'ore, tutte e misurazioni eranu in . È quì hè una selezzione di risultati cù i trè tempi d'esekzione megliu (aghju pruvatu à fà un bellu graficu, ma in u post aghju da fà cun una tavola, è in i cumenti. aghjuntu ).
SELECT TOP 7 WITH TIES
compressed_size,
block_size,
buffer_count,
transfer_size,
DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;
Attenzione, una nota assai impurtante da из :
Pudemu cunfidendu chì a relazione trà i paràmetri è a velocità di salvezza in questi intervalli di valori hè aleatoriu, ùn ci hè micca un mudellu. Ma alluntanassi da i paràmetri integrati ovviamente hà avutu un bonu effettu nantu à u risultatu
Quelli. Solu da a gestione di i paràmetri di BACKUP standard era un guadagnu 2 volte in u tempu di rimozione di salvezza: 26 seconde, versus 53 à u principiu. Ùn hè micca male, nò? Ma avemu bisognu di vede ciò chì succede cù a risturazione. E s'ellu ci vole avà 4 volte più di ricuperà?
Prima, misuremu quantu tempu ci vole à restaurà una copia di salvezza cù i paràmetri predeterminati:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Ebbè, tù stessu sapete chì, i modi ci sò, rimpiazzà ùn hè micca rimpiazzà, a ricuperazione ùn hè micca a ricuperazione. E facciu cusì:
Elaborate 1070072 pagine per a basa di dati "bt", u schedariu "bt" in u schedariu 1.
Elaborate 2 pagine per a basa di dati "bt", u schedariu "bt_log" in u schedariu 1.
RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 40.752 seconde (205.141 MB/sec).
Avà pruvaraghju à restaurà e copie di salvezza pigliate cù BLOCKSIZE, BUFFERCOUNT è MAXTRANSFERSIZE cambiati.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 32.283 seconde (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 32.682 seconde (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 32.091 seconde (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 32.401 seconde (258.015 MB/sec).
A dichjarazione RESTORE DATABASE ùn cambia micca durante a ricuperazione; sti paràmetri ùn sò micca specificati in questu; SQL Server stessu determina da a copia di salvezza. È hè chjaru chì ancu cù a ricuperazione pò esse un guadagnu - quasi 20% più veloce (Per esse onesto, ùn aghju micca passatu assai tempu nantu à a ricuperazione, aghju attraversatu parechji di i backup "più veloci" è assicuratevi chì ùn ci era micca deterioramentu.).
In casu, lasciami chjarificà chì questi ùn sò micca qualchi paràmetri chì sò ottimali per tutti. Pudete solu ottene i paràmetri ottimali per sè stessu per pruvà. Aghju ottenutu sti risultati, ne avaristi diversi. Ma vede chì pudete "sintonizà" e vostre copie di salvezza è ponu veramente furmà è implementà più veloce.
Aghju ricumandemu ancu di leghje a documentazione in tuttu, perchè pò esse sfumature specifiche à u vostru sistema.
Dapoi ch'e aghju cuminciatu à scrive nantu à e copie di salvezza, vogliu scrive immediatamente nantu à una più "ottimisazione", chì hè più cumuna di i paràmetri di "tuning" (per quantu aghju capitu, hè utilizatu da almenu qualchi utilità di salvezza, forsi inseme cù i paràmetri). descrittu prima), ma ùn hè ancu statu scrittu annantu à Habré.
Se guardemu a seconda linea in a documentazione, ghjustu sottu BACKUP DATABASE, ci vedemu:
TO <backup_device> [ ,...n ]Chì pensate chì succede si specificate parechji backup_devices ? A sintassi permette. È succederà una cosa assai interessante - a copia di salvezza serà simplicemente "diffusa" in parechji dispositi. Quelli. ogni "dispositivu" individually sarà inùtule, persu unu, persu tutta a copia di salvezza. Ma cumu averà tali smearing affettà a velocità di salvezza?
Pruvemu di fà una copia di salvezza di dui "dispositivi" chì si trovanu fiancu à fiancu in u stessu cartulare:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Padri di u mondu, perchè hè fattu questu?
Elaborate 1070072 pagine per a basa di dati "bt", u schedariu "bt" in u schedariu 1.
Elaborate 2 pagine per a basa di dati 'bt', file 'bt'log' nant'à u schedariu 1.
BACKUP DATABASE hà trattatu cù successu 1070074 pagine in 40.092 seconde (208.519 MB/sec).
A copia di salvezza hè diventata 25% più veloce solu da u turchinu? E se aghjunghjemu un paru di dispositi più?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE hà trattatu cù successu 1070074 pagine in 34.234 seconde (244.200 MB/sec).
In u tutale, u guadagnu hè di circa 35% di u tempu di piglià una copia di salvezza solu per u fattu chì a copia di salvezza hè scritta à 4 schedarii nantu à un discu à tempu. Aghju verificatu un numeru più grande - ùn ci hè micca guadagnu nantu à u mo laptop, in modu ottimale - 4 dispositi. Per voi - ùn sò micca, avete bisognu di verificà. Ebbè, per via, sè vo avete sti dispusitivi - questi sò veramente dischi diffirenti, felicitazioni, u guadagnu deve esse ancu più significativu.
Avà parlemu di cumu per restituverà sta felicità. Per fà questu, avete da cambià u cumandamentu di ricuperazione è elencu tutti i dispositi:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;RESTORE DATABASE hà trattatu cù successu 1070074 pagine in 38.027 seconde (219.842 MB/sec).
Un pocu più veloce, ma in un locu vicinu, micca significativu. In generale, a copia di salvezza hè eliminata più veloce è restaurata in u listessu modu - successu? In quantu à mè, hè abbastanza un successu. Questu hè impurtante, cusì ripetu - se tu s'è vo perde almenu unu di sti schedari, vi perde tutta a copia di salvezza.
Sè vo circate in u logu à l 'infurmazione di salvezza affissata cù Trace Flags 3213 è 3605, vi vi nutari ca, quandu a copia di salvezza di parechji dispusitivi, almenu u numeru di BUFFERCOUNT aumenta. Probabilmente, pudete pruvà à selezziunà paràmetri più ottimali per BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ma ùn aghju micca successu subitu, è eru troppu pigro per fà tali teste di novu, ma per un altru numeru di schedari. È hè una vergogna per i roti. Se vulete urganizà tali teste in casa, ùn hè micca difficiule di rimpruverà u script.
Infine, parlemu di u prezzu. Se a copia di salvezza hè sguassata in parallelu cù u travagliu di l'utilizatori, avete bisognu di piglià un accostu assai rispunsevuli à a prova, perchè se a copia di salvezza hè sguassata più veloce, i dischi sò più stretti, a carica nantu à u processatore aumenta (avete sempre da cumpressione. nantu à a mosca), è per quessa, a risposta generale di u sistema diminuisce.
Sò scherzu, ma capiscu perfettamente chì ùn aghju micca fattu alcuna rivelazione. Ciò chì hè scrittu sopra hè solu una dimostrazione di cumu pudete selezziunate i paràmetri ottimali per piglià backups.
Ricurdativi chì tuttu ciò chì fate hè fattu à u vostru propiu periculu è risicu. Verificate e vostre copie di salvezza è ùn vi scurdate di DBCC CHECKDB.
Source: www.habr.com