Marek Adamczuk

Konkurs – autonumerator bez dziur

mad — Fri, 17 Jul 2009 05:25:18 GMT

Wczoraj na http://www.wss.pl/frmThread.aspx?tid=67788 zaproponowałem nowy konkurs. Zapraszam wszystkich do zmierzenia się z tematem.

Równoległość jobów w SQL Server Agent

mad — Thu, 09 Jul 2009 08:03:00 GMT

W ostatnim czasie ćwiczyłem się trochę zrównoleglanie zadań. Uruchamiałem jednocześnie kolejne części zadania jako joby SQL Server Agenta, spodziewając się skrócenia całościowego czasu wykonania. Pierwsze wyniki były oczywiście niezadawalające – ogólny czas wykonania zadania wzrósł. Gdy zacząłem szukać przyczyny problemu, okazało się że z 50 jednocześnie uruchomionych zadań wystartowało tylko 40. Pozostałe 10 oczekiwało na zakończenie poprzednich – dawało się to łatwo zaobserwować na historii jobów. 10 z nich po prostu startowało później o kilka sekund niezależnie od tego, jak proste było zadanie.

Zacząłem szukać, gdzie można przestawić ten parametr. Niestety w interfejsie SSMS tego nie znalazłem. Okazało się jednak, że parametr istnieje i nawet jest opisany w bazie wiedzy. Artykuł dotyczy wprawdzie SQL Server 2000, ale na szczęście jest też wzmianka, gdzie funckjonalność przewędrowała na SQL Server 2005. Otóż, parametr ten trafił do tabeli syssubsystems bazy msdb. Co ciekawe, żeby go przestawić, trzeba najzwyczajniej na świecie tabelę tę zupdate’ować. O tak:

UPDATE msdb.dbo.syssubsystems SET max_worker_threads = 50 WHERE subsystem = 'TSQL';

W czasach, w których odwykliśmy już na dobre od update’owania tabel systemowych wygląda to niepokojąco, ale działa. Wystarczy już tylko restart usługi SQL Server Agent i 50 równoległych zadań przestaje być problemem. Dla Agenta oczywiście – sam SQL to zupełnie inna historia. Do opowiedzenia kolejnym razem…

MVP

mad — Fri, 03 Jul 2009 18:14:00 GMT

3 dni temu wraz z Ziemkiem Borowskim, Jackiem Doktórem i Pawłem Pławiakiem zostałem nominowany przez Microsoft do nagrody MVP.

Choć pewne znaki na Niebie i Ziemi wskazywały, że coś się kroi, to jednak nagroda jest dla mnie jakimś zaskoczeniem. Jest mi bardzo miło. Dopiero oswajam się z myślą, że to się stało.

Obdarowanemu wypada podziękować. W pierwszej kolejności, rzecz jasna, darczyńcy. Dziękuję zatem korporacji Microsoft za przyznanie mi tej nagrody. Patrząc na moje dokonania w zestawieniu z obecnymi MVP traktuję ją jako mocną zachętę do większego zaangażowania. Tak, tak. Wiem, że MVP nic nie musi. Ale może :). I to chyba znacznie więcej niż sprzed nominacji.

Składając podziękowania korporacji nie zapominam, za co ona przyznaje ten zaszczytny tytuł. Mianowicie za dzielenie się swoją wiedzą ze Społecznością. Dziękuję Wam, przedstawiciele Społeczności Microsoft, którzy macie ochotę dyskutować ze mna na forum wss.pl i zaglądać na moje sesje PLSSUG. Nie mam wątpliwości, że ta nagroda jest efektem Waszego wielkiego wsparcia, którego niejednokrotnie z Waszej strony doświadczyłem. Dziękuję serdecznie – to bardzo doładowuje.

