Collectors Inside SQL Server

Data collector is an out of box feature which comes with SQL Server 2008/above versions.This feature deals with capturing performance data mainly using API's.The data is stored and manipulated using a database MDW(Management Data Warehouse).When configuring data collector we are allowed to create MDW or use existing database which can be used as MDW.

Data collector collects 3 different types of data and this available for uses to review and report -

1. Server activity
2. Disk activity
3. Query stats

The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse. The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data. The data collector supports dynamic tuning for data collection and is extensible through its API.

The data collector API, in Microsoft.SqlServer.Management.Collector, allows programmatic control of all configuration operations through the object model. In addition, many of the data collection operations that use the API are implemented as stored procedures that are installed on the server.

The following illustration shows key elements of the data collector object model.

1. CollectorConfigStore
2. CollectionSetCollection
3. CollectionSet
4. CollectionItemCollection
5. CollectionItem

The data collector installs three System Data collection sets during the SQL Server 2008 Setup process. These collection sets can be configured to suit your monitoring requirements but cannot be deleted. The System Data collection sets consist of the following:

Disk Usage. Collects data about disk and log usage for all the databases installed on the system.

Server Activity. Collects resource usage statistics and performance data from the server and SQL Server.

Server Activity – Performance Counters

Query Statistics. Collects query statistics, individual query text, query plans, and specific queries.

Usage:(The procedure that I have been used in my latest project)

Viewing Data Collection Sets

Connect to SQL Server in SSMS

Expand Management folder and then expand Data Collection and then System Data collection sets

You can find Disk Usage, Query Statistics, Server Activities and Utility Information.

Just right click on the required set and then click on properties

In the window you can find the details as shown below (Ive taken it for server activity collection set) In the first image Ive chosen DMV details , so you can get the queries used. In the second image Ive chosen to get performance counter details, so in the below pane it shows the counters used.

Start or Stop Collection Set

Connect to SQL Server in SSMS

Expand Management folder and then expand Data Collection and then System Data collection sets

You can find Disk Usage, Query Statistics, Server Activities and Utility Information.

Just right click on the required set and then click on Start Data Collection Set or Stop Data Collection Set

Viewing Data Collection Reports

Connect to SQL Server in SSMS

Expand Management folder and then right click on Data Collection , then click on Reports and then Management Data Warehouse and then click on the respective collection set.

A data collection is a container that allows you to specify what data to collect, when to collect it, and how long to keep it. It does this by using Data Collector.

Data Collector can either be configured to run on your defined schedule or can run all the time and collect different sets of data pertaining to performance diagnostics, historical data for baseline comparisons, policy based data etc

SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity.

The data collector is a component of SQL Server 2008 that collects different sets of data.
Data collection either runs constantly or on a user-defined schedule.

Data Collector is an optional feature of SQL Server 2008 that collects and stores information about SQL
Server’s 2008’s status and performance over time, using pre-defined data collectors.

• Once data is collected and stored in the Management data warehouse database, it can be used for baselining, planning, troubleshooting, performance tuning, and much more.
• Built-in reports are included that help the DBA become more proactive

The data collector supports dynamic tuning for data collection and is extensible through its API.

The data collector provides an historical report for each of the System Data collection sets.
Data Collector includes three data collection sets,
which are used to collect data from targets and put it in the management data warehouse

Benefits of the Data Collector

• Easy to install and configure, and comes with 2008
• By default, collects most of the key data needed by a DBA to resolve many different SQL Server-related problems, especially performance-related ones.
• Allows you to customize what data is collected, in case you don’t like the default data collected
• Provides pre-defined reports
• Allows you to manually query, or create Reporting Services reports, on the historical data stored in the management data warehouse

Limitations of the Data Collector

• It can only collect data from SQL Server 2008 (not previous versions).
• Customizing data collection and reports is not particularly easy
• In most cases, a dedicated SQL Server instance is required, if many instances are to be monitored
• Memory, CPU and disk I/O resources are consumed on the SQL Server instances being monitored
• Once installed, it can’t be uninstalled, only disabled

How Much Overhead is Used by the Data Collector

• The Data Collector will add about 4% to the current CPU load of the SQL Server instance being monitored.
• Using default data collection settings, the Data Collector can generate about 250-300 MB of data every day per SQL Server instance being monitored,which is stored in the MDW.
• Each dcexec.exe process uses 30-100 MB memory each, and 2+ are running all the time.

The data collector is integrated with SQL Server Agent and Integration Services, and uses both extensively.

Key Components Used by the Data Collector

• SSMS & SPs: To access and configure the data collector.
• Local Cache: Used to store collected data temporarily before being moved to the MDW.
• msdb: Stored data collector configuration info.
• MDW: Relational database used to store data.
• SSIS Packages: Used to collect data from targets, and to move data to the MDW.
• SQL Server Agent Jobs: Used to run the scheduled SSIS packages. All activities are scheduled.
• dcexec.exe Process or the Data Collector Run-time component: Each collection set has its own process, and it runs only during the collection process. This process runs outside of sqlserver.exe.

A collection set can be designated to collect and send data to the MDW in two different ways

(1) Cached
Data is collected on one schedule, and stored in the local cache. Another schedule runs to move it from the local cache to the MDW. The Server Activity and Query Statistics collection sets use this option. Best for frequent data collection (more often than every five minutes) to reduce performance hit
(2) Non-Cached
Data is collected and sent to the MDW as a single job, in a single step. Job runs continuously. The Disk Usage collector set uses this method. Best used when data is collected in periods of 5 minutes or greater to reduce performance hit.

Collection Sets, Types, & Items

(1) Collection Set
Disk Usage, Query Activity, Server Activity

(2) Collection Type
T-SQL Query, Performance Counter, Query Activity, SQL Trac

