|
|
Posted by Erland Sommarskog on 12/27/07 22:26
Neil (nospam@nospam.net) writes:
>> You could run DBCC SHOWCONTIG on some big tables to have a look at
>> fragmentation. But if you reindex every night, and don't shrink anything,
>> the situation should improve.
>
> I ran it on the five largest tables in the db (based on the bytes used,
> not the pages). The Extent Scan Fragmentation for those five tables was:
> 86%, 70%, 97%, 98%, 74%.
That's quite high. What values do you have for Scan Density and Avg. Page
Density (full)? Or post the full output for one these tables.
> Another thing that might be a factor here is that I noticed that disk
> space is very low on the drive that contains the database. I have been
> keeping 4 weeks worth of backups for this database. When the backup
> ballooned to 3 gb instead of 1 gb, it started eating up more drive
> space. Last night's backup (after, I think, 5 of these 3 gb backups were
> on the drive) wouldn't go through, as there was only 2 gb free on the
> drive. I deleted some backups, and changed the storage to 1 week,
> instead of 4, and the backup went through. Still, I wonder how much the
> limited disk space is affecting the current situation.
The limited disk space is not going to affect your database, but
obviously the expanded database will affect the available disk
space. Then again, you are not storing the backups on the same drive
are you? (Well, if you get them on tape or some other media as well,
I guess it's OK.)
>> Or put in another way: do you have tables without a clustred index?
>
> Yes, apparently so. The query returned 51 rows.
And that included your big tables?
I think we on to something here. Your "optimization job" is in vain;
you cannot defragment a heap (heap = table without a clustered index).
Adding clustered indexes on all tables takes a little thought to
make the best choice for the index. Then again, if you only have
one index on a table, that is probably the best choice.
But you could also just add a clustered index on any column and
then drop it. That will defragment the table.
While I know a few who disagrees, I think it's best practice to
have a clustered index on all tables.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|