Data Mining in the SQL Sentry Database

SQL Sentry Datamining
For a deeper dive on datamining the SQL Sentry Database that will provide more a better understanding of its general schema, check out the blog series below.
Part 1 – https://thwack.solarwinds.com/discussion/150152/mining-performance-data-from-sql-sentry-part-1
Part 2 – https://thwack.solarwinds.com/discussion/150153/mining-performance-data-from-sql-sentry-part-2
Part 3 – https://thwack.solarwinds.com/discussion/150154/mining-performance-data-from-sql-sentry-part-3
Part 4 – https://thwack.solarwinds.com/discussion/150156/mining-performance-data-from-sql-sentry-part-4
Part 5 – https://thwack.solarwinds.com/discussion/150158/mining-performance-data-from-sql-sentry-part-5
Part 6 – https://thwack.solarwinds.com/discussion/150160/mining-performance-data-from-sql-sentry-part-6

• All SQL Sentry reports are tied to Stored Procedures with the suffix “ReportProc_” and generally have names that logically map back to the reports themselves. Reviewing and/or duplicating/modifying these procs is a great way to get to desired data observer in a SQL Sentry report.
• There are several “GetPerformanceOverview%” procs that are a great option for broad aggregate information on all targets.
  • Examples:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


EXEC [dbo].[GetPerformanceOverviewDeviceMetrics] NULL, NULL, NULL, 60

EXEC [dbo].[GetPerformanceOverviewSQLConnectionMetrics] NULL, NULL, NULL, 60

• Root tables for monitored targets. Devices are parent objects (typically servers) with children Event Source Connections (typically SQL Server Instances)
• The ID column from these tables will respectively map to other tables DeviceID and EventSourceConnectionID columns to tie metrics, events, etc back to the target object
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT  d.ID AS 'DeviceID',d.HostName AS 'DeviceName', esc.ID AS 'escID', esc.ObjectName AS 'escName', d.IPAddress, esc.IsWatched
FROM Device d JOIN EventSourceConnection esc ON d.ID = esc.DeviceID
WHERE esc.EventSourceConnectionTypeID != '21B7F3F7-64C3-4434-BBCB-75B09E69507D'

• Database File Objects

• Logical Disk Objects

• Root Object table for Event Sources (Jobs, SSRS Reports, Agent Alerts, etc)

• Root object table for General/Failsafe/Audit Conditions

• Root object table for Advisory Conditions

• Navigator Sites and Groups
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT d.HostName, s.Name
FROM Device d JOIN Site s on d.SiteID = s.ID
ORDER BY s.Name

• Root object table for monitoring services
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT d.Hostname AS 'Target', me.ServerName AS 'ActiveMonitoringService', me.ServiceAccountName
FROM Device d JOIN ManagementEngine me on d.ManagementEngineID = me.ID
ORDER BY me.ServerName

• Tables in this schema are tied to the virtualization nodes in the Navigator pane.
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT * FROM vm.VCenterServer vcs
JOIN vm.HostSystem hs ON vcs.ID = hs.VCenterServerID
JOIN vm.VirtualMachine vm ON hs.ID = vm.HostSystemID

• Tables in this schema are tied to information surrounding AG’s/AlwaysOn tab
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT ag.Name as 'AG', ag.PrimaryReplica, ar.ReplicaServerName, ad.Name as 'Database' 
FROM AlwaysOn.AvailabilityGroup ag
JOIN AlwaysOn.AvailabilityReplica ar ON ag.GroupID = ar.GroupID
JOIN AlwaysOn.AvailabilityDatabase ad ON ar.GroupID = ad.GroupID
ORDER BY ag.Name

• Stores raw Top SQL data seen in Completed Queries
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT esc.ObjectName, td.*
FROM PerformanceAnalysisTraceData td
JOIN EventSourceConnection esc ON td.EventSourceConnectionID = esc.ID

• Stores events from the Procedure Stats tab of Top SQL. These are Procedures that have potential “Death by 1000 Cuts” behavior, executing very quickly, but at high volume.
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT
              esc.ObjectName AS 'SQLServerTarget',
              ps.ObjectName AS 'ProcName',
              DatabaseName, 
              SUM(ExecutionCountDelta) AS TotalExecutionCount,
              SUM(WorkerTimeDelta)/1000 AS TotalWorkerTimeMs,
              AVG(WorkerTimeDelta)/1000 AS AvgWorkerTimeMs,
              SUM(LogicalReadsDelta) AS TotalLogicalReads,
              AVG(LogicalReadsDelta) AS AvgLogicalReads,
              SUM(LogicalWritesDelta) AS TotalLogicalWrites,
              AVG(LogicalWritesDelta) AS AvgLogicalWrites,
              SUM(PhysicalReadsDelta) AS TotalPhysicalReads,
              AVG(PhysicalReadsDelta) AS AvgPhysicalReads,
              SUM(ElapsedTimeDelta) AS TotalElapsedTime,
              AVG(ElapsedTimeDelta) AS AvgElapsedTime,
              MIN(StartTimeUtc) AS StartTimeUtc,
              MAX(StartTimeUtc) AS EndTimeUtc,
       (SUM(ElapsedTimeDelta)/1000/1000) / SUM(ExecutionCountDelta) AS ExecutionCountperSec