And at last, but not least chciałbym podziękować komuś osobiście. A mianowicie mojemu przyjacielowi, a zarazem renominowanemu MVP - Pawłowi Potasińskiemu. Pawle, gdyby nie Ty, mój udział w życiu Społeczności byłby niewspółmiernie mniejszy niż jest. To dzięki Twojej pasji i ogromnej wiedzy przekonałem się do regularnego pisania, a potem moderacji wss.pl. To Ty namówiłeś mnie do prowadzenia sesji na PLSSUG, C2C czy MTS, czy wreszcie do założenia niniejszego bloga. Wreszcie to w ogromnej mierze dzięki Twojemu osobistemu zaangażowaniu, dziś mogę cieszyć się statusem MVP. Czasami wręcz zastanawiam się czyja składowa zaangażowania w tytuł dla mnie była większa: moja własna, czy Twoja. Bez Ciebie pewnie nigdy nie przekonałbym się, że udział w życiu Społeczności to naprawdę fajna przygoda i niesamowite źródło wiedzy. Twój entuzjazm naprawdę zaraża. Jesteś Wielki – dzięki jeszcze raz.

ORDER BY - tylko w view

mad — Tue, 13 Jan 2009 13:10:00 GMT

Wszem i wobec wiadomo, że klauzula ORDER BY w view jest niedozwolona. W końcu view to "wirtualna tabela" i jako taka sama z siebie nie ma kolejności rekordów. Sytuacja zmieniła się z nadejściem SQL Server 7.0 i klauzulą TOP. Wtedy bowiem klauzula ORDER BY uzyskała znaczenie części filtra TOP. Oczywiście zdolność sortowania view niejako przy okazji pozostała, co wielu wykorzystywało do obchodzenia problemu nieobsługiwania ORDER BY. Ale to dłuższy temat...

Tymczasem ostatnio natrafiłem na ciekawy przypadek, gdzie do zastosowania ORDER BY paradoksalnie konieczne okazało się użycie view.

Wyobraźmy sobie, że chcemy wyświetlić z tabeli widoku systemowych 10 pierwszych i 10 ostatnich obiektów. Na pierwszy rzut oka nic prostrzego:

select top (10) name from sys.objects order by name union all select top (10) name from sys.objects order by name desc

A tu okazuje się, że niestety taka składnia nie jest obsługiwana. UNION dopuszcza tylko globalny ORDER BY zapominając jakoby, że w połączeniu z TOP staje się filtrem. Okazuje się jednak, że problem daje się obejść przez zastosowanie VIEW.

create view dbo.vwObiektyTrocheZPoczatkuIZKonca as select top (10) name from sys.objects order by name union all select top (10) name from sys.objects order by name desc

Ten widok założy się bez problemu i będzie działał zgodnie z oczekiwaniami.

Limit 8060 bajtów na wiersz odszedł inaczej

mad — Sat, 30 Aug 2008 19:44:00 GMT

Jedną z istotnych nowości w SQL Server 2005 było zniesienie limitu 8060 bajtów zapisanych w jednym wierszu. Oto krótki test.

create table dbo.temp_duzaTabela ( a varchar(8000), b varchar(8000), c varchar(8000) ) GO insert dbo.temp_duzaTabela (a,b,c) select replicate('x',8000),replicate('x',8000),replicate('x',8000)

Na SQL Server 2000 pierwszy batch generował ostrzeżenie, a drugi błąd. Na SQL Server 2005 oba przechodzą bez problemu. Czy zatem limit 8060 bajtów odszedł? Niestety nie do końca. Spróbujmy tego samego z kolumnami typu char.

create table dbo.temp_duzaTabela ( a char(8000), b char(8000), c char(8000) )

Próba wykonania tego batcha ku mojemu zdziwieniu skończyła się komunikatem:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'temp_duzaTabela' failed because the minimum row size would be 24007, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Chwila uparczywego wpatrywania się w komunikat obnaża problem. Kluczowe jest tu słówko minimum. Zatem dla minimalnej wielkości wiersza limit wciąż obowiązuje. Problem rozwiązuje w jakiejś mierze SQL Server 2008 w tej kwestii bynajmniej nie wprowadza żadnej nowej jakości.

Dziękuję Pawłowi Gailardowi za uświadomienie mi tego problemu.

[01-09-2008] Edycja: Okazuje się, że jednak SQL Server 2008 w jakiś sposób życie nam tu ułatwia. Nie automatycznie, ale jednak. Otóż atrybut SPARSE powoduje, że damy radę założyć tabelę z typami o stałej długości przekraczającymi sumarycznie 8060 bajtów.

create table dbo.temp_duzaTabela ( a char(8000) SPARSE, b char(8000) SPARSE, c char(8000) SPARSE )

