| Jesus M. Salvo Jr. 2005-10-27, 8:21 am |
| Sherlock, Kevin wrote:
> What does optdiag output look like for that table and index? I would
> suspect
> that there are a lot of "deleted" rows that are still pointed to. You
> would need to "reorg rebuild" the DOL table itself if there is no
> clustered index which you could rebuild.
>
HMmm ... the HK GC is still running on that index even afer almost 12 hours that I noticed it.
1) Is it possible that the "negative" deleted rowcount ( See below )
in optdiag will cause this ?
2) Since the HK GC seems to be running on the index
( based on sp_sysmon 'cache wizard' showing high physical read/sec ),
how can a "reorg rebuild" on the DOL table itself fix it ? ...
What about a "reorg compact" or "reorg reclaim_space" on the table ?
Here's the optdiag output that was done yesterday,
after a reorg rebuild of the index and after an update index statistics:
OptDiag/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/OPT/Tue Jan 25 00:39:30 2005
Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08 2005
Server name: "syddbshared"
Specified database: "sgmaster"
Specified table owner: "dbo"
Specified table: " message_notification
_history"
Specified column: not specified
Table owner: "dbo"
Table name: " message_notification
_history"
Statistics for table: " message_notification
_history"
Data page count: 3421523
Empty data page count: 0
Data row count: 117040561.0000000000000000
Forwarded row count: -3134.0000000000000000
Deleted row count: -34.0000000000000000
Data page CR count: 429342.0000000000000000
OAM + allocation page count: 37531
First extent data pages: 91295
Data row size: 88.2233554570997001
Derived statistics:
Data page cluster ratio: 0.9995153044863230
Space utilization: 0.7451530431202396
Large I/O efficiency: 0.9966186040364353
< ... snip.... >
Statistics for index: " notification_send_id
x" (nonclustered)
Index column list: " notification_sent_to
_external", "notified_on"
Leaf count: 876547
Empty leaf page count: 114557
Data page CR count: 33375304.0000000000000000
Index page CR count: 96037.0000000000000000
Data row CR count: 70259980.0000000000000000
First extent leaf pages: 48
Leaf row size: 23.7317588064490650
Index height: 3
Derived statistics:
Data page cluster ratio: 0.5281909295309782
Index page cluster ratio: 0.9989914219670344
Data row cluster ratio: 0.4117376895238360
Space utilization: 0.7824130575163538
Large I/O efficiency: 0.9929894485863244
< ... snip.... >
|