How to collect important information with SQL Server adjuster

Use the SQL Server adjuster (SQL Server Profiler) tool 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