Extended Events

Najważniejsze założenia XEvents'ów:
- zbieranie informacji (mniej lub więcej w zależności od potrzeb) niezbędnych do monitoringu, identyfikacji lub rozwiązywania problemów wydajności na serwerze (wg MS ma to zastępować Profilera)
- wysoce konfigurowalne, lekkie obciążenie i skalowalne
- dane logów zapisywane w sesjach zrzut do pliku, ew. w ring buffer

Aby zebrać dane należy przejść w MngStudio:
Management/Extended Events/Sessions i PPM wybrać New Sessions Wizard

Kroki kreatora sesji:
1. Introducions (wstęp co to jest za narzędzie)
2. Set Session Properties (wprowadź nazwę sesji i włącz czy ma się uruchamiać przy starcie serwera)
3. Choose Template (MS ma kilka wybudowanych szablonów w zależności od tego co mierzymy)
    a) Locks and Blocks
    b) Profiler Equivalents
    c) Query Executions
    d) System Monitoring
    e) User Templates
4) Select Events To Capture (tu wybieramy zdarzenia, które chcemy gromadzić, w związku z tym, że jest ich multum, w tym przykładzie biorę tylko zdarzenie login z kategorii session, kanał Analytic z paczki sqlserver. Zdarzenie to ma kilka wierszy danych stanowiących zbiór tego zdarzenia, jak na screenie)

XEvents - wybór zdarzeń jakie chcemy logować

5) Capture Global Fields (wybieramy dodatkowe pola (akcje) jakie chcemy gromadzić, będą to systemowe akcje, które możemy wrzucić na tzw. dokładkę, aby mieć powiązanie z naszymi wcześniejszymi danymi np. client_pid etc.)
6) Set Session Event Filters (ciekawa opcja, gdzie możemy zawęzić wybór danych, dot. danej bazy, danego użytkownika, tu w zależności od kolekcjonowania danych możemy robić na nich dowolne filtry)
7) Specify Ssession Data Storage (wrzut do loga i jak chcemy gromadzić dane, wszystko czy ostatnie logi)
8) Summary (podsumowanie wizarda)
9) Create Event Session (potwierdzenie wyboru i uruchomienie)

Czytanie logów

SELECT event_data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(N'Y:\XEventsLogs\aaaa*.xel', NULL, NULL, NULL);

ew. klikając
Pod stworzoną sesją XEvents wybierając PPM View Target Data...

SQL Server na Linux 1GB RAM (obraz Docker)

Czy SQL Server na Linuxie na maszynie 1GB RAM ma sens? Jest wiele tanich VPS z 1GB i istnieje pokusa zabawy.

Próbując zainstalować jednak otrzymamy komunikat:
This program requires a machine with at least 2000 megabytes of memory.

Przetestowałem to rozwiązanie na Linuxie (RHEL, Ubuntu), Zapakowałem instancję w Docker ze specjalnym obrazem justin2004/mssql_server_tiny (to obraz SQL Server 2017 i jest skonfigurowany aby omijać konfigurację sprzętową, co pozwala go instalować na maszynie z mniej niż 2GB ram).

Poniżej prosta konfiguracja i kilka wyjaśnień:
-e 'MSSQL_PID=Express' (biorę Express mniejszy narzut na pamięć, a mając VPS np. publiczy nie łamiesz licencji MS)
-p 4444:1433 (mapuję standardowy port sqlserver 1433 na własny 4444)
-v /sqldata:/var/opt/mssql (mapuję domyślny katalog mssql w /var/opt/mssql na własny /sqldata i tam ustawiam domyślne katalogi na data,log,backup,dump)
--memory-swap -1 (jak zabraknie fizycznego ram, zrzucam pamięć na dysk do pełnego jego wykorzystania)
--name ms1 (to moja nazwa instancji w Docker)
--hostname ms1 (to moja nazwa instancji w SQLServer)
--d justin2004/mssql_server_tiny

Na terminalu w Linux jeśli masz już Docker wpisz polecenie na uruchomienie obrazu:

