Hur mycket resurser tar min databas?

Har en kund som frågat mig flera gånger om det enkelt går att se vilken eller vilka databaser som används i servern och hur mycket resurser dessa tar.

Tänkte här skriva kring hur man skulle kunna bygga ett chargeback system i SQL på ett sätt som jag tycker verka vara en enkel lösning för dom som vill ha lite koll på vilka databaser som används mest i SQL servern med information om resursnyttjande.

Första frågan är vad vill vi kolla på? För att göra det så enkelt som möjligt så tänkte jag CPU nyttjande och hur mycket MB som läses och skrivs mot databasen. För att lösa detta så är det Extended Event som gäller. Man skulle kunna göra det via resource Governor också men i en miljö med många databaser skulle det blir väl komplext att hantera. Dessutom skulle man behöva hantera när man lägger till och ta bort databaser.

En lösning med Extended Event skulle kunna vara så här:

Vi skapar en session i SQL som kollar, SQL_BATCH_COMPLETED samt RPC_COMPLETED. Sen tittar vi efter databaser som inte är systemdatabaser, och sessioner som inte är system sessioner samt där det är någon aktivitet (CPU). Eventuellt skulle man kunna filtrera med på tex applikation så man inte få med saker från SQL Agenten som kanske gör index maintenance. I skriptet nedan gör vi det.

CREATE EVENT SESSION [ResourceUsageStats] ON SERVER

ADD EVENT sqlserver.rpc_completed(SET collect_statement=(0)

    ACTION(sqlserver.client_app_name,sqlserver.database_id)

    WHERE ([sqlserver].[database_id]>(4) AND [sqlserver].[database_id]<>(32767) AND [sqlserver].[session_id]>(50) AND [cpu_time]>(0) AND NOT [sqlserver].[client_app_name] like N'%SQLAgent%')),

ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(0)

    ACTION(sqlserver.client_app_name,sqlserver.database_id)

    WHERE ([sqlserver].[database_id]>(4) AND [sqlserver].[database_id]<>(32767) AND [sqlserver].[session_id]>(50) AND [cpu_time]>(0) AND NOT [sqlserver].[client_app_name] like N'%SQLAgent%'))

ADD TARGET package0.event_file(SET filename=N'C:\Temp\ResourceUsageStats.xel',max_rollover_files=(10))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

Sessionen ovan skapar en fil i C:\Temp och den kan vi lätt läsa med följande.

SELECT CAST(event_data AS XML) AS event_data_XML

INTO #Events

FROM sys.fn_xe_file_target_read_file('C:\Temp\ResourceUsageStats_*.xel', null, null, null) AS F;

select * from #Events

Resultatet är kanske inte så lockande direkt. Bara rader med XML.

Vi väljer att läsa in det till en annan temptabell.

SELECT

  event_data_XML.value ('(/event/action [@name=''database_id'']/value)[1]', 'INT') AS DBID,

  event_data_XML.value ('(/event/data [@name=''cpu_time'']/value)[1]', 'BIGINT') AS cpu_time,

  event_data_XML.value ('(/event/data [@name=''logical_reads'']/value)[1]', 'BIGINT') AS logical_reads,

  event_data_XML.value ('(/event/data [@name=''writes'']/value)[1]', 'BIGINT') AS writes

INTO #Queries

FROM #Events;

Sen aggregera vi ihop det hela och joinar med sys.databases.

SELECT a.dbid,

b.name,

SUM(cpu_time) as CPU,

MBRead = ((SUM(logical_reads) * 8 ) / 1024),

MBWrite = ((SUM(writes) * 8 ) / 1024),

MBTotal = (((SUM(logical_reads) + SUM(writes)) * 8) / 1024)

FROM #Queries a

inner join sys.databases b

            on a.dbid = b.database_id

group by b.name, a.dbid;

För vår test databas få vi nu ett snyggt resultat, förutsatt att vi läst och skrivit lite mot databasen.

Mitt exempel kanske inte bli så imponerande men tänk om du har hundra databaser.