Konkurs - przywrócenie pierwotnej kolejności kolumn tabeli

mad — Wed, 23 Jul 2008 19:24:00 GMT

Zgodnie z obietnicą ogłaszam konkurs zapowiedziany na wczorajszej sesji PLSSUG. Zadanie polega na przywróceniu pierwotnej kolejności kolumn tabeli po usunięciu i ponownym odtworzeniu kolumny wyliczanej. Np. mamy tabelę:

create table dbo.Osoby ( id int, Imie varchar(50) NOT NULL, Nazwisko varchar(100) NOT NULL, NazwaPelna as Imie+' '+Nazwisko, TelefonDoPracy varchar(30) NULL, TelefonDomowy varchar(30) NULL, TelefonKom varchar(30) NULL, Telefony as ISNULL(TelefonDoPracy+';','')+ISNULL(TelefonDomowy+';','')+ISNULL(TelefonKom+';',''), EMail varchar(100), DataRejestracji datetime )

Po usunięciu i ponownym odtworzeniu kolumn NazwaPelna i Telefony, znajdą się one na końcu listy kolumn tabeli. Słabo napisane aplikacje mogą tej zmiany nie wytrzymać. Dlatego będziemy chcieli przywrócić kolejność pierwotną.

W czasie sesji pokazałem, jak wygenerować skrypty, które dla dowolnej tabeli:

1. Zapisze definicję wszystkich kolumn wyliczanych wraz z pierwotną kolejnością do tabeli
2. Usunie wszystkie kolumny wyliczane
3. Odtworzy kolumny wyliczane (lądują na końcu listy)

Definicja tabeli jest w skrypcie recollate.sql umieszczonych w zasobach PLSSUG. Dla wygody zacytuję ją ponownie tutaj.

create table dbo.temp_ComputedCols ( TableName sysname, ColumnName sysname, definition nvarchar(max), position int, PRIMARY KEY (TableName, ColumnName) )

Zadanie konkursowe polega na tym, żeby na podstawie definicji tabeli po operacji usunięcia i odtworzenia kolumn wyliczanych + zawartości tabeli temp_ComputedCols wygenerować skrypt, który doprowadzi tabelę do pierwotnej postaci - wszystkie kolumny są w tej samej kolejności co przed operacją. Linie skryptu mają być wygenerowane w języku TSQL w postaci procedury lub funkcji tabelarycznej. Jako jedyny parametr procedury/funkcji powinna być przekazana nazwa tabeli, dla której generujemy skrypt.

Warunki oceny rozwiązania:

1. Rozwiązanie nie może spowodować jakiejkolwiek utraty danych w tabeli
2. Rozwiązanie powinno wykonać minimalną liczbę manipulacji na danych i strukturze tabeli
3. Rozwiązanie powinno przewidywać, że jest więcej niż jedna kolumna wyliczana, tak jak w przykładzie
4. Rozwiązanie powinno być możliwie jak najszybsze

Dla uproszczenia zakładamy, że w chwili rozpoczęcia naprawy na tabeli, którą mamy naprawić nie ma żadnych więzów ani indeksów.

Czekam na rozwiązania konkursowe do końca 15-08-2008 - proszę o przysyłanie ich na mój mail z profilu na wss.pl. Najciekawsze rozwiązania opublikuję, a najlepsze nagrodzę na najbliższym spotkaniu PLSSUG. Do 10.08.2008 będę poza Polską i nie wiem, czy będę miał dostęp do maila, zatem proszę nie zrażać się brakiem mojej reakcji w tym czasie.

Pozdrawiam
Marek Adamczuk

Hash czy małpa a sprawa collation

mad — Wed, 23 Jul 2008 09:56:00 GMT

Wczoraj miałem przyjemność poprowadzić na PLSSUG Warszawa prezentację dotyczącą collation na SQL Serverze. W czasie prezentacji wygłosiłem pewną, nie do końca uprawnioną tezę. Mianowicie stwierdziłem, że collation z którym zakładane są domyślnie kolumny tabeli tymczasowej ustawia się na collation bazy bieżącej w SQL Server 2005 i z collation bazy tempdb w SQL Server 2000. W rzeczywistości jest to prawdą tylko dla zmiennych tablicowych. Oto przykład:

