Performance Tuning

http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

Hi sarabhai,

There are several aspects to look at in performance tuning, you can get some basic one here:

http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

Oops, i'm 3 minutes late with the same link.

If you find that too basic for you, you might want to check out articles at SQLServerCentral:

http://www.sqlservercentral.com/tags/T-SQL/Performance+Tuning/

I personally find SQLServerCentral among the best place to get really good advice on SQL performance.

In general for SQL query tuning  which factors are to be used? like in your query don't use not exist other factor that take more time should replace with other equivalent alternative.

Did you even look at the last link i suggested to you?
all performance hints gathered there.
for example these topics is in your case:

http://www.sql-server-performance.com/tips/t_sql_select_p1.aspx
http://www.sql-server-performance.com/tips/t_sql_other_p1.aspx
http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx
http://www.sql-server-performance.com/tips/hints_query_p1.aspx

Sarabhai -

The provided links are all great. Also look at Inside SQL Server T-SQL Querying book from Itzik Ben Gan this goes over the basics of how the optimizer thinks about a query and walks through different constructs.

Then learn through empirical evidence and experimentation. Run query with the actual execution plan included. Look up the operators in books online to understand why they are used where they are and what they mean.

Before you run a query run SET STATISTICS IO ON and look at the messages tab. Which tables had the highest reads? Look at the execution plan and ask yourself why they were higher based on the operators chosen.

Then look at the query and ask how can you better express what you are asking for. How can you help ensure an index is used (think of how you search an index.. you can easily find last_name begins with 'WAL' in the phone book with a seek or perhaps an ordered scan... You can't find "last name contains 'als' unless you scan every single last name to see if it has that string... the seek means less reading and smaller number of rows to join).

Make sure you have the right indexes based on the plans. Compare the total subtree cost of the plan and the reads in the IO Statistics before and after tuning. Did you make a difference?

some performance steps need to required for it