sudo docker run
-e 'ACCEPT_EULA=Y'
-e 'SA_PASSWORD=WpiszTwojeMocneHaslo'
-e 'MSSQL_PID=Express'
-e 'MSSQL_COLLATION=Polish_CI_AS'
-p 4444:1433
-v /sqldata:/var/opt/mssql
-e 'TZ=Europe/Warsaw'
-e 'MSSQL_DATA_DIR=/sqldata/data'
-e 'MSSQL_LOG_DIR=/sqldata/log'
-e 'MSSQL_BACKUP_DIR=/sqldata/backup'
-e 'MSSQL_DUMP_DIR=/sqldata/dump'
--memory-swap -1
--name ms1
--hostname ms1

-d justin2004/mssql_server_tiny

PS: Po uruchomieniu instancja owszem działa, jednak w miarę im dłużej chodzi i kilka prostych workloadów zaraz sprawi, że będziesz otrzymywał błędy związane z pamięcią. Moim zdaniem nie warto na dłuższą metę w to się bawić i szukać VPS gdzie postawimy SQL Server z 4GB minimum do prostych zastosowań.

 

Audyt na poziomie bazy danych i serwera

Mamy dwa poziomy audytu jaki możemy przeprowadzić w SQL Server, tj.
- na samej instancji, zbieranie info z serwera
- na bazie i zbieranie logów z konkretnej bazy danych

1. Audyt na bazie danych

1.1) Polega na stworzeniu konfiguracji Audytu, a następnie Specyfikacji Audytu per baza
1.2) Tworzymy Audyt. Rozwijamy drzewko instancji, wybieramy Security/Audits/New Audit...
1.3) Wpisujemy gdzie logować zebrane dane, u mnie do pliku 20GB nadpisujący się.

Tworzenie nowego audytu

1.4) Tworzymy specyfikację audytu dla bazy.
1.5) Rozwijamy drzewo instancji, wybieramy konkretną bazę danych, dalej Security/Database Audit Specifications/New Database Audit Specification...
1.6) Wpisujemy co chcemy logować, tj.

- Audit Action Type (typ akcji), u mnie instrukcja SELECT
- Object Class (klasa obiektu), u mnie to będzie typ SCHEMA
- Object Name (nazwa obiektu), u mnie to będzie dbo ze SCHEMA
- Principal Name (główny powiązany), u mnie to będzie każdy kto leży do grupy public, a więc wszyscy

w polu Audit wybieramy nasz wcześniej stworzony obiekt Audit, i koniec. Audyt jeszcze nie działa włączamy go na poziomie obiektu Audit.

 

Specyfikacja audytu na bazę

 

 

 

 

 

 

 

 

 

2. Audyt na serwerze

Podobnie jak w pkt 1, tworzymy konfigurację Audytu, tj. gdzie będziemy logować, ja wykorzystam tu ten sam obiekt

2.1) Tworzymy specyfikację audytu dla serwera. Rozwijamy drzewo instancji i dalej Security/Audits/Server Audit Specification/New Server Audit Specification...

Wpisujemy co chcemy logować, tj.

- Audit Action Type (typ akcji), u mnie instrukcja SUCCESSFUL_LOGIN_GROUP (czyli kto się zalogował)
- Object Class (klasa obiektu), brak, tu typ akcji nie pozwala na wybranie obiektu
- Object Name (nazwa obiektu),  brak, tu typ akcji nie pozwala na wybranie obiektu
- Principal Name (główny powiązany),  brak, tu typ akcji nie pozwala na wybranie obiektu

w polu Audit wybieramy nasz wcześniej stworzony obiekt Audit, i koniec. Audyt jeszcze nie działa włączamy go na poziomie obiektu Audit. 

PS: Dokładne opisy wszystkich akcji jakie możemy logować są na MSDN

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16

w tabelach per db: Database-level audit action groups
w tabeli per serwer: Server-level audit action groups

Specyfikacja audytu na serwer

 

 

3. Czytanie logów

Mając już logowanie, możemy czytać te logi bezpośrednio funkcją systemową sys.fn_get_audit_file, ew. zapakować to w widok na bazie master i czytać już globalnie i filtrować przez kolumnę database_name

SELECT *
FROM sys.fn_get_audit_file(
    'Y:\AuditLogs\*.sqlaudit',
    DEFAULT,
    DEFAULT
);
GO

Partycjonowanie tabel

