question

vsotirov avatar image
vsotirov asked

column store object pool size limit

I have a problem with one of the SQL servers I work on. It is a data warehouse server with 1024GB or memory. The server max server memory is set at 512GB. At times the server use memory by sql server goes up to 990GB used memory. At those times it becomes very slow and we get timeouts on connections and running stored procedures. We use a lot of clustered and nonclustered columnstore indexes on the server. I checked on the server and at those times the buffer pool is at 400GB, but the column store object pool is at 500GB. My question is how do we control the size of the column store object pool.

sql memory consumption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

What version of SQL are you on 12/14/16/17/19

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vsotirov avatar image
vsotirov answered

Sql server enterprise 2017

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

If in doubt consult the column store bible https://www.nikoport.com/columnstore/

There where a lot of memory improvements for columnstore in 16 onwards so you should be good with that although more was done in 17&19.

You got memory optimised tabled at all with column store on them?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vsotirov avatar image
vsotirov answered

I read it and I did not find anything that says how to limit the memory used

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.