FROM ProcedureStats ps            
JOIN ProcedureStatsHistory psh ON ps.ID = psh.ProcedureStatsID
JOIN EventSourceConnection esc ON ps.EventSourceConnectionID = esc.ID
GROUP BY esc.ObjectName, ps.ObjectName, DatabaseName
ORDER BY TotalWorkerTimeMs DESC

• Stores events from the Query Stats tab of Top SQL. These are queries that have potential “Death by 1000 Cuts” behavior, executing very quickly, but at high volume.
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT
              esc.ObjectName AS 'SQLServerTarget',
              qs.ObjectName AS 'ParentProcName',
              DatabaseName, 
              th.NormalizedTextData,
              SUM(ExecutionCountDelta) AS TotalExecutionCount,
              SUM(WorkerTimeDelta)/1000 AS TotalWorkerTimeMs,
              AVG(WorkerTimeDelta)/1000 AS AvgWorkerTimeMs,
              SUM(LogicalReadsDelta) AS TotalLogicalReads,
              AVG(LogicalReadsDelta) AS AvgLogicalReads,
              SUM(LogicalWritesDelta) AS TotalLogicalWrites,
              AVG(LogicalWritesDelta) AS AvgLogicalWrites,
              SUM(PhysicalReadsDelta) AS TotalPhysicalReads,
              AVG(PhysicalReadsDelta) AS AvgPhysicalReads,
              SUM(ElapsedTimeDelta) AS TotalElapsedTime,
              AVG(ElapsedTimeDelta) AS AvgElapsedTime,
              MIN(StartTimeUtc) AS StartTimeUtc,
              MAX(StartTimeUtc) AS EndTimeUtc,
(SUM(ElapsedTimeDelta)/1000/1000) / SUM(ExecutionCountDelta) AS ExecutionCountperSec             
FROM QueryStats qs
JOIN QueryStatsHistory qsh ON qs.ID = qsh.QueryStatsID
JOIN PerformanceAnalysisTraceHash th ON qs.NormalizedTextMD5 = th.NormalizedTextMD5
JOIN EventSourceConnection esc ON qs.EventSourceConnectionID = esc.ID
GROUP BY esc.ObjectName, qs.ObjectName, th.NormalizedTextData, DatabaseName
ORDER BY TotalWorkerTimeMs DESC

• Stores session usage data seen in the TempDB tab of Portal(Web-Client)

• Stores raw deadlock data. EventSourceConnectionID will map back to specific instance. There are also several other Deadlock tables for joining for getting App, User, Database, and Resource.
• Template Query

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT esc.ObjectName, NormalizedTextData, ApplicationName, DatabaseName, UserName, ResourceName, COUNT (*) AS TheCount  FROM PerformanceAnalysisTraceDeadlock patd
JOIN DeadlockTraceHashDeadlock dthd ON patd.ID = dthd.DeadlockID
JOIN DeadlockTraceHash dth ON dthd.DeadlockTraceHashID = dth.ID
JOIN EventSourceConnection esc ON patd.EventSourceConnectionID = esc.ID
JOIN PerformanceAnalysisTraceHash pth ON dth.NormalizedTextMD5 = pth.NormalizedTextMD5
JOIN DeadlockApplicationDeadlock dad ON patd.ID = dad.DeadlockID
JOIN DeadlockApplication da ON dad.DeadlockApplicationID = da.ID
JOIN DeadlockDatabaseDeadlock ddd ON patd.ID = ddd.DeadlockID
JOIN DeadlockDatabase dd ON ddd.DeadlockDatabaseID = dd.ID
JOIN DeadlockUserDeadlock dud ON patd.ID = dud.DeadlockID
JOIN DeadlockUser du ON dud.DeadlockUserID = du.ID
JOIN DeadlockResourceDeadlock drd ON patd.ID = drd.DeadlockID
JOIN DeadlockResource dr ON drd.DeadlockResourceID = dr.ID
WHERE  dthd.IsVictim = 0
AND NormalizedStartTime > DATEADD(HOUR, -24, getutcdate())
--AND StartTime > '2017-03-21 19:55:35.847' and StartTime < '2017-03-21 19:55:35.847'
--AND esc.ObjectName like '%<Target Name>%'
GROUP BY esc.ObjectName, dth.NormalizedTextMD5, NormalizedTextData, DatabaseName, ApplicationName, UserName, ResourceName
ORDER BY TheCount DESC

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT esc.ObjectName, NormalizedTextData, ApplicationName, DatabaseName, UserName, ResourceName, COUNT (*) AS TheCount  FROM PerformanceAnalysisTraceDeadlock patd
JOIN DeadlockTraceHashDeadlock dthd ON patd.ID = dthd.DeadlockID
JOIN DeadlockTraceHash dth ON dthd.DeadlockTraceHashID = dth.ID
JOIN EventSourceConnection esc ON patd.EventSourceConnectionID = esc.ID
JOIN PerformanceAnalysisTraceHash pth ON dth.NormalizedTextMD5 = pth.NormalizedTextMD5
JOIN DeadlockApplicationDeadlock dad ON patd.ID = dad.DeadlockID
JOIN DeadlockApplication da ON dad.DeadlockApplicationID = da.ID
JOIN DeadlockDatabaseDeadlock ddd ON patd.ID = ddd.DeadlockID
JOIN DeadlockDatabase dd ON ddd.DeadlockDatabaseID = dd.ID
JOIN DeadlockUserDeadlock dud ON patd.ID = dud.DeadlockID
JOIN DeadlockUser du ON dud.DeadlockUserID = du.ID
JOIN DeadlockResourceDeadlock drd ON patd.ID = drd.DeadlockID
JOIN DeadlockResource dr ON drd.DeadlockResourceID = dr.ID
WHERE  dthd.IsVictim = 1
AND NormalizedStartTime > DATEADD(HOUR, -24, getutcdate())
--AND esc.ObjectName like '%<Target Name>%'
GROUP BY esc.ObjectName, dth.NormalizedTextMD5, NormalizedTextData, DatabaseName, ApplicationName, UserName, ResourceName
ORDER BY TheCount DESC

