SQL Server 2019 Administration Inside Out

SQL Server 2019 Administration Inside Out Paperback – release date: 2020 by Randolph West, Melody Zacharias, William Assaf, Sven Aelterman

Conquer SQL Server 2019 administration–from the inside out

Dive into SQL Server 2019 administration–and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of time-saving solutions, tips, and workarounds–all you need to plan, implement, manage, and secure SQL Server 2019 in any production environment: on-premises, cloud, or hybrid. SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2019 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle today’s essential tasks–and challenge yourself to new levels of mastery.

Install, customize, and use SQL Server 2019’s key administration and development tools
Manage memory, storage, clustering, virtualization, and other components
Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations
Provision SQL Server and Azure SQL databases
Secure SQL Server via encryption, row-level security, and data masking
Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing
Establish SQL Server IaaS network security groups and user-defined routes
Administer SQL Server user security and permissions
Efficiently design tables using keys, data types, columns, partitioning, and views
Utilize BLOBs and external, temporal, and memory-optimized tables
Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans
Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments

For Experienced SQL Server Administrators and Other Database Professionals

10th January 201916th January 2019

Adatbázis audit

DevOps környezetben vagy éles környezetben érdemes auditot beállítani. Saját érdekünkben érdemes tudni, hogy ki nyúlt bele és mit csinált!

CREATE SERVER AUDIT [Audit-20190110]
TO FILE
( FILEPATH = N'H:\Audit\'
,MAXSIZE = 100 MB
,MAX_FILES = 500
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 3000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)
GO

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]
FOR SERVER AUDIT [Audit-20190110]
-- ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
-- ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION [dbaudit]
FOR SERVER AUDIT [Audit-20190110]
ADD ( AUDIT_CHANGE_GROUP )
,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
,ADD ( BACKUP_RESTORE_GROUP )
,ADD ( DATABASE_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( DBCC_GROUP )
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )
-- 2012-newer allowed events
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
   ,ADD ( USER_DEFINED_AUDIT_GROUP )
   --,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

GO

SELECT event_time
,action_id
,session_server_principal_name AS UserName
,server_instance_name
,database_name
,schema_name
,object_name
,statement
FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)
-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

CREATE SERVER AUDIT [Audit-20190110]

TO FILE

( FILEPATH = N'H:\Audit\'

,MAXSIZE = 100 MB

,MAX_FILES = 500

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 3000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)

USE [master]

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]

FOR SERVER AUDIT [Audit-20190110]

-- ADD (DATABASE_OBJECT_ACCESS_GROUP),

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

ADD (BACKUP_RESTORE_GROUP),

ADD (AUDIT_CHANGE_GROUP),

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (FAILED_LOGIN_GROUP),

ADD (DATABASE_MIRRORING_LOGIN_GROUP),

-- ADD (LOGOUT_GROUP),

ADD (DATABASE_CHANGE_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SERVER_OBJECT_CHANGE_GROUP),

ADD (SERVER_OPERATION_GROUP),

ADD (LOGIN_CHANGE_PASSWORD_GROUP),

ADD (SERVER_STATE_CHANGE_GROUP),

ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (USER_CHANGE_PASSWORD_GROUP)

WITH (STATE = ON)

--Create the database Audit spec

CREATE DATABASE AUDIT SPECIFICATION [dbaudit]

FOR SERVER AUDIT [Audit-20190110]

ADD ( AUDIT_CHANGE_GROUP )

,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)

,ADD ( BACKUP_RESTORE_GROUP )

,ADD ( DATABASE_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( DBCC_GROUP )

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )

-- 2012-newer allowed events

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( USER_DEFINED_AUDIT_GROUP )

--,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

SELECT event_time

,action_id

,session_server_principal_name AS UserName

,server_instance_name

,database_name

,schema_name

,object_name

,statement

FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)

-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

14th November 2018

Nem használt oszlopok – Empty columns

Default Null értékű oszlopok , ahol minden Null értékű:

    -- Returns a list of all columns in current database
    -- where the column's value is null for all records.
    
	declare @tempTable TABLE
    (
        TableSchema nvarchar(256),
        TableName nvarchar(256),
        ColumnName sysname,
        NotNullCnt bigint
    );
     
    declare @sql nvarchar(4000);
    declare @tableSchema nvarchar(256);
    declare @tableName nvarchar(256);
    declare @columnName sysname;
    declare @cnt bigint;
     
    declare columnCursor cursor FOR
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
		-- TABLE_NAME ='tablename' and
		IS_NULLABLE = 'YES';
     
    open columnCursor;
     
    fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
     
    while @@FETCH_STATUS = 0
    begin
        -- use dynamic sql to get count of records where column is not null
        SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
            '] where [' + @columnName + '] is not null';
        print @sql; --uncomment for debugging
        exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
     
        INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
     
        fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
    end
     
    close columnCursor;
    deallocate columnCursor;
     
    SELECT * FROM @tempTable WHERE NotNullCnt = 0;

-- Returns a list of all columns in current database

-- where the column's value is null for all records.

declare @tempTable TABLE

(

TableSchema nvarchar(256),

TableName nvarchar(256),

ColumnName sysname,

NotNullCnt bigint

);

declare @sql nvarchar(4000);

declare @tableSchema nvarchar(256);

declare @tableName nvarchar(256);

declare @columnName sysname;

declare @cnt bigint;

declare columnCursor cursor FOR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE

-- TABLE_NAME ='tablename' and

IS_NULLABLE = 'YES';

open columnCursor;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0

begin

-- use dynamic sql to get count of records where column is not null

SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +

'] where [' + @columnName + '] is not null';

print @sql; --uncomment for debugging

exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

end

close columnCursor;

deallocate columnCursor;

SELECT * FROM @tempTable WHERE NotNullCnt = 0;

10th November 201810th November 2018

Table size

Tábla méretek , használt tartalommal:

-- table sizes , tábla méretek egy adatbázisban
SELECT 
    @@SERVERNAME  AS servername,
    DB_NAME ( )  AS dbname,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
	,format(getdate(), 'yyyy-MM-dd') as Datum
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

-- table sizes , tábla méretek egy adatbázisban

SELECT

@@SERVERNAME AS servername,

DB_NAME ( ) AS dbname,

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

,format(getdate(), 'yyyy-MM-dd') as Datum

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

10th September 201810th September 2018

SQL Server 2018? SQL Server 2019!

A következő SQL server verzió mégsem 2018 lesz ( az igényeknek megfelelően egyre gyakrabban adjuk ki az új verziókat ígéret), hanem SQL Server 2019 néven fog megjelenni. (hasonlóan Windwos Server 2019, Office 2019).

25th July 20182nd August 2018

HEAP table is fragmented

Általában nem ajánlott HEAP tábla létrehozása (Table without Clustered Index) , egy esetben jöhet jól, ha nagyon gyors adatbetöltés szükséges.
A Heap tábla azonban töredezik, ennek oka, hogy minden Insert a tábla végére kerül és a Deleted sorok sem kerülnek betöltésre.
Amíg a hagyományos indexeket egy index reorganizációval vagy újraépítéssel (rebuild) karbantarthajtuk és gyorssá tehetjük addig a Heap táblán nincs ilyen beépített karbantartás .
Teendő?

Continue reading “HEAP table is fragmented”

18th June 201821st June 2018

SQL Server Index Tuning

Egy több millió sorral rendelkező táblán már súlyos gondokat okozhat egy-egy index hiány.

5 Millió sor, paraméterezett select, a szerver túlterhelt:

CPU and Processor Queue Length - Performance Monitor — CPU and Processor Queue Length – Performance Monitor

11:20-kor 1 db index felrakása után látványosan csökken a CPU (kék) és CPU Queue (piros). Ellenpróba 13:35-kor az index letiltásakor ismét befullad a szerver (a határértéknek számító 80%-ról 95% átlagos cpu használatra ugrik). Ráadásul a szerveren több mint 100 adatbázis van és mégis egy darab eljárás jelentősen beterhel.
Persze a valóság nem ilyen egyszerű, egy -egy index felrakásának hatását többnyire nem lehet performance monitor-ból kimutatni, a fenti példa egy rosszul tervezett gyakori futású kódra vonatkozik.

25th May 2018

Index méretek

Index size

--  USE <databasename>
SELECT i.[name] AS IndexName
                ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                AND s.[index_id] = i.[index_id]
-- where i.[name] like 'mix%'
GROUP BY i.[name]
ORDER BY IndexSizeKB Desc, i.[name]
GO

-- USE <databasename>

SELECT i.[name] AS IndexName

,SUM(s.[used_page_count]) * 8 AS IndexSizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

AND s.[index_id] = i.[index_id]

-- where i.[name] like 'mix%'

GROUP BY i.[name]

ORDER BY IndexSizeKB Desc, i.[name]

Az egyes indexek méretei. Az adott adatbázisra kell használni, szükség esetén szűrni.

18th May 2018

Restore ellenőrzése, avagy mentés és visszaállítási információk

SELECT
rs.recovery,
bs.server_name as source_server,
@@SERVERNAME as destination_server,
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[rs].[restore_date],
rs.stop_at,
rs.user_name,
bs.user_name as backup_agent,
bmf.physical_device_name, bs.backup_size
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
where rs.recovery=1
ORDER BY rs.recovery DESC, [rs].[restore_date] DESC

SELECT

rs.recovery,

bs.server_name as source_server,

@@SERVERNAME as destination_server,

[bs].[backup_start_date],

[bs].[backup_finish_date],

[bs].[database_name] as [source_database_name],

[rs].[restore_date],

rs.stop_at,

rs.user_name,

bs.user_name as backup_agent,

bmf.physical_device_name, bs.backup_size

FROM msdb..restorehistory rs

INNER JOIN msdb..backupset bs

ON [rs].[backup_set_id] = [bs].[backup_set_id]

INNER JOIN msdb..backupmediafamily bmf

ON [bs].[media_set_id] = [bmf].[media_set_id]

where rs.recovery=1

ORDER BY rs.recovery DESC, [rs].[restore_date] DESC

15th May 2018

SQL futási idők mérése

Mennyi idő alatt fut le a query?

  SET STATISTICS IO,TIME ON
  GO

  -- lekérdezési rész:

  -- kikapcsolása:
  GO
  SET STATISTICS IO,TIME OFF
  GO

SET STATISTICS IO,TIME ON

-- lekérdezési rész:

-- kikapcsolása:

SET STATISTICS IO,TIME OFF

Magyarázat:

SET STATISTICS TIME ON az adott session-re vonatkozik, tehát SSMS-ben a nyitott kapcsolatra.
A végrehajtási idő nem csak a szerver hw erőforrásainak , hanem az éppen aktuális leterheltségnek is függvénye, ezért SQL optimalizációkor nem ezt használjuk, hanem a SET STATISTICS IO ON -t, mivel ez utóbbi megmonja, hogy a query optimizernek hány logikai “page” olvasásra van szüksége a végrehajtás során.