July 10, 2009 at 1:07 pm
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.....
July 10, 2009 at 1:30 pm
July 10, 2009 at 1:36 pm
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
July 10, 2009 at 1:37 pm
Darn it Lynn that's twice today!
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
July 13, 2009 at 1:47 am
Looks like jedi is draw faster then cowboy 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply