?
Solved

Urgent performance issue

Posted on 2006-10-31
4
Medium Priority
?
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 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