How to use SQL-SERVER profiler for database tuning - Netwoven
Blog

How to use SQL-SERVER profiler for database tuning

By Mani Shankar  |  Published on April 15, 2014

Netwoven Blog

Background

Suppose you are a Database developer. You have created lot of tables, views, SP etc. for your development. You have also put the required indexes on the tables for query optimizing etc. Now your database is deployed to production environment and you found that everything is running properly.

Now after long period of time of performance the data volume will be increased and you found that your database performance has been degraded as compared to the previous while there was a small volume of data. Now once again going through the database design, putting the new indexes is not an easy task to do, it may take huge effort and at last it may not be fruitful at all.

SQL-SERVER has SQL Server-Profiler tool to help us optimize our database.

Steps

It’s basically a four steps process:

Step 1: Generate a TRACE/LOAD file for selected database.

Open SQL Server Profiler

How to use SQL-SERVER profiler for database tuning

Click Connect to the server

How to use SQL-SERVER profiler for database tuning

Select ‘Tuning’ template of ‘General’ tab.

How to use SQL-SERVER profiler for database tuning

Select ‘Column Filters’ of ‘Events Selection’ tab.

How to use SQL-SERVER profiler for database tuning

Choose the filter options. E.g. Database name ‘ABC’ in this case on which the trace/load file for query optimization will be generated.

How to use SQL-SERVER profiler for database tuning

Click ‘Run’ button.

How to use SQL-SERVER profiler for database tuning

Click ‘Stop Selected Trace’.

How to use SQL-SERVER profiler for database tuning

Save the Trace file.

How to use SQL-SERVER profiler for database tuning
How to use SQL-SERVER profiler for database tuning

Step 2: Put that LOAD file to Database Tuning Wizard.

Open ‘Database Engine Tuning Wizard’

How to use SQL-SERVER profiler for database tuning

Select ‘File’ and Database Name of ‘General’ tab and then ‘Start Analysis’.

How to use SQL-SERVER profiler for database tuning

Step 3: Check the suggestions/definition made by Tuning wizard.

How to use SQL-SERVER profiler for database tuning

Step 4: Implement those in the Database tables.

As per the definition implement the suggested indexing into the table/s.

Conclusion:

In this blog I have described how we can use the inbuilt SQL Server tool SQL Server-Profiler for query optimization.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *

Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Unravel The Complex
Stay Connected

Subscribe and receive the latest insights

Netwoven Inc. - Microsoft Solutions Partner

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex