🥇Tumau MS SQL: WHAKAARO ki runga steroids

Tatari! Tatari! Pono, ehara tenei i tetahi atu tuhinga mo nga momo taapiri SQL Server. Kaore au e korero mo nga rereketanga i waenga i nga tauira whakaora me te pehea e pa ana ki te rakau tipu.

Tena pea (penei noa), i muri i te panui i tenei panui, ka taea e koe te mohio ko te taapiri kua tangohia mai i a koe ma te whakamahi tikanga paerewa ka tangohia i te po apopo, pai, 1.5 nga wa tere ake. A na te mea he iti ake nga tawhā BACKUP DATABASE ka whakamahia e koe.

Mena i kitea e koe nga korero o te panui, ka pouri ahau. I panuihia e au nga mea katoa i tae mai a Google mo te kupu "habr sql server backup", a kaore i te tuhinga kotahi i kitea e au tetahi korero mo te meka ka taea te awe i te waa taapiri ma te whakamahi i nga taapiri.

Ka aro tonu ahau ki te korero a Alexander Gladchenko (@mssqlhelp):

Kaua rawa e huri i nga tawhā BUFFERCOUNT, BLOCKSIZE, MAXTRANSFSERSIZE i roto i te hanga. He mea hanga noa mo te tuhi i aua tuhinga. I roto i te mahi, ka whakakorehia e koe nga raruraru mahara i roto i te wa poto.

Ko te tikanga, he pai ki te waiho i nga korero tino mohio me te tuku korero motuhake, engari, engari, ehara tenei i te keehi. Kei te reo Ingarihi me te reo Ruhia nga tuhinga / whakairinga (kei te pohehe tonu ahau he aha te karanga tika) kua whakatapua ki tenei kaupapa. Anei etahi o nga mea i kitea e au: wa, два, toru (i sql.ru).

No reira, hei timata, ka apitihia e ahau he wetereo WHAKAARO kua tihorea mai MSDN (i te ara, i tuhia e au mo runga ake mo te BACKUP DATABASE, engari e pa ana enei katoa ki te taapiri i te raarangi hokohoko me te taapiri rereke, engari he iti ake te maarama):

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 }
<...>

<...> - ko te tikanga he mea kei reira, engari i tangohia e au na te mea karekau e whai paanga ki te kaupapa.

Me pehea koe i te nuinga o te waa ki te tango i te taapiri? Me pehea te "whakaako" ki te tango i nga taapiri i roto i nga piriona tuhinga? I te nuinga o te waa, ki te hiahia ahau ki te hanga i tetahi putunga mo te wa kotahi mo etahi putunga raraunga kaore i tino nui, ka tuhia e au tetahi mea penei:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

A, i te nuinga o te waa, ko te 75-90% pea o nga tawhā katoa e whakahuahia ana i roto i nga tuhinga e pa ana ki nga taapiri kei konei. Ana, kei reira ano he INIT, SKIP. Kua toro atu koe ki MSDN? Kua kite koe he whiringa mo te kotahi me te hawhe mata? I kite ano ahau ...

Kua mohio pea koe ka korero ano matou mo nga tawhā e toru i noho ki te poraka tuatahi o te waehere - BLOCKSIZE, BUFFERCOUNT me MAXTRANSFSERSIZE. Anei a raatau korero mai i te MSDN:

RAHI POKA = { rahi poraka | @ te rahinga_poraka } - tohu te rahi paraka tinana i roto i nga paita. Ko nga rahi e tautokohia ana ko 512, 1024, 2048, 4096, 8192, 16, 384, me te 32 paita (768 KB). Ko te uara taunoa ko te 65 mo nga taputapu rīpene me te 536 mo etahi atu taputapu. Ko te tikanga karekau tenei tawhā e tika na te mea ka kowhiria e te tauākī BACKUP te rahi paraka tika mo te taputapu. Ko te tautuhi i te rahi paraka ka takahia te kowhiringa rahi paraka aunoa.

PAPURANGA = { tatau pūreirei | @ buffercount_variable } - Ka tautuhi i te tapeke o nga putunga I/O ka whakamahia mo te mahi taapiri. Ka taea e koe te tautuhi i tetahi uara integer pai, engari he maha nga kaitarai ka puta he hapa i waho o te mahara na te nui o te mokowā wāhitau mariko i roto i te tukanga Sqlservr.exe.