(3) Collection Item
Performance Counters, DMV Stats, Profiler Events, And many mor

What are the predefined collectors inside SQL Server which can be used?

(1) Disk Usage Collection Set

• Tracks the growth of database and log files and provides file-related statistics on a regular basis.
• Captures this data:
  • Snapshots of data file sizes obtained from sys.partitions and sys.allocation_units.
  • Snapshots of log file sizes obtained from DBCC SQLPERF (LOGSPACE).
  • Snapshots of I/O statistics from sys.dmiovirtualfilestats

(2) Server Activity Collection Set

• Provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention
• Captures this data:
  • sys.dmoswaitstats
  • sys.dmoslatchstats
  • sys.dmosschedulers
  • sys.dmexecsessions , sys.dmexecrequests, sys.dmoswaitingtasks(using a joined query)
  • sys.dmosprocessmemory
  • sys.dmosmemory_nodes
  • Additional system and SQL Server performance counters

(3) Query Statistics Collection Set

• Gathers data about query statistics and individual query text, query plans, and specific queries
• Captures this data:
  • sys.dmexecquery_stats view
  • The text of selected batches and queries
  • The plan of selected batches and queries
  • The normalized text of selected batche

Installing and Configuring the Data Collector

• Create the Management data warehouse (a one time step).
• Add the Data Collector to each SQL Server instance to be monitored, and point it to the MDW. If you want to monitor 25 servers, then you perform this step 25 times.
• Once each instance is added, the Data Collector begins to collect data immediately.

Three key reports (with drill-down):

• Disk Usage Report
• Query Statistics Report
• Server Activity Repor

http://bradmcgehee.com/wp-content/uploads/presentations/Mastering%20the%20SQL%20Server%202008%20Performance%20Data%20CollectorSQLCONN.pdf

Data Collector

The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule.

Data Collector is an optional feature of SQL Server 2008 that collects and stores information about SQL Server’s 2008’s status and performance over time, using pre-defined data collectors.

What are the predefined collectors inside SQL Server which can be used?

Data Collector includes three data collection sets, which are used to collect data from targets and put it in the management data warehouse

(1) Disk Usage Collection Set

Tracks the growth of database and log files and provides file-related statistics on a regular basis.

Captures this data:

• Snapshots of data file sizes obtained from sys.partitions and sys.allocation_units.
• Snapshots of log file sizes obtained from DBCC SQLPERF (LOGSPACE).

(2) Server Activity Collection Set

Provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention

Captures this data:

• sys.dmoswaitstats
• sys.dmoslatchstats
• sys.dmosschedulers
• sys.dmexecsessions , sys.dmexecrequests, sys.dmoswaitingtasks(using a joined query)
• sys.dmosprocessmemory
• sys.dmosmemory_nodes
• Additional system and SQL Server performance counters

(3) Query Statistics Collection Set

Gathers data about query statistics and individual query text, query plans, and specific queries

Captures this data:

• sys.dmexecquery_stats view
• The text of selected batches and queries
• The plan of selected batches and queries
• The normalized text of selected batche

Three key reports (with drill-down):

• Disk Usage Report
• Query Statistics Report
• Server Activity Repor
• Snapshots of I/O statistics from sys.dmiovirtualfilestats

Data Collections

The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse.

The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data.

The data collector supports dynamic tuning for data collection and is extensible through its API.

Collecter Types

The data collector provides predefined collector types that you can use for data collection. The collector types provide the actual mechanism for collecting data and uploading it to the management data warehouse.

1. Generic T-SQL Query Collector Type

The Generic T-SQL Query collector type executes a user-provided Transact-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse. This collector type is registered in the core.supportedcollectortypes view in the management data warehouse.

This collector has the following input parameters:

Value - The Transact-SQL query. You can provide more than one query as input.

OutputTable - Provide the name of the table for saving query results before they are uploaded to the management data warehouse.

Database - Specify the database or databases to run the query against. You can specify databases by name or use the * wildcard character to specify all the databases on the server. If you do not provide a value for Database, the query is run against all the system databases.

2. Generic SQL Trace Collector Type

The Generic SQL Trace collector type uses SQL Trace to monitor the SQL Server Relational Engine. The trace data can come from a default trace or from one or more custom traces. This collector type is registered in the core.supportedcollectortypes view.

The default trace, controlled by a server-wide setting, runs continuously on the server and captures general events of interest. These events of interest are not related to individual batch execution. This is a low-impact trace. Custom traces can collect any events and can have low or high impact, depending on which events are selected and the server activity at the time that the trace is running. In most cases, custom traces do not run continuously.

The Generic SQL Trace collector type runs a server-side trace that stores data in a file or set of files. The trace data is obtained from trace files by using the fntracegettable() system function. If configured to do so, the collector processes the data and then uploads the data to the management data warehouse.

The Generic SQL Trace collector type is configured to remove unused files and to keep a fixed amount of space for stored trace data.

3. Performance Counters Collector Type

You can use the Performance Counters collector type to collect specific performance counter information from a computer that is running SQL Server 2008. You can use this data later as a baseline for troubleshooting or for capacity planning. This collector type is registered in the core.supportedcollectortypes view.

This collector type has the following input parameters:

Objects. The SQL Server objects running in an instance of SQL Server.

Counters. The counters associated with a SQL Server object.

Instances. The instances of the specified object.

4. Query Activity Collector Type

The Query Activity collector type is a custom collector type used by the Query Statistics collection set, one of the predefined system data collection sets.

This collector type is used to collect query statistics and query activity information along with the query plan and query text for queries that meet predefined criteria. If you create your own collection set using this collector type, it will collect the same information as the Query Statistics collection set. Therefore, we recommend that you use the predefined Query Statistics collection set.