Rebuild vs Reorganize index

Üdv!


Kaptam pár kérést mostanában, hogy szükség lenne egy átlátható összegzésre az adatbázis indexeiről, és azok fragmentálásáról de a lehető legegyszerűbb scripttel...

Előre bocsátom, hogy most nem megyek bele a Rebuild kontra Reorganize témába mélyen, most csak a tűzoltó jellegű munkához szorosan kapcsolódó script és a rebulid/reorg használata kerül szóba. A többi majd legközelebb.


Nos a rossz hírem az, hogy DMV-ket és a rendszertáblákat össze kell kötni ahhoz, hogy ezt a célt elérjük, de azért próbálok egy lényegre törőt összerakni.

SELECT object_name(IPS.object_id) AS [Táblanév], 
   SI.name AS [Indexnév], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent as [Százalékban kifejezett menny.], 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'IDE_JÖN_AZ_ADATB_NEVE'), NULL, 
NULL,NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND 

   IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY SI.name

Ha az indexek 30% felett vannak töredezve, akkor érdemes a REBUILD -el újraépíteni az 
indexfát.Ha alatta van, akkor pedig jöhet a REORGANIZE.

Használatuk elég egyszerű, annyit kell mindössze tenni, hogy :

ALTER INDEX index_nevem ON sémanév.táblanév REBUILD

vagy

ALTER INDEX index_nevem ON sémanév.táblanév REORGANIZE
   

Érdemes a Rebuildnél egyből megadni a Fillfactor értékét, hogy a Pagek kihasználtsága egyből be is legyen állítva. (Mert az alap beállítás nem szerencsés, de erről majd máskor)

A Fillfactor beállítása pedig így nézne ki: REBUILD WITH (FILLFACTOR = 85)

Ekkor 85% -os kihasználtságot "engedélyezünk" a Pageken, ami azért lehet célszerű, mert így marad egy kevés hely egy-egy pici Updatenek is :)

De ez már más téma...

Tűzfalas kollégák vs. Sql kollégák

Sziasztok!



El szoktam mesélni Sql Admin tanfolyamon a kedvenc sztorimat, és most úgy döntöttem ide is leírom, legyen meg.

Van egy nagy cégünk, aminek neve Bőség és Jólét Bt.
Informatikai területen szolgáltatunk. Vannak tűzfalas kollégáink, akik őrzik a rendet, és megóvnak minket a sok syn flood-tól ami érkezik Kínából, és vannak az Sql admin kollegák, akiknek a munkaköre kimerül abban, hogy az óránkénti 10.000 dollárt termelő adatbázis ne hasaljon már el, legyen mindig elérhető 0-24, elég gyorsan lehessen belőle selectálgatni. Ja és ebből az adatbázisból van vagy 200 db...

Szóval semmi stressz...

Meg is vagyunk, mindenki végzi a dolgát. Aztán egyszer csak a cég elnyer egy pályázati pénzt, amiből vagány Blade szervereket és néhány SQL 2012 Enterprise Editiont vásárolunk.
Miután kész van az architekt munkafolyamat, jöhet egy adatbázis migrálás az új szerverre.

Megtörténik, majd érkeznek a telefonok, hogy itt sem megy a programunk, meg a másik megyében sem megy a programunk... Mindenki nézi, hogy vajon mi történhetett, minden beállítás jónak tűnik...

Aztán egyszer csak az architektes kollegának eszébe jut 1 (azaz egy) dolog,amiben különbséget fedezett fel,mégpedig hogy valamilyen okból kifolyólag  nem Default Instance-ot telepítettünk fel, hanem Named Instance-ot. Erre rögtön mondja is az egy DBA kolléga, hogy nincs azzal gond, hiszen a programok config fileja (ami az sql szerverhez való csatlakozás paramétereit tartalmazza) frissítve lett mindenhol. Ráadásul odafigyeltünk arra is, hogy ugyanmár fusson az a Browser Service, sőt, még a 1434-es portot ki is nyittattuk a tűzfalas srácokkal...

Akkor mégis hogy lehet az, hogy csak azokkal az adatbázisokkal van gond, amik az "új vason vannak", és a régi szerver Default Példányáról pedig minden tökéletesen megy?


Hát úgy lehet, hogy a tűzfalas kollégák azt csinálják, amit kell, engedélyeznek mindent, amit kérsz és minden más megy a levesbe (vagyis Default Drop)

És mi mit kértünk tőlük? Hogy a TCP 1433 a Database Engine miatt és a TCP 1434 a Browser Service miatt legyen nyitva, hogy a kliensek elérjék a nevesített példányt.

