Qeurying SQL Monitor 4.x Repository

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