April 15, 2014

How to use SQL-SERVER profiler for database tuning

2225 Views

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

M1

Click Connect to the server

M2

Select ‘Tuning’ template of ‘General’ tab.

M3

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

M4

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

M5

Click ‘Run’ button.

M6

Click ‘Stop Selected Trace’.

M7

Save the Trace file.

M8

M9

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

Open ‘Database Engine Tuning Wizard’

M10

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

M11

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

M12

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.

One Reply to “How to use SQL-SERVER profiler for database tuning”

Leave a Reply

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