-- ważne, żeby to nie było collation servera! create database bad_db collate SQL_Polish_CP1250_CI_AS GO use bad_db GO declare @t table (a nvarchar(128)) select * from @t t join sysobjects o on t.a = o.name GO create table #t (a nvarchar(128)) select * from #t t join sysobjects o on t.a = o.name GO

Przykład uruchamiamy na SQL Server 2000 i SQL Server 2005. Jak się okazuje, query ze zmienną tablicową wykonuje się prawidłowo tylko na 2005. Wszystkie inne zwracają błąd konfliktu collation. Zatem #tabele tymczasowe w kontekście collation nadal są złe na SQL Server 2005. Co więcej, test przeprowadzony na ostatnim dostępnym mi buildzie SQL Server 2008 daje identyczny rezultat, co 2005.

Czy zatem, będąc świadomymi zła, jesteśmy w stanie mu zaradzić? Owszem jesteśmy, ale wymaga to zmiany przyzwyczajeń w pisaniu kodu. Otóż, aby być collation insensitive, musimy przy tworzeniu tabel tymczasowych, a na SQL 2000 również zmiennych tablicowych, dopisywać przy stringowych kolumnach klauzulę COLLATE database_default. Na naszym przykładzie wygląda to tak:

-- tak już nie musimy robić na SQL Server 2005 i wyższych declare @t table (a nvarchar(128) collate database_default) select * from @t t join sysobjects o on t.a = o.name GO -- tak niestety musimy nadal create table #t (a nvarchar(128) collate database_default) select * from #t t join sysobjects o on t.a = o.name drop table #t GO

Wnioski

Jeśli nie chcemy mieć potencjalnego konfliktu collation:
1. Dopisujemy collate database_default przy definicjach kolumn stringowych #tabel tymczasowych zawsze i zmiennych @tablicowych na SQL 2000.
2. Jeśli mamy SQL 2005 i nie chcemy wyrabiać sobie odruchu z punktu 1 - bardziej lubimy @tabele niż #tabele. Abstrahując od innych warunków ich zastosowania, rzecz jasna.

Obsługa wyjątków na SQL - (nie) daj się złapać

mad — Mon, 07 Jul 2008 20:59:00 GMT

Będzie trochę o obsłudze wyjątków na SQL Server. O tym, kiedy może okazać się przydatna i jakie czyhają pułapki. A będzie na przykładzie. Otóż mamy sytuację, w której musimy na chwilę zdropić sporą ilość obiektów proceduralnych na bazie danych, aby je po wykonaniu pewnej operacji odtworzyć. Po co? O tym za następnym razem. Dziś skupię się na zawiłościach dropienia i odtwarzania obiektów. Dla uproszczenia wywodu przyjmijmy, że wszystkie interesujące nas obiekty należą do schematu dbo i są procedurami, widokami, funkcjami TSQL wszelkiej maści lub triggerami. Przyjmijmy też, że nazwy obiektów podlegających naszej operacji mamy w następującej tabeli:

create table dbo.ObjectsToRecreate ( ObjectName sysname NOT NULL, ObjectDefinition sysname NULL, ObjectId int NULL, CONSTRAINT PK_ObjectsToRecreate PRIMARY KEY (ObjectName))

Aby było co odtwarzać po zdropieniu - zapiszmy sobie definicje obiektów do naszej tabeli. Z SQL Server 2005 sprawa jest prosta - używamy nowej, fajnej funkcji object_definition.

update ObjectsToRecreate SET ObjectDefinition = OBJECT_DEFINITION(OBJECT_ID(ObjectName))

Kiedy już mamy kopię treści ciał obiektów możemy zabrać się do dropienia. O ile obiekty nie są użyte w innych z klauzulą SCHEMABINDING, tudzież w niskopoziomowych obiektach takich jak defaulty, checki czy kolumny wyliczalne - operacja powinna pójść bezproblemowo. Jak? Sprawę załatwi kawałek dynamicznego SQL-a.

