E kali! E kali! ʻOiaʻiʻo, ʻaʻole kēia ʻatikala ʻē aʻe e pili ana i nā ʻano kākoʻo SQL Server. ʻAʻole wau e kamaʻilio e pili ana i ka ʻokoʻa ma waena o nā hiʻohiʻona hoʻolaʻa a pehea e hana ai i kahi lāʻau ulu nui.
Malia paha (malia paha), ma hope o ka heluhelu ʻana i kēia pou, hiki iā ʻoe ke hōʻoia i ka hoʻihoʻi ʻia ʻana mai o ʻoe me ka hoʻohana ʻana i nā ala maʻamau e wehe ʻia i ka pō ʻapōpō, maikaʻi, 1.5 mau manawa wikiwiki. A ma muli wale nō o kou hoʻohana ʻana i nā ʻāpana BACKUP DATABASE.
Inā maopopo iā ʻoe ka ʻike o ka pou, kala wau. Ua heluhelu au i nā mea a pau i loaʻa iā Google no ka huaʻōlelo "habr sql server backup", a ʻaʻole i hoʻokahi ʻatikala i loaʻa iaʻu kahi ʻōlelo i ka ʻoiaʻiʻo e hiki ke hoʻololi ʻia ka manawa hoʻihoʻi me ka hoʻohana ʻana i nā ʻāpana.
E huki koke wau i kou manaʻo i ka ʻōlelo a Alexander Gladchenko (
Mai hoʻololi i nā ʻāpana BUFFERCOUNT, BLOCKSIZE, MAXTRANSFSERSIZE i ka hana ʻana. Hana ʻia lākou no ke kākau ʻana i ia mau ʻatikala. Ma ka hoʻomaʻamaʻa, e hoʻopau ʻoe i nā pilikia hoʻomanaʻo i ka manawa pōkole.
ʻOiaʻiʻo, ʻoluʻolu ke lilo i mea akamai a hoʻolaha i nā ʻike kūʻokoʻa, akā naʻe, ʻaʻole kēia ka hihia. Aia nā ʻatikala / ʻōlelo Pelekania a me ka ʻōlelo Lūkini (pilikia mau wau i ka mea e kapa pololei ai) i hoʻolaʻa ʻia i kēia kumuhana. Eia kekahi o nā mea aʻu i ʻike ai:
No laila, i ka hoʻomaka ʻana, e hoʻopili wau i kahi syntax BACKUP i wehe iki ʻia mai
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 }
<...>
<...> - 'o ia ho'i, aia kekahi mea ma laila, akā ua wehe au ia mea no ka mea 'a'ole pili ia i ke kumuhana.
Pehea ʻoe e lawe pinepine ai i kahi waihona? Pehea lākou e "aʻo ai" pehea e lawe i nā waihona i nā piliona ʻatikala? Ma keʻano laulā, inā pono iaʻu e hana i hoʻokahi manawa hoʻihoʻi o kahi waihona ʻikepili ʻaʻole nui loa, e kākau koke wau i kekahi mea e like me kēia:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее
A, ma ka laulā, ʻo 75-90% paha o nā ʻāpana āpau i ʻōlelo pinepine ʻia i loko o nā ʻatikala e pili ana i nā backup i helu ʻia ma aneʻi. ʻAe, aia kekahi INIT, SKIP. Ua kipa anei ʻoe iā MSDN? Ua ʻike anei ʻoe aia nā koho no hoʻokahi a me ka hapa pale? Ua ʻike nō hoʻi au ...
Ua ʻike paha ʻoe e kamaʻilio hou mākou e pili ana i nā ʻāpana ʻekolu i koe i ka poloka mua o ke code - BLOCKSIZE, BUFFERCOUNT a me MAXTRANSFSERSIZE. Eia kā lākou wehewehe ʻana mai MSDN:
PALAKAI = { nui poloka | @ blocksize_variable } - hōʻike i ka nui o ka poloka kino ma nā bytes. ʻO nā nui i kākoʻo ʻia he 512, 1024, 2048, 4096, 8192, 16, 384, a me 32 paita (768 KB). ʻO ka waiwai paʻamau ʻo 65 no nā mea lipine a me 536 no nā mea hana ʻē aʻe. ʻO ka maʻamau, ʻaʻole pono kēia ʻāpana no ka mea koho ʻokoʻa ka ʻōlelo BACKUP i ka nui poloka kūpono no ka hāmeʻa. ʻO ka hoʻonohonoho ʻana i ka nui poloka e hoʻopololei i ke koho ʻana i ka nui poloka.
BUFFERCOUNT = { helu hoʻopaʻa | @ buffercount_variable } - Wehewehe i ka huina helu o nā pale I/O e hoʻohana ʻia no ka hana hoʻihoʻi. Hiki iā ʻoe ke kuhikuhi i kekahi waiwai integer maikaʻi, akā hiki i ka nui o nā buffers ke kumu i kahi hewa i waho o ka hoʻomanaʻo ma muli o ka nui o ka wahi kikoʻī virtual i ke kaʻina hana Sqlservr.exe.
Hoʻoholo ʻia ka nui o ka nui o ka lumi i hoʻohana ʻia e nā buffers e kēia ʻano:
BUFFERCOUNT * MAXTRANSFERSIZE
.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } hōʻike i ka nui o ka pūʻolo ʻikepili nui loa, ma nā paita, e hoʻololi ma waena o SQL Server a me ka media set backup. Kākoʻo ʻia ka nui o 65 bytes (536 KB) a hiki i 64 bytes (4 MB).
Ke hoʻohiki nei au - ua heluhelu au i kēia ma mua, akā ʻaʻole i ʻike iaʻu i ka nui o ka hopena i hiki iā lākou ke loaʻa i ka huahana. Eia kekahi, ʻike ʻia, pono wau e hana i kahi ʻano "puka" a hōʻoia i kēia manawa ʻaʻole maopopo iaʻu ka mea a lākou e hana nei. Pono paha wau e heluhelu hou aku e pili ana i ka I/O i hoʻopaʻa ʻia a me ka hana ʻana me kahi kīkī paʻakikī. I kekahi lā e hana wau i kēia, akā i kēia manawa hiki iaʻu ke kākau i kahi palapala e nānā i ka hopena o kēia mau waiwai i ka wikiwiki o ka lawe ʻana i ka waihona.
Ua hana au i kahi waihona liʻiliʻi, ma kahi o 10 GB ka nui, kau ma ka SSD, a kau i ka papa kuhikuhi no nā backup ma ka HDD.
Hana wau i kahi papaʻaina no ka mālama ʻana i nā hopena (ʻaʻohe oʻu manawa pōkole, no laila hiki iaʻu ke ʻeli i nā hopena i nā kikoʻī hou aku, akā hoʻoholo ʻoe iā ʻoe iho):
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
);
He mea maʻalahi ka loina o ka palapala - nested loops, kēlā me kēia mea e hoʻololi i ka waiwai o hoʻokahi parameter, e hoʻokomo i kēia mau palena i loko o ke kauoha BACKUP, e mālama i ka moʻolelo hope loa me ka mōʻaukala mai msdb.dbo.backupset, e holoi i ka waihona waihona a me ka hoʻololi hou. . Ma muli o ka lawe ʻia ʻana o ka ʻikepili hoʻokō mai ka backupset, ua nalowale ka pololei (ʻaʻohe hapa o kekona), akā e ola mākou i kēia.
Pono mua ʻoe e hiki iā xp_cmdshell ke holoi i nā backup (a laila mai poina e hoʻopau iā ia inā ʻaʻole pono ʻoe):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GO
ʻAe, ʻoiaʻiʻo:
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;
END
Inā makemake koke ʻoe i ka wehewehe ʻana i nā mea e hana nei ma aneʻi, e kākau i nā manaʻo a i ʻole PM. I kēia manawa, e haʻi wale aku wau iā ʻoe e pili ana i nā ʻāpana aʻu i kau ai ma BACKUP DATABASE.
No BLOCKSIZE, loaʻa iā mākou kahi papa inoa "pani" o nā waiwai, a ʻaʻole wau i hana i kahi waihona me BLOCKSIZE <4KB. MAXTRANSFERSIZE kekahi helu he nui o 64KB - mai 64KB a i 4MB. ʻO ka paʻamau ma kaʻu ʻōnaehana ʻo 1024KB, lawe au i 512 - 1024 - 2048 - 4096.
ʻOi aku ka paʻakikī me BUFFERCOUNT - hiki ke helu maikaʻi, akā ʻōlelo ka loulou
ʻO Msg 3013, Papa 16, Mokuʻāina 1, Laina 7 BACKUP DATABASE ke hoʻopau ʻino nei.
Msg 701, Papa 17, Mokuʻāina 123, Laina 7 ʻAʻole lawa ka hoʻomanaʻo ʻōnaehana ma ka waihona punawai 'paʻamau' e holo i kēia nīnau.
No ka hoʻohālikelike, e hōʻike mua wau i nā hopena o ka holo ʻana i kahi hoʻihoʻi me ka ʻole o ka wehewehe ʻana i nā ʻāpana āpau:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;
ʻAe, hoʻihoʻi a hoʻihoʻi:
Hana ʻia nā ʻaoʻao 1070072 no ka waihona 'bt', faila 'bt' ma ka waihona 1.
Ua hana ʻia nā ʻaoʻao 2 no ka waihona 'bt', faila 'bt_log' ma ka waihona 1.
Ua hana maikaʻi ʻo BACKUP DATABASE i nā ʻaoʻao 1070074 i 53.171 kekona (157.227 MB/sec).
ʻO ka ʻatikala ponoʻī, e hoʻāʻo ana i nā ʻāpana, hana i loko o ʻelua mau hola, aia nā ana āpau
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;
E nānā, he palapala nui loa mai
Hiki iā mākou ke ʻōlelo wiwo ʻole ʻo ka pilina ma waena o nā ʻāpana a me ka wikiwiki o ka hoʻihoʻi ʻana i loko o kēia mau pae waiwai he random, ʻaʻohe kumu. Akā ʻo ka neʻe ʻana mai nā ʻāpana i kūkulu ʻia he hopena maikaʻi i ka hopena
ʻO kēlā mau. Ma ka hoʻokele wale ʻana i nā ʻāpana BACKUP maʻamau i loaʻa ka loaʻa 2-fold i ka manawa hoʻihoʻi hoʻihoʻi: 26 kekona, me 53 i ka hoʻomaka. ʻAʻole hewa kēlā, ʻeā? Akā pono mākou e ʻike i ka mea e hana ai me ka hoʻihoʻi. He aha inā he 4 mau manawa lōʻihi e ola hou ai?
ʻO ka mua, e ana kākou i ka lōʻihi o ka hoʻihoʻi ʻana i kahi waihona me nā hoʻonohonoho paʻamau:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;
ʻAe, ʻike ʻoe iā ʻoe iho, aia nā ala, ʻaʻole hoʻololi ke pani, ʻaʻole hoʻihoʻi ka hoʻihoʻi. A hana wau penei:
Hana ʻia nā ʻaoʻao 1070072 no ka waihona 'bt', faila 'bt' ma ka waihona 1.
Ua hana ʻia nā ʻaoʻao 2 no ka waihona 'bt', faila 'bt_log' ma ka waihona 1.
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 40.752 kekona (205.141 MB/sec).
I kēia manawa e hoʻāʻo wau e hoʻihoʻi i nā waihona i lawe ʻia me BLOCKSIZE i hoʻololi ʻia, BUFFERCOUNT a me MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 32.283 kekona (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 32.682 kekona (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 32.091 kekona (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 32.401 kekona (258.015 MB/sec).
ʻAʻole loli ka ʻōlelo RESTORE DATABASE i ka wā o ka hoʻihoʻi ʻana; ʻaʻole i kuhikuhi ʻia kēia mau ʻāpana; ʻo SQL Server ponoʻī e hoʻoholo iā lākou mai ka waihona. A maopopo ia ʻoiai me ka hoʻihoʻi ʻana hiki ke loaʻa ka loaʻa - kokoke 20% wikiwiki (ʻO kaʻoiaʻiʻo, ʻaʻole wau i hoʻolilo nui i ka manawa no ka hoʻihoʻi ʻana, ua holo wau i kekahi o nā backup "wikiwiki" a ʻike pono ʻaʻole he pōʻino.).
I ka hihia wale nō, e wehewehe wau ʻaʻole kēia kekahi mau ʻāpana i kūpono no kēlā me kēia. Hiki iā ʻoe ke loaʻa i nā ʻāpana maikaʻi loa no ʻoe iho ma ka hoʻāʻo ʻana. Loaʻa iaʻu kēia mau hopena, e loaʻa iā ʻoe nā hopena ʻokoʻa. Akā ʻike ʻoe hiki iā ʻoe ke "hoʻokani" i kāu mau backup a hiki iā lākou ke hana a hoʻonohonoho wikiwiki.
Manaʻo ikaika wau e heluhelu ʻoe i ka palapala i kona holoʻokoʻa, no ka mea aia paha nā nuances kikoʻī i kāu ʻōnaehana.
Mai koʻu hoʻomaka ʻana e kākau e pili ana i nā backup, makemake wau e kākau koke e pili ana i hoʻokahi "optimization", ʻoi aku ka maʻamau ma mua o nā ʻāpana "tuning" (e like me kaʻu e hoʻomaopopo ai, hoʻohana ʻia ia e kekahi mau mea hoʻohana hoʻihoʻi, malia paha me nā ʻāpana. i wehewehe mua ʻia), akā ʻaʻole i wehewehe ʻia ma Habré.
Inā mākou e nānā i ka laina ʻelua i ka palapala, aia ma lalo o BACKUP DATABASE, ma laila mākou e ʻike ai:
TO <backup_device> [ ,...n ]
He aha kāu e manaʻo ai inā ʻoe e kuhikuhi i kekahi mau backup_devices? ʻAe ka syntax. A e hiki mai ana kahi mea hoihoi loa - e "palaha" wale ʻia ka backup ma luna o kekahi mau polokalamu. ʻO kēlā mau mea. e lilo kēlā me kēia "mea hana" i mea ʻole, nalowale hoʻokahi, nalowale ke kākoʻo holoʻokoʻa. Akā, pehea e pili ai ka hoʻopalapala ʻana i ka wikiwiki hoʻihoʻi?
E hoʻāʻo mākou e hana i kahi hoʻihoʻi ma nā "mea hana" ʻelua i loaʻa ma ka ʻaoʻao ma ka waihona like:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;
E na makua o ke ao nei, no ke aha la i hanaia'i keia?
Hana ʻia nā ʻaoʻao 1070072 no ka waihona 'bt', faila 'bt' ma ka waihona 1.
Hana ʻia nā ʻaoʻao 2 no ka waihona 'bt', faila 'bt'log' ma ka waihona 1.
Ua hana maikaʻi ʻo BACKUP DATABASE i nā ʻaoʻao 1070074 i 40.092 kekona (208.519 MB/sec).
Ua lilo ka waihona i 25% ʻoi aku ka wikiwiki ma waho o ka uliuli? He aha inā mākou e hoʻohui i ʻelua mau mea hana?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;
Ua hana maikaʻi ʻo BACKUP DATABASE i nā ʻaoʻao 1070074 i 34.234 kekona (244.200 MB/sec).
Ma ka huina, ʻo ka loaʻa ʻana ma kahi o 35% o ka manawa o ka lawe ʻana i kahi hoʻihoʻi wale nō ma muli o ka mea i kākau ʻia ka waihona i nā faila 4 ma hoʻokahi disk i ka manawa hoʻokahi. Ua nānā au i kahi helu nui - ʻaʻohe loaʻa ma kaʻu kamepiula, maikaʻi loa - 4 mau polokalamu. No ʻoe - ʻaʻole wau ʻike, pono ʻoe e nānā. ʻAe, ma ke ala, inā loaʻa iā ʻoe kēia mau mea - he mau disks ʻokoʻa kēia, mahalo, ʻoi aku ka nui o ka loaʻa.
I kēia manawa, e kamaʻilio kākou pehea e hoʻihoʻi ai i kēia hauʻoli. No ka hana ʻana i kēia, pono ʻoe e hoʻololi i ke kauoha hoʻihoʻi a papa inoa i nā mea hana a pau:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;
Ua hoʻokō pono ʻo RESTORE DATABASE i nā ʻaoʻao 1070074 i 38.027 kekona (219.842 MB/sec).
ʻOi aku ka wikiwiki, akā ma kahi kokoke, ʻaʻole koʻikoʻi. Ma keʻano laulā, ua hoʻoneʻe wikiwiki ʻia ka waihona a hoʻihoʻi ʻia ma ke ʻano like - kūleʻa? ʻO wau, he holomua loa. ʻO kēia he mea nui, no laila ke haʻi hou aku nei au - inā ʻoe inā nalowale ʻoe ma kahi liʻiliʻi o kēia mau faila, nalowale ʻoe i ka waihona āpau.
Inā ʻoe e nānā i ka log i ka ʻike hoʻihoʻi i hōʻike ʻia me ka hoʻohana ʻana i ka Trace Flags 3213 a me 3605, e ʻike ʻoe i ke kākoʻo ʻana i kekahi mau polokalamu, ma ka liʻiliʻi o ka nui o ka BUFFERCOUNT e hoʻonui. Malia paha, hiki iā ʻoe ke hoʻāʻo e koho i nā ʻāpana maikaʻi loa no BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, akā ʻaʻole wau i kūleʻa koke, a ua moloā wau e hoʻokō hou i ia hoʻāʻo, akā no ka helu o nā faila. A he mea hilahila no na huila. Inā makemake ʻoe e hoʻonohonoho i ia hoʻokolohua ma ka home, ʻaʻole paʻakikī ke hana hou i ka palapala.
ʻO ka hope, e kamaʻilio kākou e pili ana i ke kumukūʻai. Inā hoʻoneʻe ʻia ka waihona e like me ka hana a nā mea hoʻohana, pono ʻoe e lawe i kahi ala kūpono loa i ka hoʻāʻo ʻana, no ka mea inā ʻoi aku ka wikiwiki o ka hoʻihoʻi ʻana, ʻoi aku ka paʻakikī o nā disks, piʻi ka ukana ma ka processor (pono ʻoe e kaomi. ia ma ka lele), a no laila, emi ka pane holoʻokoʻa o ka ʻōnaehana.
He ʻakaʻaka wale nō, akā maopopo iaʻu ʻaʻole wau i hana i nā hōʻike. ʻO ka mea i kākau ʻia ma luna nei he hōʻike wale ia o ke ʻano e hiki ai iā ʻoe ke koho i nā ʻāpana kūpono no ka lawe ʻana i nā backup.
E hoʻomanaʻo i nā mea a pau āu e hana ai ua hana ʻia ma kou pilikia a me ka pilikia. E nānā i kāu mau waihona a mai poina iā DBCC CHECKDB.
Source: www.habr.com