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.
Upon further analysis I discovered that 22GB of this free space is coming from a single table:
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.
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
