SQL Server Profiler – Collect Workload data for Database Engine Tuning Advisor

Many times as DBA, you must have used SQL Server profiler and Database Engine Tuning Advisor to drill down into performance issue. SQL Server profiler give you an option to collect the information related to SQL Server events which includes stored procedure execution, t-sql, triggers, connections and many more.

As database administrator, you may get request to optimize the bad running queries that hurts SQL Server database performance. So now you need to tune up the sql query to improve the performance of SQL server database, here you can use the Database Engine Tuning Advisor (DTA).

Database Engine tuning advisor requires workload file to analysis and give the recommendation on issue. Using SQL server Profiler, how should you capture this workload for use with the DTA ?

Answer is using the predefined tuning template, it is recommended that you use the predefined tuning template in sql server profiler to capture the required workload. A workload is a set of transact-sql statements that will be executed against the database you want to tune.

Leave a Reply




+ 9 = 16