Performance Sentry Performance Database (PDB) Data Model

The Performance Sentry Performance Database (PDB) is a flexible and highly scalable repository designed specifically for optimal storage and reporting of detailed, daily performance data gathered using the performance monitoring facilities of the Windows operating system. This section introduces the data model that the PDB uses to store performance data, which is influenced by several key characteristics of the performance monitoring facilities of the Windows operating system.

Performance Objects and Counters

The Windows

performance counters instrument the operating system, the hardware, its major subsystems, and many of its applications. All the performance data is available using a single, common collection mechanism and shares a common format. The performance data consists of named counter values that are time-stamped and organized by performance object. While Windows supports a large number of distinct counter types, most of the counters that are available either report an activity rate (counter type Perf_Counter_Counter) or an instantaneous value (counter type Perf_Counter_Rawcount).

Each counter value is uniquely identified in the Windows Perfmon application using a naming string with the following syntax:

 \\MachineName\ObjectName(ParentName/ObjectInstance#InstanceIndex)\CounterName

where the InstanceName, InstanceIndex, and ParentName can be necessary to disambiguate the counter reference.

Windows counters are grouped by performance object:

object → List<counters>

but not all performance objects contain multiple instances For example, the Memory object contains a set of system-wide performance counters accounting for usage of both physical and virtual memory. For any one point in time for a given machine, there is only one instance of the Memory object reported. However, instances of the Processor object report processor utilization per processor. The unique instance name associated with the Processor object is a processor ID. (See Overview of Performance Data Collection for additional details regarding the performance data naming convention.)

The data model also supports one level of parent-child nesting, but only a few performance objects, such as the Thread, implement the parent-child relationship. (The parent of a Thread is its Process.) The Instance index is an index synthesized by the Windows Performance Monitor to distinguish multiple instances of the Process object with the same process name (such as separate iexplore processes).

Figure 1 represents this conceptual data model for performance objects and counters in Windows visually showing one or more performance counters associated with a unique instance of a performance object:

Figure 1. A conceptual model showing the relationship between Performance Objects and Counters in Windows.

The Performance Sentry PDB adopts this conceptual schema, but with the following key differences:

• All the counter values associated with a given unique instance of a performance object are stored in the PDB by timestamp in a single database table data row (where the SQL column name corresponds to the performance counter name). This results in considerably more efficient use of disk storage, compared to storing each counter value separately in its own unique row in a PDB, as many other products do.
• Instead of a synthetic index for unique instances of the Process object, the PDB differentiates among unique instances of the Process object using the Process ID, or PID. At any point in time, the Process ID (or ID Process, as the counter is labeled in the Process object) is guaranteed to be unique. Using the Process ID is the identification scheme used by the OS itself.

The Process ID value is also valuable because it can be used to correlate the process level measurements taken by the OS with internal application-level measurements whenever the PID is explicitly available. (For example, the performance counters at the application level provided by the .NET Framework report the Process name as the Instance name along with a separate Process ID field, for example.) In the terminology of data modeling, the process ID is a Key to the Process table. When the Process ID is stored in a table other than the Process table, it serves as a Foreign Key that can be used to JOIN data from that table to data in the Process table. Figure 1 above shows ProcessID as a Foreign Key in the PerformanceCounter data structure.

Machine-specific Data Tables

To promote scalability, the Performance Sentry PDB stores all the performance counter data in time-series in a set of machine-specific data tables. Machine-specific database tables, one per performance object per machine, are created dynamically by the PDB loader program when the data file is processed. A unique database table is generated with a table name in the form of MachineName@ObjectName for each set of performance counters found in the data file being processed. The number of machine-specific data tables created from a data file is a function of the input data.

The PDB does contain several statically defined database tables. These include a Machines table that contains a row for every machine which has performance data loaded in the PDB. The Machines table entry for a machine contains a pointer (another Foreign Key) to the name of the Data Collection Set (DCS) that is stored in the DCS Data Definition table, also allocated statically. Entries in the DCS Data Definition table define the performance objects and counters that are present in a data file from that specific machine.

Figure 2 provides an overview of the PDB schema, indicating how the statically-defined Machines and DCS_DataDef tables are used to generate the machine-specific database tables dynamically during a database Load. (In the diagram, not all columns are represented to save space.) Note, that if the machine-specific database tables already exist for a machine, the Loader program validates them against the DCS data definition in the current data file and, if there are any schematic changes, updates the machine data tables accordingly.

Figure 2. The relationship between the static database tables that are machine-oriented and the dynamic, machine-specific tables that hold the performance counter data. The Machines table entry contains a pointer to the DCS Data Definition, which is then used to construct the machine-specific data tables. Two machine-specific data tables are illustrated, a simple MachineName@Memory table and an instanced MachineName@Process table. For the sake of brevity, not all counters that are in the objects are shown.

During a database Load operation, the NtsmfDataAccess component is called, passing a data file name to process. Each data file is then processed in three distinct stages:

1. Processing the Configuration records embedded at the beginning of the data file that define the performance objects contained in the file, and their associated performance counters.
2. Processing the data records themselves that occupy the bulk of the data file. These are formatted as a set of DataTables, one per performance object, and stored as a collection of DataTables contained in a DataSet object.
3. One by one, Inserting new rows of data into SQL Server database tables from each of the DataTables formatted in step 2.

At the outset of stage 3 in the database load process, the set of SQL Server tables that are present are validated against the DataTables resident in memory that were built from the current data file, and any changes or updates that are detected are propagated to the disk-resident database versions of the data tables automatically at that time.

Design Trade-offs

In any complex system, design decisions inevitably involve trade-offs. In the case of the dynamic data model that is implemented in the Performance Sentry PDB, there are some distinct advantages of the approach used, but also some implications are not uniformly positive.

Among the advantages of this approach, the ability of the PDB to scale to accommodate updating and reporting on thousands of machines is particularly noteworthy. The data model supports a classic SIMD (single instruction, multiple data) approach to parallelizing the nightly database load. Database loads are accomplished using a simple command:

ntdacmd –d folder-name –threads auto|n

where the loader program is pointed at a folder containing the latest set of daily data files. When loading data files in parallel, the loader program scans the input folder, instantiates a .NET ThreadPool object, and then assigns each worker thread in the thread pool a file to process, until all the files that were found in the folder are processed. Worker threads make a few accesses to the shared static data tables at the beginning of each file loading operation. Thereafter, Database load operations are accomplished in parallel. The speed-up is roughly a function of the number of update threads because it is virtually a shared nothing data model for distributing the data over multiple sets of database tables. In practice, however, the performance of Writes to the database disks, especially the database logging disk, usually serves as an upper bound on the throughput of the database load process.

In principle, the Performance Sentry PDB data model is also very effective in scaling the performance of queries that span multiple machines, which can take advantage of Microsoft SQL Server’s automatic parallel query optimization facilities. See the MSDN Help topics “Parallel Query Processing” and “Parallel LINQ” for details. When the focus is reporting data from a single machine, since data from different performance objects is stored in separate database tables, many machine-specific queries can also be parallelized.

In general, the Performance Sentry PDB data model is optimized for reporting. In the most common scenarios for reporting on Windows performance counter data, all the data needed for a single report is contained in a single data table, which are each indexed by time of day to facilitate logical JOIN operations within and across data tables. Figure 3 shows the results of a typical PDB query that Joins data from the Process table with the Processor table for a single machine. Because the machine@Process and the machine@Processor tables are distinct, SQL Server can create a query execution Plan that runs queries against each table in parallel.

In the Query to build the chart illustrated in Figure 3, the Processor table contains 32 processor instances reported every minute, while the Process table is a sparse matrix that contains only instances of processes that exceed defined thresholds for CPU (or memory) usage. The SQL Query sums the processor instances by time interval. These are JOINed by time interval to a report of the Top 5 overall processes, based on CPU consumption over the query interval. Complex queries such as these can benefit from running in parallel on a multi-core processor.

Figure 3. A common reporting scenario Joins data from multiple machine-specific database tables in the Performance Sentry PDB. Here data from the machine@Process and machine@Processor tables are Joined by time of day.

This performance optimization is not free, however. There are serious trade-offs that customers encounter with this design. The data model is simple conceptually, but, in practice, navigating to the desired data is complex. With multiple data tables generated for each machine, the developer that builds a PDB query must understand the data model and know how to navigate to the proper set of machine-specific data tables.

One general approach to navigation in the PDB is to locate the tables of interest in the System Views that explicitly represent the Database Definition Language (DDL). Using LINQ to SQL, for example, a static method IsValidPDBTableName can be called to check for a valid SQL Table name. The method is a LINQ-to-SQL wrapper around a query of PDB.INFORMATION_SCHEMA.TABLES:

string metadataquery = @”Select TABLE_NAME From [PDB].[INFORMATION_SCHEMA].[TABLES]” +

” Where TABLE_NAME = ‘” + TableName + “‘”;

Using the Machines table to navigate to the DCS Data Definition table where the schema metadata is represented implicitly is another viable approach developers can use.

Another complication is the sheer number of data tables that are generated when the PDB is used to process performance data from a large number of machines. Simple SQL Server query tools, including Excel’s otherwise excellent support for querying SQL Server databases tend to choke when the number of data tables resident in the PDB exceeds 100,000. Similarly, the time it takes to run the basic SQL Server Management Studio function to enumerate the PDB data tables can be prohibitive with large numbers of tables.

Our new DTS Data Services Software Developers’ Kit (SDK) helps to mitigate these concerns for developers interested in building their own PDB queries. The SDK packages together the common queries that reporting programs run in our Web Portal. The SDK implements the common queries used to locate machines and their associated machine-specific data tables. It contains methods to find the objects and counters associated with specific machines in the PDB, and then gather the specific counter data specified. The SDK also includes a sample desktop graphical reporting application that demonstrates the use of all the important APIs for querying the PDB.

Click here to download the Data Access Services SDK