Adjusting application performance using the Index Tuning Wizard

Program | index | performance

You can use SQL Server adjuster (SQL Server Profiler) tools to collect important information about the current activity of the server. The information tracked by this profiler tool, which contains the real load of the database, can be used on a variety of occasions. Now let's look at how to use Profiler to collect the data that can be used by the Index Tuning Wizard and how you can use these tools to debug your application.

When do you use it?

The profiler tool can be used at all stages of the database development lifecycle. For example, in the initial phase, you can use it to assist with debugging or to find out how and when your application should call stored procedures and other SQL statements. In a project I recently participated in, a compiled component timed out a call to the database, in which case we used profiler to identify the SQL declarations and the order in which they were invoked by the application. Using this tool helps us to discover logical errors so that we can successfully correct our own applications.

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

In Profiler, you have to decide what to record and consider where the logged server activity should be saved. You can choose to save your tracking information in a database form or in a file. When you choose to save the captured data in a table, you can also set the maximum number of rows to save. In this way, profiler can quickly capture large amounts of data in a task-heavy system. But because of this, you may want to limit the amount of data that you profiler to capture in one trace.

You can choose to save the information you have obtained to the tracking file. If you choose this option, you will be able to overwrite the file information after the file reaches a certain size, which limits the maximum size of the information file to be captured. Also, you can choose where to start the tracking process. It's much like writing code in the application layer to choose whether to execute pointers on the client or on the server side. In a formal product system, you may not want to put such a workload on the server, so it may be the best choice for the client to process.

When choosing between the two options, your primary focus should be on the impact on the database/application performance. For most types of logging work, saving it to a file is less burdensome than saving it to a database, so it might be a better choice to save the day to a file for a high throughput actual product system.

Using Profiler

You can use Profiler to create and save trace results that can be used by the Index Tuning Wizard. In order to use the data collected by the Profiler Wizard, you can select the default template that the SQL Server brings. To do this, choose New in the File menu | tracking (New | Trace). Select the default template for the wizard in the General tab (Figure A). You'll see that you can save the tracking results to a file or to a database.

Figure A

General tab of Profiler

In the Events tab ( figure B), there are two event types associated with the wizard: rpc:completed for Stored procedures, and sql:batchcompleted for other TSQL declarations.

Figure B

Profiler Events Tab

When using a regulator to capture the activity of a SQL Server, you will get a large amount of data in a relatively short period of time, depending on the usage of your server. So you should choose to record only the items you need instead of modifying the default template. And when running Profiler, you want to make sure that typical application activity occurs during capture, which is important to ensure the quality of the Index Tuning Wizard, unless you are trying to solve a particular problem, and in the latter case you have to be sure that the specified event is happening.

Once the trace is saved in a file or database table, you can replay all the activities that occur in the process-setting the breakpoint you want-which is similar to the IDE debugger you use for developers, as shown in figure C .

Figure C

Playback of profiler's tracking content

Again, the data captured will be quite a lot. To help limit the amount of data being captured, you can define a filter. When you define your own filters (figure D), it is common to set up queries that are longer.

Figure D

Filter data

Profiler can also record many other types of database activity. Profiler itself has many options for you to choose from. For more information, I recommend that you check the documentation for MSDN Online or SQL Server. Profiler will be a friend of any DBA, and I suggest you examine many of its other options.