Mert a Browser Service TCP 1434-en küldi az infót a kliensnek hogy  a keresett sql szerver melyik porton hallgatózik...

Igen ám, de magát a Kliens kérését, (hogy küldje már el a Named Instance portját), hol várja a Browser Service?

UDP 1434

És mi mit kértünk a tűzfalas kollégáktól? TCP 1434.

Ezt kellett volna tudnia a Firewall csapatnak? Hát nem, miért kellene? Az Sql-es sem kell hogy tudja, hogy vajon az SSL az ISO/OSI Layer 3 és Layer 4 "között" dolgozik -e vagy sem...

Szóval az én szerény véleményem az, hogy egy DBA tudja azt, hogy igenis kell néha UDP forgalmat engedni 1434 porton az sql szerverek felé.

A Service Principal Name (SPN) és a Windows Authentication kapcsolata

Üdv újra Mindenkinek!

Eltelt egy kis idő az utolsó bejegyzésem óta... Az elmúlt pár hónapban jó sok élménnyel lettem gazdagabb, sok DBA-val volt szerencsém találkozni és tapasztalatot cserélni. Az így összegyűjtött puskaporomat szeretném eldurrogtatni, több szálon (le)vezetve azt. Fogok kezdeni egy külön securitys szálat, és az egyéb/szokásos szálon is haladok majd. Így remélhetőleg átláthatóbbak lesznek a bejegyzés címei is.

Kezdem is rögtön azzal, hogy hívtak egy Sql szerverhez, ahol a helyzet a következő volt:

Mixed authentikációs mód engedélyezve, vagyis használva van (lenne) mind a Windows login, mind az Sql típusú login is. Az esetek túlnyomó többségében Sql acc. van használva, és csak ritkán a win. A hozzáférés windows login esetén mindig local gépen történt. (A helyzet így hozta.)


A probléma akkor ütötte fel a fejét, amikor a DBA kolléga egy távoli eléréssel szerette volna megoldani az aznapi dolgát, és nem tudott bejelentkezni. (Itt a távoli elérés nem RDP kapcsolatot, hanem SSMS ---> Connect ---> távoli_szerver_neve próbálkozást jelentett)


A hibakeresés során a következők voltak átgondolva (A bagatell dolgokat előre írom)

- Biztos jó loginnal akarok bejelentkezni? (Ugyanabban a domainben ugyanazzal a loginnal bejelentkezve a munkaállomsára ezt szinte lehetetlen elrontani SSMS Win Auth. esetén)

- Nem lenne jó a jelszavam ? (De hiszen be se lett írva az SSMS-be, a munkaállomáson meg bent vagyok)

- Nem lenne benne a Windows loginom a Security/Logins ágban? 
- Vagy ha benne van a Logins ágban, akkor esetleg nincs Connect jogom?  (De csak távoli eléréssel nem megy, ha helyben próbálom, simán bejelentkezik)

- Hálózati probléma, ami kihat a távelérésre, de a local kísérletet engedi? (De az sql loginok gyönyörűen működnek, az összes.)


Feltételezésem szerint kb. ekkor kerültem én a képbe.

Nem volt semmi "bonyolítás" kategóriába tartozó körülmény pl. más tartományból érkező authentikációs kérések, megbízhatósági kérdések, szerver tanúsítvány megetetése a kliensek felé, stb. Így rögtön lecsaptam arra a kérdésre, hogy vajon mi lehet a különbség két connection között, ami ugyanabból a domainből jön, ugyanabból az alhálózatból, ugyanazzal az AD hitelesített Win loginnal... És malacom volt, mert rögtön elsőre meglett a tuti. (Még engem is meglepett hogy milyen gyorsan :-)    ) 

Local gépről bejelentkezés win loginnal, majd megnéztem a connection típusát, méghozzá így:

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID

Az eredmény az lett, hogy: Net_Transport = Shared memory és auth_scheme=NTLM

Ezért az NTLM-ért szurkoltam, hogy lássam ! Ugyanis megvan a kiváltó oka a hibának.

És itt jön a képbe a Service Principal Name (SPN), ugyanis ha ez az SPN nincs sikeresen regisztrálva a tartományunkba, ezáltal a Key Distribution Center nem tud erről, akkor a Kerberos típusú hitelesítések nem fognak működni! Hogy még tovább árnyaljam: Ráadásul a Kerberos authentikáció előnyt élvez az NTLM-el szemben (mondjuk nagyon helyesen) ezért esélytelen volt, hogy a távoli elérés NTLM-el menjen végig.

