x

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

venkatreddy gravatar image

venkatreddy
562 28 29 32

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

4 answers: sort oldest

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

+1 - love it :)
Mar 19, 2010 at 04:24 PM Matt Whitfield ♦♦
up vote.This will be the top down approach i consider.is it?
Mar 19, 2010 at 04:26 PM venkatreddy
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 ♦♦
wash, rinse, repeat :)
Mar 19, 2010 at 05:43 PM Blackhawk-17
And don't hesitate to experiment and see which of the two+ options works better in your environment.
Mar 19, 2010 at 07:05 PM TimothyAWiseman
(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

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

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

+1 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

rasadulalam gravatar image

rasadulalam
0

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x51
x21

asked: Mar 19, 2010 at 03:44 PM

Seen: 3248 times

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