Sql server maximum perfomance

Hello. My network is using a configuration from hp microserver gen8 with two hdd (soft raid1) with windows server 2016 and sql server 2017 installed.Lately, there have been complaints about freezes of terminals. Having diagnosis everything, I came to the conclusion that the problem is in the system itself, and because of it, the access to the sql server slowed down significantly. I want to reinstall the system completely. what are the recommendations for tuning the system for maximum performance?

Switch to SSD drives and ditch raid. Upgrade to sql 2019 Also be sure you have sql server set to keep connection alive. By default it closes it after inactivity.

It’s my experience though that little freezes are typically sambapos configuration issues. Entity screens can cause it if your using a lot of advanced calls etc.

1 Like

Those micro servers are low power machines made for NAS type operation rather than data crunching.
Their power consumption shows their designed for 24hr background running using something like 40w peak and 10-15w idle.
Had one as my nas media server until recently, they are nice boxes but not made to be that type of server.
A reasonable spec pc with reasonable ram and fast ssd would be improvement.
Get what you can afford but I operate using an older i5 dell sff pc. Think it was 8/12gb ram and pair of 128gb ssds.
For ssds just check spec and make sure they are the better ones at least 500mbs read and wrote as cheap ones often have like 500 read but only 100 write rates.

1 Like

Does it make sense to use SQL Server Enterprise? Since SQL Server Express has limitations on performance? PS the SSD I bought.

SQL Server Express is free but Enterprise is paid, and very expensive.

1 Like

You need to have a pretty big database before you need to worry about more than free version.

To add to this, there are no enterprise features implemented in the SambaPOS database.

1 Like

There is not a limit on performance for sql express. You will not gain any performance advantage. It sounds like to me it may be a configuration issue.

1 Like

So, I think I found the perfect idea. I installed SQL Server Express on Debian and recovered from SSMS backup. Works fine and very fast. Also, I’ve tried running SambaMessageServer on Debian, but if it doesn’t work, I’ll just install it on one of the terminals. I’ll try it on the live system on Monday.

So, after 10 days I can say - MS SQL Server on Debian - is faster solution for SambaPOS. Because you can start only SQL server without garbage windows services. Also I think that you can use Windows server without GUI and receive the same results.

I would be interested in the actual performance benchmarks used. Did you happen to record any?

Also what version of windows did you originally have it on?

So far, only according to the restaurant’s waiters, the system has become more stable. Previously, there were frequent freezes applications, now this problem is fixed.

What version of windows was it on before?

Windows server 2016 R2 x64 with GUI. On windows OS this DB worked 2 years. I had some problems with windows, like performance drop over time. I am convinced that windows is a bad server solution and this is confirmed by my 14-year experience.

Your definitely entitled to that opinion. Everyone has different experiences. I don’t think it’s that simple though. Windows is powering many of the most powerful systems in the world. Linux is as well but windows is powering far more. It’s definitely a good solution so is Linux. I’m curious about SQL server performance. While I have had no issues with windows running it I have not used a Linux version of SQL server.

I would be interested to see your opinion 2-3 months from now. I’m looking forward to hearing about your experience.

1 Like

I will definitely tell you about the results of the experiment. I can’t hide the fact that I did this out of curiosity as to whether porting to a linux server would solve my problem. Before doing this, I assumed that MS SQL Express for linux is similar SOFTWARE and works the same way and there may be problems only at the SMB Protocol level. That was confirmed - backup from the program interface is not available. But I think that the problem can be solved by installing Samba (open SMB protocol for linux, not our PoS software :)) and if setting it up correctly, SambaPOS can save the backup file via SMB on client workstation.

You can setup a cron job for backups.

the sql command to backup:
BACKUP DATABASE @Database TO DISK = @FilePath WITH FORMAT, NAME = @Description

(AT)Database is the database name
(AT)FileBath is the name of the backup (databaseName_Full.bak) with absolute path (e.g. /home/username/sambabackups/databaseName_Full.bak)

When the backup is finished you can pipe the file name into zip for compression.

The filename format so that SambaPOS populates the datagridview in backups is this: databaseName_yyyyMMddHHmmSQM.zip

Also, if you include a file desc.txt in the zip holding a description of the backup, that file data will show in the Description column in SambaPOS.

1 Like

@Jesse you recommended using SSD as the database storage. I was told by a network engineer at Rackspace that for longevity disk drives (RAID 1) are the way to go. Not saying either is wrong, but do you have any thoughts on this?

I’m creating a new POS server right now and we’ve been experiencing delays at client terminals. Will SSD’s on the server greatly impact performance of the clients? Looking for consistently fast loads on the client POS machines.


SSD for the database server is not going to make a huge difference. SSD for a system running sambapos makes a big difference.

Running managed hosting, like what Rackspace does, is totally different to setting up a POS system. The Rackspace engineer is right about RAID 1 being a good solution for longevity with disk drives, however what they deal with is nothing similar to SambaPOS. Web hosting can have hundreds or thousands of concurrent connections all accessing different data and that puts a huge amount of pressure on the storage solution to deliver. RAID is great for giving redundancy, also RAID is just a technology for storage, so you can have RAID with SSD drives. Actually many hosting providers, Rackspace included, are moving away from hard drives to SSD for the increased speed and performance.

For any POS setup and relevant servers, use SSD. If you want extra redundancy, use it along with RAID but also have a proper backup solution in place. So far myself being involved with hundreds of setups with most of them using SSDs I have had not one failure in that time related to long term usage - only once a SSD failure within a month of setup which was obviously a manufacturing defect. So that’s pretty good going I would say.