Actualizarea statisticilor privind replicile secundare ale grupurilor de disponibilitate

UPDATE 25 iunie 2019: Elementul legat de acest subiect despre feedback-ul Azure: Permite actualizarea statisticilor pentru replicile secundare ale grupurilor de disponibilitate

Cu totii iubim si folosim caracteristicile minunate ale Grupurilor de Disponibilitate din replicile secundare, cum ar fi Verificarile de integritate, Backups & co.

In mod firesc, incapacitatea de a persista aceste informatii la nivelul bazei de date la nivel secundar este o durere in gat (si ganditi-va la lucruri precum CDC pentru a creste cantitatea de disconfort).

Ei bine, suficient pentru durerile si necazurile, aici vine ideea principala:

Draga Microsoft, permiteti-ne sa utilizam replicile noastre secundare pentru a actualiza statisticile … bine si sa facem multe alte chestii pentru replicile secundare.

Exista intotdeauna o cale un fel de mod *

* aproape intotdeauna

Sa enumeram detaliile de baza cunoscute pentru solutia posibila (pentru Enterprise Edition a Sql Server care este):

– Putem face replica secundara lizibila si sa citim aceleasi date pe ea. (Nu ca ar trebui sa faci asta in mod implicit, dar daca stii cu adevarat ce faci …

Array

)

– Putem copia obiectul nostru in TempDB (da, tabelul dvs. Multi-TB nu este probabil cel mai bun candidat pentru aceasta operatiune) sau poate intr-un alt DB care poate fi scris.

– Putem scrie rezultate in folderul partajat intre replici (sa zicem intr-un fisier text intr-un fisier Share)

– Putem exporta obiectul BLOB al statisticilor din SQL Server

– Putem importa obiectul BLOB al statisticilor in statisticile

Sa o facem!

Am un test AG pe cateva VM-uri cu Sql Server 2017 (joaca cu orice versiune pe care simti ca functioneaza pentru tine) si iau un tabel destul de simplu pe care vreau sa-mi actualizez statisticile.

Iata scriptul pentru crearea unui tabel si introducerea unui milion de randuri in el:

TABLA DE DROP DACA EXISTE dbo.SampleDataTable; CREATE TABLE dbo.SampleDataTable (C1 BIGINT NOT NULL, C2 BIGINT NOT NULL, CONSTRAINT PK_SampleDataTable PRIMARY KEY (C1)); INSERTA IN DBO.SampleDataTable CU (TABLOCK) SELECTA t.RN, t.RN DE (SELECT TOP (1000000) ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN FROM sys.

Array

objects t1 CROSS JOIN sys.objects t2 CROSS JOIN sys.objects t3 CROSS JOIN sys.objects t4 CROSS JOIN sys.objects t5) t OPTION (MAXDOP 1);

In plus, sa cream un obiect statistic ST_SampleDataTable_C2 care va acoperi coloana c2:

CREATE STATISTICS ST_SampleDataTable_C2 ON dbo.SampleDataTable (C2);

Dupa aceea, voi insera 1000 de randuri care vor conta cu adevarat si pe care imi voi dori cu adevarat sa le actualizez statisticile

setati numarul de pe; INSERTA IN DBO.SampleDataTable CU (TABLOCK) SELECTA 10000000 + t.RN, 999999999 DE LA (SELECT TOP (1000) ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN FROM sys.objects t1 CROSS JOIN sys.

objects t2 CROSS JOIN sys.objects t3 CROSS JOIN sys.objects t4 CROSS JOIN sys.objects t5) t OPTION (MAXDOP 1);

Acum am un caz cu valoarea 999999999 care apare de 1000 de ori pentru coloana C2 si care cu siguranta raspunde problemei cheie ascendenta si vreau cu adevarat sa-mi actualizez statisticile … pe secundar, astfel incat sa nu afectez primarul cu calculul si pot servi clientii mei finali.

