|
Return to Newsletter Contents...
Generating Indexes and Statistics with SQL Server Profiler and Database Engine Tuning Advisor
By Charles Tournear, Senior Consultant, MCT, MCSE, MCSD, MCDBA, CRCP
In this article we will look at issues related to using the SQL Server
Profiler and the Database Engine Tuning Advisor to generate indexes and statistics. It would seem that the logical
thing to do would be to record data using the profiler during the busiest time of day and then analyze the trace to find
recommended indexes and statistics to improve performance. But this is not always a functional option; because of the
amount of data that could be collected, the Tuning Advisor may not be able to analyze everything in the trace. You first
need to know what to trace in the profiler and then how much of what was traced to use for analysis.
The first step is to understand how to create an appropriate trace in
the profiler. When you create a new trace in the Profiler, you are first asked to connect to a specific SQL server.
Next you see the Trace properties form.
The easiest way to configure a trace is to select from the existing
templates. The most commonly used templates in early versions of the profiler were: Standard, TSQL_SPs, and
TSQL_Duration.
The standard template is a basic beginning point for creating your own
trace. It records basic query and stored procedure activity. For stored procedures it would record only the exec
procedure statement. To perform index analysis we need to record the actual statements that run inside the stored
procedure.
TSQL_Duration records sql statements from client applications and
orders them by the length of duration of the statement. This would seem to be the one we would want to use, because the
goal of creating indexes and statistics is to speed up slow queries. But again if the primary activity is executing
stored procedures, there won’t be any real statements to analyze in this trace.
The TSQL_SPs template is used if you want to replay the individual
steps in analyzing stored procedures. This records so much data that the Tuning Advisor may not be able to analyze all
the data.
A new template is “Tuning”. It is like a cross between TSQL_SPs and
Duration. It captures the steps within a stored procedure as well as regular SQL statements. Since we are trying to
perform index analysis we shall use this template.
In order to use the Tuning Advisor against the trace data we need to
save the trace data to a file. Check the option to save to file and specify a directory path and filename for the trace
file. The default property for the save to file is to enable file rollover and use 5 MB files. This will fill one file
up to 5MB and then create and new file. So with this option you may have several files containing data. It’s better to
just analyze 1 file. So increase the maximum from 5 to the size required for the amount of time you plan on recording -
for example 1024 to create a 1 GB file.
Next you may want to analyze just the processing for a single database
for analysis. Select the Events Selection tab.
On the Events Selection tab, notice that the DatabaseName is one of the
columns being output in this template. The output columns are not the only columns of data that can be used for
filtering. Check the Show all columns check box if you want to use columns for filtering other than the ones listed for
output.
To create a filter select the Column Filters button and then set the
Like or Not Like values for the columns that you want to filter by. For example you may want to filter to record only
queries for a specific database. Another consideration is that queries that are already performing adequately do not
need to be analyzed and could slow down the process of analysis. You may want to specify a duration value greater than
2 sec.
Once the properties of the trace have been defined, click Run to start
the Trace. After the trace data has been recorded, Stop the Trace in the profiler by selecting Stop Trace from the File
menu or clicking on the stop trace button on the toolbar.
Next we can begin the analysis of the trace data. The Database Engine
Tuning Advisor is an independent program that can be run from within the Profiler, within the SQL Server Management
Studio and from the SQL Server menu under Performance Tools.
When you first start the Tuning Advisor, you will be prompted to
connect to a SQL Server that you want to analyze. Then you’ll see the application interface shown below.
Select the trace file that you created by clicking the binoculars
button to browse for the file. Below the file name/path select the database you wish to analyze from the Database to
workload analysis drop down list. Select the database or databases you wish to tune from the list at the bottom and
click Start Analysis button on the toolbar. The tuning process will go through several phases. Once completed, you can
select Save Recommendations from the Action menu to generate a SQL script file with code for creating indexes and
statistics for you databases.
This has been a general overview of the performance tools that are
available with SQL Server 2005 and 2008 that can be used to improve the speed of stored procedures and queries. For
more detailed configuration options, look up the tools in the books online.
|
