MSSQL Server

All posts containing MSSQL-related scripts and patterns.

MSSQL Snapshot Demo

/ MSSQL Server /

Snapshot demo script…

/* ##### Maak een test database... ###### */
USE [master]
GO
CREATE DATABASE [TEST1] ON  PRIMARY 
( NAME = N'TEST1', FILENAME = N'D:MSSQLSERVERTEST1.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TEST1_log', FILENAME = N'D:MSSQLSERVERTEST1_log.ldf' , SIZE = 20480KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

/* ##### Maak een tabel in de database... ##### */
USE [TEST1]
GO
CREATE TABLE [dbo].[Table_1](
	[COL1] [int] IDENTITY(1,1) NOT NULL,
	[COL2] [nchar](10) NULL
) ON [PRIMARY]
GO

/* ##### Vul de tabel met waarden... ##### */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Oemupa3l')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Wa4ookei')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Einguix0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('eeSh8pae')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('The1thoh')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ba3eih6x')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ohw8aeYi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ZeeC7al0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('so8Mie3C')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('xu4ohNgo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aev3Iepi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('phe1Eejo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aeTiu7ai')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Vee7Sux7')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ia3aShu6')
GO

/* ##### Controleer of de tabel is gevuld */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Maak een snapshot van de test database... ###### */
USE [master]
GO
CREATE DATABASE TEST1_Snap ON
( NAME = TEST1, FILENAME =
'D:MSSQLSERVERTEST1_SNAP.mdf' )
AS SNAPSHOT OF [TEST1];

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ### Verwijder waarden uit de tabel... ##### */
USE [TEST1]
GO
DELETE FROM [TEST1].[dbo].[Table_1]
      WHERE COL1 = 10
GO

/* ##### Controleer of de waardes zijn verdwenen... */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ##### Als je zeker weet dat er geen gerelateerde gegevens zijn verdwenen ##### */
/* Set Identity insert ON */
USE [TEST1]
GO
SET IDENTITY_INSERT [TEST1].[dbo].[Table_1] ON
GO

/* Zet verwijderde record terug */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] (COL1, COL2)
SELECT * FROM [TEST1_Snap].[dbo].[Table_1] 
WHERE COL1 = 10

/* ##### Controleer of de tabel is gevuld ##### */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Restore volledige database van snapshot... ##### */
USE [master]
GO
RESTORE DATABASE [TEST1] FROM DATABASE_SNAPSHOT = 'TEST1_Snap'
GO

MSSQL Query frequently used counters

/ MSSQL Server /

The query below returns a few frequently used counters. Page life expectancy, Lazy writes/sec, Target Server Memory, Total Server Memory and Processor Utilizaton. If exist PAGEIOLATCH waiting tasks are returned.

SET QUOTED_IDENTIFIER ON

-- New temp table...
CREATE TABLE #tPrefCounters ([Name] nvarchar(40),[Value] int,[Info] nvarchar(50) )
GO

-- Prepare statements...
DECLARE @H1 int
EXEC sp_prepare @H1 OUTPUT, N'@P1 nvarchar(40), @P2 nvarchar(50)', 
     N'SELECT TOP(1) @P1 AS Name, cntr_value AS Value, @P2 AS Info FROM [master].[sys].[dm_os_performance_counters] WHERE counter_name = @P1', 1

-- Execute handles and fill #tPrefCounters table...
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Page life expectancy', N'Should be -gt 300 sec per 4GB mem'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #1)'
WAITFOR DELAY '00:00:01'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #2) Should not climb much (low or zero)'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Target Server Memory (KB)', N'Optimal memory under current load'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Total Server Memory (KB)', N'Memory used by instance, should be close to target'

-- Add CPU Utilization...
INSERT INTO #tPrefCounters
SELECT TOP(1) 'Processor Utilization' AS Name, 
              x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS Value, 
			  '% CPU Utilization' AS Info
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers 
       WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC

-- Add PAGEIOLATCH wait stats...
INSERT INTO #tPrefCounters
SELECT top(5) wait_type AS Name, wait_duration_ms AS Value, 'Long waits may indicate disk io problems'
FROM [master].[sys].[dm_os_waiting_tasks] WHERE wait_type LIKE 'PAGEIOLATCH%' ORDER BY wait_duration_ms DESC

-- Unprepare statements...
EXEC sp_unprepare @H1

-- Display prefered counters...
SELECT * FROM #tPrefCounters
GO

-- Remove old temp table...
DROP TABLE #tPrefCounters
GO

 

MSSQL Query CPU Utilization

/ MSSQL Server /

Met de onderstaande oplossing kun je snel de CPU Utilization van de laatste 5 minuten ophalen (per minuut weergegeven).

SET QUOTED_IDENTIFIER ON
SELECT TOP(5) x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS CPUUTIL
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers 
       WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC

MSSQL Query SQL Server Logs

/ MSSQL Server /

De volgende uitwerking maakt gebruik van extended stored procedure xp_readerrorlog om in de SQL Server Log records te zoeken naar specifieke term(en). Alleen records van de laatste 3 dagen worden getoond (indien aanwezig in het logbestand).

DECLARE @iLogArchive int, @iLogType int 
DECLARE @vSearchString1 nvarchar(50), @vSearchString2 nvarchar(50) 
DECLARE @vSortOrder nvarchar(4) 
DECLARE @dtFrom datetime, @dtTo datetime 
SET @dtFrom = (GETDATE() -3) 
SET @dtTo = (GETDATE()) 
SET @iLogArchive	= 0		-- 0=Current, 1=Archive #1, enz.. 
SET @iLogType		= 1		-- 1=SQL Server, 2=SQL Agent 
SET @vSortOrder		= N'asc'	-- asc=Ascending, desc=Descending  
-- Set searchstrings and start procedure... 
SET @vSearchString1 	= N'Fail' 
SET @vSearchString2 	= N'' 
EXEC master.sys.xp_readerrorlog @iLogArchive, @iLogType, @vSearchString1, @vSearchString2, @dtFrom, @dtTo, @vSortOrder

MSSQL List Active DBFiles

/ MSSQL Server /

Met behulp van de volgende query kun je een overzicht opvragen van alle actieve bestanden eventueel kun je een WHERE clause opnemen (WHERE type_desc = ‘LOG|ROWS’) om alleen de logbestanden of alleen de data bestanden weer te geven.

SELECT smf.physical_name, sdb.name as database_name
FROM sys.master_files smf
LEFT JOIN sys.databases sdb on sdb.database_id = smf.database_id
--WHERE type_desc = 'LOG'
ORDER BY smf.physical_name

MSSQL Query Linked Server

/ MSSQL Server /

Voor uitvoer van Stored Procedures op een linked server moet de optie RPC onder Server Options op True worden gezet. Hieronder een tweetal methodes m.b.t. uitvoeren SP’s op een linked server. Het eerste voorbeeld maakt gebruik van de four-part name. Het tweede voorbeeld gebruikt OPENQUERY.

EXEC [LINKEDSERVERNAME].[master].sys.sp_who2
SELECT * FROM OPENQUERY([LINKEDSERVERNAME],'EXEC master.sys.sp_who')

Hieronder een tweetal methodes om een query uit te voeren op een tabel in een linked server.

SELECT * FROM [LINKEDSERVERNAME].[master].sys.databases
SELECT * FROM OPENQUERY([LINKEDSERVERNAME], 'SELECT * FROM sys.databases')

Met de onderstaande opdracht maak je een linked server aan die terugvalt op de actuele security context.

sp_addlinkedserver 'SERVERNAME', 'SQL Server'
sp_addlinkedsrvlogin 'SERVERNAME', 'True', NULL

MSSQL Read output SP in temptable

/ MSSQL Server /

De onderstaande gegevens plaatst de gegevens uit een stored procedure in een tijdelijke tabel. Deze specifieke uitwerking plaatst de output van sp_who2 in een tijdelijke tabel met de naam #tWho2. Nu kun je de gegevens filteren d.m.v. queries op de tijdelijke tabel.

-- Oude tijdelijke tabel verwijderen...
DROP TABLE #tWho2
GO

-- Nieuwe tijdelijke tabel aanmaken met alle velden...
CREATE TABLE #tWho2 (
	SPID char(5),
	[Status] nvarchar(max),
	[Login] nvarchar(max),
	HostName nvarchar(max),
	BlkBy char(5),
	DBName nvarchar(max),
	Command nvarchar(max),
	CPUTime nvarchar(max),
	DiskIO nvarchar(max),
	LastBatch nvarchar(max),
	ProgramName nvarchar(max),
	SPID2 char(5),
	REQUESTID char(5)
)
GO

-- Gegevens uit stored procedure inlezen in tijdelijk tabel...
INSERT INTO #tWho2 EXEC sp_who2
GO

MSSQL Query Execution Plan running query

/ MSSQL Server /

Met onderstaande query kun je het Query Execution Plan bekijken voor een lopende (probleem)query op basis van de session_id.

USE [master]
SELECT * FROM sys.dm_exec_query_plan (
  (SELECT plan_handle
   FROM sys.dm_exec_requests
   WHERE session_id = 53)
)
GO

Ook handig is om performance gegevens te tonen in het messages scherm m.b.t. de query. Met onderstaande query kun je deze opties aanzetten binnen een sessie.

SET STATISTICS [TIME|IO] [ON|OFF];