Data dictionary?

lehrsj24lehrsj24 Posts: 13 Bronze 2
edited September 16, 2011 4:18AM in SQL Monitor Previous Versions
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

Comments

  • benreesbenrees Posts: 16 Bronze 1
    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
  • lehrsj24lehrsj24 Posts: 13 Bronze 2
    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
  • benreesbenrees Posts: 16 Bronze 1
    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
  • benreesbenrees Posts: 16 Bronze 1
    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

    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
    
  • 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.
  • benreesbenrees Posts: 16 Bronze 1
    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
    [data].[Cluster_SqlServer_Properties_StableSamples_View]
    

    Mem Disk Space

    Memory values can be found in
    [data].[Cluster_Machine_Memory_UnstableSamples_View]
    
    Disk space can be found in
    [data].[Cluster_Machine_LogicalDisk_UnstableSamples_View]
    

    Databases

    Lists of databases can be found in
    [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
    [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,
    [utils].[DateTimeToTicks]
    
    and
    [utils].[TicksToDateTime]
    

    Hope that helps!

    Ben
  • 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/sq ... /ssrs-pack), but I do have some questions. Currently, I am attempting to graph average IO in MB/Sec.

    The column
    data.[Cluster_Machine_LogicalDisk_UnstableSamples_View].Cluster_Machine_LogicalDisk_CumulativeReadBytes
    
    seems like it should match the sum of the values in
    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):
     
    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
    
  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
Sign In or Register to comment.