Dodanie 2 grup plików dla bazy danych. Stworzenie funkcji partycjonowania i obsługa na nowej tabeli.

use test1
alter database test1 add filegroup FG_NumberPos
alter database test1 add filegroup FG_NumberNeg
alter database test1
    add file (name = 'test1_FG_NPos', filename = 'T:\Program Files\Microsoft SQL Server\MSSQL16.EX1\MSSQL\DATA\test1_FG_NPos.ndf')
    to filegroup FG_NumberPos
alter database test1
    add file (name = 'test1_FG_NNeg', filename = 'T:\Program Files\Microsoft SQL Server\MSSQL16.EX1\MSSQL\DATA\test1_FG_NNeg.ndf')
    to filegroup FG_NumberNeg

--funkcja partycjonowania
create partition function PF_RangeByNumber(integer)
as range right for values (0)
-- schemat partycjonowania
create partition scheme PS_Numbers as partition PF_RangeByNumber to (FG_NumberNeg,FG_NumberPos)
-- tabela oparta o schemat
drop table if exists test1
create table test1 (number int) on PS_Numbers(number)

--wrzut danych
declare @i int = 1
while @i < 100000
begin
    insert into test1 (number)
    select @i as number
    set @i = @i+1
end
 
set @i = -100000              
while @i < 0
begin
    insert into test1 (number)
    select @i as number
    set @i = @i+1
end
-- podglad danych w partycjach
select
    p.partition_id,
    p.object_id,
    p.partition_number,
    p.rows,
    o.type_desc,
    o.modify_date
from
    sys.partitions as p
    join sys.objects as o
    on o.object_id = p.object_id
    where o.[name] = 'test1'

Backup z kompresją

Full Backup with compression z oznaczeniem pliku .bak i zrzuceniem statystyk wykonania co 5%

--full backup with compression
declare @db varchar(100) = 'test1'
declare @path varchar(200) = 'Y:\Backup\'
declare @tmark varchar(20) = (select Format(GetDate(), N'_yyyyMMdd_HHmm'))

declare @sql varchar(max)
set @sql = 'backup database ' + @db + ' to disk = N''' + @path + @db + @tmark + '.bak'' with compression, stats = 5;'
print @sql

select
    [database_name],
    [recovery_model],
    backup_size,
    compressed_backup_size,
    cast((backup_size/compressed_backup_size)as numeric(3,2)) as compressionRatio,
    backup_start_date,
    backup_finish_date,
    datediff(second,backup_start_date,backup_finish_date) as BackupCompleteTimeSec
from
    msdb.dbo.backupset
order by 6 desc

exec(@sql)

Output w procedurze

Jedną z metod która pozwala zwrócić wartość z procedury jest użycie parametru OUTPUT.
Procedura zwraca aktualną wartość parametru do wywołującego programu.

use [AdventureWorks]
go

create or alter procedure TakeBirthDateByEmployeeID (   
    @EmployeeID int,
    @BirthDate varchar(10) output)
as    select @BirthDate  = 
(   
    select BirthDate
    from [HumanResources].[Employee]
    where BusinessEntityID = @EmployeeID
)
    return;    
go

declare @BirthDateOut varchar(10)

exec TakeBirthDateByEmployeeID @EmployeeID = 2, @BirthDate = @BirthDateOut output

select @BirthDateOut

Wybór danych z przesunięciem OFFSET/FETCH

OFFSET pozwala w klauzuli ORDER BY na przesunięcie wyboru wierszy o wskazaną liczbę.
FETCH jest opcjonalny i wybiera wiersze od punktu przesunięcia (może zawęzić ten wynik).

-- wybór wierszy z przesunięciem, pomija pierwsze 10 wierszy
select
    a.[Name],
    a.YearOpened
from
    [Sales].[vStoreWithDemographics] as a
order by
    a.YearOpened,
    a.[Name]
offset 10 rows

-- wybór z przesunięciem od pierwszych 3 wierszy.
-- Opcjonalnie po przesunięciu wybór pierwszych/następnych (NEXT/FIRST tożsame) 6 wierszy
select
    a.[Name],
    a.YearOpened
from
    [Sales].[vStoreWithDemographics] as a
