Upgrade server Sql Server 2014 from Standard to Enterprise with Mirroing

Oleg Ivanov 21 Reputation points
2021-04-05T16:55:20.523+00:00

Hi everyone!
I need some advise for solve next task:
I have configured and fully worked Mirroring on MS SQLServer 2014 Standard Eddition.
In last time i see what my APPS generate 100% overload Load CPU.
On Server set 2 socet on 8 Core + HyperTrading on, in summ 24 logical core, all load on 100%.
I want to solv this problrm by upgrade CPU, but how i understand 24 logical core maximum CPU on hardware server jn Standard Eddition, and i must ugrade license from Standard Eddition to Entrerprise.
Please, advise me how can i solve this issue

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions
Accepted answer
  1. Erland Sommarskog 101K Reputation points MVP
    2021-04-06T21:18:54.287+00:00

    To give a little more detailed answer to this question:

    No, going to Enterprise is not going to help poorly written code. And I've been in this game long enough to know that this is a case of poorly written code and/or bad indexing.

    But beside queries that needs tuning or better indexing, there is another way code can be poor. Some developers inline all parameters, so rather than sending: 

    INSERT tbl(col1, col2, col3, ...)
   VALUES(@par1, @par2, @par3, ...)

    They send something like 

    INSERT tbl(col1, col2, col3, ...)
   VALUES(14, 'Peter', 'Smith', ...)

    That is all parameters are inlined to the query. This means that every query needs to be compiled. Well, almost, very simple queries will be auto-parameterised.

    The correct fix in this case is to have the developers to clean up their act, which may take some time. But SQL Server provides a band-aid in this case.

    When it comes to upgrade, I think a better path is to go SQL 2016 or later. This is because with SQL 2016 you get Query Store, which makes it a lot easier to find slow-running queries. On SQL 2014, you need to runs traces or X-Event sessions to identify them, and these tools do add overhead themselves.

    Yes, there is a license cost for an upgrade. But I guess it's cheaper to go to SQL 2019 Standard than to SQL 2014 Enterprise. (And with SQL 2016+, you can replace mirroring with a Basic Availability Group.)

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2021-04-05T17:29:01.203+00:00

    A cheaper option may be to find out which queries that are generating that load and tune them...

  2. CathyJi-MSFT 21,091 Reputation points Microsoft Vendor
    2021-04-06T06:43:04.287+00:00

    Hi @Oleg Ivanov ,

    > i must ugrade license from Standard Eddition to Entrerprise.

    Since you have an existing mirroring partnership between your databases on the two instances, you can perform a rolling edition upgrade. Refer to the blog How to Perform a Rolling Edition Upgrade While Using Database Mirroring to get detail steps.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments