tisdag 14 juni 2016

Kul med Transact SQL och Windowing funktioner

När jag tittade över min tjänstepension senast så funderade jag på hur mycket utdelningar har man fått sen starten på den. Tittade lite på vilket data man kunde få ut från Nordnet. Enkelt gick det ju att exportera all transaktionshistorik sen starten så sagt och gjort. Efter ett par försök så hade jag datat i en tabell i SQL servern.

Tabellen enligt nedan skapades via import/export wizarden, fick justera några datatyper bara.

CREATE TABLE [dbo].[transaktionsfil](
            [Id] [int] NOT NULL,
            [Bokföringsdag] [date] NULL,
            [Affärsdag] [date] NULL,
            [Likviddag] [date] NULL,
            [Transaktionstyp] [varchar](50) NULL,
            [Värdepapper] [nvarchar](50) NULL,
            [Instrumenttyp] [varchar](50) NULL,
            [ISIN] [varchar](50) NULL,
            [Antal] [int] NULL,
            [Kurs] [decimal](18, 2) NULL,
            [Ränta] [decimal](18, 2) NULL,
            [Avgifter] [decimal](18, 2) NULL,
            [Belopp] [decimal](18, 2) NULL,
            [Valuta] [varchar](50) NULL,
            [Inköpsvärde] [decimal](18, 2) NULL,
            [Resultat] [real] NULL,
            [Totalt antal] [real] NULL,
            [Saldo] [real] NULL,
            [Växlingskurs] [decimal](18, 2) NULL,
            [Transaktionstext] [varchar](50) NULL,
            [Makuleringsdatum] [varchar](max) NULL,
            [Verifikations- Notanummer] [int] NULL,
 CONSTRAINT [PK_transaktionsfil] PRIMARY KEY CLUSTERED
(
            [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Alla kolumner jag önska fanns med i datat från Nordnet där jag har tjänstepensionen jag ville titta på. Transaktionstyp kolumnen innehåller ”UTDELNING” och det är ju den jag är intresserad av i detta fall. Med queryn nedan få jag precis det jag önskar.

SELECT bokföringsdag, Transaktionstyp, Värdepapper, Instrumenttyp, Antal, Kurs, Belopp
,SUM(Belopp) OVER (ORDER BY bokföringsdag ASC ROWS UNBOUNDED PRECEDING) AS Balans
FROM transaktionsfil
WHERE Transaktionstyp = 'UTDELNING'
ORDER BY Bokföringsdag

Var inte själv medveten om att det var så många utdelningar på tre år och att det totalt varit lite över sju tusen. Trevligt säger jag och tack Nordnet för smidig och bra lösning. Riktigt bra att har förmånen att kunna placera pengarna själv och slippa taskiga fonder och andra dyra produkter.

Bokföringsdag
Transaktionstyp
Värdepapper
Instrumenttyp
Antal
Kurs
Belopp
Balans
2013-07-02
UTDELNING
SDY
Aktie
10
0.42
27.79
27.79
2013-10-01
UTDELNING
SDY
Aktie
10
0.40
25.58
53.37
2013-10-01
UTDELNING
PKW
Aktie
15
0.07
6.29
59.66
2014-01-02
UTDELNING
PKW
Aktie
15
0.10
9.31
68.97
2014-01-02
UTDELNING
SDY
Aktie
10
1.19
77.38
146.35
2014-01-02
UTDELNING
SDY
Aktie
10
0.47
30.17
176.52
2014-01-02
UTDELNING
SDY
Aktie
10
0.03
2.24
178.76
……
….
..
..
…..
2016-05-13
UTDELNING
INDU C
Aktie
50
5.00
250.00
6306.50
2016-05-16
UTDELNING
BMW
Aktie
10
3.20
298.24
6604.74
2016-05-16
UTDELNING
INVE B
Aktie
35
10.00
350.00
6954.74
2016-05-30
UTDELNING
ZAL
Aktie
100
0.85
84.89
7039.63

tisdag 24 maj 2016

Används din databas?

När man träffa kunder så är det vanligt att man inte riktigt vet om sina databaser faktiskt används eller inte och i vilken utsträckning. För att få en överblick av hur det ser ut så brukar jag schemalägga ett jobb som samlar information från DMV´en sys.dm_db_index_usage_stats. Denna dynamic viewe visa löpande information om hur index och tabeller (HEEP´s) används. Jag anser att detta bör kunna ge en bra bild på hur din databas används. Du kan även använda denna information för att se om index faktiskt kan tas bort.

Börja med att skapa en tabell i valfri databas för att logga datat över tiden.

CREATE TABLE [dbo].[IndexUsage](
            [CollectedTime] [datetime] NULL,
            [LastCollectedTime] [datetime] NULL,
            [DatabaseName] [varchar](256) NULL,
            [SchemaName] [varchar](50) NULL,
            [TableName] [varchar](256) NULL,
            [IndexName] [varchar](256) NULL,
            [user_seeks] [bigint] NULL,
            [init_user_seeks] [bigint] NULL,
            [user_scans] [bigint] NULL,
            [init_user_scans] [bigint] NULL,
            [user_updates] [bigint] NULL,
            [init_user_updates] [bigint] NULL,
            [row_count] [bigint] NULL,
            [init_row_count] [bigint] NULL,
            [size_in_mb] [decimal](18, 2) NULL,
            [init_size_in_mb] [decimal](18, 2) NULL
) ON [PRIMARY]

Sedan använder vi sp_MSForEachDB för att köra mot samtliga databaser. Det är två olika skript jag slår ihop i en union. Det ena är för att hämta användande från  sys.dm_db_index_usage_stats och det andra är för att hämta samtliga index från sys.indexes, även dom som då aldrig används. Kör följande kod via ett SQL jobb.

CREATE TABLE #Temp1 (
CollectedTime DATETIME,
DatabaseName VARCHAR(256),
SchemaName VARCHAR(50),
TableName VARCHAR(256),
IndexName VARCHAR(256),
user_seeks BIGINT,
user_scans BIGINT,
user_updates BIGINT,
row_count BIGINT,
size_in_mb DECIMAL(18,2)
)

EXEC sp_MSForEachDB 'USE [?];
WITH CTE AS (
SELECT d.name as DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
t.name AS TableName,
ISNULL(i.name, ''HEEP'') AS IndexName,
ISNULL(s.user_seeks, 0) as user_seeks,
ISNULL(s.user_scans, 0) as user_scans,
ISNULL(s.user_updates, 0) as user_updates,
ps.row_count,
CAST((ps.reserved_page_count * 8)/1024. as decimal(12,2)) AS size_in_mb
FROM sys.dm_db_index_usage_stats AS s
            INNER JOIN sys.databases d
                         ON d.database_id = s.database_id
            INNER JOIN sys.indexes as i
                         ON s.object_id = i.object_id
                         AND s.index_id = i.index_id
            INNER JOIN sys.tables t
                         ON s.object_id = t.object_id
            INNER JOIN sys.dm_db_partition_stats ps
                         ON i.object_id = ps.object_id
                         AND i.index_id = ps.index_id
WHERE objectproperty(s.object_id,''IsUserTable'') = 1 AND s.database_id = DB_ID()
UNION
SELECT DB_NAME() AS DatbaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS TableName,
ISNULL(i.name, ''HEEP'') AS IndexName,
0 as user_seeks,
0 as user_scans,
0 as user_updates,
ps.row_count,
CAST((ps.reserved_page_count * 8)/1024. as decimal(12,2)) as size_in_mb
FROM sys.indexes i 
            INNER JOIN sys.objects o
                         ON i.object_id = o.object_id
            LEFT OUTER JOIN sys.dm_db_index_usage_stats s
                         ON s.object_id = i.object_id
                         AND i.index_id = s.index_id
            INNER JOIN sys.dm_db_partition_stats ps
                         ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.object_id, ''IsUserTable'') = 1
AND s.object_id IS NULL
)
INSERT INTO #Temp1
SELECT GETDATE(), DatabaseName, SchemaName, TableName, IndexName,
SUM(user_seeks) as user_seeks, SUM(user_scans) as user_scans, SUM(user_updates) as user_updates, SUM(row_count) as row_count, SUM(size_in_mb) as size_in_mb
FROM CTE
GROUP BY DatabaseName, SchemaName, TableName, IndexName
ORDER BY DatabaseName, TableName'

GO

MERGE INTO dbo.IndexUsage AS trg
USING #Temp1 AS src
            ON trg.DatabaseName = src.DatabaseName
            AND trg.SchemaName = src.SchemaName
            AND trg.TableName = src.TableName
            AND trg.IndexName = src.IndexName
WHEN MATCHED THEN
UPDATE SET
            trg.LastCollectedTime = src.CollectedTime,
            trg.user_seeks = src.user_seeks,
            trg.user_scans = src.user_scans,
            trg.user_updates = src.user_updates,
            trg.row_count = src.row_count,
            trg.size_in_mb = src.size_in_mb
WHEN NOT MATCHED BY TARGET THEN
INSERT (CollectedTime, DatabaseName, SchemaName, TableName, IndexName, init_user_seeks, init_user_scans, init_user_updates, init_row_count, init_size_in_mb)
VALUES (src.CollectedTime, src.DatabaseName, src.SchemaName, src.TableName, src.IndexName, src.user_seeks, src.user_scans, src.user_updates, src.row_count, src.size_in_mb);


DROP TABLE #Temp1

För att analysera datat kan man tex använda dessafrågor för att se förändring sedan starten.

SELECT CollectedTime, LastCollectedTime, DatabaseName, SchemaName, TableName, IndexName
,(user_seeks - init_user_seeks) AS user_seeks
,(user_scans - init_user_scans) AS user_scans
,(user_updates - init_user_updates) AS user_updates
,(row_count - init_row_count) AS row_count
,(size_in_mb - init_size_in_mb) AS size_in_mb
FROM [IndexUsage]
ORDER BY user_seeks DESC, user_scans DESC, user_updates DESC

WITH CTE AS (
SELECT DatabaseName, SchemaName, TableName, IndexName
,(User_seeks + user_scans + user_updates) as Activity
FROM IndexUsage
WHERE DatabaseName NOT IN ('Master','Tempdb','Model','MSDB')
)
SELECT DatabaseName, SchemaName, TableName, IndexName
,SUM(Activity) as Activity
FROM CTE
GROUP BY DatabaseName, SchemaName, TableName, IndexName

ORDER BY DatabaseName, SchemaName, TableName, IndexName

onsdag 6 april 2016

Skapa ett dynamiskt restore skript

Många gånger kan det vara bra att ha färdiga skript för att göra restore av en databas. Antingen att man snabbt skall kunna göra restore vid en server eller databas krasch eller som i detta fall som jag tänkte visa, att man kontinuerligt restora databasen till en annan server. Tex för att kunna läsa datat där eller för att ha det som en säkerhets kopia. En fattigmans lösning för high availability helt enkelt.

Låt säga att vi har två servrar, Server1 och Server2. I detta fall så jobbar vi på Server2 där kopian skall ligga. Börja med att sätta upp en linked server, för det behöver vi för att kunna läsa backupinformationen i MSDB databasen på Server1.

Nedan följer kod för att lösa det hela. Först görs en full restore med NORECOVERY. Sedan restoras alla på följande transactionsloggar från efter att full backupen gick klart fram till @Stopat tiden som är vald. Utöka proceduren med mer logik om man önska loggning till en tabell eller retry funktion ifall tex backupfilen är låst av någon orsak. Så klart kan man bygga logik så att full restoren gör en RECOVERY ifall inga transactionsloggsbackuper finns.

Vill man kan man skriva det hela som ett restore skript med PRINT istället för EXEC och lägga ut det i en textfil. Dock inte beskrivet här.

ALTER PROCEDURE [dbo].[usp_RestoreScript] @DBname VARCHAR(100)
AS

SET NOCOUNT ON

DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastFullBackupFinishDate DATETIME, @i INT, @lastLogBackup INT, @logBackupPath VARCHAR(2000), @BackupFinishDate DATETIME, @Stopat DATETIME, @Message VARCHAR(MAX), @ErrorNumber INT, @ErrorLine INT

BEGIN
BEGIN
       -- Create temp table
       CREATE TABLE #MSDBBackupHistory (
       id INT IDENTITY(1,1),
       backup_set_id INT,
       media_set_id INT,
       position INT,
       backup_start_date DATETIME,
       backup_finish_date DATETIME,
       backup_type CHAR(1),
       physical_device_name VARCHAR(1000)
       )
       -- Dump the backup info into the temp table
INSERT INTO #MSDBBackupHistory (backup_set_id, media_set_id, position, backup_start_date, backup_finish_date, backup_type, physical_device_name)
SELECT bs.backup_set_id, bs.media_set_id, bs.position, bs.backup_start_date, bs.backup_finish_date, bs.type, RTRIM(bmf.physical_device_name) AS physical_device_name
              FROM LINKED_Server.msdb.dbo.backupset bs
                     JOIN LINKED_Server.msdb.dbo.backupmediafamily bmf
                            ON bmf.media_set_id = bs.media_set_id
       WHERE bs.database_name = @DBname
       ORDER BY bs.backup_start_date

BEGIN TRY
-- Get the last Full backup info.
SET @lastFullBackup = (SELECT MAX(id) FROM #MSDBBackupHistory WHERE backup_type='D')
SET @lastFullBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)
SET @lastFullBackupFinishDate = (SELECT MAX(backup_finish_date) FROM #MSDBBackupHistory WHERE backup_type='D')

-- Restore the Full backup
DECLARE @SQL1 VARCHAR(MAX)
SET @SQL1 = 'RESTORE DATABASE ' + @DBName +' FROM DISK ='''+ @lastFullBackupPath + ''' WITH move ''LogicalName'' to ''E:\PR14_Data2\MSSQL11.DB_PR14\MSSQL\Data\DatabaseFileName.mdf'',
move ''LogicalNamelog'' to ''E:\PR14_Logs\MSSQL11.DB_PR14\MSSQL\Data\DatabaseFileName_log.ldf'',
NORECOVERY, REPLACE, STATS = 10'
EXEC (@SQL1)

--Set parameter for log restore
SET @i = (SELECT MIN(id) FROM #MSDBBackupHistory WHERE backup_type='L' and backup_start_date >= @lastFullBackupFinishDate )
SET @Stopat = (SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
SET @BackupFinishDate = (select MAX(backup_finish_date) from #MSDBBackupHistory where backup_finish_date <= @Stopat)

-- Restore the transactionlogs
WHILE (@i <= (SELECT MAX(id) FROM #MSDBBackupHistory where backup_finish_date <= @BackupFinishDate))
BEGIN
       
       SET @logBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)

       IF (@i = (SELECT MAX(id) FROM #MSDBBackupHistory where backup_finish_date <= @BackupFinishDate))
       SET @SQL1 = 'RESTORE LOG '+ @DBName +' FROM DISK = '''+ @logBackupPath + ''' WITH RECOVERY, STATS=5, STOPAT = '''+CAST(@Stopat as varchar(50))+''';'
       ELSE
       SET @SQL1 = 'RESTORE LOG '+ @DBName +' FROM DISK = '''+ @logBackupPath + ''' WITH NORECOVERY, STATS=5;'
       EXEC (@SQL1)

SET @i = @i + 1

END
--End restore logs  
END TRY
BEGIN CATCH
THROW;
END CATCH
END
-- remove temp objects that exist
IF OBJECT_ID('tempdb..#MSDBBackupHistory') IS NOT NULL
DROP TABLE #MSDBBackupHistory

END