declare @sql nvarchar(max) select @sql = N'' select @sql = @sql +N'drop '+ case when o.type in ('IF','TF','FN') then N'function ' when o.type = 'P' then N'procedure ' when o.type = 'V' then N'view ' when o.type = 'TR' then N'trigger ' end +schema_name(o.schema_id)+N'.'+quotename(o.name)+N';' from dbo.objectsToRecreate otr join sys.objects o on otr.ObjectName = o.name exec (@sql)

Teraz śmiało wykonujemy naszą operację, której przeszkadzałyby zdropione obiekty i zabieramy się do ich odtworzenia. Tym razem raczej nie obejdzie się bez kursora. Nie wydaje się on na pierwszy rzut oka czymś specjalnie trudnym:

declare @definition nvarchar(max) declare c cursor local static read_only forward_only for select ObjectDefinition from dbo.objectsToRecreate open c fetch next from c into @definition while @@fetch_status = 0 begin exec (@definition) fetch next from c into @definition end close c deallocate c

Wszystko zadziała pięknie, o ile usunięte uprzednio obiekty nie będą zależne od siebie. Szczególnie boleśnie możemy to odczuć na widokach i funkcjach. Jeśli nie odtworzymy obiektów we właściwej kolejności – operacja po prostu się nie uda. Pytanie tylko: Jak znaleźć tę właściwą kolejność? Jak się okazuje – pytanie niebanalne. Mamy wprawdzie tabelę sys.sql_dependencies, ale po zdropieniu obiektów pustą, a i przed trudno wciąż jej ufać. Nawet gdy ją mamy porządnie uzupełnioną – rozplątanie właściwej kolejności obiektów w skomplikowanym przypadku wcale nie jest oczywiste. O sys.sql_dependencies napiszę innym razem – to temat na osobny artykuł. Tymczasem jest dużo prostsza metoda na uzyskanie właściwej kolejności. Jednym zdaniem: próbuj odtwarzać obiekty do skutku. Osiągniemy to łatwo obudowując nasz kod pętlą while. Kończymy, gdy uda nam się odtworzyć wszystkie obiekty. Aby nie oglądać paskudnych komunikatów błędów – opakujmy nasz kod bloki obsługi wyjątków:

declare @definition nvarchar(max), @name sysname while exists (select 1 from dbo.objectsToRecreate where objectId is null) begin declare c cursor local static read_only forward_only for select ObjectDefinition, ObjectName from dbo.objectsToRecreate where objectId is null open c fetch next from c into @definition, @name while @@fetch_status = 0 begin begin try exec (@definition) end try begin catch set @definition = @name+':'+error_message() print @definition end catch update dbo.objectsToRecreate set objectId = object_id(@name) where ObjectName = @name fetch next from c into @definition, @name end close c deallocate c end

Jeśli nasza baza była spójna na moment zdropienia, po kilku iteracjach będziemy mieli prawidłowo odtworzone obiekty. Niestety nasza radość trwa do momentu opakowania w/w kodu w transakcję. Po pierwszym błędzie, każda kolejna próba odtworzenia, prawidłowa lub nie, kończy się komunikatem:

Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Co gorsza, ten kod nigdy się nie kończy, bo nigdy nie dojdziemy do warunku wyjścia z pętli. I niestety dochodzimy tu do poważnego ograniczenia stosowalności mechanizmu obsługi wyjątków na SQL-u w obecnie dostępnych wersjach. I właśnie z tego powodu w tym miejscu porzuciłem ten mechanizm do rozwiązania bieżącego problemu i dobrałem się do niego w zupełnie inny sposób. Wniosek: jeśli używamy try catch, nie liczmy na to, że uda nam się w tej samej transakcji cokolwiek jeszcze wykonać poza zaprezentowaniem błędu. Musimy być zawsze gotowi na zakończenie transakcji po takiej operacji.

Pierwszy raz ...

mad — Mon, 07 Jul 2008 20:52:03 GMT

...kiedyś musi być. Do tej pory nie prowadziłem jeszcze bloga i nie mam pojęcia, jak mi to będzie szło. Namówiony przez Pawła Potasińskiego skorzystałem z uprzejmości Michała Grzegorzewskiego i postanowiłem spróbować. Inaczej nigdy się nie przekonam. Będę tu pisał o tym, czym co dzień zaskakuje mnie SQL Server i inne wynalazki świata informatyki. Pierwszy tekst już niebawem.