Question: What is the best way to determine whether the limitations of SQL Server Express are the bottleneck in an environment? Are there some performance counters that would be a good indication for example? Would some of the information within SQL Server Activity monitor provide good evidence? The database size limit is an easy one to check, but the others are a little less clear-cut. Looking at system-wide perf counters doesn't seem to be the right approach if Express is restricted in terms of what resources it will utilise.
A related question – is it possible with SQL Server to easily trial Standard edition without having to reconfigure things drastically either before or after the trial? The ideal would be a way to "switch on" the capabilities of the full-fat edition on an existing instance, and then possibly roll back if needed afterwards. If that's not possible, then installing a separate instance and moving the database temporarily wouldn't be too bad.
Background: I have an environment in which the main application is a Windows-based line of business application backed by SQL Server Express. The application has performed poorly for a long time, but up until recently, the hardware, software and general environment were old and poorly maintained, meaning moving to a new environment with updated software and modern, more adequate hardware may well help. The application vendor believed upgrading from SQL Server Express 2008 to 2014 or above would help, but it wasn't practical at the time.
Having migrated to an entirely new environment with modern hardware, Windows Server 2016 and SQL Express 2016, the application is still performing poorly. We suspected this may happen if the bottleneck is in fact the limitations of SQL Server Express; however, I'm unsure of how to definitively determine that this is the case and it's not something else. The new environment has ample resources in terms of CPU, memory and disk, so I think it's unlikely, but I would prefer to have some solid proof.
If anything, it is slightly slower than before, perhaps due to the SQL database engine and application now running on separate VMs (albeit on the same virtual switch), whereas previously they were both running on the same physical box.
As anyone who has looked into it will know, full-fat SQL Server is not cheap, so the business case would need to be backed by solid evidence that this is what's needed and will definitely provide a boost.
Best Answer
as you stated the application is running for very long time, so I'd assume that the usability and volume of usage has changed since then, which could cause the performance degradation. SQL Express have resource limitations that may directly impact the performance problem you're experiencing. Even though you have a well spec hardware, the SQL might not even using it.
The main limitations are the following (SQL2012-2016):
Regarding to the other question: Yes you could do a side-by-side installation of the Standard edition for the period of trail to see how it goes, or even a Developer edition on a TEST server, which has all the capabilities available on the Enterprise edition and it's free.
Basically you'd need to backup the database on the Express edition and Restore on the new instance. Also you'd need to change the connection string of the application to point to the new named instance.
The thing is your performance issues might be or not due to the limitations of the Express edition. It would be interesting to do a deeper investigation on how things are performing inside of the SQL, such as what SQL Server is waiting on, what's consuming the resources the most, the most resource intensive queries and so forth.
Here you have how to do a free performance check on your system, if you don't know exactly how to interpret the results, post them here, I'm sure there will be someone to help.
How to Do a Free SQL Server Health Check
How to Do a Free SQL Server Performance Check