can we tune this long ruuning query

Question

can we tune this long ruuning query

madhu-686862

SSChampion

Points: 10707
More actions
March 24, 2008 at 7:41 pm

#154224

hi could you please tell me how can we tune this long runnin query?
select qRoot.ID col0,qRoot.clainID col1,qRoot.MessageSink col2,qRoot.Status col3,qRoot.SendOrder col4 FROM cc_message qroot WHERE qRoot.sendOrder>@p0
AND qRoot.SendOrder <=@p1 ORDER BY col4 ASC, col0 ASC.
Madhu

Viewing 7 posts - 1 through 6 (of 6 total)

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

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 1

you can start by removing the order by clause. You can use the NOLOCK hit after the table [WITH (NOLOCK)].

But My guess since this is not a complicated query that the issue is with the table indexing. Run an execution plan for the query and see what it says about the indexes. Get rid of index scans and see if there are any large bookmark lookups.

Also make sure auto update statistics is turned "ON" for the database, or that statistics are regularly being updated.

Run perfmon while the script is running and see if it spikes the cpu and see what the I/O is, I would also check disk reads and writes.

run dbcc showcontig and check for fragmentation in the tables and indexes.

With out getting more information this is about all I can say.

Hope this helps point you in the right direction.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Two things that are likely to work:

1) put a clustered index on SendOrder

2) Put a covering index on the table to cover (col0, col1, col2, col3, col4, SendOrder, col10)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Something else to be keenly aware of... many databases have views that have no indication in the name that they are, in fact, views... see if the "cc_message" table is actually a view with aggregates and the like... the real problem could be in a view.

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

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

Could you post the definition of cc_message and its indexes (assuming it is a table). Could you also give us an indication of how many rows are in the table and how many are returned by the query.

If it's possible, the execution plan will help a lot. Save it as a .sqlplan file, zip it and attach to a post.

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

Frank Brennan SSC Veteran Points: 212 More actions · Answer 5

You could get answers by simply submitting the statement as a workload to the SQL Server Engine Tuning Advisor. The advisor isn't all that good at advising on more complex workloads but it would nail this statement easily i'm sure (cluster index, covering index, multi-column stats, etc.).

Also,

WITH (NOLOCK) will only allow the engine to check for uncommitted log data if there is a potential share lock conflict. If you use WITH (READCOMMITTED) which is the default while READ_COMMITTED_SNAPSHOT is enabled on the database you can avoid share locks and potential conflicts with open transactions.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Madhu... a little feedback on what you end up doing would be much appreciated.

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