Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

Browse dimension tables and retrieve data

Use Spotlight API stored procedures to browse dimension tables in the repository. Then use additional stored procedures or custom T-SQL to retrieve data.

Scenario

Your manager has asked you to provide statistics on database performance for the last quarter. You decide to query the Spotlight Statistics Repository to return file I/O statistics for the databases your manager is interested in.

To retrieve this data, you first browse the dimension tables to become familiar with the data in the Spotlight Statistics Repository. Once familiar, you write queries using the Spotlight stored procedures to get data from the fact table. You then refine the queries using custom T-SQL to retrieve more specific data from the Spotlight Statistics Repository.

Browse dimension tables

First up, we’re going to use some of the Spotlight API stored procedures to browse the dimension tables and get the range of values available for use in our queries. For more information on the Spotlight stored procedures used here, see Stored procedures and Specifications.

In SQL Server Management Studio, create a new query against the Spotlight Statistics Repository. We want to know the names of the available data sources and the names and domains of the monitored objects.

Let’s start exploring the Spotlight Statistics Repository by looking at datasources. Datasources are broad categories of data.

To get a list of data sources in the repository, run the following query:

exec spotlight_get_datasources

This returns the following results:

We can see here that we have data from Windows, SQL Server, the Diagnostic Server and alarms (availability).

Now we want to know the name of the Diagnostic Server storing data in the Spotlight Statistics Repository. We can determine this by running:

exec spotlight_get_domains

To get the following result:

We can see here that there is one Diagnostic Server writing data to this repository. There can be multiple Diagnostic Servers, each one resulting in a row.

Finally, we want to know information about the servers being monitored.

We can do this by running the following query:

exec spotlight_get_monitored_objects

To get the following results:

A common question is “what is the range of timestamps available for a monitored server?” The following query returns the time ranges for all monitored servers:

select

so.monitored_object_name,

min(st.timecollected) as 'start',

max(st.timecollected) as 'end'

from

spotlight_timestamps st

join spotlight_monitored_objects so on st.monitored_object_id = so.monitored_object_id

group by

so.monitored_object_name

Now that we have some information about the Diagnostic Server, monitored servers and categories of collections in the Spotlight Statistics Repository, we want to know what statistics are available to us.

The spotlight_get_tables stored procedure returns a list of tables in the repository for a given data source. Since we are looking for SQL Server data, from our first query above, we know that the datasource is “sqlserver_spotlight”, so we will use that as the parameter in the following query:

exec spotlight_get_tables 'sqlserver_spotlight'

The following results are returned.

We’re going to look at virtual file stats as this will give us the file IO statistics we need for our report.

To find out what columns are available to us in the virtual file stats table use the following query. With this query we need to specify the datasource and table name.

exec spotlight_get_table_columns 'sqlserver_spotlight','virtualfilestats'

We need to produce the report over a time range, but for curiosity’s sake, we would like to find out the time range of all stored data for a particular domain name, monitored object, and table combination.

To do this, use the spotlight_get_table_span stored procedure and specify the domain name, monitored object, and table name.

exec spotlight_get_table_span 'DS123:3843','Windows01_SQLServer789_sqlserver','virtualfilestats'

Query the fact table

Now we can put it all together and query the fact table. We’ll use the spotlight_get_table_range stored procedure and specify the following:

  • start date
  • end date
  • domain name
  • monitored object
  • table name

Spotlight_get_table_range returns data for the requested time range for a table, for a particular monitored object and domain.

This gives us the following query:

exec spotlight_get_table_range '2009-03-26 11:15:16.153','2009-07-26 17:15:17.113', 'DS123:3843','Windows01_SQLServer789_sqlserver', 'virtualfilestats'

Which returns all columns for the table ‘virtualfilestats’:

Although there is a lot of useful data in the query results, it’s not exactly what we are looking for. We need to refine the data returned from the Spotlight Statistics Repository. Following on from the example above, if we want to retrieve only certain columns from the ‘virtualfilestats’ table, we can use custom T-SQL to return specific data.

To return only the ‘iorate’, ‘disk’, ‘readsrate’, and ‘writesrate’ columns from the ‘virtualfilestats’ table, we can use the following custom T-SQL:

select

sp.timecollected,

max(case when sn.statistic_name = 'dbname' then sp.raw_value end) as 'dbname',

max(case when sn.statistic_name = 'dbfilename' then sp.raw_value end) as 'dbfilename',

max(case when sn.statistic_name = 'disk' then sp.raw_value end) as 'disk',

max(case when sn.statistic_name = 'iorate' then sp.raw_value end) as 'iorate',

max(case when sn.statistic_name = 'readsrate' then sp.raw_value end) as 'readsrate',

max(case when sn.statistic_name = 'writesrate' then sp.raw_value end) as 'writesrate'

from

spotlight_perfdata sp

join spotlight_stat_classes sc on sp.statistic_class_id = sc.statistic_class_id

join spotlight_stat_names sn on sp.statistic_name_id = sn.statistic_name_id

join spotlight_monitored_objects so on sp.monitored_object_id = so.monitored_object_id

where

sc.statistic_class_name = 'virtualfilestats'

and so.monitored_object_name = 'Windows01_SQLServer789_sqlserver'

and sp.timecollected between '2009-09-01' and '2009-09-30'

group by

sp.timecollected, sp.statistic_key_id

order by

sp.timecollected

This gives us the following results:

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating