Monitoring an application, Microsoft® SQL Server™, or the operating system environment (hardware and software), involves:
The events determine the activities that are monitored and captured. These events depend on what is being monitored and why. For example, when monitoring disk activity, it is not necessary to monitor SQL Server locks.
The event data describes each instance of an event as it occurred. For example, when monitoring lock events, it is useful to capture data that describes the tables, users, and connections affected by the lock event.
Limiting the event data allows the system to focus on the specific types of events pertinent to the monitoring scenario. For example, when the slow queries for a database are monitored, a filter can be applied to monitor only the queries issued by the application against a particular database that take more than 30 seconds to execute.
This is the process of actively monitoring the application, SQL Server, and so on, to see what is occurring. For example, when disk activity is monitored using Windows NT Performance Monitor, it captures and displays the event data, such as disk reads and writes, to the screen.
This allows the data to be analyzed at a later time or even replayed (when using SQL Server Profiler). Captured event data is saved to a file that can be loaded back into the tool that originally created the file for analysis by a user. SQL Server Profiler also allows event data to be saved to a SQL Server table. Saving captured event data is vital when creating a performance baseline. The performance baseline data is saved and used when comparing recently captured event data to determine if performance is optimal.
This includes the events themselves, event data, and filters that are used to capture data. These files can be used to monitor a specific set of events at a later time without redefining the events, event data, and filters. For example, if you frequently want to monitor the number of deadlocks and the users involved in those deadlocks, you can create a file defining those events, event data, and event filters; save the definition; and reapply the filter the next time you want to monitor deadlocks. SQL Server Profiler uses trace definition files for this purpose.
The saved, captured event data is loaded into the application that captured the data. For example, a captured trace from SQL Server Profiler can be reloaded into SQL Server Profiler for viewing and analysis. Analyzing event data involves determining what is happening and why. Using this information allows you to make changes that can improve performance, such as adding more memory, correcting coding problems with Transact-SQL statements or stored procedures, changing indexes, and so on, depending on the type of analysis performed. For example, using the Index Tuning Wizard, a captured trace from SQL Server Profiler can be analyzed automatically and index recommendations can be suggested and created.
This allows you to establish a test copy of the database environment from which the data was captured and replay the captured events as they originally occurred on the real system. You can replay them at the same speed as they originally occurred, as fast as possible (to stress the system), or more likely, replay them one step at a time, which allows you to analyze the system after each event has occurred. To determine the effects of the events, this allows you to analyze the exact events that occur on a production system in a test environment, thereby preventing any effect on the production system. Because the captured events can be replayed, testing and analysis can be repeated until the problem is fully understood. Only SQL Server Profiler allows you to replay captured events.