Got Optimized Query?

Developer frequently gets email from application users  or clients complaining that the applications or reports are running extremely slow since last night. Often times, you get email from your database administrator saying your queries have topped the DBA list of queries consuming high I/O or CPU resources.

This is annoying when you are focusing on the new development projects approaching strict deadlines or working on production impacting issues. I get these nasty emails sometime myself. therefore you are not alone!!  You don't have to be ashamed of writing bad queries, everyone has done it. The query that is optimized now can be a bad or worst performing queries in few months. Tuning a query is an ongoing process, not a one time deal.

My experience:  DBA thinks developer wrote a poor query and they should fix their query, but tuning a query requires a joint effort from DBA and developers.  They both need to utilize each other resources.

Developer can always make a case by saying the code running on production  db for 2 yrs and nothing has been changed since then, can you tell me what has changed in your Database System? Similarly, DBA can always make a strong case. This is not about pointing  fingers at each other, co-operating and providing all the changes made to the application helps DBA find the bottleneck. Similarly, DBA can also provide some recommendation along with the email helps the developer on where to focus on the queries.

I have compiled  the list of tuning tips for both Developer and DBA. This will help you find the bottleneck of your queries not the database. The following list is by no mean in any order so feel free to choose the one that best fits your scenario, however I personally like to follow the steps one-by-one and has always worked in my favor.

What? My LOAD/INSERT is Freaking SLOW: 

1. Data Size: check if data file has increased significantly?
2. Table Structure: Has table structure changed? 
3. Adding Indexes add load time as it has to load Indexes as well
4. Similarly, constraints does add load time like Index
5. Drop Index, load data and recreate Index - Loads faster.
6. Use Bulk Index  or use cursor to load data
7. Minimum Logging on Destination table. (nologging, tablock, etc..)
8. Research other loading utilities like export/import, bcp, SSIS, etc..)
9. Parallel Execution of Insert Statements.
10. Did that help? 

My SELECT Query is running SLOW:

1. Understand the Query
2. Run the execution Plan - Check of full table scan.
3.  Index use is less costly than full table scan (not always!!)
4. Re-write query to use Index or add index where needed.
5. Why table scan? not Index uses, or missing index?
6. Re-write query to use index or create index.
7. Table Stats & Index stats - are they current?
8. Table/Index fragmentation,  Rebuild Indexes.
9. Use materialized view or Temp Table where needed.
10.  Run Tuning Adviser  or Profiler for recommendations!
11.  Table Partitions
12.  Proper Indexing (Global vs local index) on Partitioned table
13.  Proper Indexing (clustered vs non-cluster index) - Sql Server
14.  Business Rule - Can you prune your table data?
15.  Research Table Dependencies and Impact on your query
16.  Use of Blind variable 
17.  Proper Join on tables
18.  Union vs Union ALL.
19.  Pull only the columns that you need, NOT ALL
20.  Parallel Query Execution
21.  Make it Simple.

Well, the list goes on and on, by now you should have a pretty good idea on what to check?
or your query is fully optimized!!  I strongly encourage developer to check with DBA before implementing any changes.You can always look up online for examples of any steps presented here or you can email me and I will do my best to replay.