Chat now with support
Chat with Support

Foglight for SQL Server 5.7.5.41 - PI Schema Reference Guide

SQL PI Repository Schema SSAS SQL PI Repository Schema

SQL Server Dimensions

The SQL Server dimensions include:

•
SQL Statements — The executed SQL commands
•
TSQL Batches TSQL Batch — The set of TSQL commands that are sent to execution together, usually corresponding to a single business transaction. TSQL Batch usually end with a GO command. Includes:
•
Stored Procedure
•
Functions
•
Triggers
•
Programs — Name of the programs connecting to SQL Server and executing the SQL statements
•
Users — SQL Server login names used by the program to connect to the SQL Server instance.
•
Command Types — Executed SQL command type (for example INSERT, SELECT, and so on).
•
Databases — The database context in which the session read and wrote data. A session may switch to numerous databases within its lifetime.
•
Client Machines — The machines on which the client executable (connected to SQL Server) is running.
•
Context Info — Optional trace information that a session can create using the SET CONTEXT_INFO command.
•
Sessions — Sessions details

Collected Metrics

There are 3 types of metrics collected

1
Statistics - Performance and environmental data that describes the relational SQL Server database performance such as:
a
num_executions — The number of execution times of the SQL.
b
row_count — The average amount of rows returned by the query
c
cpu_time — The CPU time consumed by the SQL.
d
elapsed_time — The total time for the SQL to be received, executed and the results sent back.
2
Wait events — Reveals wait-event data down to the statement level, speeding resolution of resource-related performance problems. All the waits that are collected described in the table pass_wait_event_dim.
3
Wait categories and sub categories — Allows wait categories and events to be collected based on the resource consumed and allows you to identify the root cause of any performance issue. For example:
a
Categories- wait_io, wait_network, wait_lock etc…,
a
Sub Categories- s_wait_lock_exclusive, s_wait_network_io, s_wait_io_completion etc…

For more information, see the following topics:

•
Schema Tables
•
Samples queries
•
General Tables
•
Dimension Tables
•
Performance Metric (Fact) Tables
•
Change Tracking Data

Schema Tables

SQL PI Repository technology allows you to save data for a long period of time. The Time-Pyramid structure for some of the tables uttered by the table names.

For example:

•
1m - means 1 minute interval in some of the 1 minute table there is a sub categorization. The sub categorization allows to use temporary tables that are relevant to a specific timeframe usually 6 hours. Those tables have a name format as follow:
•
YYYYMMDD>0000 (between 00-06)
•
YYYYMMDD>0600 (between 06-12)
•
YYYYMMDD>1200 (between 12-18)
•
YYYYMMDD>1800 (between 18-24)
•
5m - means 15 minutes interval
•
1h - means 1h interval
•
6h - means 6h interval
•
1d - means 1 day interval
•
1w - means 1 week interval

Samples queries

Get the monitored instances details (includes instance#):

select * from pass_instance_dim

Get the instance wait event summary:

SELECT SUM(wait_clr) AS sum_wait_clr, SUM(wait_memory) AS sum_wait_memory, SUM(wait_remote_provider) AS sum_wait_remote_provider, SUM(wait_for_cpu) AS sum_wait_for_cpu, SUM(cpu_time) AS sum_cpu_time, SUM(active_time) AS sum_active_time, SUM(wait_log) AS sum_wait_log, SUM(elapsed_time) AS sum_elapsed_time, SUM(wait_latch) AS sum_wait_latch, SUM(wait_network) AS sum_wait_network, SUM(wait_other) AS sum_wait_other, SUM(wait_lock) AS sum_wait_lock, SUM(wait_io) AS sum_wait_io, SUM(wait_xtp) AS sum_wait_xtp, SUM(num_executions) AS sum_num_executions, MIN(sample_starttime) AS min_sample_starttime, MAX(sample_endtime) AS max_sample_endtime FROM pass_instance_stat_fact_1m WHERE sample_endtime >= ? AND sample_starttime <= ? AND instance_key = ?

Parameters example: ['YYYY-MM-DD 10:54:10.513+0200', 'YYYY-MM-DD 11:54:10.513+0200', INSTANCE# ]

Example output:

Get summary of several statistics for a specific timeframe for a specific instance.

SELECT SUM(cpu_time) AS sum_cpu_time, SUM(active_time) AS sum_active_time, SUM(elapsed_time) AS sum_elapsed_time, SUM(num_executions) AS sum_num_executions, MIN(sample_starttime) AS min_sample_starttime, MAX(sample_endtime) AS max_sample_endtime FROM pass_unique_stat_fact_1m_<YYYYMMDDHHMM> WHERE sample_endtime >= ? AND sample_starttime <= ? AND instance_key = ?

Parameters example: ['YYYY-MM-DD 10:54:10.513+0200', 'YYYY-MM-DD 11:54:10.513+0200', INSTANCE# ]

Example output:

Get summary of several statistics for a specific timeframe for a specific instance.

SELECT SUM(cpu_time) AS sum_cpu_time, SUM(active_time) AS sum_active_time, SUM(elapsed_time) AS sum_elapsed_time, SUM(num_executions) AS sum_num_executions, MIN(sample_starttime) AS min_sample_starttime, MAX(sample_endtime) AS max_sample_endtime FROM pass_unique_stat_fact_1m_<YYYYMMDDHHMM> WHERE sample_endtime >= ? AND sample_starttime <= ? AND instance_key = ?

Parameters example: ['YYYY-MM-DD 10:54:10.513+0200', 'YYYY-MM-DD 11:54:10.513+0200', INSTANCE# ]

Example output:

Get specific details while grouping specific parameters

SSELECT SUM(active_time) AS sum_active_time, sample_starttime AS sample_starttime,sample_endtime AS sample_endtime, username AS username, sql_handle AS sql_handle,machine_name AS machine_name FROM pass_unique_stat_fact_1m_<YYYYMMDDHHMM> WHERE sample_endtime >= ? AND sample_starttime <= ? AND instance_key = ? AND username =? AND sql_handle = ? AND machine_name = ? GROUP BY sample_endtime, username, machine_name, sql_handle, sample_starttime ORDER BY sample_starttime ASC

Parameters example: ['YYYY-MM-DD 10:50:10.544+0200', 'YYYY-MM-DD 11:54:10.544+0200', Instance#, group by user, group by sql handle, group by machine name]

Example output:

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating