Microsoft SQL Server 2005 – monitoring

sqlserver2005

  • SSMS SQL Server2005 -> Maintenance -> Activity Monitor
  • pri vytvareni dotazu se delaji zamky nad db
  • pohledy v db – sys.neco jsou staticke pohledy a sys.dm_neco jsou dynamicke pohledy (jsou v db ->views->system views)
  • zjistit fragmentaci indexu pomoci sys.dm_db_index_physical_stats, ktera je ve vsech db
  • moje vlastni dotazy si pak mohu dat do vlastniho reportu, ktery si udelam pomoci nastroje SQL Server Business Intelligence Development Studio, ktery je soucasti instalace SQL server2005 SP2
  • merice v OS Performance pro SQL pouzivat
  • monitorujeme tedy CPU, RAM a HDD (navaznosti napr. pokud je malo RAM, tak o to vic se musi zapisovat na HDD a ten muze prestat stihat)
  • SQL Profiler
    • graf. utilita, instalovat vzdy na PC a ne na srv, vzdy delat vystupy do txt a ne do table (kdyz do table, tak se strasne vytezuje ta db), da se udelat ze zachycenych logu (trace) pak replay na jinem serveru a na nem se pak podivat co se delo; taky se da s timto tracem pustit Tuning Advisor a zjistit workload
    • spustim ho pres Tools – SQL Profiler; dulezite je vybrat sloupce starttime a endtime (da se pak pouzit i vuci perf logu) – to udelam tak, ze v trace properties dam SHOW ALL COLUMNS a vyberu ty sloupce; vyberu i db, kterou chci monitorovat – to udelam v TRace properties pres Filter
    • spustim OS Performance a udelam v perf.logach counterlog na performance – ten pak mohu po otevreni trace v Profileru, dat v Profileru import perf.logu a dostanu vukon vuci dotazum – SKVELA VEC!!!
    • </ul> </li>

    Triggers

    • SQL se deli na
      • DDL – Data Definition Language – prikazy CREATE/ALTER/DROP
      • DML – Data Manipulation Language – prikazy SELECT/INSERT/UPDATE/DELETE
      • DCL – Data Control Language – prikazy GRANT/DENY/REVOKE
      • </ul>
      • vse se konfiguruje pres TSQL -bez grafickeho rozhrani
      • nalezaji se v sys.tabulce db sys.triggers
      • jsou fakticky sp, ale je spousteny systemem
      • vznikne po udalosti, ktera ho odpali (je reaktivni), ale je soucasti transakce, takze muze pak na konci pouzit napr. rollback, aby operaci vratil
      • DML trigger (startovan prikazy INSERT/UPDATE/DELETE) – vztahuje se k table/view
      • DDL trigger (startovan prikazy CREATE/ALTER/DROP) – vztahuje se k server/db – je to udelane takto – protoze, napr. kdyz udelam create login, tak tento DDL trigger je vlastne DML triggerem nad systemovou tabulkou (v tomto pripade nad db master nad syslogins), (na co se da vsechno delat je v sql books online v DDL triggers v event groups used for firing)
      • akce, na ktere je povesen trigger, generuje EventData – na ktere se mohu pak dotazovat v tele triggeru
      • pokud zakazu v triggeru i DROP, tak ovsem samotny trigger dropnout mohu – to je vyjimka
      • prikaz triggeru:
      • </ul>

        USE UserDB

        GO

        CREATE TRIGGER UserDB_Safety

        ON DATABASE

        FOR DDL_DATABASE_LEVEL_EVENTS

        AS

        DECLARE @TSQLCommand nvarchar(max)

        SELECT EVENTDATA().value(‚(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)‘)

        WHERE EVENTDATA().value(‚(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)‘) LIKE ‚%STATISTICS%‘

        IF @@rowcount = 0 ROLLBACK

        GO

        CREATE TABLE dbo.myTable (Id INT IDENTITY PRIMARY KEY, eventDetail xml NULL);

        UPDATE STATISTICS dbo.UsageLog

        –SELECT * FROM sys.triggers WHERE is_disabled = 0

        ENABLE TRIGGER UserDB_Safety

        ON DATABASE

        Event Notifications

        • vse se konfiguruje pres TSQL -bez grafickeho rozhrani
        • vlastne alternativa k SQL profileru
        • vytvori se na zaklade na nejake udalosti
        • vyzaduje sluzbu ServiceBroker
        • ServiceBroker – soucasti SOA – Service Orientated Architecture
          • aplikace obsahuje svoje casti – bloky – a mezi nimi jsou interfaces, ktere umoznuji si vymenovat data, ktere reprezentuji to reseni – jsou to vlastne zpravy, ktere si mezi sebou posilaji ty sluzby tech bloku
          • napr. bloky A a B – A posle zpravu B a B ji musi ulozit do nejake fronty na sobe (~ buffer => async system)
          • B odpovi A a A tuto odpoved ulozi do sve fronty
          • ServiceBroker resi sifrovani teto komunikace a jeste to, ze tyto fronty mohou byt na vice serverech
          • mohu pak tedy do techto front "strkat" EventData z triggeru, ale to je xml, ktere musim umet
          • takovato fronta je pouzita i pri mailovani z SQL – maily se tam hromadi a pokud se treba do 3h neodeslou tak se smazou
          • </ul>
          • postup vyuziti event notif a servicebrokeru
            • 1. vytvorim schemu
            • 2. vytvorim frontu (CREATE QUEUE)
            • 3. vytvorim sluzbu servicebrokeru (CREATE SERVICE) na frontu
            • 4. vytvorim route (CREATE ROUTE), aby sluzba fungovala mistne
            • 5. vytvorim notifikaci
            • cely skript:
            • — Prepare Database for using with Service Broker service instance
            • </ul>

              USE userDB

              GO

              –Create a queue to receive messages.

              CREATE QUEUE NotifyQueue ;

              GO

              –Create a service on the queue that references

              –the event notifications contract.

              CREATE SERVICE NotifyService

              ON QUEUE NotifyQueue

              ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

              GO

              –Create a route on the service to define the address

              –to which Service Broker sends messages for the service.

              CREATE ROUTE NotifyRoute

              WITH SERVICE_NAME = ‚NotifyService‘,

              ADDRESS = ‚LOCAL‘;

              GO

              –Create the event notification.

              CREATE EVENT NOTIFICATION myDemoEventNotification

              ON SERVER

              FOR Audit_Login, Audit_Logout, Audit_Login_Failed

              TO SERVICE ‚NotifyService‘,

              ‚current database‘ ;

              • v tuto chvili se udeje neco, vygeneruje se zprava a da se do servicebrokerove fronty, z ni to ale potrebuji cist a zapsat do nejake tabulky, takze:
              • udela se to tak, ze nad zapisovanim do fronty (coz je vlastne tabulka – mohu nad ni delat SELECT, jenze zpravy jsou zasifrovane, proto mi SELECT nestaci) se udela trigger a na zaklade neho se to presune do tabulky
              • 1. spustim servicebroker na sql serveru (na masterdb) (pozor nesmi byt session k db)
              • 2. nactu zpravy z fronty (RECEIVE TOP(1) @msg…… ) a toto zacyklim pomoci WHILE (donekonecna … 1=1), abych nacetl vsechny zpravy fronty az do vyprazdneni a teprve za obdrzenim udelam BREAK (vyskok z WHILE) az bude vse precteno
              • 3. po nacteni radky ji zapisuji do tabulky (INSERT INTO …)
              • 4. protoze INSERT muze selhat, bude vlozen do BEGIN TRY a BEGIN TRAN, pricemz COMMIT udelam a bude nasledovat odchyceni chyby pomoci BEGIN CATCH kde pouziji ROLLBACK (a mohu pouzit zobrazeni erroru RAISERROR)
              • 5. cele to ulozim jako ulozenou proceduru (sp) (CREATE PROCEDURE)
              • 6. nastavim frontu tak, aby spoustela tuto proceduru (ALTER QUEUE WITH ACTIVATION …)
              • cely skript:
              • </ul> </li> </li> </li>

              USE Master

              ALTER DATABASE UserDB SET ENABLE_BROKER

              USE UserDB

              GO

              CREATE PROCEDURE dbo.usp_ParseLog

              AS

              DECLARE @msg_body XML

              WHILE (1=1)

              BEGIN

              RECEIVE TOP(1) @msg_body = message_body

              FROM dbo.NotifyQueue

              IF @@rowcount = 0 BREAK

              BEGIN TRY

              BEGIN TRAN

              INSERT INTO dbo.UsageLog (detail) VALUES (@msg_body)

              COMMIT TRAN

              END TRY

              BEGIN CATCH

              –RAISERROR ()

              ROLLBACK

              END CATCH

              END — END WHILE

              GO

              — SELECT * FROM dbo.NotifyQueue

              — EXEC dbo.usp_ParseLog

              ALTER QUEUE dbo.NotifyQueue

              WITH ACTIVATION (

              PROCEDURE_NAME = dbo.usp_ParseLog,

              EXECUTE AS SELF) ;

              • pokud bych chtel logovat audit vseho na celem serveru, pak v options na serveru zapnu ENABLE C2 AUDIT TRACING
              </div>
Written on January 1, 2010