SQL teljesítményelemzés morzsák

Az egyik visszatérő kérdés: mit nézzek, ha baj van? Erre több megközelítés is lehetséges: lehet az Indexet, majd elmúlik a baj; esetleg olvasgatni dokumentációt vagy guglizni; vagy álláskereső portált nézni; netán a szerver teljesítményét megvizsgálni.

Ja, de hogyan? Hát, elmondom, hogy én kb. miket csinálok, amikor döglődik az SQL. Ez részben szisztematikus, részben nem, ugyanis vannak dolgok, amiket lehet párhuzamosan csinálni, vagy felcserélhetőek, vagy esetleg egyik a másikat feleslegessé teszi, vagy valami tök más is beugorhat, ha valami tök mást látok. De mondjuk, hogy az esetek 85%-ában az általam üzemeltetett szerverek 90%-a ilyenekkel kezelhető. Ezek kis ötletgombócok, nem egy script, amit le kell futtatni.

Először is: ne csak performance countert nézzünk, hanem minden mást is. Tipikusan DMV-ket és perfocuntert. Az activity monitort csak indikátornak ajánlom: ha működik, akkor nem lehet nagy baj. Valahogy igen erőforrásigényes szegényke, és amikor jön a terhelés, rendszeresen aszongya, h timeout. Én a következő útvonalon haladok:

Kezdetnek írjuk fel egy papírra, és tegyük a monitor mellé: Memória, CPU, Diszk I/O. Ez a három dolog kell a SQL-nak. Egyszerű rendszer, egyszerű lelkeknek.
Continue reading ‘SQL teljesítményelemzés morzsák’ »

sqlcmd mint SSMS light

Itt ülök az MVP summiton, és kiderült, hogy még itt is van olyan ember, aki nem tudja, hogy az sqlcmd nagyon sokoldalú. Valaki felvetette az igényt egy SSMS lightra, ahogy ő fogalmazott,

“egy Notepad, ami tud SQL lekérdezést futtatni”.

Megmutattam neki, hogy ez már létezik. Úgy hívják, hogy sqlcmd. Ez az osql utódja, a command-line interface SQL Serverhez. Ha még nem tudná valaki, CLI-mániám van, pedig Windows-on szocializálódtam. Szóval nézzük a varázslatot: Nyissunk egy parancssort (cmd.exe vagy powershell), és pötyögjünk:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Erik>sqlcmd -E -S .\mysql
1> :ed
'edit.com' is not recognized as an internal or external command,
operable program or batch file.
1> exit

C:\Users\Erik>SET SQLCMDEDITOR=notepad

C:\Users\Erik>sqlcmd -E -S .\mysql
1> :ed
1> select top 3 name, database_id, recovery_model_desc
2> from
3> sys.databases
4> GO
name
         database_id recovery_model_desc
------------------------------------------------------------------------------------------------------------------------
-------- ----------- ------------------------------------------------------------
master
                   1 SIMPLE
tempdb
                   2 SIMPLE
model
                   3 FULL

(21 rows affected)
1> :ed
1> select top 3 cast(name as varchar(30)) name, database_id, recovery_model_desc
2> from
3> sys.databases
4> GO
name                           database_id recovery_model_desc
------------------------------ ----------- ------------------------------------------------------------
master                                   1 SIMPLE
tempdb                                   2 SIMPLE
model                                    3 FULL

(3 rows affected)
1> 

Mi történt? Létrehoztam egy kapcsolatot az SQL Serverhez (igen, mysql az instancename, mert én használom), majd megpróbáltam szerkeszteni egyet az :ed paranccsal, de hibát kaptam, mivel az edit.com nem volt elérhető a gépemen. Erre kiléptem, beállítottam a SQLCMDEDITOR környezeti változóban a Notepad nevű szövegszerkesztőt, majd megint nekiugrottam. Ami sajnos így nem látszik, az az, hogy a 13. sornál kinyílt a notepad egy üres fájllal. Belegépeltem a 14-16. sort, majd bezártam a notepadot, és elmentettem a fájlt, amikor kérdezte, hogy mi legyen. Erre a fájl tartalma megjelent a sqlcmd-ben. utánaírtam, hogy GO, aztán lefutott. Mivel az adatbázisnév sysname, ami 128 karakter hosszú, ezért rondán nézett ki a kimenet, úgyhogy megint azt mondtam, hogy :ed. Erre megjelent az előzőleg végrehajtott batch a notepadban, és átírtam a megfelelő 1 sort, hogy ne legyen beláthatatlanul hosszú az output. Bezár-ment, enter, ott az eredmény.