Ko te tapeke mokowā e whakamahia ana e ngā pūreirei ka whakatauhia e te tātai e whai ake nei: BUFFERCOUNT * MAXTRANSFERSIZE.

WHAKAMAHI KAUPAPA = { maxtransfersize | @ maxtransfersize_variable } ka whakapūtā te rahinga pākete raraunga nui rawa atu, i roto i ngā paita, hei whakawhiti i waenga i te Tūmau SQL me te papapāho huinga pūrua. He maha nga paita 65 (536 KB) tae atu ki te 64 paita (4 MB) e tautokohia ana.

Ka oati ahau - Kua panuihia e au i mua, engari kaore au i mohio he pehea te nui o te paanga ki runga i nga hua. I tua atu, ko te ahua, me hanga e au tetahi momo "puta mai" me te whakaae ahakoa inaianei kaore au i te tino marama ki ta raatau mahi. Ka hiahia pea au ki te panui atu mo te I/O buffered me te mahi me te puku pakeke. I tetahi ra ka mahi ahau i tenei, engari mo tenei wa ka taea e au te tuhi i tetahi tuhinga hei tirotiro i te paanga o enei uara ki te tere o te tango i te taapiri.

I hanga e ahau he putunga iti, tata ki te 10 GB te rahi, ka maka ki runga i te SSD, ka waiho te raarangi mo nga taapiri ki te HDD.

Ka hangaia e ahau he ripanga rangitahi hei penapena i nga hua (Kaore au i te wa poto, na ka taea e au te keri i nga hua i roto i nga korero, engari maau koe e whakatau):

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 maamaa te kaupapa o te tuhinga - kohanga nga kohanga, ka huri ia i te uara o te tawhā kotahi, whakauruhia enei tawhā ki te whakahau BACKUP, tiakina te rekoata whakamutunga me te hitori mai i te msdb.dbo.backupset, mukua te konae taapiri me te huringa e whai ake nei . I te mea ka tangohia mai nga raraunga mahi whakaora mai i te putunga, kua ngaro te tika (kare he hautanga o te hēkona), engari ka ora tonu tatou.

Tuatahi me taea e koe te xp_cmdshell ki te muku i nga taapiri (kaore e wareware ki te whakakore mena kaore koe e hiahia):

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Ae, tino:

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

Mena ka hiahia koe ki te whakamarama mo nga mea kei konei, tuhia ki roto i nga korero, PM ranei. Mo tenei wa, ka korero noa ahau ki a koe mo nga tawhā i hoatu e ahau ki roto i te PAHA KAUPAPA KAUPAPA.

Mo BLOCKSIZE kei a matou he rarangi uara "kati", a kaore au i mahi i tetahi taapiri me te BLOCKSIZE <4KB. WHAKAMAHI WHAKAMAHI i tetahi tau he maha o te 64KB - mai i te 64KB ki te 4MB. Ko te taunoa i runga i taku punaha he 1024KB, i tangohia e ahau te 512 - 1024 - 2048 - 4096.

He uaua ake ki a BUFFERCOUNT - he tau pai tera, engari e kii ana te hono me pehea te tatau i roto i te BACKUP DATABASE me te aha e kino ai nga uara nui?. E kii ana hoki me pehea te tiki korero mo te BUFFERCOUNT he mea hanga te taapiri - mo au ko te 7. Karekau he take ki te whakaiti, a ka kitea te rohe o runga ma te whakamatau - me te BUFFERCOUNT = 896 me MAXTRANSFERSIZE = 4194304 ka hinga te taapiri me he hapa (mo tera i tuhia ki te hono i runga ake nei):

Msg 3013, Taumata 16, State 1, Raina 7 KAUPAPA KAUPAPA KAUPAPA kei te mutu kino.

Msg 701, Taumata 17, State 123, Raina 7 He iti rawa te mahara o te punaha i te puna puna 'taunoa' hei whakahaere i tenei patai.

Hei whakataurite, ka whakaatu tuatahi ahau i nga hua o te whakahaere i te taapiri me te kore e tohu i nga tawhā:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Ana, te taapiri me te taapiri:

