december 2013 havi bejegyzések

SQL képzés ingyen (?)

Szeretnék a figyelmetekbe ajánlani két ingyenes tanulási lehetőséget.

Az egyik a Hungarian User Group of Microsoft SQL Server. Többnyire magyar nyelvűek az előadások és jó hangulatban telnek. A helyszín változó, de gyakran adnak neves cégek helyszínt az előadásoknak. Nagyszerű hely a kapcsolatok építésére, ápolására és nem utolsó sorban szokott ingyen kaja is lenni. Nekem nagyon bejött 🙂

A másik az SQLSaturday, ami 2014 március 01-el indul. Erről még nem tudok nyilatkozni, de a témát és az előadókat elnézve érdemes ott lenni. Részletes program itt látható.

Reklámok

Log gyűjtés SQL-be, logparser-el – DB optimalizaltan

Ahogy ígértem, itt a folytatása a log gyűjtés logparser-rel sql-be témának, ahol az adatbázist fogjuk optimalizálni. Az optimalizálás folyamán végig kell gondolni a folyamatot. Nálam ez így ment:

–          Adatok gyűjtése és betöltése:

  • Kell e párhuzamosítani?

–          Adatbázis, adatok:

  • Kell e módosítani az adatszerkezeten?
  • Mennyi ideig maradjanak meg az adatok?
  • Kell e archiválni az adatokat?
  • Mekkora lesz az adatbázis?

–          Adatok kinyerése:

  • Mennyire lesz hatékony?
  • Hova tegyek indexeket?

A fenti kérdéseket a Mire akarom használni? kérdés fényében vizsgáltam. Nálam ez fogalmazódott meg:

–          Számomra akkor érdekesek ezek az adatok, ha baj van valamelyik szerveren. Ilyenkor ránézek a monitorozó felületre és látom, ha értékes információ van bejegyezve a naplóba.

–          Legyen hatékony az adatok kinyerése, hiszen nekem azonnal van szükségem rájuk. Később már nem vagy csak kevésbé lesznek érdekesek.

–          Férjen el kis helyen (10 GB), mert SQL Server 2012 Express-t akarok használni.

A fentiek alapján megszületett a következő táblaszerkezet:


CREATE TABLE [dbo].[tbl_windows_logs] (
    [ServerName] [varchar](255) NOT NULL,
    [ComputerName] [varchar](255) NULL,
    [EventLog] [varchar](255) NOT NULL,
    [RecordNumber] [int] NOT NULL,
    [TimeGenerated] [datetime] NOT NULL,
    [EventID] [int] NOT NULL,
    [EventType] [int] NOT NULL,
    [EventTypeName] [varchar](255) NULL,
    [EventCategory] [int] NULL,
    [EventCategoryName] [varchar](255) NULL,
    [SourceName] [varchar](255) NULL,
    [Strings] [varchar](255) NULL,
    [Message] [varchar](255) NULL
)

Ha akarunk még spórolni a hellyel, a varchar-ok lehetnek rövidebbek, max csonkolódik az adat. Ráadásul a varchar-t felfele lehet bővíteni, de visszafele már táblaújraépítéssel jár.

A tábla megvan, kellene valamiféle index rá. Erre ezt használom:


CREATE CLUSTERED INDEX [CX_tbl_windows_logs] ON [dbo].[tbl_windows_logs] (
    [TimeGenerated] ASC,
    [ServerName] ASC,
    [EventLog] ASC,
    [EventID] ASC,
    [EventType] ASC,
    [RecordNumber] ASC
) WITH (PAD_INDEX = ON, FILLFACTOR = 80)
GO

Egy darab nonunique clustered index.

–          Mivel 1 index van, így gyors a betöltés.

–          Mivel clustered, így kis plusz helyet foglal.

–          Mivel nonunique, így nem lesz kulcsütközés, pl.: óraállításkor

–           Az oszlopsorrend pedig pont olyan, ahogy lekérdezni fogom, így hatékony az adatelérés.

Amire szükségem van: Mindig az X perccel ezelőtti adatok vagy az összes szerverre vagy csak bizonyos szerverekre. Ehhez google-ztam egy függvényt, ami megvalósítja a split-et:


