Replikacja transakcji SQL Server ignoruje DML, który nie zmienia danych



Mam konfigurację replikacji transakcyjnej między dwoma serwerami i zauważyłem, że jeśli uruchomi się instrukcja podobna do tej:

UPDATE mytableSET mycolumn = mycolumn

replikacja w jakiś sposób wie, aby zignorować tę transakcję i nie zostanie zastosowana dla subskrybenta.Potwierdziłem to, uruchamiając SQL Profiler, a także dodając kolumnę TIMESTAMP do mojej tabeli subskrybentów (nie zmienia się).Podejrzewam, że istnieje jakiś mechanizm, który umożliwia takie „inteligentne” zachowanie i zastanawiałem się, czy ktokolwiek mógłby rzucić na to trochę światła.

Dziękuję Ci!
Replikacja [works by reading the transaction log](https://technet.microsoft.com/en-us/library/ms151706%28v=sql.105%29.aspx) .

Ten rodzaj „aktualizacji bez aktualizacji” często nie generuje żadnych rekordów dziennika transakcji.

Więcej informacji na ten temat można znaleźć w artykule Paula White'a [The Impact of Non-Updating Updates](http://sqlblog.com/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx) .
<p>najlepszym sposobem byłoby użycie tabel, które już masz. utwórz dwie tabele - tabela-a, tabela-b na potrzeby testu możesz nawet zaktualizować tę samą kolumnę o te same informacje, aby nie wpływać na żadne rzeczywiste dane.</p><p>na przykład update table\_a ustaw id = id gdzie id = 100;</p><p>otwórz dwie sesje dla tej samej bazy danych. na jeden biegnij</p><pre><code>begin tran update table\_a set id=id where id = 100; </code></pre><p>dwa biegi</p><pre><code>begin tran update table\_b set id=id where id =100; </code></pre><p>następnie skopiuj instrukcje aktualizacji do sesji przeciwnych i uruchom je w tym samym czasie. w jednym,</p><pre><code>update table\_b set id=id where id =100; </code></pre><p>we dwóch</p><pre><code>update table\_a set id=id where id = 100; </code></pre><p>właśnie próbowałem tego teraz i dostałem na ms-sql</p><pre><code>msg 1205, level 13, state 56, line 1 transaction (process id 23) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction. </code></pre> <p>w witrynach, w których pracowałem, wszelkie zmiany, które należy wprowadzić w instancjach produkcyjnych, muszą być skryptowane jako skrypty zmian, które będą działać w sql * plus; ponadto skrypty potrzebne do odtworzenia od podstaw wszystkich obiektów schematu muszą być aktualizowane. wszystkie te skrypty są sprawdzane pod kątem kontroli zmian i stamtąd migrowane.</p><p>możesz kontrolować zmiany ddl lub używać wyzwalaczy ddl, aby wykrywać zmiany, a nawet używać oprogramowania różnicowego do porównywania dwóch wystąpień, ale metody te są niedyskryminujące; często programista wprowadza i cofa wiele zmian w schemacie (np. małe zmiany testowe, tworzenie fałszywych tabel do testowania koncepcji itp.) przed ustaleniem, co dokładnie należy zmienić.</p> <p>przekazanie <a href="https://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775#5775">moja odpowiedź</a> na podobne pytanie dotyczące programu sql server:</p><blockquote> <p>w świecie sql porządek nie jest nieodłączną właściwością zestawu danych. w ten sposób nie otrzymujesz żadnych gwarancji z rdbms, że twoje dane wrócą w określonej kolejności - lub nawet w spójnej kolejności - chyba że zapytasz o swoje dane klauzulą ​​order by.</p> </blockquote><p>tak więc, aby odpowiedzieć na twoje pytanie:</p><ul> <li>mysql sortuje rekordy, jak chce, bez żadnej gwarancji spójności.</li> <li>jeśli zamierzasz cokolwiek polegać na tym zamówieniu, <strong> musisz określić żądane zamówienie za pomocą <code>order by</code>. robienie czegokolwiek innego to przygotowywanie się na niepożądane niespodzianki. </strong></li> </ul><p>jest to właściwość całego sql, nie tylko mysql. odpowiedni tekst w <a href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt">specyfikacja sql-92</a> to:</p><blockquote> <p>jeśli nie podano &lt;kolejność według klauzuli&gt;, kolejność wierszy q zależy od implementacji.</p> </blockquote><p>w specyfikacji kursorów znajdują się podobne fragmenty tekstu.</p> <p>ma więcej kolumn niż pokaż status tabeli; ale robi sztuczkę:</p><pre><code>select * from information\_schema.tables where table\_schema = database(); </code></pre><p>aktualizacja 2011-06-07 19:02</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = database(); </code></pre><p>te zapytania działają, jeśli ustawisz bieżącą bazę danych.</p><p>możesz także kodować konkretną bazę danych:</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = 'mysql'; </code></pre> <h1><strong> nr. </strong></h1><p><strong> <code>truncate</code> i <code>drop</code> są prawie identyczne pod względem zachowania i prędkości, więc wykonanie <code>truncate</code> tuż przed <code>drop</code> jest po prostu niepotrzebne. </strong></p><hr><p><em>waga: napisałem tę odpowiedź z perspektywy sql server i założyłem, że będzie ona miała zastosowanie w równym stopniu do sybase. wygląda na to, że <a href="https://dba.stackexchange.com/q/186154/2660">nie jest tak do końca</a> .</em></p><p><em>waga: kiedy po raz pierwszy opublikowałem tę odpowiedź, było kilka innych wysoko ocenianych odpowiedzi - w tym również odpowiedź zaakceptowana - które złożyły kilka fałszywych twierdzeń, takich jak: <code>truncate</code> nie jest rejestrowany; <code>truncate</code> nie można przywrócić; <code>truncate</code> jest szybszy niż <code>drop</code>; itp.</em></p><p><em>teraz, gdy ten wątek został oczyszczony, następujące obalenia mogą wydawać się styczne do pierwotnego pytania. zostawiam je tutaj jako odniesienie dla innych, którzy chcą obalić te mity.</em></p><hr><p>istnieje kilka popularnych kłamstw - rozpowszechnionych nawet wśród doświadczonych dba - które mogły być tego powodem <code>truncate-then-drop</code> wzór. oni są:</p><ul> <li><strong> mit </strong>: <code>truncate</code> nie jest rejestrowany, dlatego nie można go przywrócić.</li> <li><strong> mit </strong>: <code>truncate</code> jest szybszy niż <code>drop</code>.</li> </ul><p>pozwól mi obalić te kłamstwa. piszę to obalenie z perspektywy sql server, ale wszystko, co tu powiem, powinno mieć również zastosowanie do sybase.</p><h1> <a href="http://msdn.microsoft.com/en-us/library/ms177570.aspx"> obciąć </a> <em>is</em> zalogowany, a <em>can</em> można przywrócić.</h1><ul> <li> <p><strong> <code>truncate</code> jest zarejestrowaną operacją, więc <a href="http://sqlblog.com/blogs/kalen\_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx">to <em>można</em> wycofać</a> . </strong> po prostu zawiń to w transakcji.</p> <pre><code>use [tempdb]; set nocount on; create table truncate\_demo ( whatever varchar(10) ); insert into truncate\_demo (whatever) values ('log this'); begin transaction; truncate table truncate\_demo; rollback transaction; select * from truncate\_demo; drop table truncate\_demo; </code></pre> <p>zauważ jednak, że <strong> jest to <a href="http://psoug.org/reference/truncate.html">nieprawda</a> dla oracle </strong>. chociaż zalogowany i chroniony przez funkcję cofania i ponawiania oracle, <code>truncate</code> i inne instrukcje ddl <em>nie można</em> zostać wycofane przez użytkownika, ponieważ oracle wydaje <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11\_question\_id:2860243091362">niejawne zobowiązania</a> natychmiast przed i po wszystkich instrukcjach ddl.</p> </li> <li> <p><strong> <code>truncate</code> jest minimalnie zalogowany </strong>, a nie w pełni zalogowany. co to znaczy? powiedzmy, że <code>truncate</code> stół. zamiast umieszczać każdy usunięty wiersz w dzienniku transakcji, <code>truncate</code> po prostu zaznacza strony danych, na których żyją, jako nieprzydzielone. właśnie dlatego jest tak szybki. dlatego też nie można odzyskać wierszy tabeli <code>truncate</code> z dziennika transakcji przy użyciu czytnika dziennika. znajdziesz tam tylko odniesienia do stron zwolnionych danych.</p> <p>porównaj to z <code>delete</code>. jeśli <code>delete</code> wszystkie wiersze w tabeli i zatwierdzisz transakcję, nadal teoretycznie możesz znaleźć usunięte wiersze w dzienniku transakcji i odzyskać je stamtąd. jest tak, ponieważ <code>delete</code> zapisuje każdy usunięty wiersz w dzienniku transakcji. w przypadku dużych tabel spowoduje to, że będzie on znacznie wolniejszy niż <code>truncate</code>.</p> </li> </ul><h1> <a href="http://msdn.microsoft.com/en-us/library/ms173790.aspx"> upuść </a> jest równie szybki jak truncate.</h1><ul> <li>podobnie jak <code>truncate</code>, <strong> <code>drop</code> jest operacją minimalnie rejestrowaną. </strong> oznacza to, że <code>drop</code> można wycofać też. oznacza to również <a href="http://msdn.microsoft.com/en-us/library/dd425070.aspx">działa dokładnie tak samo</a> jak <code>truncate</code>. zamiast usuwać poszczególne wiersze, <strong> <code>drop</code> oznacza odpowiednie strony danych jako nieprzydzielone i dodatkowo oznacza metadane tabeli jako usunięte </strong>.</li> <li> <p>ponieważ <code>truncate</code> i <code>drop</code> działają dokładnie w ten sam sposób, działają tak samo szybko. <strong> nie ma sensu <code>truncate</code> - przechodzenie do tabeli przed <code>drop</code> - pisanie jej. </strong> uruchom <a href="https://gist.github.com/1349319">this skrypt demo</a> w twojej instancji programistycznej, jeśli mi nie wierzysz.</p> <p>na mojej lokalnej maszynie z ciepłą pamięcią podręczną wyniki są następujące:</p> <pre><code>table row count: 134,217,728 run# transaction duration (ms) truncate truncate then drop drop  01 0 1 4 02 0 39 1 03 0 1 1 04 0 2 1 05 0 1 1 06 0 25 1 07 0 1 1 08 0 1 1 09 0 1 1 10 0 12 1  avg 0 8.4 1.3 </code></pre> <p>tak więc, dla tabeli 134 <em>milionów</em> wierszy, zarówno <code>drop</code>, jak i <code>truncate</code> nie zajmują w ogóle czasu. (w przypadku zimnego bufora zajmują około 2-3 sekund dla pierwszego uruchomienia lub dwóch). uważam również, że wyższy średni czas trwania operacji <code>truncate</code>, a następnie <code>drop</code>, można przypisać zmianom obciążenia na moja lokalna maszyna i <em>nie</em>, ponieważ kombinacja jest w magiczny sposób o rząd wielkości gorsza niż poszczególne operacje. w końcu są prawie dokładnie tym samym.</p> <p>jeśli interesują cię bardziej szczegółowe informacje na temat narzutu związanego z logowaniem tych operacji, <a href="https://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop/7685#7685">martin ma proste wyjaśnienie</a> tego.</p> </li> </ul> Ok. Znalazłem to w czym problem.


Na początku myślałem, że to problem z DataProvided, ale to nie było. Odkryłem ten sam problem w SQLServer 2000 ....


Wyszukiwanie w google dowiedziałem się czegoś o plan wykonania. Biorąc w ten sposób, że zapytania zwiększyło wydajność 50%.


Krótki życiorys tego problemu jest to, że podczas wykonywania SP kodem, DBMS ma pewne problemy z planu wykonania, a nie korzystać z indeksów ...


Lepszą odpowiedź jest w tym poście:
 [Parametr Wąchania (lub Fałszowanie) w SQL Server](https://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server)


Mam nadzieję, że to Ci pomoże. <p>nigdy nie odłączaj podejrzanej bazy danych. w każdym razie, jak załączyłeś bazę danych po jej odłączeniu? użyłeś <code>create database</code> z opcją <code>for attach\_rebuild\_log</code>?</p><p>te polecenia powinny załatwić sprawę:</p><pre><code>alter database recovery\_test\_2 set emergency; alter database recovery\_test\_2 set single\_user; dbcc checkdb (recovery\_test\_2, repair\_allow\_data\_loss) with no\_infomsgs, all\_errormsgs; </code></pre><p>napisałem post dotyczący tej sytuacji:</p><p> <a href="http://yrushka.com/index.php/database-recovery/sql-20052008-log-file-deleted-part-3-2/">sql 2005/2008 procedura odzyskiwania bazy danych - usunięto plik dziennika (część 3)</a> </p><p>pytałeś o różnicę między:</p><ul> <li><code>dbcc checkdb ('&lt;dbname&gt;', repair\_allow\_data\_loss)</code> i</li> <li><code>alter database &lt;dbname&gt; rebuild log on (name=&lt;dbname&gt;,filename='&lt;logfilepath&gt;')</code></li> </ul><p>chodzi o to, że możesz uruchomić oba pliki, aby odbudować plik dziennika, ale używając <code>checkdb</code> odbudujesz dziennik i sprawdzisz bazę danych pod kątem błędów integralności.</p><p>druga (zmiana bazy danych) nie będzie działać, jeśli po utracie pliku dziennika wystąpiły aktywne transakcje (niepisane na dysk). podczas uruchamiania lub dołączania sql server będzie chciał wykonać odzyskiwanie (wycofanie i przywrócenie) z pliku dziennika, którego nie ma. zdarza się to, gdy dysk ulega awarii lub następuje nieoczekiwane zamknięcie serwera, a baza danych nie zostaje całkowicie zamknięta. wydaje mi się, że to nie była twoja sprawa i wszystko dobrze dla ciebie rozwiązało.</p><ol> <li> <p><code>dbcc checkdb (dbname, repair\_allow\_data\_loss)</code> uruchom na bazie danych w stanie awaryjnym sprawdza bazę danych pod kątem błędów niespójności, próbuje najpierw użyć pliku dziennika do odzyskania od wszelkich niespójności. jeśli go brakuje, dziennik transakcji jest odbudowywany.</p> </li> <li> <p><code>alter database rebuild log on...</code> to procedura nieudokumentowana i wymaga kolejnego <code>dbcc checkdb</code>, aby naprawić wszelkie błędy.</p> </li> </ol> <p>w większości baz danych a <code>not null</code> kolumna będzie bardziej wydajna pod względem przechowywanych danych z podanego powodu, a także bardziej wydajna w wyszukiwaniu i indeksowaniu - więc jeśli nie chcesz zezwalać na wartości null w kolumnie, powinieneś je wyraźnie zabronić.</p><p>będzie to miało niewielki wpływ na wydajność, ponieważ dodatkowe <code>not null</code> ograniczenia będą potencjalnie musiały być sprawdzane dla każdego wiersza, którego dotyczy zmiana za pomocą insert lub update, ale ponieważ większość baz danych jest względnie łatwa do zapisu i bardzo obciążona odczytem, ​​prawdopodobnie nie stanowi to problemu (mało czasu poświęconego na to jest zauważalne przy w każdym razie, ponieważ jest to operacja związana z procesorem, w której reszta operacji wstawiania/aktualizacji będzie związana z operacjami we/wy, a więc o wiele bardziej znaczącym „wąskim gardłem” i daje pewne „bezpłatne” sprawdzanie danych, więc kod (lub inny) kod osoby) nie może przypadkowo umieścić wartości null tam, gdzie inny kod ich nie oczekuje, a zatem może dawać nieprawidłowe wyniki w ich obecności.</p><p>edycja: jak peter wskazuje w swoim komentarzu, powyższe jest generalizmem i może nie być prawdziwe dla <em>wszystkich</em> dmbs, chociaż jestem prawie pewien, że tak jest w przypadku mysql i mssql. inne komplikacje w tym obszarze mogą obejmować takie funkcje, jak rzadkie tabele (na przykład zaimplementowane mssql 2008), które zmienią dynamikę wydajności (nie) zerowalnych kolumn.</p> <p>od <a href="http://msdn.microsoft.com/en-us/library/ms188029.aspx">books online</a> </p><blockquote> <p>format nowej\_tabeli określa się, oceniając wyrażenia na liście wyboru. kolumny w nowej\_tabeli są tworzone w kolejności określonej przez listę wyboru. każda kolumna w tabeli nowa\_ ma taką samą nazwę, typ danych, dopuszczalność nullab i wartość jak odpowiednie wyrażenie na liście wyboru. <strong> właściwość tożsamość kolumny jest przenoszona </strong>, z wyjątkiem warunków określonych w „praca z kolumnami tożsamości” w sekcji uwagi.</p> </blockquote><p>w dół strony:</p><blockquote> <p>po wybraniu istniejącej kolumny tożsamości do nowej tabeli nowa kolumna dziedziczy właściwość tożsamość, chyba że spełniony jest jeden z następujących warunków:</p> <ul> <li>instrukcja select zawiera funkcję join, group by lub funkcję agregującą.</li> <li>wiele instrukcji select jest łączonych za pomocą union.</li> <li>kolumna tożsamości znajduje się na liście wyboru więcej niż jeden raz.</li> <li>kolumna tożsamości jest częścią wyrażenia.</li> <li>kolumna tożsamości pochodzi ze zdalnego źródła danych.</li> </ul> <p>jeśli którykolwiek z tych warunków jest spełniony, kolumna jest tworzona nie null zamiast dziedziczenia właściwości tożsamość. jeśli w nowej tabeli wymagana jest kolumna tożsamości, ale taka kolumna nie jest dostępna lub chcesz mieć wartość początkową lub przyrostową inną niż kolumna tożsamości źródłowej, zdefiniuj kolumnę na liście wyboru za pomocą funkcji tożsamość. zobacz „tworzenie kolumny tożsamości za pomocą funkcji tożsamość” w sekcji przykłady poniżej.</p> </blockquote><p>więc ... teoretycznie można uciec:</p><pre><code>select id, val into copy\_from\_with\_id\_2 from with\_id union all select 0, 'test\_row' where 1 = 0; </code></pre><p>ważne byłoby skomentowanie tego kodu, aby go wyjaśnić, aby nie został usunięty następnym razem, gdy ktoś na niego spojrzy.</p> <p>użycie sql server profiler (narzędzie gui) do śledzenia serwera produkcyjnego nie jest dobrym pomysłem. ale to zależy od obciążenia. zamiast tego należy używać śledzenia sql po stronie serwera (patrz <a href="http://msdn.microsoft.com/en-us/library/ms188662%28v=sql.100%29.aspx">sp\_trace\_xxx</a> procedury). znalazłem również artykuły:</p><p> <a href="http://sqlblog.com/blogs/linchi\_shea/archive/2007/08/01/trace-profiler-test.aspx">wpływ na wydajność: śledzenie profilera vs. śledzenie sql po stronie serwera</a> ,</p><p> <a href="http://vyaskn.tripod.com/server\_side\_tracing\_in\_sql\_server.htm">automatyzacja śledzenia po stronie serwera w programie sql server</a> </p><p> <a href="http://weblogs.sqlteam.com/dang/archive/2007/12/16/avoid-causing-problems-with-profiler.aspx">nikaj powodowania problemów z profiler</a> </p><p>może będzie zainteresowany i przydatny.</p><p> <a href="http://msdn.microsoft.com/en-us/library/ms187023.aspx">rezerwuj online</a> mówi:</p><ul> <li>uruchom profiler zdalnie zamiast bezpośrednio na serwerze</li> <li>unikaj włączania zdarzeń, które występują często (np. zablokuj: nabyte), chyba że jest to absolutnie konieczne</li> <li>uwzględnij tylko potrzebne klasy zdarzeń</li> <li>określ filtry ograniczające, aby zmniejszyć liczbę zdarzeń</li> <li>unikaj zbędnych danych (np. sql: batchstarting i sql: batchcompleted)</li> <li>unikaj uruchamiania dużych śladów za pomocą profiler; zamiast tego rozważ śledzenie sql po stronie serwera</li> <li>ogranicz rozmiar pliku śledzenia po stronie serwera i zarządzaj wykorzystaniem miejsca</li> </ul>