Red Gate forums :: View topic - Data dictionary?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 2
SQL Monitor 2 forum

Data dictionary?

Search in SQL Monitor 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
lehrsj24



Joined: 02 Jan 2011
Posts: 10

PostPosted: Mon Jan 17, 2011 9:46 pm    Post subject: Data dictionary? Reply with quote

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
View user's profile Send private message
benrees



Joined: 27 Sep 2010
Posts: 16

PostPosted: Mon Jan 17, 2011 10:36 pm    Post subject: Reply with quote

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
View user's profile Send private message
lehrsj24



Joined: 02 Jan 2011
Posts: 10

PostPosted: Mon Jan 17, 2011 11:13 pm    Post subject: Reply with quote

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
View user's profile Send private message
benrees



Joined: 27 Sep 2010
Posts: 16

PostPosted: Wed Jan 19, 2011 12:50 pm    Post subject: Reply with quote

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
View user's profile Send private message
benrees



Joined: 27 Sep 2010
Posts: 16

PostPosted: Thu Jan 20, 2011 11:19 am    Post subject: Reply with quote

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
View user's profile Send private message
Dmacintire



Joined: 01 Feb 2011
Posts: 1
Location: Madison, Wisconsin, USA

PostPosted: Tue Feb 01, 2011 3:49 pm    Post subject: Server status view Reply with quote

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
View user's profile Send private message Send e-mail
benrees



Joined: 27 Sep 2010
Posts: 16

PostPosted: Tue Feb 01, 2011 4:48 pm    Post subject: Reply with quote

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
View user's profile Send private message
MatthewGantz



Joined: 15 Sep 2011
Posts: 1

PostPosted: Thu Sep 15, 2011 7:49 pm    Post subject: Reply with quote

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
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Fri Sep 16, 2011 9:18 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group