CREATE FUNCTION dbo.udf_split (
    @RowData varchar(2000),
    @SplitOn varchar(5)
)
RETURNS @RtnValue table (
    Id int identity(1,1),
    Data varchar(100)
)
AS
BEGIN
    Declare @Cnt int
    Set @Cnt = 1
    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select
          Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
          Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))
    Return
END

És készítettem egy eljárást néhány bemeneti paraméterrel:


create procedure [dbo].[usp_get_windows_log]
    @server_names varchar(1000) = null,
    @last_x_minutes int = 5,
    @message_text varchar(255) = ''
as
    set nocount on;
    set transaction isolation level read uncommitted;
    set xact_abort on;

    declare
      @from_date datetime = dateadd(mi, -@last_x_minutes, getdate())
     ,@to_date datetime = getdate()

    if @server_names > ''
    begin
      select
        l.ServerName as '[Server Name]',
        format(l.TimeGenerated, 'G', 'hu') as '[Time Generated]',
        l.EventLog as '[Event Log]',
        l.EventTypeName as '[Event Type Name]',
        l.SourceName as '[Source Name]',
        l.EventID as '[EventID]',
        l.[Message] as '[Message]'
      from dbo.tbl_windows_logs l
      inner join dbo.udf_split(@server_names, '|') s
         on l.ServerName = s.data
      where l.TimeGenerated between @from_date and @to_date
         and [Message] like '%' + @message_text + '%'
      order by TimeGenerated desc, ServerName desc
    end
    else
    begin
      select
        l.ServerName as '[Server Name]',
        format(l.TimeGenerated, 'G', 'hu') as '[Time Generated]',
        l.EventLog as '[Event Log]',
        l.EventTypeName as '[Event Type Name]',
        l.SourceName as '[Source Name]',
        l.EventID as '[EventID]',
        l.[Message] as '[Message]'
      from dbo.tbl_windows_logs l
      where l.TimeGenerated between @from_date and @to_date
          and [Message] like '%' + @message_text + '%'
      order by TimeGenerated desc, ServerName DESC
end

Generáltam néhány adatot a táblába:


select
    ServerName,
    count(*) as db
from tbl_windows_logs
group by ServerName

01_data_count

Ez az adatmennyiség 51571 darab 8KB-os page-en terül el.

Futtassuk az sp-t, kérjük le az utolsó 5 percben bejegyzett adatokat:

exec [dbo].[usp_get_windows_log]
    @server_names = null,
    --@server_names = 'ZSOLT-SQL|ZSOLT-SQL-1',
    @last_x_minutes = 5,
    @message_text  = 'error'

Eredmény:

Table ‘tbl_windows_logs’. Scan count 1, logical reads 721, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 203 ms,  elapsed time = 303 ms.

02_execution_plan_all_server

És most nézzük meg, ha szerverekre is szűrünk:

exec [dbo].[usp_get_windows_log]
    --@server_names = null,
    @server_names = 'ZSOLT-SQL|ZSOLT-SQL-1',
    @last_x_minutes = 5,
    @message_text  = 'error'

Eredmény:

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘tbl_windows_logs’. Scan count 1, logical reads 721, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘#A989BE94’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 141 ms,  elapsed time = 236 ms.

03_execution_plan_filtered_server

Mindenhol Clustered index seek és ami nagyon fontos, hogy kevés page-et olvasunk fel!

Az utolsó előnye annak, hogy az idő van elől a clustered indexben a fölösleges adatok törlésénél jön elő:


delete top (1000)
from dbo.tbl_windows_logs
where TimeGenerated < dateadd(day, -1, getdate())

04_delete_data

Itt is clustered index seek-et kapunk. És mivel nincs több index a táblán, így nincs többletköltség a törlés során.

A cikk idő hiányában eddig tart. Aki kedvet kapott az ilyen fajta log gyűjtésre, az kísérletezzen bátran, lehet még javítani, tökéletesíteni a dolgot. Aztán ossza meg velünk is 🙂