1

I have an test environment with SQL Server 2016 SP1 installed.

My machine has 32GB of RAM and I give 30GB to SQL Server. I have 2 databases in my server - BGD and, BGD_Mem. The second one contains memory optimized data about 19 GB. I have table in my BGD database which has non-clustered columnstore index.

CREATE TABLE [dbo].[household](
[householdid] [INT] IDENTITY(1,1) NOT NULL,
[householddate] [DATE] NULL,
[householdcode] [VARCHAR](100) NULL,
[povertyscore] [SMALLINT] NULL,
[location1id] [SMALLINT] NULL,
[location2id] [SMALLINT] NULL,
[location3id] [SMALLINT] NULL,
[location4id] [SMALLINT] NULL,
[location5id] [SMALLINT] NULL,
[location6id] [INT] NULL,
[membercount] [SMALLINT] NULL,
[roomnumber] [SMALLINT] NULL,
[wallmaterialid] [SMALLINT] NULL,
[roofmaterialid] [SMALLINT] NULL,
[latrinetypeid] [SMALLINT] NULL,
[watersourceid] [INT] NULL,
[owneruserid] [INT] NULL,
[updateduser_name_eng] [VARCHAR](100) NULL,
[updateduser_name_ben] [VARCHAR](200) NULL,
[dateupdated] [DATE] NULL,
[datecreated] [DATE] NULL,
[createduser_name_eng] [VARCHAR](100) NULL,
[createduser_name_ben] [VARCHAR](200) NULL,
[agriculturallandid] [SMALLINT] NULL,
[agriculturallaborid] [SMALLINT] NULL,
[povertyscorerangeid] [SMALLINT] NULL,
[yearid] [SMALLINT] NULL,
[hhheadgenderid] [SMALLINT] NULL,
[complainttypeid] [SMALLINT] NULL,
[complaintstateid] [SMALLINT] NULL,
[watersourceother_eng] [VARCHAR](100) NULL,
[watersourceother_ben] [VARCHAR](100) NULL,
[wallmaterialother_eng] [VARCHAR](100) NULL,
[wallmaterialother_ben] [VARCHAR](100) NULL,
[roofmaterialother_eng] [VARCHAR](100) NULL,
[roofmaterialother_ben] [VARCHAR](100) NULL,
[agriculturalland_eng] [VARCHAR](100) NULL,
[agriculturalland_ben] [VARCHAR](100) NULL,
[latrintype_eng] [VARCHAR](100) NULL,
[latrintype_ben] [VARCHAR](100) NULL,
[ownership_eng] [VARCHAR](100) NULL,
[ownership_ben] [VARCHAR](100) NULL,
[foreignremittance_eng] [VARCHAR](100) NULL,
[foreignremittance_ben] [VARCHAR](100) NULL,
[safetynets_eng] [VARCHAR](100) NULL,
[safetynets_ben] [VARCHAR](100) NULL,
[hhhead_name_eng] [VARCHAR](100) NULL,
[hhhead_name_ben] [VARCHAR](200) NULL,
[hhhead_nid] [VARCHAR](100) NULL,
[hhhead_birthdate] [DATE] NULL,
[hhhead_age] [SMALLINT] NULL,
[hhhead_occupation_eng] [VARCHAR](100) NULL,
[hhhead_occupation_ben] [VARCHAR](200) NULL,
[asset1] [BIT] NULL,
[asset2] [BIT] NULL,
[asset3] [BIT] NULL,
[asset4] [BIT] NULL,
[asset5] [BIT] NULL,
[asset6] [BIT] NULL,
[asset7] [BIT] NULL,
[asset8] [BIT] NULL,
[asset9] [BIT] NULL,
[asset10] [BIT] NULL,
[asset11] [BIT] NULL,
[ownershipyesnoid] [SMALLINT] NULL,
[householddate_yearid] [SMALLINT] NULL,
[dateupdated_yearid] [SMALLINT] NULL,
[datecreated_yearid] [SMALLINT] NULL,
[poverty_index] [SMALLINT] NULL,
[population_index] [SMALLINT] NULL,
[createduserid] [SMALLINT] NULL,
[updateduserid] [SMALLINT] NULL,
[haswatersource] [BIT] NULL,
[location7id] [INT] NULL,
[location8id] [INT] NULL,
[location9id] [INT] NULL
)

GO

And this is my index.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_Household] ON [dbo].[household]
(
    [location3id],
    [povertyscore],
    [householdid],
    [location1id],
    [location2id],
    [location4id],
    [location5id],
    [location6id],
    [location7id],
    [location8id],
    [location9id]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

If I write the following query

SELECT physical_memory_kb /1024, 
virtual_memory_kb /1024, 
committed_kb /1024, 
committed_target_kb /1024
FROM sys.dm_os_sys_info

I get following results

-------------------- -------------------- -------------------- --------------------
32732                134217727            19832                30000

So at this moment only the data for my memory optimized database is in the memory.

If I query to my table with columnstore index, it put the its pages into memory.

select count(*)
from household

Now my results in sys.dm_os_sys_info looks like this

-------------------- -------------------- -------------------- --------------------
32732                134217727            20708                30000

You can see that about 1.8 GB of data is added the the buffer pool.

There is no activity on any of this databases, this is just a local test environment.

I dont understand why, but after some time my columnstore index pages are removed from the buffer pool (After 10-20 seconds).

I understand this by querying sys.dm_os_sys_info.

SELECT physical_memory_kb /1024, 
virtual_memory_kb /1024, 
committed_kb /1024, 
committed_target_kb /1024
FROM sys.dm_os_sys_info


-------------------- -------------------- -------------------- --------------------
32732                134217727            19810                30000

It seems like there is background job that removes the columnstore index pages from the buffer pool. But I dont understand why. There is more 10GB free memory that SQL Server can use, why it removes that 1.8GB of columnstore index pages.

3
  • First question: is this server patched to CU5 for SP1? If not, please rerun your tests after patch/reboot.
    – NedOtter
    Oct 23, 2017 at 14:16
  • I'm not sure if it's related, but connect.microsoft.com/SQLServer/feedback/details/1163152 might be of interest if you haven't come across it in your research thus far. We were consistently seeing columnstore data get a lower cache priority than we'd hoped (without memory-optimized tables in play), with frequent cache evictions that we did not expect. The behavior is better (for our use cases) in SQL 2016 than SQL 2014, but still persists to a lesser degree. Oct 23, 2017 at 15:09
  • @NedOtter the server is patched to CU8 Oct 24, 2017 at 19:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.