Query tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query tuning

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-07-06 : 20:25:06

What are the steps need to take for query tuning

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-07 : 03:47:00

Hi

Avoid Indexing Small Tables
Improve Indexes
Create Highly-Selective Indexes
Create Multiple-Column Indexes
Use Indexes with Filter Clauses
Use the Query Optimizer
Understand Response Time vs. Total Time
Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time
Rewrite Subqueries to Use JOIN
Limit Using Outer JOINs
Use Parameterized Queries

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-07 : 04:10:03

I's say start by finding the slow queries. Once you've found them, analyse why they are slow and rectify that. It may be that the query needs rewriting, it may be that the indexes need tuning. It'll probably be both.

These articles should get you started
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-07 : 04:12:43

quote:
Originally posted by rajdaksha

Avoid Indexing Small Tables

Not sure I agree there. Sometimes it's beneficial, sometimes not.

quote:
Use Indexes with Filter Clauses

Filtered indexes are only available on SQL 2008

quote:
Use the Query Optimizer

All queries go through the query optimiser.

quote:
Rewrite Subqueries to Use JOIN

Majority of the time, query with subquery performs exactly the same as query with joins. Optimiser's smart these days

quote:
Limit Using Outer JOINs

Why?

--
Gail Shaw
SQL Server MVP

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-07 : 04:39:29

quote:
Limit Using Outer JOINs
Why?

--
Gail Shaw
SQL Server MVP

OUTER JOINs are treated differently from INNER JOINs in that the optimizer does not try to rearrange the join order of OUTER JOIN tables as it does to INNER JOIN tables. The outer table (the left table in LEFT OUTER JOIN and the right table in RIGHT OUTER JOIN) is accessed first, followed by the inner table. This fixed join order could lead to execution plans that are less than optimal.

-------------------------
Your time is a valuable resource.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-07 : 04:45:12

quote:

Majority of the time, query with subquery performs exactly the same as query with joins. Optimiser's smart these days
--
Gail Shaw
SQL Server MVP

The Query Processor always rewrites the IN subquery to use JOIN...

-------------------------
Your time is a valuable resource.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-07 : 04:49:31

Hi

SELECT QS.SQL_HANDLE, QS.EXECUTION_COUNT
, QS.TOTAL_ELAPSED_TIME, QS.LAST_ELAPSED_TIME
, QS.MIN_ELAPSED_TIME, QS.MAX_ELAPSED_TIME
, QS.TOTAL_CLR_TIME, QS.LAST_CLR_TIME
, QS.MIN_CLR_TIME, QS.MAX_CLR_TIME
FROM SYS.DM_EXEC_QUERY_STATS AS QS

Returns aggregate performance statistics for cached query plans..

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-07 : 09:05:07

quote:
Originally posted by rajdaksha

quote:

Majority of the time, query with subquery performs exactly the same as query with joins. Optimiser's smart these days
--
Gail Shaw
SQL Server MVP

The Query Processor always rewrites the IN subquery to use JOIN...

And some other subqueries. Most subqueries in the from and most correlated subqueries in the select can (and will) also be treated equivalently to joins.

Subqueries used within IN are not equivalent to joins. One uses a full join, one uses a semi-join. Other than that, optimiser usually finds very similar plan.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources