SQL Server Profiler SQL Server Profiler is a graphical tool that helps in the monitoring of an instance of SQL Server Database Engine or Analysis Services. The data about each event can be captured to a file or table for analysis at a later date. SQL Server 2005 incorporates certain new features into the SQL Server Profiler.
The significant enhancements are as under:
1. Rollover trace files.
2. New extensibility standard.
3. Profiling of Microsoft SQL Server 2005 Analysis Services (SSAS).
4. Profiling of Microsoft SQL Server 2005 Integration Services (SSIS).
5. Saving of traced Showplan as XML.
6. Save trace results as XML.
7. Aggregate view.
8. Correlation of Trace Events to Performance Monitor Counters.
Before examining the process of using SQL Server Profiler, let us look at the terminology associated with the tool.
1. An Event is an action that is generated within an instance of a SQL Server Database Engine. These could be login failures, connection failures or disconnections. It would include events such as T-SQL statements, remote procedure call batch status, the start or end of a stored procedure, the start or end of statements within a stored procedure and so on.. These are displayed in the trace in a single row intersected by data columns with descriptive details.
2. An Event Class is an event that can be traced and contains all of the data that can be reported by the event. SQL: Batch completed for instance is an event class, just as .Audit Login, .Audit Logout etc are event classes.
3. An Event Category defines the methodology used for grouping events within the SQL Server Profiler. For instance lock events will be categorized under Lock event category.
4. A Data Column is an attribute of an event class that is captured in the trace. The event class determines the type of data that can be collected and not all data columns are applicable to all event classes.
5. A Template is the default configuration for a trace. It includes the event classes that are required for monitoring with the SQL Server Profiler.
6. A Trace captures data based on selected event classes, data columns and filters. For instance a trace monitor can be created to capture Exception event class with the Error, State and Severity data columns.
7. Data can be Filtered by specifying criteria of selection during the execution of an event. This feature is used to reduce the size of the Trace.
The SQL Server Profiler tool captures the events and stores them in a trace file for analysis. The trace file enables the replay of the events for the diagnosis of the problems. The SQL Server Profiler is used for stepping through the problem to find the cause or finding and diagnosing slow running queries, or capturing T-SQL statements that lead to a problem or Monitoring the performance of the SQL Server for tuning workloads.
Auditing actions are also supported by the Profiler. Audit traces help in maintaining the security of the server.
The rich interface of the Profiler helps the administrator create and manage traces and analyze and replay trace outputs. In the production environment the DBA will have to create a focused and well organized series of traces.
He may want to do the following actions:
1. Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server or Integration Services.
2. Debug Transact SQL statements and stored procedures
3. Identify slowly executing procedures and queries.
4. Perform stress testing and quality assurance by replaying traces
5. Replay traces of one or more users
6. Perform query analysis.
7. Test T-SQL statements and stored procedures in the development phase
8. Troubleshoot problems
9. Audit and review activity
10. Provide standardized hierarchical structure to trace results by saving them to the XML file.
11. Aggregate trace results
12. Correlate performance counters
13. Configure trace problems.
14. Allow non administrators create traces.
The SQL Server Profiler can be accessed in several ways. It can be invoked from the Start Menu, from the tools menu in the SQL Server Management Studio and from the Tools menu in the Database Engine Tuning Advisor.
To start SQL Server Profiler from the Start menu
On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click SQL Server Profiler.
SQL Server Profiler can correlate Performance Monitor Counters with SQL Server or SSAS events. Administrators can select from a predefined set of Performance Monitor Counters and save them at specified time intervals while also collecting a SQL Server or SSAS trace. Users can choose an aggregate option and select a key for aggregation. This will enable users to see a view that shows the column on which the aggregation was performed, along with a count for the number of rows that make up the aggregate value. Trace results can be saved in an XML format in addition to the standard save formats of ANSI, UNICODE, and OEM. Results saved in this fashion can be edited and used as input for the Replay capability of SQL Server Profiler. Showplan results can be saved in an XML format, which can be later loaded for graphical Showplan display in Query Editor without the need to have an underlying database. SQL Server Profiler will also display a graphical representation of Showplan XML events at that the time they are captured by SQL Server Profiler. SQL Server Profiler can now display events raised by SSIS. SQL Server Profiler now supports capturing and displaying events raised by SSAS. SQL Server Profiler uses an XML-based definition that allows SQL Server Profiler to more easily capture events from other types of servers and programming interfaces. SQL Server Profiler can replay one or more collected rollover trace files continuously and in order.
The significant enhancements are as under:
1. Rollover trace files.
2. New extensibility standard.
3. Profiling of Microsoft SQL Server 2005 Analysis Services (SSAS).
4. Profiling of Microsoft SQL Server 2005 Integration Services (SSIS).
5. Saving of traced Showplan as XML.
6. Save trace results as XML.
7. Aggregate view.
8. Correlation of Trace Events to Performance Monitor Counters.
Before examining the process of using SQL Server Profiler, let us look at the terminology associated with the tool.
1. An Event is an action that is generated within an instance of a SQL Server Database Engine. These could be login failures, connection failures or disconnections. It would include events such as T-SQL statements, remote procedure call batch status, the start or end of a stored procedure, the start or end of statements within a stored procedure and so on.. These are displayed in the trace in a single row intersected by data columns with descriptive details.
2. An Event Class is an event that can be traced and contains all of the data that can be reported by the event. SQL: Batch completed for instance is an event class, just as .Audit Login, .Audit Logout etc are event classes.
3. An Event Category defines the methodology used for grouping events within the SQL Server Profiler. For instance lock events will be categorized under Lock event category.
4. A Data Column is an attribute of an event class that is captured in the trace. The event class determines the type of data that can be collected and not all data columns are applicable to all event classes.
5. A Template is the default configuration for a trace. It includes the event classes that are required for monitoring with the SQL Server Profiler.
6. A Trace captures data based on selected event classes, data columns and filters. For instance a trace monitor can be created to capture Exception event class with the Error, State and Severity data columns.
7. Data can be Filtered by specifying criteria of selection during the execution of an event. This feature is used to reduce the size of the Trace.
The SQL Server Profiler tool captures the events and stores them in a trace file for analysis. The trace file enables the replay of the events for the diagnosis of the problems. The SQL Server Profiler is used for stepping through the problem to find the cause or finding and diagnosing slow running queries, or capturing T-SQL statements that lead to a problem or Monitoring the performance of the SQL Server for tuning workloads.
Auditing actions are also supported by the Profiler. Audit traces help in maintaining the security of the server.
The rich interface of the Profiler helps the administrator create and manage traces and analyze and replay trace outputs. In the production environment the DBA will have to create a focused and well organized series of traces.
He may want to do the following actions:
1. Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server or Integration Services.
2. Debug Transact SQL statements and stored procedures
3. Identify slowly executing procedures and queries.
4. Perform stress testing and quality assurance by replaying traces
5. Replay traces of one or more users
6. Perform query analysis.
7. Test T-SQL statements and stored procedures in the development phase
8. Troubleshoot problems
9. Audit and review activity
10. Provide standardized hierarchical structure to trace results by saving them to the XML file.
11. Aggregate trace results
12. Correlate performance counters
13. Configure trace problems.
14. Allow non administrators create traces.
The SQL Server Profiler can be accessed in several ways. It can be invoked from the Start Menu, from the tools menu in the SQL Server Management Studio and from the Tools menu in the Database Engine Tuning Advisor.
To start SQL Server Profiler from the Start menu
On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click SQL Server Profiler.
SQL Server Profiler can correlate Performance Monitor Counters with SQL Server or SSAS events. Administrators can select from a predefined set of Performance Monitor Counters and save them at specified time intervals while also collecting a SQL Server or SSAS trace. Users can choose an aggregate option and select a key for aggregation. This will enable users to see a view that shows the column on which the aggregation was performed, along with a count for the number of rows that make up the aggregate value. Trace results can be saved in an XML format in addition to the standard save formats of ANSI, UNICODE, and OEM. Results saved in this fashion can be edited and used as input for the Replay capability of SQL Server Profiler. Showplan results can be saved in an XML format, which can be later loaded for graphical Showplan display in Query Editor without the need to have an underlying database. SQL Server Profiler will also display a graphical representation of Showplan XML events at that the time they are captured by SQL Server Profiler. SQL Server Profiler can now display events raised by SSIS. SQL Server Profiler now supports capturing and displaying events raised by SSAS. SQL Server Profiler uses an XML-based definition that allows SQL Server Profiler to more easily capture events from other types of servers and programming interfaces. SQL Server Profiler can replay one or more collected rollover trace files continuously and in order.
No comments:
Post a Comment