Defrag using DBCC REINDEX

[johnny76]

Hi,

I am trying to defrag a table that is 50% fragmented and it doesn't seem to be working. I found out it was fragmenetd using DBCC SHOWCONTIG and after I run DBCC REINDEX I get the same results. Any suggestions why I might be seeing no improvements? On one of the tables I am trying to defrag it has 1 clustered index...is that a problem? Any idea why this is not working? Also, should you be able to get any table back to 100% clean?

CES

 

[mrdenny]

Yes you can get it back to 100%. Can you post the DBCC SHOWCONTIG?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johnny76]

Here is one table:

DBCC SHOWCONTIG scanning 'FSBO_Notify' table...
Table: 'FSBO_Notify' (585769144); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 4
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 1398.5
- Avg. Page Density (full).....................: 82.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When I run the command DBCC REINDEX I get no change to the results...There is one clustered index that is on the primary key. The primary key consists of 3 fields. Any suggestions?

CES

 

[SQLDenis]

What is the fillfactor?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[johnny76]

The fill factor is currently 100 but I am looking to optimize the SQL Server performance. This is an old poorly written DB and I have been asked to look at it and attempt to optimize it.

I first found that it was badly fragmented and figured it was a good place to start. If you have other suggestions for optimization on top of the question I posted, I am all ears. I am really a programming and am attempting to broaded my scope in IT.

CES

 

[mrdenny]

If you are doing any inserting into the table, or updating of the table, you'll want to lower the fill factor to a better number.

How many records are in this table? The table is only 4 pages.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johnny76]

There are only 893 rows...this was just one of the samples. I was also doing some reading on the fill factor and plan to adjust the fillfactor on the tables as per the rule of thumb that I found...100% for Low Update tables, 50%-70% for High Update tables and 80%-90% for everything in between. And then play around with it a little until I am satisfied. Does this sound like a good plan?

Is it possible that the table will not defrag because it is so small?

I am not the creator of the DB/Tables...I'm just a developer learning SQL Server admin stuff to improve performance.

 

[mrdenny]

It is posible that the table won't defrag because it's that small, yes.

Your plan sounds good.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johnny76]

Thanks!

Do you have any suggestions of where to look for performance issues?

I am currently looking into Deadlocks and I will add the parameter -T1204 to output deadlock info to the log file. I see that it outputs the DB and Table involved but I need a way to find the SQL involved by either this or the preferrably the Profiler. I seem to be able to set up traces without a problem but reading the data is a little tricky...any suggestions on this or other areas to look into SQL Server performance issues?

CES

 

[icemel]

There are whole books written on this subject... if you are just starting out, you really need a book to guide you - it's a complex topic to say the least

I highly recommend:

Sajal Dam's "SQL Server Query Performance Tuning Distilled"

 

[johnny76]

Thanks for the suggestion...I just went looking for a book at lunch and couldn't find an appropriate one. I'll check this one out.

CES