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.
Architecture and Processing
The Query Activity collector type consists of the following elements:
An input parameters schema (internal)
An SSIS package for collecting data (QueryActivityCollect.dtsx)
An SSIS package for uploading data (QueryActivityUpload.dtsx)
In addition, this collector type utilizes custom tasks or transforms to analyze and select data for storage in the management data warehouse.
The Query Activity collector type performs the following operations:
It collects samples of dm_exec_requests, dm_exec_sessions, and selected other related dynamic management views. This is implemented as a single joined query. Data collection happens with the frequency specified for the collection item.
It collects snapshots of the dm_exec_query_stats dynamic management view, with a frequency equal to the collection set upload frequency. By default, the Query Statistics collection set has an upload frequency of 15 minutes.
The following table shows the query that is used during the collection phase. This query is defined in the QueryActivityCollect.dtsx SSIS package.
During the upload phase, the collected data is analyzed to determine what data will be saved in the management data warehouse. This analysis determines a set of query statistics, query plans, and query text that needs to be saved.
A key element is an algorithm that selects which queries and query plans to save in the data warehouse. This algorithm works as follows:
Collects a snapshot of sys.dm_exec_query_stats. This snapshot is collected at the frequency equal to the collection set upload frequency. (By default, this is 15 minutes.)
Retrieves the most recent snapshot (from 15 minutes earlier) for comparison with the new snapshot. The most recent snapshot is cached locally and does not have to be retrieved from the management data warehouse.
Selects the top three queries from each snapshot by using the following metrics:
This process provides 6 x 3 sql_handles and plan_handles.
Identifies the unique sql_handles and plan_handles.
Intersects this result with the sql_handles and plan_handles that are stored in the data warehouse.
For new sql_handles and plan_handles, obtains the plan and text from the server. If the plan or text cannot be found (it may have been already been removed from the local cache), stores the handles in the management data warehouse.
For each sql_handle text that is gathered, normalizes the text (for example, removes parameters and literals) and calculates the unique hash value for the normalized text. Stores the normalized text, the hash value, and mapping to the original sql_handle in the management data warehouse.
The following table shows the query that is used to obtain the snapshots, and to analyze and then upload the data to the management data warehouse. This query is defined in the QueryActivityUpload.dtsx SSIS package.
snapshots.query_stats, snapshots.notable_query_text, and snapshots.notable_query_plan