long running quries

Question

long running quries

ch.mohini

Mr or Mrs. 500

Points: 557
More actions
June 26, 2008 at 1:23 am

#193250

how to tune the long running quries

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Find what is making them run long and fix it.

Seriously now. First you need to identify why the query is running slow. Is there blocking? Are there excessively long waits? Are you using an inefficient query structure (row-by-row processing)? Does the query allow index usage? Are there appropriate indexes?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Along with what Gail said...

... find and rewrite all RBAR to be good set based code. Learn how to use tricks of the trade like a Tally table. Learn how to correctly write code so it's actually capable of using and index and then correctly index the tables. Check for RBAR triggers, views of views, aggragate views, functions of functions, functions that refer to tables, functions that have embedded RBAR. Check the design of the database. Check the design of the tables. Check the design of the columns in the tables. Check the triggers and indexes that live on the tables...

... and determine how long that piece of string is... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)