6

I have two servers.

A garbage picked, slapped together with duct tape and bubble gum. It is a mix of Dell R620 and Gen 8 HPE parts with 40 cores / 1.5 TB ram. Running ubuntu 22.04 and sql server 2022 Dev Version. Raid 10 3.7 TB SSD's. MDOP is set to 16.

The other is a VM hosted on a triple head cluster Xenon Gold's with ssd Nas the whole 9 yards. Server 2019, sql server standard 2022. 128 gigs / 16 cores allocated. MDOP is also set to 16. Database was a backup / restore from the test server.

The main table houses live streamed data averaging 100-200 mil new records a day, currently approaching 30 billion rows with 4 columns. It is setup with a column store index and the data is sorted and populated via a timestamp field.

Doing a simple query takes seconds to minutes on the test server but minutes to almost an hour on the soon to be production server.

The test server shows all 16 cores being utilized. On the production server it will only utilize two cores. If I run multiple instances of the same query the production server will consume an additional set of two cores per query executed. This can be done until the CPU is at 100% utilization.

The query I am testing with is select top 100 * from mlinkdatacsunixtime order by serialtime desc On the test server it runs in 1 min to 1.5 min. On production it is 11 minutes.

I have checked: VM socket vs Core count. 2 Sockets / 8 Cores each. Verified sql server shows all 16 as online and visible. Matched up all the options between the two sql instances. Doubled checked server and DB MDOP settings. Executed Query multiple times to try to load the table in memory. Table is about 50 gigs, Prod server shows only 63 gigs of 128 utilized after testing multiple query's. Test server is hovering about 250 gigs but has a lot more going with other projects.

Really hoping someone can tell me I missed something stupid.

My next test is rebuilding the server with sql Dev version to see if it is something with sql standard. My last straw is trying to convince the sys admin and management to let me run a linux server in production.

Improve this question
1
  • If I were looking at asking for 16 enterprise licenses, I would probably look into why it takes 60-90 seconds on the development rig in the first place. A top 100 query on a supposedly indexed field should never take that long
    – mikkel
    Commented Jul 29 at 6:21

1 Answer 1

Reset to default
12

👨‍🎓👨‍🎓👨‍🎓

An educated guess would be that Microsoft hates you for using Standard Edition, and your batch mode queries are being limited to a DOP of 2.

See this post for details on that, and other ways Microsoft intentionally hobbles batch mode in Standard Edition in order to bully you into buying Enterprise Edition licensing.

Improve this answer
0

Your Answer

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

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