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.