Az OUTPUT clause

Ideje, hogy valami szakmait is írjak, mert kezd uncsi lenni a blog. Szóval van egy szép hosszú listám arról, hogy mi mindent nem mondtam el a Millenárisban tartott beszédemben, és ebből mazsolázgatok. Kezdjük is el!

Az első egy kicsi, de hasznos apróság: az OUTPUT clause vagy ahogy Serény tanár úr írná, a klóz. Mire jó? Tipikus jelenet, hogy kell update-elnem rekordokat egy OLTP adatbázisban, azaz az adat változik alattam, de szeretném tudni, hogy pontosan melyeket update-eltem meg. Erre a gyenge megoldás a

select primary_key into #temptabla from tabla
where status = 'NEW'

update tabla where primary_key in (select primary_key from #temptabla)
set status = 'ACTIVE'

drop table #temptabla

Ez egy egész jó megközelítés, két feltétel teljesülése esetén:

  1. Nulla konkurrencia lehetséges, azaz pl. nem akar egyszerre két alkalmazásszerver dolgozni az adatbáziban, ekkor ugyanis ők lelkesen dolgozzák fel a rekordokat – egyszerre. Ezt nem részletezném, hogy miért gáz.
  2. 2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.

A dolog roppant egyszerű: bele van dugva egy belső trigger gyakorlatilag, ami a következőt tudja: ha beletömjük az OUTPUT-ot az INSERT/UPDATE/DELETE utasításba, akkor lesz inserted és/vagy deleted táblánk, és lehet kiszipkázni az adatokat, akár csak kiechózni, akár betenni táblaváltozókba. Az OUTPUT helye a WHERE feltétel előtt van még, erre figyeljetek. Valahogy így:

UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key
WHERE status = 'NEW'

Valljuk be, hogy jobban néz ki. Ha el akarjuk menteni az outputot későbbi reszeléshez vagy csak nyakonvágni a teljesítményt, akkor pedig a fenti példa így néz ki:

DECLARE @tablacska table (primary_key int);
UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key INTO @tablacska
WHERE status = 'NEW';
SELECT primary_key FROM @tablacska;

Ez egyébként fokozható, például ha már így előjött a konkurrencia, mint probléma, kiválóan megoldható a sok alkalmazás egy táblát túr probléma ezzel meg egy kis locking hinteléssel:

UPDATE tabla WITH (READPAST)
SET status = 'ACTIVE'
OUTPUT inserted.*
WHERE status = 'NEW'

Így ha több szerver dolgoz fel egy queue-t, hogy szépen mondjam, nem kell nagyon foglalkozniuk a másikkal. Egy konkrét szerver odamegy, kivesz annyi rekordot, amennyit akar (mert lehet TOP-ot is mondani az update-ben), ha valaki más is éppen update-el, akkor az általa lockolt rekordokat átugorja, és keres olyat, ami nincs lockolva. Ez a READPAST ajándéka: átugorja a lockolt rekordokat, így nem akadnak fenn egymáson a párhuzamos processzek.

(disclaimer: épp nincs SQL szerverem, ezért a példákat fejből írtam, ha nem működnek, bocsi, kijavítom :)

SQL Server and forgotten sysadmin passwords

Up to SQL 2005, the sysadmin role contains the local Adminstrators group by default (and by design), under the name BUILTIN\Administrators. This bothered some folks as it wasn’t secure enough for them, so the SQL 2008 asks you during the install who should be the member of the sysadmin role, no automatic membership granted to local admins. This is a pretty well-known feature.

However, it it lesser known that if you start the SQL Server in a single user, minimal mode (sqlservr -m -c started from the directory of the program directory, you can see it at the installed service), the local admins become sysadmins, independently of their normal privileges. This can be a failsafe solution, for example in an over-hardened environment where someone accidentally lost the password of the renamed and disabled sa account, which was the only sysadmin. You can reset passwords, add new sysadmins or whatever you want.

SQL Server és elfelejtett sysadmin jelszavak

SQL 2005-ig alapból a felinstallált SQL Serveren bekerült a sysadminok közé a lokális OS adminok csoportja, BUILTIN\Administrators néven. Ez sokakat zavart, meg nem volt elég secure, úgyhogy az SQL 2008 install során megkérdezni, hogy ki legyen sa a szerveren, nem adja hozzá a lokáladminokat helyből. Ez egy elég ismert feature.

Azt azonban kevesebben tudják, hogy ha single user módban indul el a szerver (parancssorban sqlservr –m –c, az SQL Server bináris könyvtárából lehet indítani, ezt a service-nél nézhetjük meg), akkor a lokáladminok mégiscsak sa-k lesznek, függetlenül a „normális” jogosultságuktól. Ez kiváló failsafe megoldás lehet, például egy túlszigorított környezetben, ahol az átnevezett és letiltott sa-nak elveszítették a jelszavát, és senki más nem volt sysadmin. Ilyenkor nekiállhatunk kiütni a jelszavakat, vagy hozzáadhatunk bárkit új sysadminnak. Vagy akármi más.

Deprecation warningok

(mostanság nem volt időm írni, mivel dolgozom :), ha nem a munkahelyemen, akkor pedig egy menedzsment adatbázist reszelek, ami reményeim szerint tök kényelmessé teszi a DBA életét)