• The BlockChainDetails table will have the individual events/SPIDs involved in a Blocking event, which can be mapped to a specific blocking event via the BlockChain table
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT COUNT(*) AS TheCount, esc.ObjectName, CommandText, DatabaseName, LoginName, ProgramName
FROM BlockChain bc
JOIN BlockChainDetail bcd ON bc.ID = bcd.BlockChainID
JOIN EventSourceConnection esc ON bc.EventSourceConnectionID = esc.ID
WHERE BlockedBySpid = 0
--AND Waittime > 60000 -- 1 Minute (Milliseconds)
--AND StartTime > '2017-03-21 19:55:35.847' and StartTime < '2017-03-21 19:55:35.847'
--AND esc.ObjectName like '%<Target Name>%'
--AND Database = ''
GROUP BY esc.ObjectName, NormalizedTextMD5, CommandText,  DatabaseName, LoginName, ProgramName
ORDER BY theCount DESC

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT COUNT(*) AS TheCount, esc.ObjectName, CommandText, DatabaseName, LoginName, ProgramName, WaitText, AVG(CAST(waittime AS float))/1000 AS AvgWaitTimeSeconds, SUM(CAST(waittime AS float))/1000 AS TotalWaitTimeSeconds 
FROM BlockChain bc
JOIN BlockChainDetail bcd ON bc.ID = bcd.BlockChainID
JOIN EventSourceConnection esc ON bc.EventSourceConnectionID = esc.ID
WHERE BlockedBySpid != 0
--AND Waittime > 60000 -- 1 Minute (Milliseconds)
--AND StartTime > '2017-03-21 19:55:35.847' and StartTime < '2017-03-21 19:55:35.847'
--AND esc.ObjectName like '%<Target Name>%'
--AND Database = ''
GROUP BY esc.ObjectName, NormalizedTextMD5, CommandText,  WaitText, DatabaseName, LoginName, ProgramName
ORDER BY theCount DESC

• Start Page/Environmental Health Score Events

• All Alert/Action history (Action Log)
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT ConditionTypeName AS Condition,
       Count(*) AS TheCount
FROM   vwObjectConditionActionHistory ocah
WHERE  NormalizedEventStartTime > DATEADD(day, -1, GETUTCDATE())
      -- AND CategoryName = 'General Conditions' -- See ConditionTypeCategory table
         -- AND ActionTypeName = 'Send Email' -- See ActionType table
         -- AND ParentObjectName = '<Target Name>'
GROUP  BY ConditionTypeName
ORDER  BY TheCount DESC

• All available counters for collection and where you can get the PerformanceAnalysisCounterID for desired counters.  Category table can be used to look up counters by category (i.e. 9 = PHYSICALDISK)
• If SampleIntervalID = 0, then this counter is not actively being collected, but can be by updating to ID of desired Sample Interval.
  • Enabling Additional Performance Counters
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT pac.ID AS 'CounterID', CounterName, CategoryResourceName, pac.PerformanceAnalysisSampleIntervalID, IsDeviceLevel
FROM PerformanceAnalysisCounter  pac
JOIN PerformanceAnalysisCounterCategory pacc ON pac.PerformanceAnalysisCounterCategoryID = pacc.ID

• Holds most of the raw detailed performance counter data.
• Template Query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


SELECT dbo.fnConvertTimestampToDateTime(Timestamp),*
FROM PerformanceAnalysisData
WHERE PerformanceAnalysisCounterID = 1858
AND DeviceID = 1 AND InstanceName = '_Total'

• Holds most of the raw detailed data for database and disk related performance counters.

• Rollup tables hold the increasing aggregation of data as it ages.
• See documentation for Retention/Resolutions for reference.