| Author |
Message |
lehrsj24
Joined: 02 Jan 2011
Posts: 10
|
 Posted: Mon Jan 17, 2011 9:46 pm Post subject: Data dictionary? |
 |
|
I'd like to do some reports against some of the data in the SQLMonitor database. Is there any type of documentation on how the data all relates? Some of the data is pretty self explanetory but others is not.
thanks,
Sherry |
|
| Back to top |
|
 |
benrees
Joined: 27 Sep 2010
Posts: 16
|
| Posted: Mon Jan 17, 2011 10:36 pm Post subject: |
|
|
Hi Lehrsj24
We're going to provide some sample SSRS reports soon, to help people get started, but you're right - though some of the schema is obvious, not all of it is. NB: We've added a number of VIEWs to make some of the obvious joins so that you don't have to (for example, joining various data tables to the server reference tables).
I'll add a data dictionary to the plans - are there any particular pieces of data you were after in the mean time?
Ben |
|
| Back to top |
|
|
lehrsj24
Joined: 02 Jan 2011
Posts: 10
|
| Posted: Mon Jan 17, 2011 11:13 pm Post subject: |
|
|
I'll take a look at the views.
As an example, I have alertID 70009 that is for a SQL server job that failed. On the web page when I click on the alert I see the error message for the failed job:
Job outcome message: The job failed. The Job was invoked by Schedule 13 (Sundays). The last step to run was step 3 (CaptureProject). NOTE: Failed to notify ....
There is a view called data.cluster_sqlserver_agent_job_history_instances_view.
In this view I see ID 906119 that a field for Cluster_SQLServer_Agent_Job_History_Message, which is the error message for this alert. It matches the server from the alert, the time, etc.(actually I started in this view, found an error, and then traced it back to the alert).
If I have the AlertID from the alert.Alert table how can I get to this view for the error message?
Thanks |
|
| Back to top |
|
|
benrees
Joined: 27 Sep 2010
Posts: 16
|
| Posted: Wed Jan 19, 2011 12:50 pm Post subject: |
|
|
Hi there
I'm taking a look at this request, though I'm afraid it's not as trivial as some other possible reports! I'll post back as soon as I've put some T-SQL together..
Apologies for the delay.
Regards
Ben |
|
| Back to top |
|
|
benrees
Joined: 27 Sep 2010
Posts: 16
|
| Posted: Thu Jan 20, 2011 11:19 am Post subject: |
|
|
Hi lehrsj24
Below is some code I've put together linking the Alerts VIEW [alert].[Alert_Current] and the Job History VIEW [data].[Cluster_SqlServer_Agent_Job_History_Instances_View]. I've put the most relevant info in the first few columns (alert ID, error message, date/time of alert), but have also included other columns in case you're interested in these.
NB: I've put this together quite hastily - let me know if you have any problems with this code and I'll try to fix it!
Hope this helps.
Ben Rees
| Code: |
SELECT AlertID ,
Cluster_SqlServer_Agent_Job_History_Message ,
DateTimeRaised ,
Id AS Job_History_Id ,
IdCollectionDate ,
Cluster_SqlServer_Agent_Job_History_RunDuration ,
Cluster_SqlServer_Agent_Job_History_RunStatus ,
Cluster_SqlServer_Agent_Job_History_SqlMessageId ,
Cluster_SqlServer_Agent_Job_History_SqlSeverity ,
CollectionDate ,
CollectionDate_DateTime ,
Cluster_Name ,
Cluster_SqlServer_Name ,
Cluster_SqlServer_Agent_Job_Id ,
Cluster_SqlServer_Agent_Job_History_Id ,
Cluster_SqlServer_Agent_Job_History_RunDate ,
Cluster_SqlServer_Agent_Job_History_RunDate_DateTime ,
AlertType ,
AlertDescription ,
AlertTicks
FROM ( SELECT [Id] ,
[IdCollectionDate] ,
[Cluster_SqlServer_Agent_Job_History_Message] ,
[Cluster_SqlServer_Agent_Job_History_RunDuration] ,
[Cluster_SqlServer_Agent_Job_History_RunStatus] ,
[Cluster_SqlServer_Agent_Job_History_SqlMessageId] ,
[Cluster_SqlServer_Agent_Job_History_SqlSeverity] ,
[CollectionDate] ,
[CollectionDate_DateTime] ,
[Cluster_Name] ,
[Cluster_SqlServer_Name] ,
[Cluster_SqlServer_Agent_Job_Id] ,
[Cluster_SqlServer_Agent_Job_History_Id] ,
[Cluster_SqlServer_Agent_Job_History_RunDate] ,
[Cluster_SqlServer_Agent_Job_History_RunDate_DateTime]
FROM [data].[Cluster_SqlServer_Agent_Job_History_Instances_View]
) AS jh
INNER JOIN ( SELECT SUBSTRING(TargetObject,
21 + PATINDEX('%:%',
SUBSTRING(TargetObject, 21,
4)),
CAST(SUBSTRING(TargetObject, 21,
PATINDEX('%:%',
SUBSTRING(TargetObject,
21, 4)) - 1) AS INT)) AS ClusterName ,
CASE WHEN PATINDEX('%7:Machine,1,4:Name%',
TargetObject) > 0
THEN SUBSTRING(TargetObject,
PATINDEX('%7:Machine,1,4:Name%',
TargetObject) + 20
+ PATINDEX('%:%',
SUBSTRING(TargetObject,
PATINDEX('%7:Machine,1,4:Name%',
TargetObject)
+ 20, 4)),
CAST(SUBSTRING(TargetObject,
PATINDEX('%7:Machine,1,4:Name%',
TargetObject)
+ 20,
PATINDEX('%:%',
SUBSTRING(TargetObject,
PATINDEX('%7:Machine,1,4:Name%',
TargetObject)
+ 20, 4)) - 1) AS INT))
ELSE ''
END AS MachineName ,
CASE WHEN PATINDEX('%9:SqlServer,1,4:Name%',
TargetObject) > 0
THEN SUBSTRING(TargetObject,
PATINDEX('%9:SqlServer,1,4:Name%',
TargetObject) + 22
+ PATINDEX('%:%',
SUBSTRING(TargetObject,
PATINDEX('%9:SqlServer,1,4:Name%',
TargetObject)
+ 22, 4)),
CAST(SUBSTRING(TargetObject,
PATINDEX('%9:SqlServer,1,4:Name%',
TargetObject)
+ 22,
PATINDEX('%:%',
SUBSTRING(TargetObject,
PATINDEX('%9:SqlServer,1,4:Name%',
TargetObject)
+ 22, 4)) - 1) AS INT))
ELSE ''
END AS SQLServerName ,
at.Name AS AlertType ,
at.Description AS AlertDescription ,
[utils].[TicksToDateTime](ai.Raised) AS DateTimeRaised ,
ai.Raised AS AlertTicks ,
ai.AlertId AS AlertID
FROM alert.Alert_Current AS ai
INNER JOIN alert.Alert_Type AS at ON ai.AlertType = at.AlertType
) AS ad ON ad.ClusterName = jh.Cluster_Name
AND ad.SQLServerName = jh.Cluster_SqlServer_Name
WHERE ad.AlertType = 'Job failed'
AND AlertTicks = Cluster_SqlServer_Agent_Job_History_RunDate
ORDER BY ad.DateTimeRaised DESC |
|
|
| Back to top |
|
|
Dmacintire
Joined: 01 Feb 2011
Posts: 1
Location: Madison, Wisconsin, USA
|
| Posted: Tue Feb 01, 2011 3:49 pm Post subject: Server status view |
|
|
| Looking for a view or data dic so I can create my own, that will show the monitored server, mem disk space, OS etc, and databases and database size. Can you point me in the right direction. I want to pull this into a sharepoint list as a referance point. |
|
| Back to top |
|
|
benrees
Joined: 27 Sep 2010
Posts: 16
|
| Posted: Tue Feb 01, 2011 4:48 pm Post subject: |
|
|
Hi there
We have put in a number of VIEWs which should, hopefully, help you create reports or queries for the sort of data you’re looking for.
The most interesting VIEWs can be found under [data].xxxxx_View. A lot of these should be self-explanatory. For example, looking at the information that you mention:
Monitored Servers with OS info etc
This can be found in the view
| Code: |
| [data].[Cluster_SqlServer_Properties_StableSamples_View] |
Mem Disk Space
Memory values can be found in
| Code: |
| [data].[Cluster_Machine_Memory_UnstableSamples_View] |
Disk space can be found in
| Code: |
| [data].[Cluster_Machine_LogicalDisk_UnstableSamples_View] |
Databases
Lists of databases can be found in
| Code: |
| [data].[Cluster_SqlServer_Database_StableSamples_View] |
though this needs to be grouped (there is more than one row per database as this table also includes some availability information.)
Database Size
This can be found in
| Code: |
| [data].[Cluster_SqlServer_Database_Storage_UnstableSamples_View] |
NB: There’s also a couple of functions to convert between Ticks (as used in many views and tables) and proper date times,
| Code: |
| [utils].[DateTimeToTicks] |
and
| Code: |
| [utils].[TicksToDateTime] |
Hope that helps!
Ben |
|
| Back to top |
|
|
MatthewGantz
Joined: 15 Sep 2011
Posts: 1
|
| Posted: Thu Sep 15, 2011 7:49 pm Post subject: |
|
|
I've been working with extracting some of the data out of the repository, and I'd like to add to the requests for a data dictionary. I used the ssrs-pack reports as a starting point (http://www.red-gate.com/products/dba/sql-monitor/ssrs-pack), but I do have some questions. Currently, I am attempting to graph average IO in MB/Sec.
The column
| Code: |
data.[Cluster_Machine_LogicalDisk_UnstableSamples_View].Cluster_Machine_LogicalDisk_CumulativeReadBytes |
seems like it should match the sum of the values in
| Code: |
| sys.dm_io_virtual_file_stats() |
for all of the files on the drive; however these values are way off. Could you shed some light?
Here is my code, in case it is useful to anyone (this does create values that seem to match what is in Activity Monitor, but I haven't done any in-depth validation):
| Code: |
DECLARE @servername VARCHAR(100) ,
@disk CHAR(1) ,
@pStart DATETIME ,
@pEnd DATETIME
SELECT @servername = 'myserver'
SELECT @pEnd = '20110915'
SELECT @pStart = '20110914'
SELECT @disk = 'I' ;
--Set Default Dates if none provided
IF @pEnd IS NULL
SELECT @pEnd = GETDATE() ;
IF @pStart IS NULL
SELECT @pStart = DATEADD(HOUR, -2, @pEnd)
--Set disk and server to proper case used by DB
SELECT @disk = UPPER(@disk) ,
@servername = LOWER(@servername) ;
--Create CTE with IO info for time period
WITH Disk_CTE
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CollectionDate DESC ) AS rownum ,
cs.CollectionDate ,
cs.Cluster_Machine_LogicalDisk_CumulativeReadBytes ,
cs.Cluster_Machine_LogicalDisk_CumulativeWriteBytes
FROM data.[Cluster_Machine_LogicalDisk_UnstableSamples_View]
AS cs
WHERE cs.Cluster_Name = @servername
AND cs.Cluster_Machine_LogicalDisk_Name = @disk + ':'
AND CollectionDate BETWEEN utils.DateTimeToTicks(@pStart)
AND utils.DateTimeToTicks(@pEnd)
),
--Create CTE with Deltas between measurements
Delta_CTE
AS ( SELECT ( CAST(( DATEDIFF(ms, utils.DateTimeToTicks(earlier.CollectionDate),
utils.DateTimeToTicks(later.CollectionDate) )) AS FLOAT)
/ 1000 ) AS TimeDiff ,
later.CollectionDate AS DateInt ,
( ( later.Cluster_Machine_LogicalDisk_CumulativeReadBytes
- earlier.Cluster_Machine_LogicalDisk_CumulativeReadBytes )
/ ( 1024 * 1024 ) ) AS MBReadDiff ,
( ( later.Cluster_Machine_LogicalDisk_CumulativeWriteBytes
- earlier.Cluster_Machine_LogicalDisk_CumulativeWriteBytes )
/ ( 1024 * 1024 ) ) AS MBWriteDiff ,
CONVERT(CHAR(12), utils.DateTimeToTicks(later.CollectionDate), 106) AS CollectionDate ,
CONVERT(SMALLDATETIME, utils.DateTimeToTicks(earlier.CollectionDate), 106) AS CollectionDateTime ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 10 ) * 10,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Ten Min] ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 30 ) * 30,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Thirty Min] ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 60 ) * 60,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Hour]
FROM disk_CTE AS later
INNER JOIN disk_CTE AS earlier ON later.rownum = earlier.rownum
- 1
)
--Create the final Dataset to be consumed by SSRS
SELECT Delta_CTE.CollectionDateTime ,
CASE WHEN TimeDiff = 0 THEN 0
ELSE CAST(Delta_CTE.MBReadDiff / TimeDiff AS NUMERIC(18, 2))
END AS MBReadPerSec ,
CASE WHEN TimeDiff = 0 THEN 0
ELSE CAST(Delta_CTE.MBWriteDiff / TimeDiff AS NUMERIC(18, 2))
END AS MBWritePerSec ,
Delta_CTE.[Ten Min] ,
Delta_CTE.[Thirty Min] ,
Delta_CTE.[Hour]
FROM Delta_CTE
ORDER BY Delta_CTE.DateInt ASC
|
|
|
| Back to top |
|
|
priyasinha
Joined: 03 Jan 2007
Posts: 483
|
| Posted: Fri Sep 16, 2011 9:18 am Post subject: |
|
|
Hi Matthew,
I haven't looked at your code but as far as I understand sys.dm_io_virtual_file_stats returns I/O stats only for data and log files whereas data stored in Logical Disk performance objects relates to everything on that disk which will include non SQL Server data and log files too.
Thanks,
Priya |
|
| Back to top |
|
|
|
