Auto Sizing Columns

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?

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.

1 Like

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?

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:

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.

1 Like

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

Thanks again!