Database optimization Practices: Performance testing Tools

Diagnosing database performance problems as doctors diagnose patients, it is necessary to combine their own accumulated experience and rely on scientific diagnostic reports to determine exactly where the source of the problem is. In the previous three articles we introduced many methods to optimize database performance, although it is important to master the optimization skills, but the diagnosis database performance problem is the premise of optimization, this article describes how to diagnose database performance problems.

Step eighth: Use SQL Profiler and performance monitoring tools to effectively diagnose performance problems

SQL Profiler is probably the most famous performance troubleshooting tool in the SQL Server application domain, and in most cases, when you get a performance problem report, you typically start it for diagnostics first.

As you may already know, SQL Profiler is a graphical tool for tracking and monitoring instances of SQL Server, primarily used to analyze and measure TSQL performance on the database server, and you can capture each event on the server instance and save it to a file or table for later analysis. For example, if the production database is slow, you can use SQL Profiler to see which stored procedures are too time-consuming to execute.

Basic usage of SQL Profiler

You may already know how to use it, so you can skip this section, but I will repeat that there may be a lot of beginners reading this article.

1 Start SQL Profiler, connect to the target database instance, create a new trace, specify a trace template (the trace template presets some events and columns for tracking), as shown in Figure 1;

Figure 1 Selecting a trace template

2 As an optional step, you can also select specific events and columns

Figure 2 Selecting the events to be captured by the trace process