After we have done our ETL development we need to understand if the Data Warehouse is optimized to run the workload created by the ETL processing, a very good and easy approach is to use the Database Engine Tuning Advisor available on the SQL Server Management Studio.
1º Prepare the SQL Server Profiler to get the Workload file.
A) Open the SQL Server Profile, choose the Tuning Template and save the trace into a file, (don’t forget to set the maximum file size to an adequate value that fits to your test's purpose)
B) Once the Database server might host more databases than the Data Warehouse, we need to filter the trace file to have only the workload of the Data Warehouse database.
2º Execute the ETL process to create the workload to be optimized.
A) Start the Profiler
B) Execute the ETL process.
C) Stop the Profiler.
3º Get the Recommendations from the Database Engine Tuning Advisor for the workload captured.
After you have the trace file of the Workload that you want to improve the performance, is time to use the Database Engine Tuning Advisor to analyse that Workload.
A) Open the Database Engine Tuning Advisor
B) Execute the analysis of the workload file
1º) Select the workload file, (the SQL Server Profiler trace file);
2º) Choose the Data Warehouse for workload analysis;
3º) Select Databases and tables to tune.
4º) click “Start Analysis” bottom.
After the analysis execution the recommendations are shown and also the estimated improvement, (%).
Regarding the Recommendations, you might have three options, apply recommendations immediately, apply recommendations using a scheduler, or save the