Az SQL 2005 profilerben megjelent egy érdekes eseménycsalád, a Deprecation, azaz elavulás. Ez mutatja meg, hogy milyen dolgokat használunk az SQL Serverben azokból, amik meg lesznek szüntetve. Két esemény van benne: a Deprecation Announcement, ami azt mondja, hogy vigyázz, mert ezt rátettük a halállistára, és a Deprecation Final Support, ami azt mondja, hogy ez a lista tetején van, és a következő major release-ben már kiveszik. A következő major release SQL 2005-nél a 2008, 2008-nál pedig a 2008 R2.

Könnyű kipróbálni: indítsunk el egy profilert a Deprecation eseményekre (ha bepipáljuk a show all events boxot, akkor biztos meglesz), és nyissunk egy query window-t a profilerezett SQL szerveren! (Amennyiben a szervert használjuk másra is, mint játékra, akkor szűrjük le a profilert a query window spidjére, amit a status baron megtalálunk a felhasználónevünk után zárójelben.) Abba pedig írjuk bele a következőt:

SELECT name FROM msdb.dbo.sysjobs (NOLOCK)

Majd nézzük meg a profilert!

Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.

Tehát működik. A haszna nyilvánvaló: migráció előtt látja az ember, hogy mivel lehet probléma, de én egy kicsit továbbmegyek ennél: éppen most készülök bedobni azt, hogy nem veszünk át olyan adatbázist vagy módosítást, ahol deprecation warning van. Javítsák ki most, ne legyen még egy csontváz a szekrényben, az már így is tele van…

Szerintem érdemes időnként futtatni egy kicsit, és megnézni, hogy miket kap el. Nálam a master, msdb és AdventureWorks adatbázisokat :) Szóval érdemes szűrni felhasználói adatbázisokra (databaseid > 4) éles üzemben, a Microsoft meg majd rendet tesz a rendszeradatbázisokban.

SQL Server Standard Edition és Lock Pages in memory 64 biten

Ha az ember egy erősebb vason SQL Server Standard Editiont futtat, akkor találkozhat a kedves hibaüzenettel az errorlogban:
A significant part of sql server process memory has been paged out. This may result in performance degradation
Ez kellemetlen lehet, a oprendszer kitalálja, hogy neki másra (is) kell memória, és kiülteti az SQL-t a kispadra, aminek a teljesítménye tényleg leromolhat ilyenkor, nem viccel az errorlog.

Enterprise Edition esetén ez nem probléma, mivel az rendelkezik a mágikus “Lock pages in memory” képességgel (feltéve, hogy a futtató user megkapta a szükséges OS-szintű jogot), de a standardon bizony sokat szívtak ezzel emberek, beleértve engemet is. Éppen ezért örülök nagyon annak, hogy végre kivették ezt a bosszantó korlátozást, pontosabban kiveszik hamarosan. Még pontosabban egy trace flag segítségével be lehet majd kapcsolni azt, hogy lehessen lockolni a memóriába a lapokat. A várható szállítási idők:

  • SQL 2008 SP1 CU 2 – 2009 május
  • SQL 2005 SP3 CU 4 – 2009 június

Én már felírtam a naptáramba :) A PSS blogon az eredeti post itt olvasható.