Tehát ha helyben jók vagyunk, távolról pedig nem (és minden hálózati, routing ill. tűzfal probléma forrást kiszűrtünk) akkor nagy eséllyel "csak" annyi a gond, hogy a Kerberos megakad ott, ahol még az NTLM  még elmegy, ergo SPN regisztrálási problémánk van.

Ha idáig eljutottunk, akkor már a nehezén túl vagyunk, ugyanis ennek a beregisztrálása kézzel elég hamar megvan. ( Azért legyen jogunk regisztrálni a Az Active Directory Domain Services-be)

pl.:   setspn -A MSSQLvc/eles_sql_szerverem.cegnevem.hu:1433 loginnév

Minimális magyarázat :  Service név/FQDN:port az_a_login_neve_akivel_registrálom

Ez a Default Instance esetén jó lesz, de ha nevesített példány fut, akkor a port helyére az instance neve kerüljön!  (Nevesített példány esetén nem kell túl aggódni a port témakörét ugye, mert akkor minek találták volna ki a Browser Servicet :)    )


Megjegyzés: Bár az én esetemben nem volt beregisztrált SPN az Sql servicenek, attól még simán előfordulhat az, hogy nálad már lesz egy, de az neked nem lesz jó. Miért is?

Miért ne regisztráljunk be mellé egy működő SPN-t, és hagyjuk a régit úgy? Azért, mert a Kerberos alapvetően támogatja a "mutual" authentikálást, és a Kerberos v5 protocolt használó kliens nem fogja tudni megugrani azt, hogy duplikálva látja ugyanannak az Sql Servicenek az SPN-jét.(Na jó, van egy kivétel, ha nem beépített loginnal futtatod a Database Enginet, hanem Domain Accounttal, és annak meg van adva a jelszava is a Server Managerben ugye.)

A tiszta megoldás, ha egy db reg van, ezért ha törölni akarsz akkor a -A helyett használd nyugodtan a -D kapcsolót a setspn parancsnál.


Kettő darab gyors kiegészítés, mielőtt egy Windows Server Expert megjegyezné :) 

1.: Amennyiben az SPN regisztrálás nem sikerül, akkor egy ilyen bejelentkezési kísérletnél igazából a Windows security layer fog hisztizni, hogy nem tudta kideríteni az account + SPN párost.

2.: Nem mentünk bele abba, hogy vajon mehetne-e távolról az NTML a Kerberos helyett. Igazából fel sem merült bennük ez a fajta megoldás, engem meg nem is izgatott, mivel nem lett volna teljes megoldás. Az SPN-t nem gyógyítja meg.


Konklúzió:  Legyünk jóban a windows szerver admin kollégánkkal, mert egy hibás SPN regisztrációs kísérlet bekerül Warning formájában a Windows Server Application logjába... ;)



Bye.

Amikor a halott DB-nk még utoljára belénk rúg

Üdv!



Elmesélek egy sztorit, ami egy Raid  tömb megnyikkanásával kezdődött... Ugye milyen jól indul? :)

A Rebuild szerencsére sikeres volt, bár 26 óra busy állapot után már azon gondolkodtam, hogy az ölembe veszem a vasat, és újraírom én magam a szektorokat/clustereket tollal, még az is gyorsabb lesz!

Van egy sql szerver, ami ezen tárolt néhány adatbázist, és hát nem örült, amikor rájött, hogy kihúzták alóla a szőnyeget... Amikor a partíció újra elérhető volt, gyorsan vissza is csattintottam mentésből az adatbázisokat, viszont volt egy (nevezzük kiflinek) db, amit a világért sem akart elengedni a jó kis Management Studiom.

Megnyitás... hiba...
Akkor álljunk vissza mentésből.... hiba...
Akkor ne kínlódjunk, droppoljuk kiflikénket, és utána egy restore!

Erre meg kiírja a lelkem, hogy :

Cannot drop the table 'kifli_table', because it does not exist or you do not have permission.


1.: Odaszaladok a ..\databases\kifli\ könyvtárba és látom hogy megvannak a fileok... Ennyir arról hogy does not exist...

2.: Dehát én vagyok az essa! Permission issuem csak nincs...


Nézem a management studiot mégegyszer (kínomban már ráfrissítek) és még mindig ott van a kifli adatbázis.
Akkor meg miért mondja hogy nincs ott? (Adatfájlokról nem is beszélve)



Na ekkor adtam ki azt a queryt, hogy :

select * from sys.databases


És wao! Tényleg nem szerepel a listámban a kifli!

Megoldás:

1.: Droppolás helyett jön az adat - és logfile átmozgatás másik helyre (vagy akár törlés, mert úgyis jön a restore)

2.: Database Engine Service restart, és már el is tűnt a Databases fülről a kifli!

Jöhet az utolsó restore arra a napra és készen is vagyok.

Amikor nem jön a csoda

Üdvözletem mindenkinek!


Nemrég egy tanfolyamon kérték, hogy nézzünk már rá egy példát arra, amikor az index használata nem feltétlenül fogja csoda szerűen meggyorsítani a folyamatunkat úgy, ahogy azt elvárnánk tőle. Bár akkor is játszottunk a teszt környezetben, de most is bemásolok egy friss adatot egy mai szituból.


Adott egy tábla, benne kb 12-13 millió rekord.

Létre lett hozva egy non-clustered index egy string típusú mezőre. Legyen mondjuk a neve "M"
(Van clustered index is a táblán, de az most nem használható fel). Erre az indexre azért van szükség, mert sokszor lesz ún. "nagy szelektivitású" lekérdezés ezen mező segítségével.

Az index létrehozása kb 13 percig tartott.

Ezután nem sokkal jött az isteni sugallat, hogy van jónéhány olyan rekord, aminek az értéke ezen mezőn nem az igazi, ezért azt mad jól megápdételem.

update táblám SET M=NULL where datalength(M) > 20

Aztán csak vártam és vártam és vártam... :) Majd megkaptam azt hogy 3.558.279 row(s) affected.

Ja és ezt természetesen 15 és fél perc után.

Tehát elmondhatjuk, hogy az indextől nem várhatunk mindig csodát...

Miért is volt ez ennyire lassú?

Olyan "szerencsés" helyzetben vagyok, hogy több probléma is volt ezzel a lekérdezéssel:

1.: Az indexbe mi került bele? Hát maga a string tartalom... Én meg mit használok a feltételben? Egy int típusú számot, ami mellesleg a kulcs hossza, de hát kit érdekel, mivel azt szépen ki kell számolnia minden egyes esetben a datalength függvénnyel.

2.: Akárhogy is csűrjük-csavarjuk, ha 12 millió rekordból 3.5 milliót kezelned kell, az már nem annyi időbe kerül, mintha csak pár 10 ezret kezelnél...

Mi lehetett volna erre egy megoldás, így utólag belegondolva? :)

Talán lehetne az, hogy csinálni kell egy számított oszlopot, ami tartalmazza a hosszakat, na azzal lehetne szép gyorsulást elérni! Főleg ha dobunk rá egy indexet ;)


Kreatívabb ötlet a számított oszlopnál? Valaki?

(Természetesen ha az adattartalmat kellett volna vizsgálni, akkor jobban muzsikált volna az index.)


Bye.

Rekord "karbantartás"

Sziasztok!


Nemrég esett meg velem, hogy egy syslog file 1 napnyi tartalmát importálni kellett MsSqlbe. Létre is jött egy szerény 5.000.000 darab rekordot tartalmzó import nevű tábla, ami ugye tartalmazott kőkemény 1 db oszlopot, ami egy valami-string típusú volt.

Aki még nem játszott sysloggal: Egy hatalmas,hosszú stringben tárolja el az összes adatot, amit megkapott valamilyen szolgáltatástól (pl Ips) Tehát ugyanabban a stringben fogod látni a dátumot, időt, forrás ip, cél ip, stbstb adatokat. Ugye milyen elegáns?

Szóval, gyorsan kiegészítettem egy id mezővel az import táblát, hogy mégis legyen egy clusterelt index is a családban. (Egyszer úgy kipróbálnám milyen jó játék lehet egy sok gigás adatbázist HEAP struktúrával üzemelteteni :)   )

Ezután futtatam egy ideig a scriptem, amit string-cincáló tárolt eljárásnak neveztem csak el.
(Egy ideig = Egy tisztességes adag amerikai kávét simán elkortyolgattam, mire végzett, dehát érthető is)


Ekkor már megvolt a cél táblám, ami tartalmazott mindenféle ID-t, forrás és cél ip címeket, dátumot, és más egyéb okosságokat.

Már kezdett megfogalmazódni bennem az "ez is megvan, még sincs este" szlogen amit egy cimborámtól szoktam volt kölcsönvenni, amikor megkopoktatták a vállamat, hogy ugyanmár, miért van a rekordok majd' felénél többszöröződés? Mint kiderült, a syslog is így kapta már meg az adatokat, noha ez engem nem vígasztalt, mivel a táblám így nem a valóságot tartalmazza...

Tehát most több millió rekordot nyálazzak át, hogy miben van egy, és miből több...

Eddig a prológus.


Mivel az első google találatok szvsz agyonbonyolított subkveris megoldásokkal akartak segíteni, inkább erőt vettem magamon, és nekiálltam gondolkodni.

Hogyan kellene kitörölni?

1.: Számoljuk meg hogy az X db mezők alapján hány egyforma rekord van.
2.: Ezt irassuk ki a táblával
3.: Ha ez a mennyiség több, mint 1, akkor törlés

Ahhoz hogy ez átlátható legyen, ja és nem mellesleg hatékony Execution Plannel működjön a CTE mellett voksoltam.

Íme:

with cte_tobb_azonos_rekord_torlese
as
(
select row_number() over(partition by source_ip,dest_ip,datum order by source_ip) as sorszam,
source_ip,dest_ip,datum
from adatbázis.dbo.táblám
)
delete from cte_tobb_azonos_rekord_torlese
where sorszam>1



Sziasztok!
Jó munkát! :)

PIVOT / UNPIVOT / Tessék ? :)

Sziasztok!


Ha lenne egy felmérés arról, hogy mik azok a mondatok, amiket a legpontosabban mondanának el ugyanúgy az emberek,akkor szerintem a PIVOT fogalma jó eséllyel pályázhatna egy dobogós helyezésre!

Mi a Pivot? "Hááát, megfordítja 90 fokkal az adatokat, és az oszlopokból sorok lesznek, a sorokból meg oszlopok."

Nice.

-  És hogy működik a gyakorlatban?
-  És ha Pivotolok egy táblát, majd azt Unpivotolom (nekem a visszapivotolás sokkal jobban tetszene) akkor az eredeti értékeket kapom vissza vajon, vagy nem?
-  És ...


Nézzük meg:


Készítünk egy játszós táblát (sql2012 szintaktika), ami mondjuk a Snooker játékosokat fogja tárolni, és azt, hogy ezek a profik melyik pontszerző tornát nyerték meg és mennyiszer.

Azok kedvéért elmondom, akik kevésbé szeretik a snookert, hogy minden évben minden tornát megrendeznek, és világbajnokság is minden évben van.

CREATE TABLE Snooker(Nev VARCHAR(35), Torna VARCHAR(40), Menny INT)


INSERT Snooker(Nev,Torna,Menny) VALUES('Sullivan','VB',2),
VALUES('Sullivan','China Open',2),
VALUES('Sullivan','Masters',4),
VALUES('Sullivan','British Open',1), 
VALUES('Trump','Masters',1),
VALUES('Trump','British Open',3),
VALUES('Sullivan','VB',4)


Nézzünk egy alap lekérdezést :

Select * from Snooker

Nev                   Torna             Menny
-------------------------------------------------
Sullivan               VB                 2  
Sullivan               China Open         2
Sullivan               Masters            4
Sullivan               British Open       1
Trump                  Masters            1
Trump                  British Open       3
Sullivan               VB                 4





Nézzünk egy Pivot lekérdezést a Tornákra vonatkozóan:


SELECT Torna,Sullivan,Trump
FROM (
SELECT Nev, Torna, Menny FROM Snooker) abc
PIVOT (SUM(Menny) FOR Nev IN (Sullivan, Trump)) AS pvt
ORDER BY Torna


Torna      Sullivan     Trump 
------------------------------------------------------------------
British Open          1                     3
China Open           2                     NULL
Masters                 4                     1
VB                        6                     NULL


És ezt már lehet is kiexportálni excelbe! :)



Példa az Unpivotra:

SELECT Torna,Sullivan,Trump
FROM (
SELECT Nev, Torna, Menny FROM Snooker) abc
PIVOT (SUM(Menny) FOR Nev IN (Sullivan, Trump)) AS pvt
UNPIVOT (Menny FOR Nev IN (Sullivan, Trump))  As unpvt


Név                    Torna           Mennyiség
---------------------------------------------------

Sullivan               VB                 6  
Sullivan               China Open         2
Sullivan               Masters            4
Sullivan               British Open       1
Trump                  Masters            1
Trump                  British Open       3



Konklúzió: Az utolsó példában is lehetett látni, hogy aggregált függvények miatt simán előfordulhat az, hogy a Pivot utána Unpivot nem állítja elő nekünk az eredeti adatokat! 



Hajrá Pivot! :)