Using SQL Server Profiler to Monitor Data Mining (Analysis Services - Data Mining)
If you have the necessary permissions, you can use SQL Server Profiler to monitor data mining activities that are issued as requests sent to an instance of SQL Server Analysis Services. Data mining activity can include the processing of models or structures, prediction queries or content queries, or the creation of new models or structures.
SQL Server Profiler uses a trace to monitor requests sent from multiple clients, including Business Intelligence Development Studio, SQL Server Management Studio, Web services, or the Data Mining Add-ins for Excel, so long as the activities all use the same instance of SQL Server Analysis Services. You must create a separate trace for each instance of SQL Server Analysis Services that you want to monitor. For general information about traces, and how to use SQL Server Profiler, see Using SQL Server Profiler to Monitor Analysis Services.
For specific guidance about the types of events to capture, see Creating Traces for Replay.
Using Traces to Monitor Data Mining
When you capture information in a trace, you can specify whether the information is saved in a file or in a table on an instance of SQL Server. Regardless of the method you use to store the data, you can use SQL Server Profiler to view the trace and filter by events. The following table lists some of the events and subclasses in the default Analysis Service trace that are of interest for data mining.
EventClass |
EventSubclass |
Description |
|---|---|---|
Query Begin Query End |
0 - MDXQuery |
Contains the text of all calls to Analysis Services stored procedures. |
Query Begin Query End |
1 - DMXQuery |
Contains the text and results of Data Mining Extensions (DMX) statements. |
Progress Report Begin Progress Report End |
34 - DataMiningProgress |
Provides information about the progress of the data mining algorithm: for example, if you are building a clustering model, the progress message tells you which candidate cluster is being built |
Query Begin Query End |
EXECUTESQL |
Contains the text of the Transact-SQL query that is being executed |
Query Begin Query End |
2- SQLQuery |
Contains the text of any queries against the schema rowsets in the form of system tables. |
DISCOVER Begin DISCOVER End |
Multiple |
Contains the text of DMX function calls or DISCOVER statements, encapsulated in XMLA. |
Error |
(none) |
Contains the text of errors sent by the server to the client. Error messages prefaced with Error (Data Mining): or Informational (Data Mining): are generated specifically in response to DMX requests. However, it not sufficient to view only these error messages. Other errors, such as those generated by the parser, may be related to data mining but do not have this prefix. |
By viewing the command statements in the trace log, you can also see the syntax of complex statements sent by the client to the Analysis Services server, including calls to system stored procedures. This information can be useful for debugging, or you can use valid statements as a template for creating new prediction queries or models. For some examples of stored procedure calls that you can capture via a trace, see Querying a Clustering Model (Analysis Services - Data Mining).