3

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?

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
9
  • What is the exact meaning of "Limited to lesser of 1 Socket or 4 cores"? Is it same in 2008 r2 express too?
    – IT researcher
    Jun 11, 2014 at 9:35
  • It is in every version since SQL 2005. It means that the engine runs only, at most, 4 threads at any time. There might be more threads present, but all but 4 will be suspended. Read Thread and Task Architecture.
    – Remus Rusanu
    Jun 11, 2014 at 9:57
  • 1
    You best measure and compare. Read Choosing Between SQL Server 2012 and SQL Server 2014, for Express Edition the Eager tempdb writes and the new CE both apply.
    – Remus Rusanu
    Jun 11, 2014 at 10:12
  • 3
    @ITresearcher: "Limited to lesser of 1 Socket or 4 cores" means it will use at most one CPU and if that CPU has many cores it will use at most four of them. For a single CPU with up to four cores it'll use the lot. A single CPU with more than four cores will only see up to four being used. Two dual core CPUs will see one CPU (so two cores) being used. Two quad-or-more core CPUs will see up to four cores in one CPU (so up to four cores) being used. I'm not sure how it counts the virtual cores made available by hyperthreading being present and enabled.
    – David Spillett
    Jun 11, 2014 at 10:31
  • 1
    Also be careful when trying to judge multi core/CPU use that you test with a paraliseable workload. If you push in queries on a single thread you might see just one core being used where more potentially could be as the query planner decides it is more efficient to lock the process to one core than spread it and risk making things slower due to extra IO contention between the threads.
    – David Spillett
    Jun 11, 2014 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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