Use the index adjustment wizard to adjust the application performance _ MySQL

You can use the SQL Server regulator (SQLServerProfiler) tool to collect important information about the current server activity. The information tracked by this Profiler tool (including the real load of the database) can be used in multiple scenarios. Now let's take a look at how to use Profiler to collect data that can be used by the index adjustment wizard, and how to use it. you can use the SQL Server regulator (SQL Server Profiler) tool to collect important information about the current activity of the server. The information tracked by this Profiler tool (including the real load of the database) can be used in multiple scenarios. Now let's take a look at how to use Profiler to collect data that can be used by the index adjustment wizard, and how to use these tools to debug your application.

When will it be used?

The Profiler tool can be used in all stages of the database development lifecycle. For example, in the initial stage, you can use it to help debug or find out how your application should and when to call stored procedures and other SQL statements. In a project I recently participated in, a compiled component times out when it calls the database. in this case, we use Profiler to identify the SQL statements and the order in which applications call them. Using this tool helps us find logical errors, so that we can successfully correct our applications.

In the later stages of the development process, Profiler can help identify application bottlenecks during load/stress testing. You can also use it to monitor routine activities, perform security reviews, and identify other factors that affect performance (such as poorly designed queries ).

In Profiler, you must decide what to record and consider where the recorded server activity should be saved. You can save your tracing information to a database table or file. When you select to save the captured data to a table, you can also set the maximum number of data rows to be saved. In this way, Profiler can quickly capture a large amount of data in a system with heavy tasks. However, you may want to limit the total amount of data to be captured by the Profiler in one trace.

You can save the obtained information to the tracing file. If this option is selected, you can overwrite the file information after the file size reaches a certain level, which will limit the size of the file to capture information. In addition, you can choose where to start tracing. This is like writing code at the application layer to select whether to execute pointers on the client or on the server. In a formal product system, you may not want to put such a workload on the server. in this case, the client may be the best choice.

When selecting between these two options, you should focus on its impact on database/application performance. For most types of logging, saving a file is less costly than storing it in a database. Therefore, for a high-traffic product system, it may be better to save the day to the file.

Use Profiler

You can use Profiler to create and save the tracing results that can be used by the index adjustment wizard. To use Profiler to collect data from the wizard, you can select the default template that comes with the SQL Server. To do this, select New | Trace from the File menu ). On the General tab (Figure A), select the default template for the Wizard. You can see that the tracing result is saved as a file or stored in the database.

Figure

General tab of Profiler

On the Events tab (FigureB). There are two event types associated with the Wizard: RPC: Completed for stored procedures, and SQL: BatchCompleted for other TSQL statements.

Figure B

Profiler event tab

When using the regulator to capture SQL server activities, you will get a large amount of data in a relatively short period of time, depending on your server usage. Therefore, you should choose to record only the projects you need, instead of modifying the default template. When running Profiler, make sure that typical application activities occur during the capture period, which is very important to ensure the quality of the index adjustment wizard, unless you are trying to solve a specific problem, you should be sure that the specified event is happening in the future.

Once a trail is saved to a file or database table, you can play back all the activities that occurred in this process-set the expected breakpoint-this is similar to using the IDE Debugger of developers, suchFigureC.

Figure C

Play back the tracing content of Profiler

Again, it is emphasized that a considerable amount of data will be captured. To help limit the number of captured data, you can define a filter. When defining your own filter (Figure D), it is very common to set a query that takes a long time.

Figure D

Filter data

Profiler can also record many other types of database activities. Profiler itself has many options for you to choose from. For more information, I suggest you query the MSDN online or SQL Server documentation. Profiler will become a friend of any DBA. In addition, I suggest you examine many other options of Profiler.