Folosind comanda buna veche DBCC SHOW_STATISTICS sa aruncam o privire in obiectul nostru de statistici:

DBCC SHOW_STATISTICS (‘dbo.SampleDataTable’, ‘ST_SampleDataTable_C2’)

Totul este bine in regat, iar statisticile noastre sunt in totalitate bune, cu doar 1 milion de randuri responsabile, iar acele perky 1000 de randuri vor face in cele din urma parte din noile statistici, calculate pe replica primara.

De asemenea, putem verifica fluxul de statistici cu ajutorul optiunii STATS_STREAM din comanda DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS (‘dbo.SampleDataTable’, ‘ST_SampleDataTable_C2’) CU STATS_STREAM;

Acesta este un flux de caractere destul de simplu, care a fost pe blog de varsta si chiar daca nu sunt sigur daca este o caracteristica complet documentata, nu a impiedicat niciodata oamenii sa o foloseasca.

Pe replica secundara

Sa presupunem ca copiem tabelul din replica secundara in TempDB (avand in vedere ca rulam grupuri de disponibilitate sincrone, dar am putea face la fel cu AG asincron, de asemenea, informatiile ar putea fi putin intarziate):

folositi TempDB; TABLA DE DROP DACA EXISTE dbo.SampleDataTable; CREATE TABLE dbo.SampleDataTable (C1 BIGINT NOT NULL, C2 BIGINT NOT NULL, CONSTRAINT PK_SampleDataTable PRIMARY KEY (C1)); INSERT INTO dbo.SampleDataTable SELECT C1, C2 FROM AvGroupDb.dbo.SampleDataTable;

Suntem gata sa actualizam statisticile cu fullscanul din replica secundara de pe TempDB:

folositi TempDB; ACTUALIZARE STATISTICI ST_SampleDataTable_C2 PE dbo.SampleDataTable (C2) CU FULLSCAN;

Revenind la DBCC SHOW_STATISTICS, sa aruncam o privire in obiectul nostru de statistici:

DBCC SHOW_STATISTICS (‘dbo.SampleDataTable’, ‘ST_SampleDataTable_C2’)

Pare destul de diferit de cel initial, cu doar 3 randuri in loc de 178 ca in cel original, dar descrie perfect distributia datelor – avem 1 milion de randuri distincte si 1000 de randuri cu aceeasi valoare – histograma este la fel de buna dupa cum se poate.

Trecerea la obiectul fluxului de statistici:

DBCC SHOW_STATISTICS (‘dbo.SampleDataTable’, ‘ST_SampleDataTable_C2’) CU STATS_STREAM;

Nu trebuie sa fiti un adevarat geniu pentru a observa ca fluxul arata destul de diferit – putem vedea simbolurile 5689A0C6 in fluxul actualizat, in timp ce in original intre toate acele zerouri pe care le-am avut EDF10EB4.

Haideti sa ne concentram acum pe exportul acestor date intr-un fisier text undeva in afara SQL Server si o voi face cu ajutorul minunatei comenzi BCP, care necesita activarea CMDSHELL (avertizare: este posibil sa nu doriti sa faceti asta pe serverul dvs. de productie):

EXEC xp_cmdshell ‘BCP „DBCC SHOW_STATISTICS (‘ ‘AvGroupDb.dbo.SampleDataTable’ ‘,’ ‘ST_SampleDataTable_C2’ ‘) CU STATS_STREAM” queryout \\ SharedServer \ Tempdb \ stats.txt -c -T’;

Iata cat de mare va fi acest fisier stats.txt in folderul nostru comun:

Doar cateva KB, nimic altceva!

Usor de transportat, usor de gestionat!

Inapoi la primar

