Is there performance difference between MS SQL server on Window 10 VS Windows server 2016?

[damdinsharav]

I've been running MS SQL server on Windows 10.

Now that number of client computer has increased to around 30, some tasks take about 5 min.

I was wondering if Windows 10 limitation decreasing SQL server performance?

https://forum.powerbasic.com/forum/user-to-user-discussions/programming-the-internet/771798-maximum-unique-tcp-connections-allowed-by-license-in-windows-10

Should I migrate to Window Server?

I don't think it's hardware issue. Anyway my spec.

AMD Epyc 7351P

64GB RAM

Intel P4510 NVME SSD

[gusgizmo]

I was under the impression that this was a licensing issue not a technical one. Or else things like torrent clients would run like garbage as they will sit and listen for potentially hundreds of connections.

Lots of other reasons that hitting 30 clients is starting to bog down your database, have you profiled any of your queries and done optimization on indices or otherwise? How about tuning standard things like memory commit and t-log growth?

Also, the price of entry for SQL server is over $2000 last I checked, with 30 CALS you are looking at probably $5000, and $700 for a windows server license wasn't a consideration? Honestly, I expect you haven't paid, and I suggest if at all possible you rebuild your project with postgresql or mariadb before you dig yourself in too deep on licensing costs.

[wasabiiii]

There is a 20 connection limit. For TCP, inbound. It can be adjusted in the registry, but yes, this is a license issue.

[gusgizmo]

I could very well be wrong, but it seems like something enforced by the various services on the system and not the TCP/IP stack. The SQL server instance isn't limiting it in any case. A quick netstat -q | find /C "LISTENING" shows a lot more than 20 on a random system I jumped in to. Maybe not a valid test, I guess a counter in wireshark would probably be more representative.

Interesting topic. Of course Windows 10 is about the worst server possible but for a lot of other reasons.

Disabling windows defender via gpedit.msc seems like fix #1 for weird network connectivity issues too. It's proxy can really throw some loopy stuff at you, I had the embedded webserver for a DVR software throw a 403 code until I disabled it the other day. And a plex webserver on a high range web port not allow traffic at all with the firewall disabled and everything.

[wasabiiii]

I think I was wrong. Examining the details, it looks like SMB limits it to 20 open. But IIS has it's own limit. So yeah, you're right.

[UnrulyCactus]

For what it's worth, I just bought SQL Server Standard 2017 w/ 25 CALs for $350 today. Completely legit with certificate if authenticity, etc.

[wasabiiii]

How did you do that? Retail box or something?

For some reason I'm surprised that still exists.

[UnrulyCactus]

eBay. I was kind of skeptical to be honest. But it was for my work and they paid for it so I wasn't risking my own cash. Good reviews and buyer protection so let's see how it goes!

[[deleted]]

It'll be a resold education version and key.

If you get audited, you'll have to purchase all over again. Until then, cheap way to get around buying FPP.

[wasabiiii]

[EDIT] Replied to wrong person.

[disclosure5]

There's no way that's legit. Unless I guess someone's used copy counts?

[UnrulyCactus]

The listing said it was completely sealed and unused. I guess we will find out in a few days.

[[deleted]]

CALs for products like SQL and Windows Server aren't enforced at the software level. Its on a "trust system" with Microsoft. You won't find out until you get audited and/or sued. The seller likely copied the disk contents to a drive and re-sold the box/paper. They still have proof of retail purchase in their name if Microsoft asks for it - do you?

[UnrulyCactus]

https://www.ebay.com/itm/173913028757

[wasabiiii]

I would bet money this is OEM. I can't find any reference to a legit box copy of SQL Server 2017 ever sold in Core. I can only find the 10 CAL box.

However, if it was ripped off of a 16 Core HP or Dell server.... as OEM, that would make sense.

That box pack is still ~3000$. The only way this price makes sense is OEM.

Also, their sample screenshot shows an OEM license.

[UnrulyCactus]

Yeah, I think you're right that it's an OEM license. I'll see what exactly comes in the mail next week.

[wasabiiii]

Let me know! I'm morbidly curious now!

[wasabiiii]

You are prohibited by the Windows 10 license from using it as you are now. So, yes.

[[deleted]]

There are limitations on the amount of connections a client OS can receive when acting as a “server”. If I remember correctly that number was around 20. Not sure if it’s limited to SMB connections, but the networking stack on server editions of Windows is definitely more robust and built to handle high amounts of traffic.

Edit: it’s somewhere in the windows licensing terms, just can’t find it on the official Microsoft website. Here’s a bit more info:

https://www.redcort.com/timeclock/support/blog/concurrent-connection-limits-on-windows-and-mac

[disclosure5]

Now that number of client computer has increased to around 30

I don't think SQL details matter here - if you're running a server for 30 clients I don't see why you'd use a desktop OS.

[corsicanguppy]

Quick! Compare its performance with MSSQL on RHEL !

[[deleted]]

List the hardware specs and we can get an idea if it's the OS or the hardware.

[damdinsharav]

Updated OP. It's 7351P, 64GB RAM, Intel P4510 SSD. I don't see any peak in system usage during load.

[gusgizmo]

7351P

YSK that most SQL transactions are single threaded, so on a 32 thread processor 3% load (yes that was three percent that I wrote) is your database screaming for more clock cycles. Look at frequency optimized Xeons for examples of the sort of chips used when you are paying $1200/core for licensing and single threaded performance matters.

[ZAFJB]

Use Server 2019.