Konklúzió: ez az egyik legnagyobb találmány.

SQL 2005-2008 upgrade, játékosan

Alig használt SQL 2005 szerverünket SQL 2008-ra cseréljük, gariban. Jó az a 2005 Enterprise, az online minden félelmetesen jól működik, de hát az ember vérszemet kap, és akar filtered indexet meg tömörítést meg policy managementet meg mindenféle rettenetet, ha már fizetett érte…

Ha upgrade-ről van szó, csakis a side-by-side upgrade-et támogatom, azaz az átköltözést egy új verziójú szerverre, szemben az in-place upgrade-del, amikor az ember lefuttatja az upgrade-et az éppen futó szerverére. Miért? Mert üzemeltető vagyok, és szeretem, ha van hova visszatérni probléma esetén. Nem viccből van az oldal tetejére írva az, hogy ROLLBACK. Az üzemeltetésben fontos szempont, hogy legalább rontani ne rontsunk a helyzeten. A fejlesztő megteheti, neki kísérleteznie KELL, ami magában hordja a bukta lehetőségét – az üzemeltető ezt nem teheti meg. És mégis kísérleteznie kell, ezért jó a rollback. Mint minden jófajta stratégiai játékban.
Continue reading ‘SQL 2005-2008 upgrade, játékosan’ »

Rendszeradatbázisok mozgatása SQL 2008-ban

Az új verzió mindig jobb, szebb, gyorsabb, kényelmesebb, stb. És tényleg: most először kellett elmozgatnom SQL 2008 szerver rendszeradatbázisait, és király volt. Aki csinált ilyet 2005 vagy 2000 alatt, az tudja, hogy milyen szívás mindenféle hülye trace flaggel indítgatni a SQL-t, aztán rossz sorrendben attacsolni a modelt meg az msdb-t. Ehhez képest az SQL 2008-ban négyféle adatbázis van:

  • Resource: Ő már nem is az adatfájloknál, hanem a programbinárisoknál lakik, hozzá se lehet nyúlni (és nem is kell).
  • master: Változatlanul, a service startup paraméterét kell átírni.
  • tempdb: Változatlanul alter database és nem kell fájlt másolni.
  • a többi: (igen, model, msdb is többi) Alter database, szerver leáll, fájlmásolás, szerver start.

Annyira egyszerű volt, hogy lescripteltem az egészet, egy SQL, egy Powershell:

ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'D:\mssql10\db_data\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'E:\mssql10\db_logs\modellog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\mssql10\db_data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\mssql10\db_logs\templog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\mssql10\db_data\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'E:\mssql10\db_logs\MSDBLog.ldf')
# atirjuk a registryben a startup parametereket
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg0" -value "-dD:\mssql10\db_data\master.mdf"
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg2" -value "-eD:\mssql10\SrvLogs\ERRORLOG"
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg1" -value "-lE:\mssql10\db_logs\mastlog.ldf"
# SQL stop - a force azert kell, h a dependens service-ek is megalljanak
Stop-Service 'MSSQL$INSTANCENAME' -Force
Start-Sleep 10
# kopi adatbazis fajlok
Move-Item 'D:\mssql10\db_data\MSSQL10.INSTANCENAME\MSSQL\DATA\*.mdf' 'D:\mssql10\db_data'
Move-Item 'D:\mssql10\db_data\MSSQL10.INSTANCENAME\MSSQL\DATA\*.ldf' 'E:\mssql10\db_logs'
#Start-Service - az agent elinditja a SQL-t magat is
Start-Service 'SQLAgent$INSTANCENAME'

És kész is vagyunk… Scriptelni jó.

Indexek, amiket a kutya sem használ

(apró technikai malőr miatt a táblázat pillanatnyilag bebújik a sidebar mögé, bocs, legjobb embereink dolgoznak a hiba elhárításán)

