Banner

Tune your database using SQL Profiler

Back to Blog Listing


The following list of steps can be followed to tune your SQL Server Database using SQL Profiler, providing an estimated benefit of 98%. 

1)    Get Database ID (In SQL Server Management Studio run the query SELECT DB_ID() )

tuning-1.png

2)     Open SQL Server Profiler and create a new trace (file -> new trace). Give the trace a name and select the "TSQL_Replay" template

tuning-2.png 
3)     From the "Events Selection" tab click the "Column Filters" button. On the pop up select "DatabaseID" from the left hand list and then expand the "Equals" item on the right and enter the ID of your database to the text box

tuning-3.png

Select "Run"

*Do whatever function needs tracing against the database i.e. opening area manager or loading custom page*

Stop the trace and then save the trace file (file -> Save as -> Trace file)


4)     From the "Tools" menu select the "Database Engine Tuning Advisor"

tuning-4.png

Select the trace file you just saved.

5)     Select the database for workload analysis to be the same as the one you got the ID for and then select it in the list of databases underneath as well.

tuning-5.png 
6)     In the "Tuning Options" tab select "Advanced Options" and define the max. space for recommendations to be 400MB (or a size large enough that does not cause “not enough space” messages when Analysis is started).

tuning-6.png

7)    Start analysis

tuning-7.png  
8)    Once complete check if there are recommendations.

tuning-8.png

The Estimated improvement above is 98%.

The recommendations are usually Indexes and Statistics. Normally we do not apply Indexes unless they are on the AEXML table, as indexes are constraints and may cause issues with deletion of data during normal usage. It is safe to apply all statistics, these usually provide the majority of the improvement.

To implement the selected recommendations, go to the "Actions" menu and choose to "Apply Recommendations".


15 Jul 2014

About the Author

James CTwo is our blogging alter ego for the Dynamics CRM and Web experts here at C2. We have a passion for anything CRM, Web Design or Social Media. We love writing about it! Find us on YouTube & Twitter.

comments powered by Disqus

Our White Papers

Discover the content that matters to you on business transformation. Explore how you can drive customer engagement, empower your team, optimise your businesses and revolutionise your products with the next digital revolution. For more information visit our white paper library.

Library

C2 Newsletter

Our newsletter is sent on a quarterly basis, offering detailed insights into all aspects of business transformation with Microsoft cloud technologies. We also promote upcoming events and special offers throughout the year. Sign up to get involved today.

Dynamics 365 Office 365 Power BI Silver Partner