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
Solved

Urgent performance issue

Posted on 2006-10-31
4
241 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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

840 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