question

OlegIvanov-4652 avatar image
0 Votes"
OlegIvanov-4652 asked Cathyji-msft commented

Upgrade server Sql Server 2014 from Standard to Enterprise with Mirroing

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlegIvanov-4652,

We have not received a response from you. Any update for this thread? Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Shashank-Singh commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi
This is my head pain - programmers says all queries is optimazi, yours servers
sucks.

0 Votes 0 ·

I agree with @ErlandSommarskog here, you must find out what is maxing out CPU. My hunch, assuming queries are optimized, excessive parallelism and MAXDOP not set correct. What is output of select @@version. Is the link between principal and mirror server working fine and able to handle the load ?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @OlegIvanov-4652,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.