| Jesus M. Salvo Jr. 2006-01-24, 7:24 am |
|
1> select @@version
2> go
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
Adaptive Server Enterprise/12.5.3/EBF 12868 ESD#4/P/Sun_svr4/OS 5.8/ase1253/1923/32-bit/FBO/Thu Sep 8 14:14:28 2005
I have a DOL table that only has a single column and that is almost always empty.
The idea is that table is essentially a queue.
Some processes insert to it, other processes delete from it.
No updates.
The table has a primary key, non-clustered index.
What I am seeing is that the index size keeps on increasing.
Thus, after a long time, the index becomes larger and larger and
querying on the table using the primary key index takes longer and longer.
Doing a 'reorg rebuild' on the index does not help.
The only way I could reduce the size is to drop the primary key constraint,
thereby dropping the index, and recreating the primary key constraint / index,
as shown below:
1> sp_spaceused outgoing_notificatio
nhistory, 1
2> go
index_name size reserved unused
------------------------------ ---------- ---------- ----------
outgoing_notificatio
nhistory 0 KB 32 KB 28 KB
outgoing_notificatio
nhistorypk 34328 KB 35036 KB 708 KB
(1 row affected)
name rowtotal reserved data index_size unused
------------------------------ ----------- --------------- --------------- --------------- ---------------
outgoing_notificatio
nhistory 0 35068 KB 4 KB 34328 KB 736 KB
(return status = 0)
1> alter table outgoing_notificatio
nhistory drop constraint outgoing_notificatio
nhistorypk
2> go
1> 2> 3> 4> alter table outgoing_notificatio
nhistory add constraint outgoing_notificatio
nhistorypk
primary key nonclustered ( message_notification
_historyid )
on index_segment5> 6>
7> go
1> sp_spaceused outgoing_notificatio
nhistory, 1
2> go
index_name size reserved unused
------------------------------ ---------- ---------- ----------
outgoing_notificatio
nhistory 0 KB 32 KB 28 KB
outgoing_notificatio
nhistorypk 8 KB 64 KB 56 KB
(1 row affected)
name rowtotal reserved data index_size unused
------------------------------ ----------- --------------- --------------- --------------- ---------------
outgoing_notificatio
nhistory 1 96 KB 4 KB 8 KB 84 KB
(return status = 0)
Maybe related to housekeeper not garbage collecting the deleted pages ??
I have housekeeper GC set to 1. Setting it to 4 causes ASE to use a lot of I/O on a different table that is very huge,
so I cannot just change housekeeper GC to any other value other than 1.
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
enable housekeeper GC 1 0 1 1 switch dynamic
Here is the sp_help output for that table:
1> sp_help outgoing_notificatio
nhistory
2> go
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
outgoing_notificatio
nhistory dbo user table
(1 row affected)
Data_located_on_segm
ent When_created
------------------------------ --------------------------
default Oct 20 2005 2:14PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Access_Rule_name Identity
------------------------------ ------------------------------ ----------- ---- ----- ----- ------------------------------
------------------------------ ------------------------------ --------
message_notification
_historyid numeric 9 18 0 0 NULL
NULL NULL 0
index_name index_description index_keys
index_max_rows_per_p
age index_fillfactor index_reservepagegap
index_created
------------------------------ -------------------------------------------------------------------- ----------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
------------- ---------------- -------------------- -------------------
outgoing_notificatio
nhistorypk nonclustered, unique located on index_segment message_notification
_historyid
0 0 0 Jan 24 2006 8:25PM
(1 row affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Datarows
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
1 0 0 0 0
(1 row affected)
concurrency_opt_thre
shold optimistic_index_loc
k dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0
(return status = 0)
|