SQL Server Memory has been always a topic of discussion at most client locations that I visit. So, I thought I will dedicate the third post of my Power View and System Health Session series to Memory usage. SQL Server 2012 instances tracks some very useful information with the help of the sp_server_diagnostics output. I will be concentrating my efforts on the RESOURCE component’s output.

So get ready to have a plethora of information about your SQL Server instance’s memory usage available in a single Power View sheet in Excel 2013!

First, I will retrieve the output of the Resource component from the System Health Session and store it in a Power Pivot table. This gives me insights into the following information:

1. Available page file

2. Available physical memory

3. Page faults

4. Number of Out of Memory (OOM exceptions) reported

5. Committed memory percentage of the SQL Server instance

6. Target and committed memory for the SQL Server instance

After I have the above information in a Power Pivot table, I created five calculated fields with a KPI definitions attached to them. The definitions were as follows:

1. A new day field using the DAY() function on the Event Time field

2. A new time field using the TIME() function on the Event Time field

3. An average of Available Physical Memory with a KPI definition of <0.4GB (as red), 0.4 – 0.8GB (as yellow) and >0.8GB (as green)

4. An average of Committed Memory % with a KPI definition of <80% (as red), 80-99% (as yellow) and 100% (as green)

5. Maximum OOMs encountered with a KPI definition of =0 (as green) and >0 (as red)

With all this data available, I created a new Memory Usage Power View sheet. This sheet has the following components:

1. A matrix showing the KPIs created against a day field with an available drill-down

2. A bar chart showing the maximum number of OOM exceptions in a day

3. A line graph showing the average Target vs Committed memory against a timeline

4. Another line graph showing the average Large Pages and Lock Pages allocated against a timeline

5. Another matrix showing all the averages of the fields obtained from the Power Pivot sheet with a drill down option available

Now for the good part!! The first screenshot shows what the Power View sheet looks like.

The second and third screenshots show the drill down views available in each of the matrix views in the sheet.

The Excel sheet is available for download on SkyDrive at http://sdrv.ms/10O0udO

The query used to retrieve the above mentioned data is given below.

SET NOCOUNT ON

-- Fetch data for only SQL Server 2012 instances

IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)

BEGIN

-- Get UTC time difference for reporting event times local to server time

DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());

-- Store XML data retrieved in temp table

SELECT TOP 1 CAST(xet.target_data AS XML) AS XMLDATA

INTO #SystemHealthSessionData

FROM sys.dm_xe_session_targets xet

JOIN sys.dm_xe_sessions xe

ON (xe.address = xet.event_session_address)

WHERE xe.name = 'system_health'

AND xet.target_name = 'ring_buffer';

;WITH CTE_HealthSession (EventXML) AS

(

SELECT C.query('.') EventXML

FROM #SystemHealthSessionData a

CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)

)

SELECT

DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],

EventXML.value('(/event/data/text)[1]','varchar(255)') as Component,

EventXML.value('(/event/data/value/resource/@outOfMemoryExceptions)[1]','bigint') as [OOM Exceptions],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[1]','bigint')/(1024*1024*1024) as [Available Physical Memory (GB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[3]','bigint')/(1024*1024*1024) as [Available Paging File (GB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[5]','int') as [Percent of Committed Memory in WS],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[6]','bigint') as [Page Faults],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[12]','bigint')/1024 as [VM Committed (MB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[13]','bigint')/(1024*1024) as [Locked Pages Allocated (GB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[14]','bigint')/(1024*1024) as [Large Pages Allocated (GB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[17]','bigint')/(1024*1024) as [Target Committed (GB)],

EventXML.value('(/event/data/value/resource/memoryReport/entry/@value)[18]','bigint')/(1024*1024) as [Current Committed (GB)]

FROM CTE_HealthSession

WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'

AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'RESOURCE'

ORDER BY [Event Time] desc;

DROP TABLE #SystemHealthSessionData

END

Previous post in this series

PowerView and System Health Session–CPU health

PowerView and System Health Session–Scheduler Health