Why is the size of clustered DOL index very big, when the table is almost always empty?
Jesus M. Salvo Jr.
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_notificationhistory, 1
2> go
index_name size reserved unused
------------------------------ ---------- ---------- ----------
outgoing_notificationhistory 0 KB 32 KB 28 KB
outgoing_notificationhistorypk 34328 KB 35036 KB 708 KB
(1 row affected)
name rowtotal reserved data index_size unused
------------------------------ ----------- --------------- --------------- --------------- ---------------
outgoing_notificationhistory 0 35068 KB 4 KB 34328 KB 736 KB
(return status = 0)
1> alter table outgoing_notificationhistory drop constraint outgoing_notificationhistorypk
2> go
1> 2> 3> 4> alter table outgoing_notificationhistory add constraint outgoing_notificationhistorypk
primary key nonclustered ( message_notification_historyid )
on index_segment5> 6>
7> go
1> sp_spaceused outgoing_notificationhistory, 1
2> go
index_name size reserved unused
------------------------------ ---------- ---------- ----------
outgoing_notificationhistory 0 KB 32 KB 28 KB
outgoing_notificationhistorypk 8 KB 64 KB 56 KB
(1 row affected)
name rowtotal reserved data index_size unused
------------------------------ ----------- --------------- --------------- --------------- ---------------
outgoing_notificationhistory 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_notificationhistory
2> go
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
outgoing_notificationhistory dbo user table
(1 row affected)
Data_located_on_segment 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_page index_fillfactor index_reservepagegap index_created
------------------------------ -------------------------------------------------------------------- ----------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ---------------- -------------------- -------------------
outgoing_notificationhistorypk 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_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0
(return status = 0)