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.

  • First question: is this server patched to CU5 for SP1? If not, please rerun your tests after patch/reboot. – NedOtter Oct 23 '17 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. – Geoff Patterson Oct 23 '17 at 15:09
  • @NedOtter the server is patched to CU8 – Artashes Khachatryan Oct 24 '17 at 19:48

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.