torsdag 29 mars 2018

Hämta information från flera servrar med SSIS

Tänkte dela med mig om ett bra användningsområde som man som DBA kan nytta av när det gäller SSIS. Allt som oftast vill vi hämta information från våra servrar. Har vi inget system för detta så få vi användar det som finns att tillgå i SQL. Antingen Centralmanagement Server eller SSIS ser jag som det som fungera bäst. I detta fallet så kommer jag visa hur man använder SSIS och en sk Foreach Loop för att hämta det datat vi vill har. Själva listan på alla servrar får jag med en query mot en Centralmanagement Server.

Tanken är att Connection Managern som skall koppla upp sig mot respektive server för informations inhämtningen skall vara dynamisk. I detta fall behöver Server Name samt Initial Catalog vara dynamiska.

Låt oss starta med att lägga till en Execute SQL Task i paketet. Samt de nödvändiga variablerna. Det är viktigt att dessa variabler är av rätt datatyp.













Execute SQL Task konfigureras enligt följande med en Connection Manager som pekar på listan med servrar.


































Variabeln för att spara datat från queryn med serverlistan sätts under ResultSet. Detta är allt som behöver göras på Execute SQL Task.


































Nästa steg är att lägga till en Foreach Loop kontainer. Denna konfigureras enligt följande. Tanken är att den använder datat som vi hämtar i Execute SQL Task. I Foreach Loop kontainern lägger vi också till en Data Flow Task.





















För att använda oss av serverlistan vi generera från Execute SQL Task skall Enumeration vara en Foreach ADO Enumerator. Mappa sedan variabeln objServerList vi skapat tidigare enligt nedan.


































Under Variable Mappings mappar vi de andra variablerna också. Det vi behöver för att använda en dynamisk connection manager är ServerName och DBName.



































I Data Flow Task konfigurera vi sedan en vanlig OLEDB Source och en OLEDB Destination och knyter dessa till varsin Connection Manager.


















Om vi nu markera vår "source" Connection Manager, i mitt fall SourceQuery så väljer vi att göra den dynamisk. Under properties skall det se ut så här. Knyt variablerna till ServerName och InitialCatalog i Expression Editorn genom att klicka på den lilla fyrkanten i höger överkant.














Gå sedan in på respektive property och knyt variabeln till den.




























































Nu är hela lösningen klar och redo att användas.

måndag 3 april 2017

Använda standard edition på för bra server

Detta låter kanske inte som ett problem att en server kan vara för bra. Men så är det och det är något man måste tänka på när man sätter upp en SQL Standard Edition på fysisk hårdvara. Många servrar idag är väl utrustade med CPU och många cores. Har ett exempel på där kunden kör HP BL460c G9. Dessa är bestyckade med två st CPU med vardera 14 cores. Det blir då hela 56 logical cores med hyperthreadingen. Låter ju bra men standard edition hantera bara maximalt 16 cores (32 logical cores) i SQL 2012 och 2014 samt 24 cores (48 logical cores) i SQL 2016.

Man kan se detta med hjälp av DMV sys.dm_os_sys_info. Exempel på hur det kan se ut i en SQL 2012. Scheduler_count är antal cores knutna till SQL (Schedulers är CPU i SQL).

SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)], committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);






För att se hur fördelningen ser ut över NUMA noderna använder vi DMV´n sys.dm_os_nodes (se nedan bild). I bilden ser man att den första NUMA noden har 28 CPU´s medans den andra enbart har fyra. I detta läget skulle det blir en stor skevhet i arbetsfördelningen över NUMA noderna. Dom fyra CPU på NUMA nod två skulle få ta en mycket högre last än de andra på den första NUMA noden.

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);






Det hela är enkelt justerat med ALTER SERVER CONFIGURATION. I detta specifika fall delar vi upp de 32 corsen som SQL kan hantera med sexton st per NUMA node enligt nedan. Tillgängliga cores ser man enklast med DMVén sys.dm_os_schedulers. Använd cpu_id för att mappa till rätt NUMA node.

ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 TO 15, 64 TO 79;

Skripten är från Glenn Berry på SQLSkills

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