Please educate me: leaf_ghost_count

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  October 15, 2013 at 7:35 am

  #299981

  During the study of a production database I run in a table which is used to cache some data.

  My questions:

  1. where is the space gone ? (43 Mb used for only .7 Mb of information).

  2. What is leaf_ghost count ?

  3. Can anybody explain the mechanism to me ?

  4. Advises/remarks/questions ?

  The table (Disk usage by table)

  #records 31600 reserved(KB) 5456= 43 MB Data (KB) 2392= 20 MB Indexes (KB) 600= 4 MB Unused (KB) 2464 = 19 MB

  Sum of all datalength's is : 717467 = 0.7 MB (script see below)

  So .7 Mb of information sits in 2.3 Mb of data (OK), which is present in 5456 pages 43 mb. For me this is out of proportion.

  dbcc showcontig

  - Pages Scanned................................: 299 -- = 2.3 MB

  In sys.dm_db_index_operational_stats, I see

  241610 leaf_ghost_count

  Thanks for educating me,

  Ben

  (PLE is over 10 hours, memory is over Gigabytes, so there are no resource or performance problems).

  Table usage during office hours. (Rough estimates).

  Inserts: 1 each second.

  Search (clustered key), 2 times each second.

  Deletes (in batches) all records which are older than 32 hours.

  -- Table definitions

  CREATE TABLE [dbo].[The_Table](

  [An_id] [dbo].[id_type] NOT NULL, -- varchar(20)

  [account_nm] [dbo].[txt_type] NOT NULL, -- varchar(200) On average 12.03277 long.

  [settime] [smalldatetime] NOT NULL CONSTRAINT [DF_The_Table_settime] DEFAULT (getdate())

  ) ON [PRIMARY]

  CREATE CLUSTERED INDEX [IX_The_Table] ON [dbo].[The_Table]

  (

  [An_id] ASC,

  [account_nm] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

  -- sys.dm_db_index_operational_stats

  SELECT object_name( object_id),* FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) order by object_name( object_id)

  -- RESULT:

  name database_id object_id index_id partition_number leaf_insert_count leaf_delete_count leaf_update_count leaf_ghost_count nonleaf_insert_count nonleaf_delete_count nonleaf_update_count leaf_allocation_count nonleaf_allocation_count leaf_page_merge_count nonleaf_page_merge_count range_scan_count singleton_lookup_count forwarded_fetch_count lob_fetch_in_pages lob_fetch_in_bytes lob_orphan_create_count lob_orphan_insert_count row_overflow_fetch_in_pages row_overflow_fetch_in_bytes column_value_push_off_row_count column_value_pull_in_row_count row_lock_count row_lock_wait_count row_lock_wait_in_ms page_lock_count page_lock_wait_count page_lock_wait_in_ms index_lock_promotion_attempt_count index_lock_promotion_count page_latch_wait_count page_latch_wait_in_ms page_io_latch_wait_count page_io_latch_wait_in_ms tree_page_latch_wait_count tree_page_latch_wait_in_ms tree_page_io_latch_wait_count tree_page_io_latch_wait_in_ms page_compression_attempt_count page_compression_success_count database_id object_id index_id partition_number leaf_insert_count leaf_delete_count leaf_update_count leaf_ghost_count nonleaf_insert_count nonleaf_delete_count nonleaf_update_count leaf_allocation_count nonleaf_allocation_count leaf_page_merge_count nonleaf_page_merge_count range_scan_count singleton_lookup_count forwarded_fetch_count lob_fetch_in_pages lob_fetch_in_bytes lob_orphan_create_count lob_orphan_insert_count row_overflow_fetch_in_pages row_overflow_fetch_in_bytes column_value_push_off_row_count column_value_pull_in_row_count row_lock_count row_lock_wait_count row_lock_wait_in_ms page_lock_count page_lock_wait_count page_lock_wait_in_ms index_lock_promotion_attempt_count index_lock_promotion_count page_latch_wait_count page_latch_wait_in_ms page_io_latch_wait_count page_io_latch_wait_in_ms tree_page_latch_wait_count tree_page_latch_wait_in_ms tree_page_io_latch_wait_count tree_page_io_latch_wait_in_ms page_compression_attempt_count page_compression_success_count

  --------------------

  The_Table720570583641156899632685302416101881670188068024152900000000000108060672231748166702430024933997715840740073101400

  The most important (?) numbers of the above query:

  [font="Courier New"]

  568490 leaf_insert_count

  326853 leaf_delete_count

  241610 leaf_ghost_count

  000068 leaf_page_merge_count

  2412984 range_scan_count

  000000 singleton_lookup_count

  1 078 881 537 row_lock_count

  16 650 173 page_lock_count[/font]

  The table

  29731 records

  5456 reserved

  2384 data

  600 index

  2472 Unused

  [font="Courier New"]

  -- dbcc show contig

  TABLE level scan performed.

  - Pages Scanned................................: 299

  - Extents Scanned..............................: 77

  - Extent Switches..............................: 293

  - Avg. Pages per Extent........................: 3.9

  - Scan Density [Best Count:Actual Count].......: 12.93% [38:294]

  - Logical Scan Fragmentation ..................: 99.00%

  - Extent Scan Fragmentation ...................: 97.40%

  - Avg. Bytes Free per Page.....................: 3865.2

  - Avg. Page Density (full).....................: 52.25%

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  [/font]

  -- Datalength script calculate total amount of information the table.

  -- Calculate total number of bytes and average number of bytes for each column.

  select count(*) from The_Table

  -- 32074

  select

  1.0*datalength(An_id),

  1.0*datalength(account_nm),

  1.0*datalength(settime),

  1.0*datalength(An_id)+

  1.0*datalength(account_nm)+

  1.0*datalength(settime)

  from The_Table

  compute sum(

  1.0*datalength(An_id)+

  1.0*datalength(account_nm)+

  1.0*datalength(settime)

  )

  , avg(1.0*datalength(An_id))

  , avg(1.0*datalength(account_nm))

  , avg(1.0*datalength(settime))

  -- the computed result:

  -- 738432.07.00000012.0227594.000000