Edit

Management data warehouse

  • Article

Applies to: SQL Server

The management data warehouse is a relational database containing the data collected from a server that is a data collection target. This data is used to generate the reports for the System Data collection sets, and can also be used to create custom reports.

The data collector infrastructure defines the jobs and maintenance plans that are needed to implement the retention policies defined by the database administrator.

Important

For this release of the data collector, the management data warehouse is created using the Simple recovery model, to minimize logging. You should implement the appropriate recovery model for your organization.

Deploy and use the data warehouse

You can install the management data warehouse on the same instance of SQL Server that runs the data collector. However, if server resources or performance is an issue on the server being monitored, you can install the management data warehouse on a different computer.

The required schemas and their objects for the predefined system collection sets are created when you create the management data warehouse. The schemas that are created are core and snapshots.A third schema, custom_snapshots, is created when user-defined collection sets are created that include collection items that use the Generic T-SQL Query collector type.

Core schema

The core schema describes the tables, stored procedures, and views that are used to organize and to identify collected data. These tables are shared among all the data tables created for individual collector types. This schema is locked, and only the owner of the management data warehouse database can modify it. The names of the tables in this schema are prefixed by core.

The following table describes the database tables in the core schema. These database tables enable the data collector to track where the data came from, who inserted it, and when it was uploaded to the data warehouse.

Table name Description
core.performance_counter_report_group_items Stores information about how the management data warehouse reports should group and aggregate performance counters.
core.snapshots_internal Identifies each new snapshot. A new row is inserted into this table whenever an upload package starts uploading a new batch of data.
core.snapshot_timetable_internal Stores information about the snapshot times. The snapshot time is stored in a separate table because many snapshots can happen at nearly the same time.
core.source_info_internal This table stores information about the data source. This table is updated whenever a new collection set starts uploading data to the data warehouse.
core.supported_collector_types_internal Contains the IDs of registered collector types that can upload data to the management data warehouse. This table is only updated when the schema of the warehouse is updated to support a new collector type. When the management data warehouse is created, this table is populated with the IDs of the collector types provided by the data collector.
core.wait_categories Contains the categories used to group wait types according to wait_type characteristic.
core.wait_types Contains the wait types recognized by the data collector.
core.purge_info_internal Indicates that a request was made to stop the removal of data from the management data warehouse.

The preceding tables are used with collector type tables to store information. For example, the Generic SQL Trace collector type uses the following tables to store trace data:

  • core.source_info_internal
  • core.snapshots_internal
  • snapshots.trace_info
  • snapshots.trace_data

Snapshots schema

The snapshots schema describes the objects needed to store and maintain the data collected by the collector types that are provided. The tables in this schema are fixed and don't need to be changed during the lifetime of the collector type. If changes are needed, the schema can only be changed by members of the mdw_admin role. These tables are created to store the data collected by the System Data collection sets.

The following tables illustrate a portion of the management data warehouse schema that is required for the Server Activity and Query Statistics collection sets.

Schema Objects
System-level resource tables snapshots.os_wait_stats
snapshots.os_latch_stats
snapshots.os_schedulers
snapshots.os_memory_clerks
snapshots.os_memory_nodes
snapshots.sql_process_and_system_memory
System activity snapshots.active_sessions_and_requests
Query statistics snapshots.query_stats
I/O statistics snapshots.io_virtual_file_stats
Query text and plan snapshots.notable_query_text
snapshots.notable_query_plan
Normalized query statistics snapshots.distinct_queries
snapshots.distinct_query_to_handle

Custom_snapshots schema

The custom_snapshots schema describes new tables and views that are created when standard or third-party collector types are used to create user-defined collection sets. Any collector type that requires a new data table for a collection item can create that table in this schema. New tables can be added in this schema by members of the mdw_writer role. Any other changes to the schema can only be made by members of the mdw_admin role.

You can get detailed data type and content information for the database table columns by reading the documentation for the appropriate data collector stored procedure for each of the tables.

Best practices

When working with the management data warehouse, we recommend following these best practices:

  • Don't modify the metadata of management data warehouse tables unless you're adding a new collector type.

  • Don't directly modify the data in the management data warehouse. Changing the data that you have collected invalidates the legitimacy of the collected data.

  • Instead of directly using the tables, use the documented stored procedures and functions that are provided with the data collector to access instance and application data. The table names and definitions can change, do change when you update the application, and might change in future releases.

Related content