Use SQL Server Profiler to monitor and optimize Databases

SQL Server Profiler is a performance monitoring tool for MS SQL. You can use this tool to find the SQL statements to be optimized, and then use the database engine optimization advisor tool to help optimize the database.

SQL Server Profiler usage

 

 

Click Start -- program -- Microsoft SQL Server 2005 -- performance tool -- SQL Server Profiler

 

For example

 

 

The following page is displayed:

 

 

Click file --- [Create Trail (N)...], which is a tool with multiple windows. We can create different tracking windows at the same time or different databases.

 

See

 

 

Here, we enter the server name, user name, and password of the database we tracked. Click Connect to go to the next page.

 

 

You can perform basic settings, Template Selection, and file storage selection on the left. We usually use the default option, so we don't need to touch the above. The picture on the right is the event selection. That is to say, the event we want to track has one to one options here, basically, SQL events can be tracked, including the process of using SQL Server Management Studio to operate databases. You can take a look at the specific events and descriptions.

 

You can select all events by clicking show events.

 

We can also filter the statistical fields and click any column title to view the column description, as shown in

 

 

We will explain the following in sequence:

 

TextDate depends on the text value of the event class captured in the trail;

 

ApplicationName: the name of the client application connecting to SQL Server. This column is filled by the value passed by the application, rather than by the displayed program name;

 

NTusername Windows user name.

 

LoginName user's login name (SQL Server Security Login or Windows login creden, in the format of "domain \ User Name ")

 

The CPU time (in milliseconds) used by the CPU event ).

 

The number of times that the server Reads the Logical Disk from the event.

 

The number of times the Writes event is written to a physical disk by the server.

 

The time used by the Duration event. Although the duration of the Server is measured in microseconds, SQL Server Profiler can display this value in milliseconds, depending on the settings in the tools> Options dialog box.

 

The process ID of the SQL Server application called by ClientProcessID.

 

SPID: the ID of the Server process allocated by SQL Server for the client-related processes.

 

The start time of the StratTime event (if available.

 

The end time of the EndTime event. This column is not filled with event classes that indicate the start of an event (for example, SQL: BatchStarting or SP: Starting.

 

BinaryData depends on the binary value of the event class captured in the trail.

 

 

 

Then we can click "run". If you are interested, you can also arrange and filter the columns. You only need to click the corresponding button below to operate as prompted, here we will install the default settings.

 

 

 

 

Through the above figure, we can clearly track the process of each step,

 

Now, if you do not know much about the writing of the SQL statements of that edition, you can refer to the above, and it is always time-honored, after you perform a few operations in SQL, the corresponding SQL statements will appear, which is also a good learning and improvement tool.

 

In addition, we can analyze, query, and track the data, pause, start, and stop operations, Start Multiple traces at the same time, and track different databases and tables at the same time.

 

It would be better to use it with the database engine optimization consultant of SQL. We can analyze the performance of your SQL statements and tell you how to make better changes. Let's take a look.

 

 

 

Database Engine Optimization consultant usage

 

 

 

 

 

With this tracking record, how can we use the database engine optimization consultant to analyze and optimize it? Of course, the first step is to export the tracked records to a file of the. trc type. Click file -- save

 

 

If we save the file as 123.trc

 

Now let's open the database engine optimization consultant. We click Start -- program -- Microsoft SQL Server 2005 -- performance tool -- database engine optimization consultant.

 

For example

 

 

As above, we need to enter the login information of the database first.

 

Click Connect To Go To The following page:

 

 

 

Let's take a look at the interface. Generally, you don't have to select the default setting as long as it is OK. I have comments above the configuration. Here, you must remember to select the database and table used for workload, which means to set the database to be analyzed or the analysis will fail.

 

Now you can click Start.

 

 

Here we can see the analysis report after the analysis is successful.

 

Recommendation Tab

 

 

Here we also show your tables that need to be optimized. How should we create indexes and views to improve performance more effectively? What's more fun is that even the SQL statements that need to be optimized are generated,

 

We just need to copy and execute it.