Solved

Urgent performance issue

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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