Databases Reference
In-Depth Information
or use the partitioned tables functionality of SQL Server. As the second step of your
update, you need to build indexes for these tables.
Another solution is to drop indexes for your databases before updating data warehouse
tables and then rebuild indexes afterward.
One of the hardest problems in tuning relational database performance is coming up with
a set of indexes best suited to the workload for your tables.
Fortunately, SQL Server provides the Database Engine Tuning Advisor, which enables you to
analyze specific query workload and suggests indexes to help optimize query performance.
For example, let's run the Database Engine Tuning Advisor to optimize the performance of
relational tables in the FoodMart 2008 relational database for the types of queries Analysis
Server sends during processing.
To prepare, you need to capture a trace containing a workload of relational queries
Analysis Services generates, as follows:
1.
Start SQL Server Profiler. Create a new trace. Connect to your relational database
holding the FoodMart 2008 database. Then, on the TraceProperties page, select the
TSQL_Replay template and click the Run button to start tracing your SQL Server.
2.
Open SQL Management Studio. Connect to Analysis Services and fully process the
FoodMart 2008 multidimensional database.
3.
When Analysis Services completes processing of your multidimensional database,
stop the trace in the SQL Server Profiler. Save your trace to a file. We saved the trace
to c:\tmp\ProcessingTrace.trc file.
After you have captured queries in a trace file, you are ready to start using the Tuning
Advisor:
4.
Open the Database Engine Tuning Advisor either from the Start menu or from Tools
menu in SQL Server Management Studio or SQL Server Profiler.
5.
When you are in the Database Engine Tuning Advisor, connect to your SQL Server
relational database. You will see a new session opened for you, and you will see a list
of the database on your server, similar to that shown in Figure 19.1.
6.
In the Workload text box, specify the name of the trace file you used to capture the
query load, and select FoodMart 2008 as the Database for Workload Analysis.
7.
In the Select Databases and Tables to Tune list, select FoodMart 2008, as shown in
Figure 19.1. At this point, you are ready to start analyzing your query workload.
8.
Click the Start Analysis button to start analyzing your query workload.
A progress window will display. In about a minute, depending on the speed of your rela-
tional database server, you will see results of analysis similar to that shown in Figure 19.2.
The Database Engine Tuning Advisor can make various recommendations. In our example,
it recommended to create quite a few new indexes (as shown in the Index Recommendations
list).
Search WWH ::
Custom Search