SQL optimization and monitoring (SQL Server Profiler)

 

0. Introduction

SQL Server provides SQL tracking and Event Notification to monitor events in the database engine.

By recording specified events, SQL tracking helps you solve performance problems, review database activities, collect sample data for testing environments, debug Transact-SQL statements and stored procedures, and collect data for performance analysis tools. You can access SQL tracing through SQL Server Profiler (a graphical user interface) or the stored procedure of the transact-SQL System entered in the command line.

During running, SQL tracking captures an event when an event occurs in the database engine. A captured event is an event-type instance that you choose to monitor in a process.Trace. Each event class contains one event name and multiple data columns (used to describe the attributes of an event class in a specific situation ). When an event of the specified event type occurs in the trail, the SQL trail records the event name and associated data in the trail file.

Generally, SQL tracing is accessed through SQL Server Profiler. You can also use the stored procedures of the transact-SQL System to access SQL tracing. SQL Server Profiler can use all event capture functions of SQL tracking, add trace table information, save trace definitions as templates, extract query plans and deadlock events as separate XML files, and replay tracking results. for diagnosis and optimization.

Event Notification sends information about many identical events captured by SQL tracking to the Service Broker service. However, unlike tracking, event notification can be used to respond to events and perform operations in SQL Server. Because Event Notifications are asynchronously executed, these operations do not occupy any resources defined by the real-time transaction.

The following topics describe how to use SQL Server Profiler, SQL tracking, and Event Notification to monitor events.

SQL Server Profiler Introduction

SQL Server Profiler is a tool used to capture SQL Server events from the server. The event is saved in a trace file. You can analyze the file later or use it to replay a series of steps when trying to diagnose a problem. SQL Server Profiler is used in the following activities:

• Analyze problematic queries to find the cause of the problem.

• Search for and diagnose slow queries.

• Capture a series of transact-SQL statements that cause a problem. Then, use the saved trace to copy the problem on a test server, and then diagnose the problem on the test server.

• Monitors SQL server performance to optimize workload. For more information about optimizing the design of a physical database for database workloads, see database engine optimization advisor overview.

• Associate performance counters with diagnostic problems

SQL Server Profiler also allows you to review operations performed on SQL Server instances. Security-related operations will be recorded for future review by the security administrator.

How to Create a trail

• On the File menu, click New Trail and connect to the SQL server instance. The "trace properties" dialog box is displayed.
• In the Trail Name box, type the trail name.
• In the "use template" list, select a trail template for this trail. If you do not want to use a template, select "blank ".
• To save the trace result, Perform one of the following operations:
• Click Save to file to capture the trail to the file. Specify the value of "set maximum file size. The default value is 5 MB. Alternatively, select "enable file rolling update" to automatically create a new file when the file size reaches the maximum value. You can also select "server processing trace data", which is a service that is running a trail, rather than a client application.ProgramTo process the tracking data. When the server processes the tracking data, the event is not skipped even when the pressure is high, but the server performance may be affected.
• Click Save to table to capture the trail to the database table.

• As needed, you can click "set maximum number of rows" and specify the value.

• Select the "enable tracking stop time" check box as needed, and then specify the stop date and time.
• To add or delete events, data columns, or filters, click the Event Selection tab. For more information, see: How to specify the events and data columns of a trace file (SQL Server Profiler)
• Click "run" to start the tracking function.

Note:

If you do not save the trace result to a file or table, you can view the trace when SQL Server Profiler is enabled. However, the tracing results will be lost after the tracing is stopped and the SQL Server Profiler is disabled. To avoid this loss of tracking results, you can click Save on the File menu before disabling SQL Server Profiler to save the results.

1. Select the "enable tracking stop time" check box as needed, and then specify the stop date and time.

2. To add or delete events, data columns, or filters, click the Event Selection tab. For more information, see: How to specify the events and data columns of a trace file (SQL Server Profiler)

3. Click "run" to start the tracking function.

In SQL Server 2008, you can use extended events to eliminate performance problems. Extended events can more deeply explore the internal working principles of SQL Server and can be used in complex troubleshooting situations. For more information, see SQL Server expansion events.

 

Topic	Description
SQL tracking	Describes how SQL tracing works and how to create a trail using stored procedures.
Introduction to SQL Server Profiler	Describes how to use SQL Server Profiler for tracking.
Optimize the performance of utility and Tool	Describes the server operations that can be optimized to Improve the Performance of utility and tool.
Optimize server performance	Describes how to optimize SQL Server to improve server performance.
Event Notification Overview	Describes how Event Notifications work and how to use Event Notifications to monitor and respond to database and server events.
Use SQL Server Management studio for monitoring	Describes how to use SQL Server Management studio to monitor SQL server performance.
Monitor error logs	Describes how to use the SQL Server Error Log and Microsoft Windows Application Log to monitor SQL Server activities.
Monitoring Using Transact-SQL statements	Describes how to use a Transact-SQL statement to monitor SQL Server instances.

Reference

SQL Server event reference

Other resources

Event Notification (database engine)
Help and information

Obtain SQL Server 2008 Help