Pe serverul principal va trebui sa cream un tabel temporar care va stoca fluxul de statistici inainte de a actualiza tabelul nostru de principiu SampleDataTable (sau in practica am putea extinde tabelul pentru mai multe baze de date / tabele / statistici:

CREATE TABLE dbo.TempStats (Stats_Stream VARBINARY (MAX), Rows BIGINT, DataPages BIGINT);

Sa importam acele date din fisierul text in noul nostru tabel de timp stralucitor si sa selectam rezultatul pe care l-am importat:

BULK INSERT dbo.TempStats FROM ‘\\ SharedServer \ Tempdb \ stats.txt’ SELECT * FROM dbo.TempStats;

Putem vedea date destul de similare cu cele pe care le-am calculat pe serverul nostru secundar, dar datele sunt deja pe serverul principal si tot ce va trebui sa facem este sa actualizam statisticile noastre pe tabelul nostru de testare. Aceasta operatie se poate face cu ajutorul declaratiei UDPATE STATISTICS, folosind optiunea WITH STATS_STREAM = …

DECLARE @script NVARCHAR (MAX) SELECT @script = ‘ACTUALIZARE STATISTICA dbo.SampleDataTable (ST_SampleDataTable_C2) CU STATS_STREAM =’ + CONVERT (nvarchar (max), [Stats_Stream], 1) din dbo.TempStats PRINT EXECUTE sp_executesql @script;

Urmatorul script de mai sus citeste valoarea importata (da, fac aici un exemplu de tabel si nu ma intereseaza mai multe statistici, fire, tabele, baze de date etc.), creeaza instructiunea ACTUALIZARE STATISTICA, o tipareste si apoi o executa in cele din urma.

Iata ce am primit din rezultat:

ACTUALIZARE STATISTICA dbo.SampleDataTable (ST_SampleDataTable_C2) CU STATS_STREAM =0x010000000100000000000000000000005689A0C6000000007D020000000000003D020000000000007F0300007F0000000800130000000000000000000000000007000000C5BB2F0172AA000028460F000000000028460F00000000000000803FB4378635000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000030000000100000018000000000000418062744900000000000000410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190400000000000000000000000000006900000000000000B900000000000000C100000000000000C900000000000000180000000000000033000000000000004E00000000000000100018000000803F000000000000803F0100000000000000040000100018000000803FC02073490000803F0E320F00000000000400001000180000007A44009081450000803FFFC99A3B000000000400000100000079DC280172AA00000000000080842E4140420F0000000000AE000000000000A0924EB33E9BD9BB9DFC19B4404449E6ABFE2F84400000000000408F400000000000000000DFE68293BDC8803F28460F00000000000000000000000000

Rularea DBCC SHOW_STATISTICS pe primara imi ofera exact rezultatul pe care il speram – acelasi pe care l-am obtinut pe replica secundara. Bucla este inchisa

DBCC SHOW_STATISTICS (‘dbo.SampleDataTable’, ‘ST_SampleDataTable_C2’);

Partea cu adevarat minunata din aceasta poveste este ca dimensiunea obiectului statistic este cu adevarat nesemnificativa si il putem transporta cu usurinta / instantaneu la replica primara.

Scenariul nu este atat de de baza:

– Daca aveti mai multe grupuri de disponibilitate intre aceleasi replici, unde o replica conduce un AG, in timp ce cealalta conduce cea de-a doua AG -> puteti introduce datele BLOB in fluxul AG-urilor si puteti replica o baza de date minuscula suplimentara cu datele transportate .

Ca si in imaginea din stanga, daca avem 2 grupuri de disponibilitate (AG1 si AG2) care sunt localizate si obligate sa fie pe serverele distincte – si avem o anumita tabela pe Serverul 1 din Grupul de disponibilitate 1 pe care dorim actualizati statisticile pe, apoi am putea extrage o copie a tabelului (sa o numim dbo.MyTable) pe Server 2 din TempDB si sa utilizam Channel pentru Grupul de Disponibilitate 2 pentru a livra obiectul fluxului de statistici inapoi la serverul 1, unde pur si simplu am reimporta datele inapoi in tabelul nostru principal dbo.Mytable. Y

Da, stiu ca suna destul de confuz, dar pur si simplu ganditi-va la AG 2 ca la canalul de feedback pentru a da rezultate inapoi, in loc sa le impingeti prin partajarea fisierelor sau Azure Blob Storage.

Zona reclama

S-ar putea sa aveti o obiectie sau doua, cum ar fi:

– de ce as face acest lucru pe secundar, cand as putea face acelasi lucru perfect pe primar, fara alte intarzieri …

(Ei bine, ideea de aici este sa descarcati elementul primar)

– nu sunt ” Putem lovi o sarcina serioasa de munca pe secundar (daca este blocata – atunci da, si de aceea ar trebui sa incercam sa-i folosim puterile)

– nu putem sa afectam pur si simplu primarul? (nu, citim datele si putem scrie inapoi cateva KB, unde in epoca GB & TB suna ca „spune whaaaaaat?”)

– ce se intampla daca la mijlocul timpului primarul va incepe actualizarea statisticilor de unul singur? In acest caz, poate anula procesul secundar sau va reexecuta cu datele actualizate (si poate limita aceste tentative la numarul de 3)

dar nu o cumpar. 🙂

Grupuri de disponibilitate Optiunea canalului de feedback

Exista un canal de feedback pentru comunicarea de la replica secundara la cea primara – odata ce inregistram o tranzactie pe replica sincrona, primarul va astepta confirmarea care este trimisa de replica secundara – si ma gandesc ca acest canal poate sa fie potential utilizat pentru implementarea acestei imbunatatiri. Aruncati o privire la imaginea din stanga, care a fost luata din postarea originala pe blog de Simon Su –

https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/, unde se afla mecanismul exact al feedback-ului canal este explicat. Replica secundara folosind etapa 12 si cele posterioare confirma replicii primare ca informatia a persistat. Acelasi canal trebuie utilizat pentru a trimite obiectul fluxului de statistici dupa ce a fost recalculat pe replica secundara. Desigur, nu ar trebui sa folosim TempDB in acest scop, ci crearea unui obiect din memorie intr-o baza de date care nu este persistenta (uitandu-va la tabelele de numai schema OLTP in memorie sau sa ne gandim la tabelele NOLOGGING din Oracle) care va sa fie distrus la sfarsitul operatiunii – acest lucru ar fi cu adevarat nemaipomenit.

Ganduri generale

Nu prea conteaza daca replica este sincrona sau asincrona – de cele mai multe ori actualizarea statisticilor nu are loc la fiecare doua secunde, iar acest lucru ne aduce la a doua parte a ideii:

faceti din actualizarea statisticilor o posibilitate invocabila de la Primarul Replica, cu un parametru cum ar fi

ACTUALIZARE STATISTICA dbo.MyAwesomeTable (HugeImportantStatOnC17) CU FULLSCAN, SECUNDAR

unde indicarea locului unde trebuie executata comanda este setata cu comanda SECONDARY.

Si la fel ca in cazul copiilor de rezerva, am putea avea o configuratie pentru preferintele pentru actualizarea statisticilor, alegand replica care ar trebui sa fie preferata pentru procesarea Actualizarii statisticilor (sau a oricarei alte comenzi in viitor).

Sunt increzator ca o astfel de caracteristica ar fi ceva care ar motiva o multime de utilizatori Enterprise Edition sa migreze catre o noua versiune Sql Server care ar putea raspandi comenzile grele intre replicile secundare.

Oh, si imaginati-va ca ajustati acest gen de lucruri pentru baza de date SQL Azure pe cele 2 replici secundare din spatele scenei – ar putea fi aproape o caracteristica criminala 🙂

Intre timp – oh da, vad total cum se poate automatiza aceasta solutie cu ajutorul Powershell 🙂

Microsoft, randul tau! 🙂

Over & Out.