| Sherlock, Kevin 2005-10-27, 8:21 am |
| Does the table have a clustered (placement) index? If not, create one and drop
it. If you don't want to do that, try updating the stats on the heap itself to
see if the negative number disappear:
update statistics message_notification
_history message_notification
_history
"Jesus M. Salvo Jr." <noone@noone.org> wrote in message
news:4355623d$2@foru
ms-2-dub...
> Sherlock, Kevin wrote:
>
>
> 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.... >
>
|