Qeurying SQL Monitor 4.x Repository

jankla

Hi All,

I am searching for means of qeurying the SQL MOnitor 4.x Data repository. How about sharing findings sofar?

My contributions:

--- Overview Servers, instances and databases:

use [RedGateSQLMonitoring]

SELECT clstr._Name AS cluster_name,
srvr._Name AS instance_name,
db._Name AS database_name
FROM data.Cluster_SqlServer_Database_Keys db
JOIN
data.Cluster_SqlServer_Keys srvr
ON
db.ParentId = srvr.Id -- Note here how the parent of a Database is a Server
JOIN
data.Cluster_Keys clstr
ON
srvr.ParentId = clstr.Id -- Note here how the parent of a Server is a Cluster
--WHERE clstr._Name = '<name of sqlserver>'
ORDER BY clstr._Name,
srvr._Name,
db._Name;

--- Data Schema:

SELECT sch.name + '.' + obj.name AS [name]
FROM sys.objects obj
JOIN
sys.schemas sch
ON
sch.schema_id = obj.schema_id
WHERE obj.type_desc = 'USER_TABLE'
AND sch.name = 'data'
ORDER BY sch.name,
obj.name;


--- Batch requests/sec for a certain server:


use [RedGateSQLMonitoring]

DECLARE @startDateUtc BIGINT;
DECLARE @endDateUtc BIGINT;
DECLARE @machineName NVARCHAR(max);
DECLARE @sqlServerInstanceName NVARCHAR(max);
DECLARE @maxDataPoints BIGINT;
DECLARE @ticksPerSecond FLOAT(24);

-- SET THESE THINGS --
SET @startDateUtc = utils.DateTimeToTicks('2015-04-01 14:30');
SET @endDateUtc = utils.DateTimeToTicks('2015-04-30 15:00');
SET @machineName = '<sql server you require this info for>' -- set this to the network name of the machine or cluster hosting the SQL Server Instance
SET @sqlServerInstanceName = '' -- set this to the name of the SQL Server Instance, or leave empty for unnamed instances
SET @maxDataPoints = 500;
--SET @maxDataPoints = 50000;
-- END SET THESE THINGS --

SET @ticksPerSecond = 10000000.0;

;WITH Cte AS (
SELECT Id,
Cluster_Name AS [Machine Name],
Cluster_SqlServer_Name AS [SQL Instance Name],
CollectionDate AS [Collection Date (ticks)],
CollectionDate_DateTime AS [Collection Date],
--Cluster_SqlServer_SqlStatistics_CumulativeBatchRequests AS [Cumulative Batch Requets],
Cluster_SqlServer_SqlStatistics_CumulativeBatchRequests AS [Cumulative Batch Requets],
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CollectionDate ASC) AS [row]
FROM data.Cluster_SqlServer_SqlStatistics_Unstablesamples_DateRange(@startDateUtc, @endDateUtc, @maxDataPoints)
WHERE Cluster_Name = @machineName
AND Cluster_SqlServer_Name = @sqlServerInstanceName
)
SELECT aft.[Machine Name] ,
aft.[SQL Instance Name] ,
aft.[Collection Date] ,
@ticksPerSecond*(aft.[Cumulative Batch Requets] - bef.[Cumulative Batch Requets])/(aft.[Collection Date (ticks)] - bef.[Collection Date (ticks)]) AS [Batch requests/sec]
FROM Cte aft
INNER JOIN Cte bef ON aft.Id = bef.Id AND aft.row = bef.row + 1

-- User connections

use [RedGateSQLMonitoring]

DECLARE @startDateUtc BIGINT;
DECLARE @endDateUtc BIGINT;
DECLARE @machineName NVARCHAR(max);
DECLARE @sqlServerInstanceName NVARCHAR(max);
DECLARE @maxDataPoints BIGINT;
DECLARE @ticksPerSecond FLOAT(24);

-- SET THESE THINGS --
SET @startDateUtc = utils.DateTimeToTicks('2015-04-01 14:30');
SET @endDateUtc = utils.DateTimeToTicks('2015-04-30 15:00');
SET @machineName = '<name of sql server you require this info for>' -- set this to the network name of the machine or cluster hosting the SQL Server Instance
SET @sqlServerInstanceName = '' -- set this to the name of the SQL Server Instance, or leave empty for unnamed instances
SET @maxDataPoints = 500;
--SET @maxDataPoints = 50000;
-- END SET THESE THINGS --

SET @ticksPerSecond = 10000000.0;

;WITH Cte AS (

SELECT Id AS [id],
Cluster_Name AS [Machine Name],
Cluster_SqlServer_Name AS [SQL Instance Name],
CollectionDate AS [Collection Date (ticks)],
CollectionDate_DateTime AS [Collection Date],
Cluster_sqlserver_GeneralStatistics_UserConnections AS [User_Connections],
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CollectionDate ASC) AS [row]
FROM data.Cluster_SqlServer_GeneralStatistics_UnstableSamples_DateRange(@startDateUtc, @endDateUtc, @maxDataPoints)
WHERE Cluster_Name = @machineName
AND Cluster_SqlServer_Name = @sqlServerInstanceName
)

SELECT aft.[Machine Name] ,
aft.[SQL Instance Name] ,
aft.[Collection Date],
aft.User_Connections
FROM Cte aft
INNER JOIN Cte bef ON aft.Id = bef.Id AND aft.row = bef.row + 1


Please add your own usefull qeuries..


Kind Regards,
Jan

Alex B

Hi Jan,

Here is a link to some blog posts on Simple Talk related to querying against the Data Repository. Note that despite these blog posts being relatively old, the areas of the schema that it describes haven't changed much with subsequent development work (ie, we've largely extended the schema, rather than modifying it).

Kind regards,
Alex