Index Tuning Wizard

A short SQL Server 2000 tutorial explaining how to use the SQL Profiler in combination with the Index Tuning Wizard to optimize your database.

The SQL Server Tuning wizard is a great tool to automate the creation of indexes in SQL Server, to work best the Tuning Wizard wants a file (or table) with a workload. Workload is a series of Queries, stored procedures etc that are run in your database, to create a Workload we use the Query Profiler, let's tune a database now.

Step 1: Creating the Workload

First we need to launch the SQL Profiler, we can do that by using the Tools menu in the Enterprise Manager, scroll down to SQL Profiler and select it. The profiler now opens a blank screen.

Figure 1

In the SQL Profiler we need to tell our "workload tool" to start a New Trace, a trace is a process that captures all activity in a SQL Server Instance or a database, depending on what you specify in the Trace Events.

Figure 2

I am naming my trace "TestTrace" and i'm happy with the standard SQLProfiler template and i'm not going to save a file, I will store the workload in a Database Table instead. With enable trace stop time we can tell SQL Profiler when to stop storing workload, this is handy for letting a trace run for a few days.

Figure 3

After checking the box "Save to table" the following screen pops up in which we specify the database to store the Workload in. I told it to store my Workload into database FIS_Portal and into a new table called "TestTrace", when we are happy with the Workload we simply tell our Tuning Wizard to use this table.

Figure 4

Now we are ready to add event classes that we want to capture (Trace), I am adding ALL Database classes, ALL Performance classes, Security Audits Login and Logout, ALL Sessions, ALL Stored Procedures and ALL TSQL. Now that seems like alot to capture but we want to tune as specific as possible, and I will let this run for 2 days to capture a degree of Application usage so the proper indexes will be built later on. The more data is available, the better the Tuning Wizard can do it's job, so this is perfect.

Figure 5

You can further define things to Exclude out of the Trace process by using the Tab screens Filter and Data Columns, but as you will see later on in this article this is quite enough info for our Tuning sessions, so I clicked on Run and the following screen shows up. It's interesting to take a look at this from time to time, especially if many users are simultaniously busy with the application / website, although sometimes it gets impossible to track for the human eye, so just let this run for a while.

Figure 6

Great, after our trace ends we have a Database Table that resembles the next screenshot and my table had 13.520 rows of usable data. That should make for a pretty nice workload, don't you agree ?

Figure 7

Goto Part 2

Dennis van Galen

Dennis van GalenDennis started his career as order picker in warehouses. In the past 10 years he did alot of data-entry work for Government agencies and around the age of 20 he helped clean the KPN Telecom customer databases. At the age of 27 Dennis returned to KPN Telecom where he was a full time webmaster / webdeveloper. In his spare time he used to be a voluntary Manager for DMXzone.com. After leaving KPN in 2012 Dennis worked for Tevreden.nl on webbased customer satisfaction platforms.

In the past 12 years Dennis became experienced with various webtools, web-languages and database systems.

See All Postings From Dennis van Galen >>

Comments

Be the first to write a comment

You must me logged in to write a comment.