Re: DB size increase from SQL 2000 to 2005
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 25 Feb 2008 22:46:49 +0000 (UTC)
Artie (artie2269@xxxxxxxxx) writes:
The largest table I previously mentioned has increased in rows from
449,000 to 451,000. There are no text/ntext/image columns in the db.
Good, then we at least do not have to worry about blobs.
The script returned 9 tables - what does it mean? Orphaned indexes?
This db does have a weekly optimization/reindex job that runs.
Those nine tables are heaps, tables without a clustered index. Heaps are
prone to fragmentation, and what worse is: they are not handled
reindexing jobs. Run DBCC SHOWCONTIG on these tables.
Also run this query:
select TOP 20 o.name, i.name, i.index_id,
ps.reserved_page_count, ps.used_page_count, ps.row_count
from sys.objects o
join sys.indexes i ON o.object_id = i.object_id
join sys.dm_db_partition_stats ps on ps.object_id = o.object_id
and ps.index_id = i.index_id
order by ps.reserved_page_count desc
This lists the 20 biggest objects in your database. One page count
corresponds to 8192 bytes.
Index_id = 0 => Heap.
Index_id = 1 => The clustered idnex.
Index_id >= 2 => non-clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- References:
- DB size increase from SQL 2000 to 2005
- From: Artie
- Re: DB size increase from SQL 2000 to 2005
- From: Erland Sommarskog
- Re: DB size increase from SQL 2000 to 2005
- From: Artie
- DB size increase from SQL 2000 to 2005
- Prev by Date: Re: sql query which has got 3 dynamic parameters
- Next by Date: Re: sql query which has got 3 dynamic parameters
- Previous by thread: Re: DB size increase from SQL 2000 to 2005
- Next by thread: Query string encryption in T-SQL
- Index(es):
Relevant Pages
|
