SQL Server Profiler

SQL Server Profiler records detailed information about activity occurring on the database server. SQL Server Profiler can be configured to watch and record one or many users executing queries on SQL Server and to provide a widely configurable amount of performance information, including I/O statistics, CPU statistics, locking requests, Transact-SQL and RPC statistics, index and table scans, warnings and errors raised, database object create/drop, connection connect/disconnects, stored procedure operations, cursor operation, and more. For more information about what SQL Server Profiler can record, see SQL Server Books Online.

Using SQL Server Profiler with Index Tuning Wizard

SQL Server Profiler and Index Tuning Wizard can be used together to help database administrators create proper indexes on tables. SQL Server Profiler records resource consumption for queries into a .trc file. The .trc file can be read by Index Tuning Wizard, which evaluates the .trc information and the database tables, and then provides recommendations for indexes that should be created. Index Tuning Wizard can either automatically create the proper indexes for the database by scheduling the automatic index creation or generate a Transact-SQL script that can be reviewed and executed later.

These are the steps for analyzing a query load:

To set up SQL Server Profiler (Enterprise Manager)

  1. On the Tools menu, click SQL Server Profiler.
  2. On the File menu, point to New, and then click Trace.
  3. Type a name for the trace.
  4. Select Capture to file, then select a .trc file to which to output the SQL Server Profiler information.

To run the workload (Enterprise Manager)

  1. On the Tools menu, click SQL Server Query Analyzer.
  2. Connect to SQL Server and set the current database to be where the table was created.
  3. Enter these queries into the query window of SQL Server Query Analyzer: 

    SELECT ckey1,col2 FROM testtable WHERE ckey1 = 'a' 

    SELECT nkey1,col2 FROM testtable WHERE nkey1 = 5000
  4. On the Query menu, click Execute.

To stop SQL Server Profiler

  1. On the File menu, click Stop Traces.
  2. In the Stop Selected Traces dialog box, choose the traces to stop.

To load the .trc file into the Index Tuning Wizard (SQL Server Profiler)

  1. On the Tools menu, click Index Tuning Wizard, and then click Next.
  2. Select the database to analyze, and then click Next.
  3. Make sure I have a saved workload file is selected, and then click Next.
  4. Select My workload file, locate the .trc file created with SQL Server Profiler, click OK, and then click Next.
  5. In Select Tables to Tune, select the tables, and then click Next.
  6. In Index Recommendations, select the indexes to create, and then click Next.
  7. Select the preferred option, and then click Next.
  8. Click Finish.

This is the Transact-SQL generated by Index Tuning Wizard for the sample database and workload:

/* Created by: Index Tuning Wizard     */

/* Date: 9/7/98             */

/* Time: 6:42:00 PM             */

/* Server: HENRYLNT2             */

/* Database : test             */

/* Workload file : E:\Mssql7\Binn\Profiler_load.sql */

  

USE [test] 

BEGIN TRANSACTION

CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1])

if (@@error <> 0) rollback transaction

CREATE NONCLUSTERED INDEX [testtable1] ON [dbo].[testtable] ([nkey1])

if (@@error <> 0) rollback transaction

COMMIT TRANSACTION

  

The indexes recommended by Index Tuning Wizard for the sample table and data are expected. There are only five unique values for ckey1 and 2,000 rows of each value. Because one of the sample queries (SELECT ckey1, col2 FROM testtable WHERE ckey1 = a) requires retrieval from the table based on one of the values in ckey1, it is appropriate to create a clustered index on the ckey1 column. The second query (SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000) fetches one row based on the value of the column nkey1. nkey1 is unique, and there are 10,000 rows; therefore, it is appropriate to create a nonclustered index on this column.

SQL Server Profiler and Index Tuning Wizard are powerful tools in database server environments in which there are many tables and queries. Use SQL Server Profiler to record a .trc file while the database server is experiencing a representative set of queries. Then load the .trc file into Index Tuning Wizard to determine the proper indexes to build. Follow the prompts in Index Tuning Wizard to automatically generate and schedule index creation jobs to run at off-peak times. Run SQL Server Profiler and Index Tuning Wizard regularly (perhaps weekly) to see if queries executed on the database server have changed significantly, thus possibly requiring different indexes. Regular use of SQL Server Profiler and Index Tuning Wizard can keep SQL Server running in top form as query workloads change and database size increase over time.

For more information, see SQL Server Books Online.

Analyzing SQL Server Profiler Information

SQL Server Profiler provides an option to log information into a SQL Server table. When it is complete, the table can be queried to determine if specific queries are using excessive resources.

To log SQL Server Profiler information into a SQL Server table (Enterprise Manager)

  1. On the Tools menu, click SQL Server Profiler.
  2. On the File menu, point to New, and then click Trace.
  3. Type a name for the trace, then select Capture to Table.
  4. In the Capture to Table dialog box, enter a SQL Server table name to which to output the SQL Server Profiler information. Click OK.
  5. On the File menu, click Stop Traces.
  6. In the Stop Traces dialog box, choose the traces to stop.

For more information, see SQL Server Books Online.