We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Urgent performance issue

anwarmir
anwarmir asked
on
Medium Priority
319 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



Comment
Watch Question

Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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 ?

Commented:
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).

Author

Commented:
Excellent! Thanks!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.