Data Management

Tweak application performance with the Index Tuning Wizard

Debugging is a critical aspect of any development project. SQL Server provides the Profiler tool and the Index Tuning Wizard to aid you in the search for rogue code in your applications.

You can utilize the SQL Server Profiler tool to gather important information about current server activity. The trace that is captured from this Profiler tool, which contains the database’s actual workload, can be used in a variety of situations. Let's take a look at how to use Profiler to gather the data used with the Index Tuning Wizard, and how you can use these tools to debug your applications.

When is it used?
The Profiler tool can be useful in all phases in the database development life cycle. For example, during initial development, you can employ it to aid in debugging and or stepping through how and when your application is calling stored procedures and other SQL statements. On a project I'm involved with, we recently used Profiler to identify SQL statements and the order in which the application called them when working with a compiled component that was timing out when it called the database. Using the tool helped us uncover an error in logic, and we were able to correct our application.

Later in the development process, Profiler can aid in identifying application bottlenecks during load/stress testing. You can also use it to monitor regular activity, perform security audits, and identify other hindrances to performance (e.g., poorly designed queries).

In Profiler, you must decide what to log and consider where to store the recorded database activity. You can choose to save your profile trace to either a database table or a file. When you choose to save the captured data to a table, you can also set a maximum number of rows to capture. That way, Profiler can quickly capture vast amounts of data in a heavily utilized system. But because of this, you may want to restrict the total amount of data that Profiler captures in a trace.

Your can choose to save the profile to a trace file. With this option selected, you can have the file information overwritten when it reaches a certain size, which will limit the total size of the captured file. Also, you can choose where you want the processing of the trace to occur. This is much like choosing between having a cursor execute on the client or server in your application level code. In a production system, you probably don't want to place such a workload on the server, so using client-side processing may be the best choice.

When choosing between these two options, your main focus should be the impact upon database/application performance. As with most types of logging, saving to a file places less of a burden on your system than saving to a database table, so for high-traffic production systems, logging to a trace file may be the better option.

Profiler in action
You can use Profiler to create and save a trace that is usable by the Index Tuning Wizard. In order to use Profiler to gather data for the wizard, you can select the default template that comes with SQL Server. To do this, choose New | Trace on the File menu. On the General tab (Figure A), select the default template for the wizard. You can see that you have the ability to save the trace to a file or to the database.

Figure A
Profiler's General tab

On the Events tab (Figure B), there are two event types with which the wizard is concerned: RPC:Completed for stored procedures and SQL:BatchCompleted for other TSQL statements.

Figure B
Profiler's Events tab

When capturing SQL Server activity using Profiler, you can obtain a great deal of data in a relatively short period of time, depending on your utilization of the server. So you should choose only the items that you require instead of altering the default template. Also, when running Profiler, it is critical to the quality of the Index Tuning Wizard’s recommendations that you ensure that typical application activity occurs during the capture period, unless you're trying to attack a certain problem, in which case you must make sure that the specific event is taking place.

Once a trace has been saved either to a file or a database table, you can replay the session and step through the activity—setting breakpoints if you wish—much as you do when using a debugger from a developer’s IDE, as shown in Figure C.

Figure C
Replaying a Profiler trace

Again, the captured data can be substantial. To help limit the amount of data captured, you can define filters. It's common to look for queries that take longer than a certain amount of time when defining your filters (Figure D).

Figure D
Filtering profile data

There are numerous other types of database activities Profiler can record. And there are many options you can select from within Profiler. For more information, I’d recommend consulting MSDN Online or the SQL Server documentation. Profiler can be any DBA's friend, and I recommend that you investigate its many additional options.

Editor's Picks