2

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.

1

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):

  1. memory available to the buffer pool: 1.2GB
  2. maximum number of CPU cores: Limited to lesser of 1 socket or 4 cores
  3. database size: 10GB

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

  • When it comes to volume of usage over time, that isn't really what I'm driving at with this question; I'm asking specifically about how you can definitively determine whether it's the limitations of SQL Server Express that are the bottleneck, regardless of why that might have come about. Thanks for the Brentozar links - I'd actually read a few articles on that site, but hadn't spotted those specific ones. I'll have a read. – dbr Oct 13 '17 at 21:42
  • You will definitively determine whether it's your bottleneck or not by analyzing your work load, that's why I mentioned about Wait Types, resource intensive queries, etc. A poorly designed query might easily fill up all your buffer pool, but you'll figure that out only when you start to see recurrently RESOURCE_SEMEPHORE on your list of wait types. This is just one example. And when it comes to the volume of usage over time, it's because key tables might have grown, and the amount of memory(available to SQL) is not enough to deal with the queries + exec plans anymore. – Everton Barciela Oct 14 '17 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.