I tukatukahia nga wharangi 1070072 mo te putunga raraunga 'bt', te konae 'bt' kei te konae 1.

I tukatukahia nga wharangi e 2 mo te 'bt', te konae 'bt_log' kei te konae 1.

I momoho te tukatuka 1070074 wharangi i roto i te 53.171 hēkona (157.227 MB/hekona).

Ko te tuhinga ake, he whakamatautau i nga tawhā, i mahi i roto i nga haora e rua, kua uru katoa nga inenga ripanga google. Anei te kowhiringa o nga hua me nga wa tino pai rawa atu e toru (I ngana ahau ki te hanga kauwhata pai, engari i roto i te panui me mahi ahau ki te tepu, me nga korero. @whakaranu tapiritia whakairoiro tino hauhautanga).

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;

Kia mahara, he korero tino nui mai @whakaranu Tuhinga ka whai mai korero:

Ka taea e maatau te kii ko te hononga i waenga i nga tawhā me te tere taapiri i roto i enei awhe o nga uara he matapōkeretia, kaore he tauira. Engari ko te neke atu i nga tawhā hanga-i roto i te maarama he pai te paanga ki te hua

Ko era. Na te whakahaere noa i nga tawhā BACKUP paerewa ka whiwhi 2-tiki i te wa tango tārua: 26 hēkona, ki te 53 i te timatanga. Ehara tena i te kino, tika? Engari me kite tatou he aha te mea ka pa ki te whakaoranga. He aha mena ka 4 nga wa te roa o te whakaora?

Tuatahi, me ine te roa ki te whakaora i te taapiri me nga tautuhinga taunoa:

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

Ana, kei te mohio koe, kei kona nga huarahi, ko te whakakapi ehara i te whakakapi, ko te whakaora ehara i te whakaora. Na ka penei taku mahi:

I tukatukahia nga wharangi 1070072 mo te putunga raraunga 'bt', te konae 'bt' kei te konae 1.

I tukatukahia nga wharangi e 2 mo te 'bt', te konae 'bt_log' kei te konae 1.

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 40.752 hēkona (205.141 MB/hekona).

Inaianei ka ngana ahau ki te whakaora i nga taapiri i tangohia me te whakarereke BLOCKSIZE, BUFFERCOUNT me MAXTRANSFSERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 32.283 hēkona (258.958 MB/hekona).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 32.682 hēkona (255.796 MB/hekona).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 32.091 hēkona (260.507 MB/hekona).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 32.401 hēkona (258.015 MB/hekona).

Ko te tauākī RESTORE DATABASE e kore e huri i te wa whakaora; kaore enei tawhā i tohua ki roto; Ko te SQL Server ake e whakatau mai i te taapiri. A e marama ana ahakoa me te whakaora ka taea te whiwhi - tata ki te 20% tere ake (Ma te pono, kaore au i whakapau i te wa roa mo te whakaora, i oma ahau i roto i te maha o nga taapiri "tere" me te whakarite kia kore he kino.).

Ko te take, me whakamarama ahau ehara enei i etahi tawhā e pai ana mo te katoa. Ka taea e koe te tiki i nga tawhā tino pai mo koe ma te whakamatautau. I whiwhi ahau i enei hua, ka whiwhi koe i nga mea rereke. Engari ka kite koe ka taea e koe te "whakatangi" i o taapiri ka taea te hanga me te tuku tere.

Ka tino taunaki ano ahau kia panuihia e koe nga tuhinga katoa, na te mea tera pea he ahuatanga motuhake mo to punaha.

Mai i taku tiimata ki te tuhi mo nga taapiri, ka hiahia ahau ki te tuhi tonu mo tetahi atu "arotautanga", he mea noa ake i te "whakatangi" i nga tawhā (ki taku mohio, kei te whakamahia e etahi taputapu taapiri, me nga taapiri. i korerohia i mua), engari kaore ano kia korerohia mo Habré.

Mena ka titiro tatou ki te rarangi tuarua i roto i nga tuhinga, kei raro tonu i te PAPA KAUPAPA KAUPAPA, ka kite tatou:

TO <backup_device> [ ,...n ]

