0

I've been working on an environment where every index creation is happening on a single core. Big or small indexes/tables. Even when i force maxdop in the creation it still ignores it and runs on a single core.

MaxDop is set to 0. Available cores are 24. Cost treshold is set at 5. The index creation cost more than 1000. SQL configured memory is: 160GB, even tho 128GB is max.

SQL-server running: Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Why is it not able to go parallel?

1 Answer 1

3

This is because you are using Standard Edition. Parallelism for index creation is only supported in Enterprise Edition. This is noted in the Editions and supported features of SQL Server 2019 - RDBMS manageability documentation for "Parallel indexed operations".

It's also noted in the CREATE INDEX documentation:

Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features...

And also in the Configure Parallel Index Operations documentation:

On multiprocessor systems that are running SQL Server Enterprise or higher, index statements may use multiple processors (CPUs) to perform the scan, sort, and index operations associated with the index statement just like other queries do.

4
  • thank you.. should have known that.. was comparing with my demo environments which are all developers editions. Jul 26, 2023 at 13:07
  • @FiratAygur Ah yea, that's the gotcha with Microsoft's licensing model. Developer Edition is effectively the same as Enterprise Edition as far as what features are available. Unfortunately that makes it a little more difficult to test things consistently between one's development environment and their production environment if they use Standard Edition in their production environment.
    – J.D.
    Jul 26, 2023 at 13:20
  • 1
    MS have been asked for many years to add a switch for developer edition to make it behave like the standard edition - sad that they just ignore this request Jul 26, 2023 at 13:52
  • @StephenMorris-Mo64 IMO, I'm sure it was viewed at one point as a good marketing tactic by someone at Microsoft to force anyone trying SQL Server Developer Edition to be locked into Enterprise-features, so that they may end up reliant on something only offered by Enterprise Edition when they go to buy licensing for their production environment.
    – J.D.
    Jul 26, 2023 at 15:25

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.