order by
    a.YearOpened, a.[Name]
offset 3 rows
fetch FIRST 6 rows only

Sql_Variant

sql_variant to typ danych, który pozwala przechowywać wartości różnego typu (do 8000 bajtów) w kolumnie lub zmiennej.

 

Możesz wprowadzić dowolny typ danych oprócz:

1. varchar(max)
2. varbinary(max)
3. nvarchar(max)
4. xml
5. text
6. ntext
7. image
8. rowversion (timestamp)
9. geography
10. hierarchyid
11. geometry
12. datetimeoffset
13. User-defined types

-- kolumna typu sql variant pozwala przechowywac roznego typu wartosci
DROP TABLE IF EXISTS #TEST
CREATE TABLE #TEST (
    colA sql_variant,
    colB INT)
    
INSERT INTO #TEST select CAST(101.1 as decimal(8,2)), 1
INSERT INTO #TEST select CAST('Test123' as varchar(10)), 2
INSERT INTO #TEST select cast(getdate() as datetime),3

SELECT   
    colA,
    SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',  
    SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',  
    SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale',
    SQL_VARIANT_PROPERTY(colA,'TotalBytes') AS 'TotalBytes',
    SQL_VARIANT_PROPERTY(colA,'Collation') AS 'Collation',
    SQL_VARIANT_PROPERTY(colA,'MaxLength') AS 'MaxLength'
FROM     
    #TEST  

Wybór wierszy

 

Rollup() vs Cube()

ROLLUP - tworzy podsumowania w hierarchii  grup kolumn w podanej kolejności.
CUBE - tworzy podsumowania dla każdej możliwej kombinacji kolumn. (kombinacji kolumn jest więcej niż w ROLLUP)

--1 GROUP BY ROLLUP (wiele kolumn)
SELECT
    ISNULL(cr.[Name],'_ALL') as Country,
    ISNULL(s.[Name],'_ALL') as CountryState,
    count(a.AddressID) as CountAddressInState
FROM
    [Person].[Address] as a
    join Person.StateProvince as s
    on s.StateProvinceID = a.StateProvinceID
    join Person.CountryRegion as cr
    on  cr.CountryRegionCode = s.CountryRegionCode and
        cr.[Name] in ('Canada','Germany') and
        s.[Name] in ('Brandenburg','Bayern','Alberta','Quebec')
    GROUP BY
        ROLLUP(cr.[Name], s.[Name])
    ORDER BY
        1 desc,
        2 desc

--2 GROUP BY CUBE (wiele kolumn)
SELECT
    ISNULL(cr.[Name],'_ALL') as Country,
    ISNULL(s.[Name],'_ALL') as CountryState,
    count(a.AddressID) as CountAddressInState
FROM
    [Person].[Address] as a
    join Person.StateProvince as s
    on s.StateProvinceID = a.StateProvinceID
    join Person.CountryRegion as cr
    on  cr.CountryRegionCode = s.CountryRegionCode and
        cr.[Name] in ('Canada','Germany') and
        s.[Name] in ('Brandenburg','Bayern','Alberta','Quebec')
    GROUP BY
        CUBE(cr.[Name], s.[Name])
    ORDER BY
        1 desc,
        2 desc

Rollup vs Cube

Dane z procedury bez definiowania tabeli (OPENQUERY)

Aby pobrać dane z tabeli do nowej tabeli używamy zapisu "select * into from". Dla wywołania procedury taka forma jest niemożliwa. Jednak mając obiekt LinkedServer do servera i użycie OPENQUERY sprawia, że jest to wykonalne.

--1 skonfigurowanie linkedserver
use [master]
go

EXEC master.dbo.sp_addlinkedserver @server=N'YourServerName', @srvproduct = N'SQL Server';
go

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'YourServerName', @locallogin = NULL, @useself = N'True'

EXEC sp_serveroption 'YourServerName','DATA ACCESS', TRUE
go

--2 procedura wywolana spod OPENQUERY, wrzut do tabeli bez def.
create or alter proc dbo.ReturnValue
as select 1 as val
go

DROP TABLE IF EXISTS #TAB
select * into #TAB FROM OPENQUERY([YourServerName],N'EXEC dbo.ReturnValue')