I am running a desktop based application built in C# connected to a Microsoft SQL Server Express database. About 20-30 users are accessing this application while 20-30 more systems are connected with database at the same time and it is working 24 hours daily. OS running is Windows Server 2012 Standard.
Sometimes I am facing serious performance issues in the application, when I am restarting server everything is becoming normal.
I already scanned all computers and there is no virus detected. Looks like no problem in network devices as well.
What could be the problem? Garbage collection?
There are imitations for the SQL Server Express Edition:
Number of CPUs supported: Only one CPU at a time (max core =4 for sql express 2016). So, if your server has multiple CPUs, it will use only one CPU at a time.
Maximum memory used : A maximum of 1 GB memory for data buffer. So, if your server has more GB memeory, SQL Server Express cannot take advantage of it.
Database size limit: Maximum database size is limited to 4 GB (10GB for sql express 2016)
DISK I/O speed and Memory are one of the main resources for sql performance. As data are in cache for long time, server read from memory not from disk.
One of the measures that can help of finding server performance bottleneck is measuring the Page Life Expectancy (PLE). It should be >300 for server with 4GB memory (but really sql express use limited 1GB).
PLE is the number of seconds the average page of data has been in the buffer pool. Keeping the data in memory gives SQL Server quicker access to it instead of making the long, slow trip to disk. This measure can open your eyes and lead you towards issues that can be resolved. you can get by executing:
SELECT object_name, counter_name, cntr_value AS [value] FROM sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) = 'SQLServer:Buffer Manager' AND LTRIM(RTRIM(counter_name)) = 'Page life expectancy' ;
In sql express you loose the key resources (memory limitation 1GB) that help in enhancing PLE counter.
Your system is 20-30 users plus 20-30 more systems are connected with database at the same time and it is working 24 hours daily.
It's better to upgrade to SQL Standard Edition 2016 with no limitation of Memory (up to 128GB) and CPU/Cores (up to 24 core).