April 8, 2022 at 5:25 pm
Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold for parallelism', 40;
RECONFIGURE;
As for the second setting, how many CPUs do you have licensed for SQL Server? If it's only 12, or close to it, you should reduce the MAXDOP setting too. Something like, say, 8, i.e., leave 4 other CPUs free to do other things even if a big parallel query is running.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2022 at 5:26 pm
--Removed, double post.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2022 at 5:44 pm
water490 wrote:Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold for parallelism', 40; RECONFIGURE;
As for the second setting, how many CPUs do you have licensed for SQL Server? If it's only 12, or close to it, you should reduce the MAXDOP setting too. Something like, say, 8, i.e., leave 4 other CPUs free to do other things even if a big parallel query is running.
I have 16 cores. What should I change Max Degree of Parallelism to?
April 8, 2022 at 5:50 pm
ScottPletcher wrote:water490 wrote:Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold for parallelism', 40; RECONFIGURE;
As for the second setting, how many CPUs do you have licensed for SQL Server? If it's only 12, or close to it, you should reduce the MAXDOP setting too. Something like, say, 8, i.e., leave 4 other CPUs free to do other things even if a big parallel query is running.
I have 16 cores. What should I change Max Degree of Parallelism to?
That's up to you. The big thing is that you don't allow SQL to use (almost) all cores for one query. If you have some extremely large tables, you might want to stick with 12. If not, I would go with something like 8. SQL will choose the actual number of cores to use for parallelism anyway, based on its estimates at that moment, up to the limit you set.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2022 at 5:51 pm
For more specific tuning, yes, we must see the query plan and the DDL for the table(s) involved, including all indexes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2022 at 8:57 pm
What happens to indexes when a database is restored from an old machine into a new machine? Do they transfer over or do I have re-do them?
April 8, 2022 at 9:17 pm
The indexes will come along. Since they are part of the db, they get restored with it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2022 at 2:19 am
I have a copy of the execution plan. It contains my query so I do not want to share that publicly. Is there a way to share the performance items but not the query itself?
April 10, 2022 at 7:28 am
use https://www.sentryone.com/plan-explorer - it has a option to anonymize the plan so all object names/columns get replaced with "object1, object2" and so on.
and put it on https://www.brentozar.com/pastetheplan/ and share the link to it.
April 10, 2022 at 5:05 pm
water490 wrote:ScottPletcher wrote:water490 wrote:Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold for parallelism', 40; RECONFIGURE;
As for the second setting, how many CPUs do you have licensed for SQL Server? If it's only 12, or close to it, you should reduce the MAXDOP setting too. Something like, say, 8, i.e., leave 4 other CPUs free to do other things even if a big parallel query is running.
I have 16 cores. What should I change Max Degree of Parallelism to?
That's up to you. The big thing is that you don't allow SQL to use (almost) all cores for one query. If you have some extremely large tables, you might want to stick with 12. If not, I would go with something like 8. SQL will choose the actual number of cores to use for parallelism anyway, based on its estimates at that moment, up to the limit you set.
The setting for MAXDOP should be less than or equal to 8 logical processors - for servers with a single NUMA node. For servers with multiple NUMA nodes then the setting should be less than or equal to 16 logical processors. For example, if you have 2 NUMA nodes (hardware or soft-NUMA) and each node has 8 logical processors then MAXDOP would be set to 8.
For the OP - it shouldn't be set to more than 8 and possibly lower depending on the type of database and queries that being run. If the application is primarily an OLTP type application - and most of the queries are to support the application and not reporting/analytics then it can be set as low as 2.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 10, 2022 at 7:20 pm
water490 wrote:ScottPletcher wrote:water490 wrote:Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold for parallelism', 40; RECONFIGURE;
As for the second setting, how many CPUs do you have licensed for SQL Server? If it's only 12, or close to it, you should reduce the MAXDOP setting too. Something like, say, 8, i.e., leave 4 other CPUs free to do other things even if a big parallel query is running.
I have 16 cores. What should I change Max Degree of Parallelism to?
That's up to you. The big thing is that you don't allow SQL to use (almost) all cores for one query. If you have some extremely large tables, you might want to stick with 12. If not, I would go with something like 8. SQL will choose the actual number of cores to use for parallelism anyway, based on its estimates at that moment, up to the limit you set.
The setting for MAXDOP should be less than or equal to 8 logical processors - for servers with a single NUMA node. For servers with multiple NUMA nodes then the setting should be less than or equal to 16 logical processors. For example, if you have 2 NUMA nodes (hardware or soft-NUMA) and each node has 8 logical processors then MAXDOP would be set to 8.
For the OP - it shouldn't be set to more than 8 and possibly lower depending on the type of database and queries that being run. If the application is primarily an OLTP type application - and most of the queries are to support the application and not reporting/analytics then it can be set as low as 2.
I have a data warehouse so I am just running queries against it. no OLTP. what should i set it to?
how do i know how many NUMA my system has? I have i9 12900K 16C
April 10, 2022 at 8:47 pm
use https://www.sentryone.com/plan-explorer - it has a option to anonymize the plan so all object names/columns get replaced with "object1, object2" and so on.
and put it on https://www.brentozar.com/pastetheplan/ and share the link to it.
hopefully I did this right. Please let me know if something isn't looking right.
i5
https://www.brentozar.com/pastetheplan/?id=ryGGZTeN9
i9
https://www.brentozar.com/pastetheplan/?id=B1Ctb6gV9
I have posted two version of the plan. one is run on my old machine (i5) and other is from new machine (i9). i backed up the db from old machine and then restored it to new machine so the SQL are/should be identical. this means that if i9 version is missing an index then that should also be happening in i5 version.
any help is much appreciated.
thank you
April 10, 2022 at 9:00 pm
what is the compatibility mode of the database? - never mind - its 150.
Before trying anything else do a rebuild of the columnstore indexes on the tables used on the first 2 main queries. stats seem out the roof.
April 10, 2022 at 9:17 pm
what is the compatibility mode of the database? - never mind - its 150.
Before trying anything else do a rebuild of the columnstore indexes on the tables used on the first 2 main queries. stats seem out the roof.
Yes it is 150
ok i will try that and report back shortly
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply