Problem with SP, its taking high response time.....

Question

Post reply

Problem with SP, its taking high response time.....

jetking_lk

Newbie

Points: 7
More actions
July 10, 2009 at 1:07 pm

#206842

I have a problem with one of the stored procedure which contain Updates and alter statements.

Actually its accessing only table .. which has only one clustered indexe, 49 columns, 895475 rows.

I have ran the dbcc showcontig and found the below result:

DBCC SHOWCONTIG scanning 'Temp_table1' table...

Table: 'Temp_table1' (1573580644); index ID: 1, database ID: 51

TABLE level scan performed.

- Pages Scanned................................: 53444

- Extents Scanned..............................: 6694

- Extent Switches..............................: 9568

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 92.29%

- Logical Scan Fragmentation ..................: 6.15%

- Extent Scan Fragmentation ...................: 0.07%

- Avg. Bytes Free per Page.....................: 444.3

- Avg. Page Density (full).....................: 94.51%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

and also i have run the SP by enabling the execution plan..

it shows three queries, all query cost shows 33%.. in the execution plan i could see table scan cost is showing as 97% for all three queries.. remaining fields cost is showing as 2%, 0%....

I have already updated the statistics by rebuilding the indexes.

Can you suggest how to go about this... do i need to create more indexes on this table, if yes how can i decide on which columns i need to create indexes or etc.. i am currently in dilama.. waiting for an early response.....

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

July 10, 2009 at 1:30 pm

#1023298

Please read this article[/url] and follow the guidelines within to post the necessary info so we may better help you with your performance issue.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

Those stats aren't too bad. I would say that you should definitely have other indexes besides the clustered index, but no one can tell what you should have for indexes unless they know the queries being run against the table. You also have to consider the read/write ratio because indexes do affect write operations as well.

If you post the code or a reasonable example of it someone may be able to help you with tuning it. Here's a link[/url] to an article that tells how to post performance issues.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question