question

venkatreddy avatar image
venkatreddy asked

Need guidelines for Query optimization techniques in SQL server

Can anybody give the guidelines for query optimization techniques as part of performence tuning in SQL server.Does it involves specific tasks to follow?.Thanks in advance..

performance-tuningsubjective
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I'm feeling bad about my last answer. In a nutshell:

  1. Monitor your systems. Collect metrics specifically on query times and wait stats
  2. Working from either the query times or the wait stats, identify the worst performing queries
  3. Look at the execution plan and code for these queries. Identify bad coding practices or performance bottlenecks. There are WAY too many to list here, but you can look for scans instead of seeks (although scans aren't always bad), fat pipes (the arrows connecting operators in the execution plan), key or rowid lookups (formerly called bookmark lookups, but these aren't always bad), any type of work tables or spools (and again, these aren't always bad). Fix the code causing the problematic behavior.
  4. If necessary create/modify indexes (but usually, it's a code issue)
  5. Post the changed code and start the process over again.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's the simplified approach. In addition to query time, you also need to worry about the number of calls, cpu, memory, disk i/o, and I'm barely scratching the surface on things to watch for within queries that will cause problems. But basically, this is the approach. Identify the pain point, fix it, identify the next pain point, fix that, repeat, repeatedly.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered

This might not be the best way to answer this question, and if it's not, please just vote it down or delete it, but I can suggest a pretty good book on the topic: SQL Server 2008 Query Performance Tuning Distilled.

5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Over at Simple-Talk.com Gail Shaw has some really good articles on performance tuning.
1 Like 1 ·
Definite up vote. There seems to be a certain air of homework to a lot of questions recently, and I think reading your book would be a very apt next step...
0 Likes 0 ·
Thanks, I was nervous writing that, but since I've already answered the question, with 600 pages....
0 Likes 0 ·
I have it already,can i expect any simplified articles(only the best one)..
0 Likes 0 ·
I do not get the feeling this was a homework question, but yes this is more of a question for entire books, even articles about it normally focus on one small subsection of a very deep topic, much less a short answer here.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

Originally posted a comment... but rather than delete it, and since I'm in the answer editor:

I defer to Grant for the SQL side to be sure.

Also make sure you look at things from a more physical aspect. Are your indexes, tables and temp dB competing for the same disk resources? Use PerfMon and check bytes/transfer, sec/transfer and queue lengths. Moving some files may give you a boost.

Check your network usage. Does the code require unnecessary trips from Client to Server and back?

Are there other applications or SQL instances competeing for RAM? Did you allow the O/S enough to get by without introducing paging?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Black.almost im new to the above concepts.i will plan to workout one by one..
0 Likes 0 ·
rasadulalam avatar image
rasadulalam answered
All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are. Use views and stored procedures instead of heavy-duty queries. Try to use constraints instead of triggers, whenever possible. Use table variables instead of temporary tables. Try to use UNION ALL statement instead of UNION Try to avoid using the DISTINCT clause Try to avoid using SQL Server cursors, whenever possible. For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html Hope it will help you.... Thanks n regard
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.