Identified and tuning your Query to improve Performance

As a part of my current job I have got opportunity to work with production server with so much work load on database. There were million records in a table and Query tooks too much time to execute. I engaged to find out performance tuning of your query. That time I was little confusion how we can identified improve our query performance. Finally I have come with one solution.

SQL have tool called SQL Profiler tool have tuning template to calculate work load for database. SQL profiler tool capture event that were performing of current database.

From Trace property --> Generate template we select “tuning” template and save it as .trc file.  With help of this we can create work load of our query.  Now all of us have question how to improve our query performance till now we only generate .trc file. How it will used to improve our query? Now be attention SQL having one tool called Database Engine Tuning Advisor – that can help you determine, tune and monitor your indexes.

In this article, I will explain how to use these tools to get answers to the following questions:

• Which indexes do I need for my queries?
• How do I monitor index usage and their effectiveness?
• How do I identify redundant indexes that could negatively impact performance of my DML queries (insert, updates and deletes)
• As workload changes, how I do I identify any missing indexes that could enhance performance for my new queries?

Find the right indexes for your work load

Determining exactly the right indexes for your system can be quite a taxing process. For example, you have to consider:

• Which columns should be indexed (based on your knowledge of how the data is queried)
• Whether to choose a single-column index or a multiple column index
• Whether you need a clustered index or a non-clustered index
• Whether or not (in SQL 2005) you could benefit from an index with included columns to avoid bookmark lookups
• How to utilize indexed views (which the optimizer might access instead of the underlying tables to retrieve a subset of your data)

.DTA can analyze both OLTP and OLAP workloads. You can either tune a single query or the entire workload to which your server is subjected. Based on the options that you select, you can use the DTA to make recommendations for several Physical Design Structures (PDS), which include:

• Clustered indexes
• Non-clustered indexes
• Indexes with included columns (to avoid bookmark lookups)
• Indexed views
• Partitions

Following step to import trace file into DETA tool

1)    Create New Session

2)    Import .trc file as per following screen shot

3)    Select database for work load analysis

4)    Press “Start Analysis” button to identified improvement of your work load.