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..

more ▼

asked Mar 19, 2010 at 03:44 PM in Default

avatar image

562 29 31 36

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Mar 19, 2010 at 04:19 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

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.

Mar 19, 2010 at 04:30 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 19, 2010 at 03:52 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

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...

Mar 19, 2010 at 04:09 PM Matt Whitfield ♦♦

Thanks, I was nervous writing that, but since I've already answered the question, with 600 pages....

Mar 19, 2010 at 04:14 PM Grant Fritchey ♦♦

I have it already,can i expect any simplified articles(only the best one)..

Mar 19, 2010 at 04:18 PM venkatreddy

Over at Simple-Talk.com Gail Shaw has some really good articles on performance tuning.

Mar 19, 2010 at 04:28 PM Grant Fritchey ♦♦

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.

Mar 19, 2010 at 07:03 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Mar 19, 2010 at 05:36 PM

avatar image

12.1k 30 36 42

  • Black.almost im new to the above concepts.i will plan to workout one by one..

Mar 20, 2010 at 01:22 AM venkatreddy
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 30, 2013 at 07:21 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 19, 2010 at 03:44 PM

Seen: 3672 times

Last Updated: Mar 30, 2013 at 07:21 AM

Copyright 2018 Redgate Software. Privacy Policy