Data dictionary?

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Data dictionary?

Postby lehrsj24 » Mon Jan 17, 2011 9:46 pm

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
lehrsj24
 
Posts: 10
Joined: Sun Jan 02, 2011 2:57 am

Postby benrees » Mon Jan 17, 2011 10:36 pm

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
benrees
 
Posts: 16
Joined: Mon Sep 27, 2010 9:31 am

Postby lehrsj24 » Mon Jan 17, 2011 11:13 pm

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
lehrsj24
 
Posts: 10
Joined: Sun Jan 02, 2011 2:57 am

Postby benrees » Wed Jan 19, 2011 12:50 pm

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
benrees
 
Posts: 16
Joined: Mon Sep 27, 2010 9:31 am

Postby benrees » Thu Jan 20, 2011 11:19 am

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 all
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
benrees
 
Posts: 16
Joined: Mon Sep 27, 2010 9:31 am

Server status view

Postby Dmacintire » Tue Feb 01, 2011 3:49 pm

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.
Dmacintire
 
Posts: 1
Joined: Tue Feb 01, 2011 3:44 pm
Location: Madison, Wisconsin, USA

Postby benrees » Tue Feb 01, 2011 4:48 pm

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: Select all
[data].[Cluster_SqlServer_Properties_StableSamples_View]


Mem Disk Space

Memory values can be found in
Code: Select all
 [data].[Cluster_Machine_Memory_UnstableSamples_View]

Disk space can be found in
Code: Select all
 [data].[Cluster_Machine_LogicalDisk_UnstableSamples_View]


Databases

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


Hope that helps!

Ben
benrees
 
Posts: 16
Joined: Mon Sep 27, 2010 9:31 am

Postby MatthewGantz » Thu Sep 15, 2011 7:49 pm

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
Code: Select all
data.[Cluster_Machine_LogicalDisk_UnstableSamples_View].Cluster_Machine_LogicalDisk_CumulativeReadBytes
seems like it should match the sum of the values in
Code: Select all
 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: Select all
 
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
MatthewGantz
 
Posts: 1
Joined: Thu Sep 15, 2011 7:37 pm

Postby priyasinha » Fri Sep 16, 2011 9:18 am

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm


Return to SQL Monitor 2

Who is online

Users browsing this forum: No registered users and 0 guests