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

asked Mar 19 '10 at 03:44 PM in Default

venkatreddy
562 ● 26 ● 28 ● 32

(comments are locked)

4 answers:

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

Monitor your systems. Collect metrics specifically on query times and wait stats
Working from either the query times or the wait stats, identify the worst performing queries
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.
If necessary create/modify indexes (but usually, it's a code issue)
Post the changed code and start the process over again.

answered Mar 19 '10 at 04:19 PM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

+1 - love it :)

Mar 19 '10 at 04:24 PM Matt Whitfield ♦♦

up vote.This will be the top down approach i consider.is it?

Mar 19 '10 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 '10 at 04:30 PM Grant Fritchey ♦♦

wash, rinse, repeat :)

Mar 19 '10 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 '10 at 07:05 PM TimothyAWiseman

(comments are locked)

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.

answered Mar 19 '10 at 03:52 PM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

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 '10 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 '10 at 04:14 PM Grant Fritchey ♦♦

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

Mar 19 '10 at 04:18 PM venkatreddy

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

Mar 19 '10 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 '10 at 07:03 PM TimothyAWiseman

(comments are locked)

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?

answered Mar 19 '10 at 05:36 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

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

Mar 20 '10 at 01:22 AM venkatreddy

(comments are locked)

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

answered Mar 30 at 07:21 AM