What to do when SELECT query is Running Slowly?

In this post, i would like to give you 10 simple tips to follow when query is running slowly.

1) Review objects involved in the Query.
-- 1.1) Make sure you dont have any other columns in the SELECT clause which are not used any where in the application or in any other code.
-- 1.2) Make sure you are not using * to retrieve the columns from the table when you are not interested in all the columns.

2) Close look at the tables and their structures.
-- 2.1) Gather the No.Of Records in the table and Access pattern of the table.
-- 2.2) Make sure the table is partitioned if it holds huge amount of data.
-- 2.3) Gather the Indexes information on these tables.
-- 2.4) Make sure you are using Alias to the tables, if you have too many tables in FROM clause.

3) Very Close look at the Joining conditions in WHERE clause.
-- 3.1) Verify the joining conditions between the tables.
-- 3.2) For N tables in FROM clause you should have atleast N-1 where conditions.
-- 3.3) Make sure your query is not producing cartesian product.

4) Check on the Joining columns for data type conversions.
-- 4.1) Make sure you are not comparing Numbes with Char. etc. If you are doing this then there is an issue in the Table design. If you can't alter the column the try to denormalize the table. which means have duplicate information. If this takes less time to implement.

5) Check on the Joining columns for any functions
-- 5.1) Make sure you are not using any functions on the Joining columns. Its important to note that if you use any functions on the indexed columns in WHERE clause then engine will not use the Index to fetch or locate the data.

6) Check these Joining columns against Indexes on the table and Order of the columns in the Index.
-- 6.1) Make sure the order of the columns in WHERE clause should match with the Order of the columns in the Index.

7) Look at the Actual Execution Plan and see if there is anything strange.
-- 7.1) Look at the execution plan and take necessary actions like adding hint or creating a covering Index or Indexed views or make use of the temp table to break the logic of the query critieria.

8) Check whether statistics are up to date.
-- 8.1) This is very important step. If the stats are not up to date then this can lead the engine to take wrong path to locate the data and taking wrong path means that full scan is made when an index scan would have been appropriate.

9) Run the trace to find more information around the query.
-- 9.1) SQL Server Profiler shows how SQL Server resolves queries internally. SQL:BatchCompleted event and the RPC:CompletedEvent events should capture performance for all your SQL batches and stored procedures. The main attributes in trace are Reads, Writes, Duration, CPU, EndTime.

10) Run the Query using Database Engine Tuning Advisor.
-- 10.1) Database Engine Tuning Advisor is used examine how queries are processed in the databases and then it recommends how you can improve query performance by providing tips on having additional indexes.

This article is mainly to give general tips and these are very common tips. There are no hard and fast rules to solve Query performance issues. Its completly based on the queries and the requirement and most importantly, it is specific to the environment.

Hopefully i can come up with an article with all the measures.