Sql-server – SQL Server 2008 R2 Express single CPU core usage and need of upgrade to SQL Server 2014 Express

performancesql serversql server 2014sql-server-expresswindows-server

In our office we are using SQL Server 2008R2 Express which is installed in Windows server 2012. The SQL Server database is accessed by more than 60 users in network simultaneously. In recent days we are experiencing slow performance of SQL Server and our programs which makes use of SQL Server.

From last few years we were using Windows Server 2003 with SQL Server 2008 R2 Express. Few months we changed server to Windows Server 2012.

I can analyze the performance of SQL Server using performance counters etc. But before that I wanted to know below details

  1. Is there any limitation in SQL Server 2008 R2 Express like it uses only one core of the CPU? And does it may be a reason for slow performance?

  2. Are there compatibility problems when using old version (SQL Server 2008 R2 Express) on Windows Server 2012?

  3. Is there any need of upgrading to SQL Server 2014 Express? Will I get any good benefits?

Best Answer

Express editions are limited, see Features Supported by the Editions of SQL Server:

  • Maximum Compute Capacity Used by a Single Instance: Limited to lesser of 1 Socket or 4 cores
  • Maximum memory utilized: 1 GB
  • Maximum relational Database size: 10GB

These limitations apply to 2008R2, 2012 and 2014 versions, and for 2005 and 2008 the max database size is even less: 4GB (CPU and memory restrictions are the same).

To troubleshoot your performance problems I recommend you read How to analyse SQL Server performance. If you discover that (likely) you are hitting the Express Edition limitations then you need to upgrade to a paid license edition (probably Standard). You will know you are hitting the limitations:

  • if you see CPU contention you'll see high SOS_SCHEDULER_YIELD wait times and high CPU perf counters on only 4 cores while otehr are idle.
  • symptoms of the 1GB memory utilization you'll see high PAGEIOLATCH_XX wait times and high disk utilization on OS counters (low memory triggers more IO).

Upgrading Express Edition from 2008R2 to 2012 should be a trivial matter, same for 2014. I don't think there is any feature in 2014 or 2012 that you could leverage in Express Edition, but reasons to choose the latest version always exists: T-SQL language enhancements, product support. See What's new in SQL Server 2014 and What's new in SQL Server 2012.

Related Question