Auto Sizing Columns

[Memo]

I was poking around in the DB earlier. Something I did triggered SambaPOS to start resizing columns - maintenance backup and everything… before the crash (which was definitely my doing).

What is the Auto Size maintenance task?

[markjw]

It is to reduce SQL Server memory usage. This will reduce the memory required to handle text records in the database and you should notice an improvement after doing this. I usually recommend using “Auto Size” on all systems as it can improve performance even significantly on some systems.

There is “Auto Size” and “Safely Auto Size” - the difference is “Auto Size” will use the smallest size needed to store text fields, but you may notice quite often if you store a larger amount of text in a field, you will get the “resizing columns” popup and have to wait until it has completed (if your DB is large, it could take a minute or longer). So, “Safely Auto Size” still reduces the text field sizes but not as much, so it is less likely to see “resizing columns”. AFAIK it should do an auto backup before resizing columns.

[Memo]

Thanks for the info.

Is this altering indices or the size of the datatype(s)?

At what point of usage do you recommend this be done?

[markjw]

@Memo

Is this altering indices or the size of the datatype(s)?

It alters the size of varchar & nvarchar datatypes - without using auto size, some are set to nvarchar(MAX) which will use a considerable amount of memory. Auto size will set them to minimum values of nvarchar(50) or to the current maximum length of data stored in the field, if greater than 50 characters. Safely Auto Size will set nvarchar to higher default value, there is a comparison I posted a while back here:

SambaPOS 5.2.12 Release

When was the new “Safely Auto Size Columns” added and what exactly does it do differently from “Auto Size Columns” ? [image] EDIT: Ok so I can partly answer this myself, comparing using Auto Size vs Safely Auto Size, for example on the ScreenMenuItems table, I can see some columns are set as nvarchar(MAX) as opposed to setting specific size on every column. I am guessing this is to reduce the amount of “auto resizing” that happens during use? @emre what is the difference regarding performan…

@Memo

At what point of usage do you recommend this be done?

I’d suggest it straight away, there is no disadvantage from turning it on. I’d recommend “Safely Auto Size”. Without using this, the SQL Server memory usage for the SambaPOS DB is very inefficient and will use far more memory than necessary. So even from the start, turning on this feature should show a performance improvement.

On all the setups we do for customers, we have it turned on from the start.

[Memo]

Gotcha. Plus, you can’t create an index on NVARCHAR(MAX).

Thanks again!