Lesson 3 Using SQL Server Profiler, Exercise 2: Tracing SQL Server Activit смотреть онлайн

Overview
SQL Server Profiler is one of the most important tool in analyzing SQL Server database performance. In this exercise we will create a profiler trace to log activity in the AdventureWorks2019 database, view the event data, and then correlate the Profiler trace with the Performance monitor data collector set.
Lesson 3: Using SQL Server Profiler
SQL Server Profiler provides the ability to trace server and database activity such as login, user, and application activity. It is a graphical user interface tool for monitoring an instance of the SQL Server Database Engine or Analysis Services. It shows how SQL Server resolves queries internally, enabling administrators to view exactly which Transact-SQL statements are submitted to the server and how the server accesses the database to return the result sets. You can capture and save the data in a table, file, or Transact-SQL script for later analysis.
Using SQL Server Profiler, you can:a. Create a trace that is based on a reusable template.b. Watch the trace results as the trace runs.c. Store the trace results in a table or file for further analysis.d. Start, stop, pause, and modify the trace results as necessary.e. Replay the trace results.
Use Profiler to monitor only the events you are interested.You can filter events based on the information you want. Monitoring too many events adds overhead to the server and monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes over a long period of time.
Activity you might want to monitor with Profiler includes:a. Poorly performing queries.b. Activities of individual users or applications.c. Queries that cause table scans.d. Performance of the tempdb database.e. Deadlock problems.f. Login attempts, failures, connections, and disconnections.g. Logical disk reads and writes.h. CPU use at the statement level.i. Wait times for all post execution events.
You can set system stored procedures in SQL Server Profiler to trace a defined set of events and filter the level of information collected about those events.
By using sp_trace_create system stored procedure, you can trace specified events on your server.