Upgrade server Sql Server 2014 from Standard to Enterprise with Mirroing

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

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.
8,467 questions
{count} votes

Accepted answer
  1. answered 2021-04-06T21:18:54.287+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

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

    No comments

2 additional answers

Sort by: Most helpful
  1. answered 2021-04-05T17:29:01.203+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

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


  2. answered 2021-04-06T06:43:04.287+00:00
    CathyJi-MSFT 20,651 Reputation points Microsoft Employee

    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.

    No comments