?
Solved

Urgent performance issue

Posted on 2006-10-31
4
Medium Priority
?
276 Views
Last Modified: 2006-11-18
We have a db with about 30 tables. Uploads to the DB via our external web application has seriously slowed down to the point where the page just crashes when doing an upload.
I have checked the fragmentation levels on the DB using Showcontig and the levels are quite high saying that the db is quite small. here is the logical fragmenation list for all tables:

Executing DBCC INDEXDEFRAG (0, tblCampaign,
      6) - fragmentation currently 9%
Executing DBCC INDEXDEFRAG (0, tblCampaign,
      7) - fragmentation currently 5%
Executing DBCC INDEXDEFRAG (0, tblCampaignGroup,
      16) - fragmentation currently 21%
Executing DBCC INDEXDEFRAG (0, tblCampaignGroup,
      17) - fragmentation currently 8%
Executing DBCC INDEXDEFRAG (0, tblCampaignGroup,
      18) - fragmentation currently 16%
Executing DBCC INDEXDEFRAG (0, tblCampaignLandingPage,
      1) - fragmentation currently 17%
Executing DBCC INDEXDEFRAG (0, tblCampaignLandingPage,
      11) - fragmentation currently 67%
Executing DBCC INDEXDEFRAG (0, tblCampaignList,
      7) - fragmentation currently 42%
Executing DBCC INDEXDEFRAG (0, tblCampaignMarketingPiece,
      8) - fragmentation currently 50%
Executing DBCC INDEXDEFRAG (0, tblCampaignMediaType,
      10) - fragmentation currently 67%
Executing DBCC INDEXDEFRAG (0, tblCampaignUnmatchedCodes,
      1) - fragmentation currently 46%
Executing DBCC INDEXDEFRAG (0, tblCampaignUnmatchedCodes,
      2) - fragmentation currently 38%

Now I have run DBCC INDEX DEFRAG ON ALL INDEXES and the fragmentation levels  do not change. For example here is the dbcc showcontig report for one of the tables:

DBCC SHOWCONTIG scanning 'tblCampaignList' table...
Table: 'tblCampaignList' (114099447); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 20
- Extent Switches..............................: 8
- Scan Density [Best Count:Actual Count].......: 33.33% [3:9]
- Logical Scan Fragmentation ..................: 0.00%
DBCC SHOWCONTIG scanning 'tblCampaignList' table...
Table: 'tblCampaignList' (114099447); index ID: 7, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 12
- Extent Switches..............................: 9
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Logical Scan Fragmentation ..................: 41.67%


When I ran DBCC INDEX DEFRAG on Index ID 7 the logical fragmentation statys at 47%. Should I use REINDEX instead
Can anybody help me in Improving  the performance

Thanks



0
Comment
Question by:anwarmir
  • 2
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 17843181
I would recommend REINDEX, but be aware that this does NOT mean that your problems will be resolved. If this does not help, you also need to look at locking/blocking transactions and long running transactions (use sp_who2 and SQL Profiler), as well as looking at your other performance counters on the server (disk, memory and CPU).

Also, look at query plans and consider that you may need optimisation there as well.
0
 

Author Comment

by:anwarmir
ID: 17843315
NigtMan Thanks..I used DBCC REINDEX and all fragmentation levels went to 0%....Do u know why the defrag did not work and the reindex did ?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17843842
Defrag will re-organise the data in the page as best it can, but will not reorganise the extents and pages. Put simply, it will 'reorder' without rearranging. Reindex rebuilds from scratch, so will remove excess pages by moving the data from page a to page b, restructuring the extents and essentially rebuilding the index completely.

In SQL 2000, this will lock the data pages while the rebuild is in process (although defrag is online). In SQL 2005, non-text indexes can be completely rebuild while online (I think this might be Enterprise only - must check).
0
 

Author Comment

by:anwarmir
ID: 17844124
Excellent! Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question