Az egyik nagy jósága a SQL 2005+ szervernek, hogy megmutatja az indexek ki(nem)használtságát, illetve elmondja, hogy milyen jelenleg nemlétező index mekkora hasznot hozna. Ezeket természetesen DMV-ken keresztül teszi meg velünk.

Az indexeink kihasználtságát a sys.dm_db_index_usage_stats DMV mutatja meg, egy kis minta így néz ki belőle:

SELECT top 3 * FROM sys.dm_db_index_usage_stats 
WHERE object_id > 255

Az erősen irányított válasz pedig

database_idobject_idindex_iduser_seeksuser_scansuser_lookupsuser_updateslast_user_seeklast_user_scanlast_user_lookuplast_user_updatesystem_seekssystem_scanssystem_lookupssystem_updateslast_system_seeklast_system_scanlast_system_lookuplast_system_update
570957756623345602154356NULL2009.10.18 15:46NULL2009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL
5709577566121543560215435621543562009-10-18 16:00:37.097NULL2009-10-18 16:00:37.0972009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL
570957756630002154356NULLNULLNULL2009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL

Ebből a következőket kell figyelnünk: a db-object-index id-k magukért beszélnek, abból tudjuk, hogy minek is nézzük az adatait. A user seek/scan/lookup és a hozzájuk tartozó last… oszlopok talán a legfontosabbak, ők mutatják meg a tényleges indexhasználatot. Az user_update oszlop érdekes, az index ugyanis akkor update-elődik, ha a tartalmazott adat változik. Ez az úgynevezett indexkarbantartás, ami kissé drága, ezért nem szeretjük a felesleges indexeket. Merő véletlenségből a fenti minta pont tartalmaz egy ilyen indexet, a 3-as számút (egy tábla három indexének az értékeit választottam ki). Látszik, hogy semmi másra nem használja a szerver az indexet, csak karbantartja, frissítgeti. Ez egy tipikus jó jelölt arra, hogy letörölje az ember fia egy vidám hétfő reggelen, ha elég nagy minta alapján még mindig nulla a három mező összege. Volt szerencsém találkozni olyan indexszel, amit 1.1 milliárdszor kellett update-elnie az engine-nek, és soha nem használta. Lehet azt mondani, hogy nem kér enni, de hát szemmel láthatóan ez nem igaz.

Éles szemű egyének észrevehetik, hogy azért a system_scan oszlop értéke egyáltalán nem nulla. Ez anna kköszönhető, hogy az indexstatisztikák frissítéséhez az SQL szerver scannel. Úgyhogy ezt nem kell feltétlenül figyelembe venni.

Mire jó még ez a használati statisztika? Láthatjuk, hogy user_lookup mindig csak az index_id = 1 indexeken történik, azaz a clustered indexen. Természetes, hiszen a bookmark/key lookup itt szokott történni. Lehet, hogy van rá ellenpélda, de azt most kihagynám. És most keressük meg a legnagyobb user_scan értékű indexeket és a hozzájuk tartozó clustered indexeket: ha nagyon magas a clustered indexen a user_lookup érték is és kicsi a scan, akkor találtunk egy jó ellenjelöltet clustered indexre.

És megint, csak egyetlen DMV a sokból, és milyen sok hasznos dolgot árul el… Hát nem csodálatos? (nem, a csodálatos az az, hogy három tesztgépre sikerült három különböző SQL verziót/editiont telepítenem, anélkül, hogy észrevettem volna addig, amíg el nem frakkolt a teszt…)

Visszatérve a címre:

select * from sys.dm_db_index_usage_stats 
where object_id > 255
and user_seeks + user_scans + user_lookups = 0
order by database_id, object_id

Ők azok az indexek, akiket a kutya sem használt – az SQL Server legutóbbi indulása óta. Ez a DMV ugyanis minden szerver restartkor kiürül és újrakezdi a feltöltését a szerver. Tehát ha tegnap óta fut a gép, ne használjuk nagytakarításra az outputot. Tanácsos elmenteni a kimenetet – én mindennap elmentem egy külön táblába, és ha nem indult újra előző nap óta a szerver, akkor az előző napit meeg kitörlöm. Így csak legfeljebb egy napi statisztikát veszíthetek, de megvan hónapokra visszamenőleg minden.