3

I was doing some reading and came across this article from Paul Randal: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

Using the queries from that article, I decided to investigate on one of our databases, and found that about 40% of our buffer pool was empty space.

MBEmpty

Upon further analysis I discovered that 22GB of this free space is coming from a single table: QueueItem

Here is the QueueItem table definition and current sp_spaceused info. There is an image column and fill factor is at 80%. This is a third party database and we cannot change the structure of the table.

enter image description here

    CREATE TABLE [dbo].[QueueItem](
    [object_id] [uniqueidentifier] NOT NULL,
    [object_class_id] [uniqueidentifier] NOT NULL,
    [security_id] [uniqueidentifier] NULL,
    [epoch_id] [int] NOT NULL,
    [home_id] [uniqueidentifier] NULL,
    [creator] [nvarchar](80) NULL,
    [create_date] [datetime] NULL,
    [modify_user] [nvarchar](80) NULL,
    [modify_date] [datetime] NULL,
    [dequeue_host] [nvarchar](15) NULL,
    [retry_count] [int] NOT NULL,
    [queued_object_id] [uniqueidentifier] NULL,
    [queued_object_id_class] [uniqueidentifier] NULL,
    [serialized_data] [image] NULL,
    [event_operation] [int] NULL,
    [next_retry_date] [datetime] NULL,
    [lease_expiry_date] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [object_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My question is this: If the table is only ~2.5GB in size, why are we seeing 24GB of the table in the buffer pool? Also, why is 93% of the table space in the buffer pool empty space?

I also included the detailed output from sys.dm_db_index_physical_stats. (sorry if format is hard to read)

    database_id object_id   index_id    partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count  avg_page_space_used_in_percent  record_count    ghost_record_count  version_ghost_record_count  min_record_size_in_bytes    max_record_size_in_bytes    avg_record_size_in_bytes    forwarded_record_count  compressed_page_count
        15          565577053       1           1               CLUSTERED INDEX     IN_ROW_DATA         3               0           0.176289114147201           561                 8.0891265597148         4538            81.8488510007413            131480              8                   4                       149                         231                         226.701                     NULL                        0
        15          565577053       1           1               CLUSTERED INDEX     IN_ROW_DATA         3               1           50                          8                   2                       16              87.57876204596              4538                0                   0                       23                          23                          23                          NULL                        0
        15          565577053       1           1               CLUSTERED INDEX     IN_ROW_DATA         3               2           0                           1                   1                       1               4.91722263404991            16                  0                   0                       23                          23                          23                          NULL                        0
        15          565577053       1           1               CLUSTERED INDEX     LOB_DATA            1               0           0                           NULL                NULL                    246634          79.3822955275513            384511              22                  10                      29                          8068                        4117.481                    NULL                        NULL
        15          565577053       3           1               NONCLUSTERED INDEX  IN_ROW_DATA         4               0           0.124688279301746           78                  10.2820512820513        802             91.1373115888312            131480              16                  8                       43                          43                          43                          NULL                        0
        15          565577053       3           1               NONCLUSTERED INDEX  IN_ROW_DATA         4               1           44.4444444444444            9                   1                       9               39.6054731900173            802                 0                   0                       31                          34                          33.996                      NULL                        0
        15          565577053       3           1               NONCLUSTERED INDEX  IN_ROW_DATA         4               2           75                          4                   1                       4               0.966765505312577           9                   0                   0                       31                          34                          33.666                      NULL                        0
        15          565577053       3           1               NONCLUSTERED INDEX  IN_ROW_DATA         4               3           0                           1                   1                       1               1.71732147269582            4                   0                   0                       31                          34                          33.25                       NULL                        0
        15          565577053       14          1               NONCLUSTERED INDEX  IN_ROW_DATA         4               0           0                           289                 7.98615916955017        2308            80.2252532740301            131480              16                  8                       112                         112                         112                         NULL                        0
        15          565577053       14          1               NONCLUSTERED INDEX  IN_ROW_DATA         4               1           11.7647058823529            6                   2.83333333333333        17              80.4859154929577            2308                0                   0                       43                          46                          45.998                      NULL                        0
        15          565577053       14          1               NONCLUSTERED INDEX  IN_ROW_DATA         4               2           100                         2                   1                       2               4.99752903385224            17                  0                   0                       43                          46                          45.823                      NULL                        0
        15          565577053       14          1               NONCLUSTERED INDEX  IN_ROW_DATA         4               3           0                           1                   1                       1               1.12428959723252            2                   0                   0                       43                          46                          44.5                        NULL

0
  • 1
    Seems to me like your avg space used for 384K rows over 246K pages is weighted toward a lot of full pages and a healthy number of empty pages. Can you really only get ~1.5 rows per page? Have you done any rebuilds? It'd be the first thing I'd try to reclaim space. – Aaron Bertrand Mar 6 '14 at 17:15
  • As Aaron hinted at, empty pages are empty pages, whether on disk or in-memory. Solve the empty pages on disk, and the problem will be resolved in memory. – Max Vernon Mar 6 '14 at 18:06
  • Rebuilding the table was the first thing we tried, but we are still seeing the empty space in the buffer pool. Will we need to manually empty the buffer pool in order to see if the table rebuild helped any? It just doesn't seem to make sense that this table would have 22GB of empty space out there. – GoodwinSQL Mar 6 '14 at 18:06
  • What causes a table with only 2.5GB of data to take up 24GB in the buffer pool? – GoodwinSQL Mar 6 '14 at 19:06
  • 1
    @SQL_JGood, you wrote -- "Upon further analysis I discovered that 22GB of this free space is coming from a single table", could you please post the query that you used to get 24 GB number. Perhaps your math is a bit of? – DenisT Mar 7 '14 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.