Ki to whakaaro ka aha mena ka tohua e koe etahi taputapu_purua? Ka whakaaetia e te wetereo. A ka puta he mea tino whakamere - ka "horohia" te taapiri ki nga taputapu maha. Ko era. ia "pūrere" takitahi ka horihori, ngaro tetahi, ngaro te tārua katoa. Engari me pehea e pa ai taua paninga ki te tere whakaora?

Me ngana ki te hanga i tetahi taapiri i runga i nga "taputapu" e rua kei te taha ki te taha o te kōpaki kotahi:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

E nga matua o te ao, he aha i penei ai?

I tukatukahia nga wharangi 1070072 mo te putunga raraunga 'bt', te konae 'bt' kei te konae 1.

I tukatukahia nga wharangi e 2 mo te 'bt', te konae 'bt'takiuru' ki te kōnae 1.

I momoho te tukatuka 1070074 wharangi i roto i te 40.092 hēkona (208.519 MB/hekona).

I 25% te tere ake o te taapiri mai i te kikorangi? He aha mena ka taapirihia etahi atu taputapu?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

I momoho te tukatuka 1070074 wharangi i roto i te 34.234 hēkona (244.200 MB/hekona).

I te katoa, ko te whiwhinga ko te 35% o te wa o te tango i te taapiri na te mea kua tuhia te taapiri ki nga konae 4 i runga i te kōpae kotahi i te wa kotahi. I tirohia e au he nama nui ake - karekau he hua i runga i taku pona, pai rawa - e 4 nga taputapu. Mo koe - kaore au e mohio, me tirotiro koe. Ae ra, mena kei a koe enei taputapu - he tino rereke enei kopae, tena koe, kia nui ake te painga.

Inaianei ka korerohia me pehea te whakahoki mai i tenei harikoa. Ki te mahi i tenei, me huri koe i te whakahau whakaora me te whakararangi i nga taputapu katoa:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

I tutuki pai te mahi a RESTORE DATABASE i nga wharangi 1070074 i roto i te 38.027 hēkona (219.842 MB/hekona).

He tere ake, engari he waahi tata, ehara i te mea nui. I te nuinga o te waa, ka tere ake te tango i te taapiri me te whakahoki ano - angitu? Ko ahau, he tino angitu. Tenei he mea nui, no reira ka whakahoki ano ahau - mena ko koe ki te ngaro koe i tetahi o enei konae, ka ngaro te katoa o te taapiri.

Mena ka tirohia e koe i roto i te raarangi nga korero taapiri e whakaatuhia ana ma te whakamahi i nga Kara Kararehe 3213 me te 3605, ka kite koe i te wa e tautoko ana koe ki etahi taputapu, ka piki ake te maha o te BUFFERCOUNT. Tena pea, ka taea e koe te ngana ki te kowhiri i nga tawhā tino pai ake mo te BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, engari kaore au i angitu i tenei wa, a he mangere ahau ki te kawe ano i nga whakamatautau, engari mo te maha o nga konae. A he whakama mo nga wira. Mena kei te hiahia koe ki te whakarite i nga whakamatautau penei i te kainga, ehara i te mea uaua ki te hanga ano i te tuhinga.

Ka mutu, me korero tatou mo te utu. Mena ka tangohia te taapiri kia rite ki nga mahi a nga kaiwhakamahi, me whai whakaaro nui koe ki te whakamatautau, na te mea ka tere ake te tango i te taapiri, ka kaha ake nga kopae, ka piki ake te kawenga o te kaitukatuka (kei a koe tonu te kopiri i runga i te rere), a na reira, ka heke te urupare katoa o te punaha.

Maama noa, engari ka tino mohio ahau kaore au i whakaatu i nga whakakitenga. Ko nga mea i tuhia i runga ake nei he whakaaturanga noa me pehea e taea ai e koe te kowhiri i nga tawhā tino pai mo te tango taapiri.

Kia mahara ko nga mea katoa e mahia ana e koe i runga i to tupono me to tupono. Tirohia o taapiri, kaua e wareware ki te DBCC CHECKDB.

Source: will.com

MS SQL Server: WHAKAARO ki runga steroids

Tāpiri i te kōrero whakakore whakautu