Rebuild indexes -> 8GB to 2.4GB than back to 8GB

Last Post 13 May 2008 07:43 AM by SQLUSA. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
wakko303
New Member
New Member

--
13 May 2008 06:58 AM
Hi All,

I customer told me the following about his database:

After rebuilding the indexes, the db size shrank from 8GB to 2.4GB. Within 3 days use, the DB was back up to 8GB... Is this normal??

SQLUSA
New Member
New Member

--
13 May 2008 07:43 AM
Probably it was always 8GB.

Indexing not changing size.

How did you check for size?

You have to use

DBCC SHRINKDATABASE to shrink it.

Anyhow if you have space, don't shrink it.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
14 May 2008 08:26 AM
quote:

Originally posted by: Pro Pete
2. Database activity analysis followed by review of the Fill factor being used for indexes - .


FILLFACTOR is a tricky one. For highly dynamic tables I use 70.

The problem is the uneven distribution of data in the keys. So even though 90 would be good at most part of the key sprectum, at dense places (like Smith for LastName), it would cause slow downs.

Dynamic table FILLFACTOR 80.

Average table FILLFACTOR 90.

You should check for index fragmentation to set your index REBUILD schedule: nightly, every other night, weekly, etc.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

SQLUSA
New Member
New Member

--
14 May 2008 11:18 PM
quote:

Originally posted by: russellb

A better strategy is to actually perform analysis as Pete recommends rather than make blanket statements.

One must understand how their databases are used before they can properly tune it.

------------------------------------------
SQL Server Magazine Forum Pro


Of course analysis is the best course of action. Even better: good table design!

However, what percent of the DBA-s have Andy's skills for DB performance analysis? Even time for it?

What makes FILLFACTOR (originated with Sybase circa 1990, did you know?) analysis so difficult is the uneven distribution of mos indexes.

My PRACTICAL recommendation is on the MORE side: leave enough empty space in the index tree for INSERTs to fly. It may slow down reads somewhat, but that is hardly noticeable. Blocking on the other hand is a real perfomance degrading event.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/







SwePeso
New Member
New Member

--
15 May 2008 04:59 AM
Setting FILLFACTOR too low (~ 70%) makes PAGE SPLITS more prone to happen. Which is not a good thing in SQL Server.

Just something to think about.
You are not authorized to post a reply.

Acceptable Use Policy