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
Click Connect to the server
Select ‘Tuning’ template of ‘General’ tab.
Select ‘Column Filters’ of ‘Events Selection’ tab.
Choose the filter options. E.g. Database name ‘ABC’ in this case on which the trace/load file for query optimization will be generated.
Click ‘Run’ button.
Click ‘Stop Selected Trace’.
Save the Trace file.
Step 2: Put that LOAD file to Database Tuning Wizard.
Open ‘Database Engine Tuning Wizard’
Select ‘File’ and Database Name of ‘General’ tab and then ‘Start Analysis’.
Step 3: Check the suggestions/definition made by Tuning wizard.
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.
Good 1