Page 171 - DCAP508_DATABASE_ADMINISTRATION

Unit 12: Tools and Techniques for Monitoring Performance

Notes

Notes As the number of users increases, so does the competition for a server’s resources,
which in turn increases response time and decreases overall.

12.3 Monitoring with SQL Profiler

SQL Profiler is a graphical tool that allows system administrators to monitor events in an
instance of Microsoft® SQL Server™. You can capture and save data about each event to a file or
SQL Server table to analyze later. For example, you can monitor a production environment to
see which stored procedures are hampering performance by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming
too large, you can filter them based on the information you want, so that only a subset of the
event data is collected. Monitoring too many events adds overhead to the server and the
monitoring process and can cause the trace file or trace table to grow very large, especially when
the monitoring process takes place over a long period of time.
After you have traced events, SQL Profiler allows captured event data to be replayed against an
instance of SQL Server, thereby effectively reexecuting the saved events as they occurred
originally.

12.4 Uses of SQL Profiler

Use SQL Profiler to:
Monitor the performance of an instance of SQL Server.
Debug Transact-SQL statements and stored procedures.
Identify slow-executing queries.

Test SQL statements and stored procedures in the development phase of a project by
single-stepping through statements to confirm that the code works as expected.
Troubleshoot problems in SQL Server by capturing events on a production system and
replaying them on a test system. This is useful for testing or debugging purposes and
allows users to continue using the production system without interference.

Audit and review activity that occurred on an instance of SQL Server. This allows a security
administrator to review any of the auditing events, including the success and failure of a
login attempt and the success and failure of permissions in accessing statements and
objects.
SQL Profiler provides a graphical user interface to a set of stored procedures that can be used to
monitor an instance of SQL Server. For example, it is possible to create your own application
that uses SQL Profiler stored procedures to monitor SQL Server.

You must have at least 10 megabytes (MB) of free space to run SQL Profiler. If free space drops
below 10 MB while you are using SQL Profiler, all SQL Profiler functions will stop.

12.5 Starting SQL Profiler

SQL Profiler is started from the Microsoft® Windows NT® 4.0, Microsoft Windows® 2000 or
Microsoft Windows 98 Start menu, or from SQL Server Enterprise Manager.

LOVELY PROFESSIONAL UNIVERSITY 165