January 22, 2019 at 6:02 pm
Sorry,
For the life of me I cannot remember how to tell the optimizer to create an execution plan as if there were more CPUs.
I have some DEVs writing SQL who just aren't aware of the impacts of how they are writing their TSQL, but I can't remember the hint and am coming up with nothing in the GoogleVerse. I know, the results are only as good as the search predicate and I am not doing a good job of it today.
something like (OPTIMIZE FOR CPU=16),
fail
January 23, 2019 at 3:21 am
Use the MAXDOP query hint? That can go above the configured value as well as below...
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 23, 2019 at 9:28 am
No, that just tells a query it's OK to use additional processors in parallel if it thinks it needs them. What I am talking about is if your development instance has 4 CPUs but your production box has 16, you can tell the optimizer to create an execution plan as if it had 16 CPUs.
But thanks for taking the time to post.
January 24, 2019 at 5:55 am
Oh, there's a DBCC command to force simulation. Do an internet search. It's out there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2019 at 12:27 pm
Thanks, Grant. Found it.
Great tool to use, especially with statistics exported to your dev environment. For those reading this, it is an undocumented command, so proceed with caution and remember to clean up after using it:
January 25, 2019 at 5:47 am
Davis H - Thursday, January 24, 2019 12:27 PMThanks, Grant. Found it.DBCC OPTIMIZER_WHATIF
Great tool to use, especially with statistics exported to your dev environment. For those reading this, it is an undocumented command, so proceed with caution and remember to clean up after using it:
DBCC OPTIMIZER_WHATIF(ResetAll);
In my best Charlie Brown voice... THAT'S IT!
And never, ever, run this in production.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2019 at 6:09 am
The trouble is that you can look at execution plans all day and still have problems because even actual execution plans are riddled with estimates. The only method to accurately gauge performance is to actually run the code and measure the sections of code properly using different use cases. I'll also state that if you actually do need a shedload of processors for your code to run quickly, then you've likely written the code incorrectly. Heh... anyone that actually needs more than 8 processors should turn in their man-card. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 6:25 am
Jeff Moden - Friday, January 25, 2019 6:09 AMThe trouble is that you can look at execution plans all day and still have problems because even actual execution plans are riddled with estimates. The only method to accurately gauge performance is to actually run the code and measure the sections of code properly using different use cases. I'll also state that if you actually do need a shedload of processors for your code to run quickly, then you've likely written the code incorrectly. Heh... anyone that actually needs more than 8 processors should turn in their man-card. 😉
On that note, an execution plan that uses 2, 8, 16, or 9,000 processors will all look the same. The overall shape will not be any different because of differences in the number of processors. The only difference will be in the details of the operators. The system with 2 processors will only have 2 threads. The system with 9,000 processors will have, up to, 9,000 (yeah, I know, not a thing.... yet).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2019 at 1:19 pm
I am completely on board with everything said. My issue is we have a C# development team who have been tasked to begin writing their own TSQL. But neither I, nor my SQL developers have the bandwidth to do a "good enough" code review. (SQL Developers are writing C#, too) As I am trying to help them understand good query writing and learning to read an execution plan, most of the time (after it crawls on prod), they always say, "Well, prod has more processors and memory than our dev instance, so it shouldn't have done that."
Our prod box has 16 cpus WITH MAXDOP=4 and 122 GB max mem.
I would like to build an instance on their dev box where I script out the prod database including the stats and histograms where we can look at the execution plans with something like:
DBCC OPTIMIZER_WHATIF (1,4)
DBCC OPTIMIZER_WHATIF (2,122)
That way, we can all see what the plan would look like there.
What do you think?
January 25, 2019 at 1:39 pm
Yeah, that works. It's hard to convince people that behavior is behavior is behavior. It's down to the system settings and the statistics. In fact, I'd worry more about inconsistency in the stats resulting in plan differences between systems a lot more than other stuff.
Make sure the Cost Threshold is the same on both servers (and not the default value of 5, for all that is holy).
They'll stop sweating it after a while because it's just the threads that are going to show any differences at all (assuming all the other settings are the same).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2019 at 2:32 pm
Davis H - Friday, January 25, 2019 1:19 PMI am completely on board with everything said. My issue is we have a C# development team who have been tasked to begin writing their own TSQL. But neither I, nor my SQL developers have the bandwidth to do a "good enough" code review. (SQL Developers are writing C#, too) As I am trying to help them understand good query writing and learning to read an execution plan, most of the time (after it crawls on prod), they always say, "Well, prod has more processors and memory than our dev instance, so it shouldn't have done that."Our prod box has 16 cpus WITH MAXDOP=4 and 122 GB max mem.
I would like to build an instance on their dev box where I script out the prod database including the stats and histograms where we can look at the execution plans with something like:
DBCC OPTIMIZER_WHATIF (1,4)
DBCC OPTIMIZER_WHATIF (2,122)That way, we can all see what the plan would look like there.
What do you think?
Since your Dev Box has 4 CPUs and your prod box has a system MAXDOP of 4, the WHATIF stuff is totally unnecessary. And, yeah... I totally agree with your prod box setting. No one gets more the 1/4 of the total horsepower and that's my first rule of thumb for the setting. My second rule of thumb is that if you have more than 32 CPUs on your prod box, no one get's more than 8, period. We have a 48 CPU box at work and system maxdop is set to 8.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 2:35 pm
Thanks, Jeff
Our DEV box has 2 CPUs.
January 25, 2019 at 2:42 pm
Jeff Moden - Friday, January 25, 2019 2:32 PMDavis H - Friday, January 25, 2019 1:19 PMI am completely on board with everything said. My issue is we have a C# development team who have been tasked to begin writing their own TSQL. But neither I, nor my SQL developers have the bandwidth to do a "good enough" code review. (SQL Developers are writing C#, too) As I am trying to help them understand good query writing and learning to read an execution plan, most of the time (after it crawls on prod), they always say, "Well, prod has more processors and memory than our dev instance, so it shouldn't have done that."Our prod box has 16 cpus WITH MAXDOP=4 and 122 GB max mem.
I would like to build an instance on their dev box where I script out the prod database including the stats and histograms where we can look at the execution plans with something like:
DBCC OPTIMIZER_WHATIF (1,4)
DBCC OPTIMIZER_WHATIF (2,122)That way, we can all see what the plan would look like there.
What do you think?
Since your Dev Box has 4 CPUs and your prod box has a system MAXDOP of 4, the WHATIF stuff is totally unnecessary. And, yeah... I totally agree with your prod box setting. No one gets more the 1/4 of the total horsepower and that's my first rule of thumb for the setting. My second rule of thumb is that if you have more than 32 CPUs on your prod box, no one get's more than 8, period. We have a 48 CPU box at work and system maxdop is set to 8.
What are you talking about? It was so fun watching how a production server was using 16 or more CPUs in a single query and blocking other processes during hours. This was a data warehouse database with all the default settings in place. Thanks God I'm no longer working there or dealing with their "DBAs".
January 25, 2019 at 4:04 pm
Luis Cazares - Friday, January 25, 2019 2:42 PMJeff Moden - Friday, January 25, 2019 2:32 PMDavis H - Friday, January 25, 2019 1:19 PMI am completely on board with everything said. My issue is we have a C# development team who have been tasked to begin writing their own TSQL. But neither I, nor my SQL developers have the bandwidth to do a "good enough" code review. (SQL Developers are writing C#, too) As I am trying to help them understand good query writing and learning to read an execution plan, most of the time (after it crawls on prod), they always say, "Well, prod has more processors and memory than our dev instance, so it shouldn't have done that."Our prod box has 16 cpus WITH MAXDOP=4 and 122 GB max mem.
I would like to build an instance on their dev box where I script out the prod database including the stats and histograms where we can look at the execution plans with something like:
DBCC OPTIMIZER_WHATIF (1,4)
DBCC OPTIMIZER_WHATIF (2,122)That way, we can all see what the plan would look like there.
What do you think?
Since your Dev Box has 4 CPUs and your prod box has a system MAXDOP of 4, the WHATIF stuff is totally unnecessary. And, yeah... I totally agree with your prod box setting. No one gets more the 1/4 of the total horsepower and that's my first rule of thumb for the setting. My second rule of thumb is that if you have more than 32 CPUs on your prod box, no one get's more than 8, period. We have a 48 CPU box at work and system maxdop is set to 8.
What are you talking about? It was so fun watching how a production server was using 16 or more CPUs in a single query and blocking other processes during hours. This was a data warehouse database with all the default settings in place. Thanks God I'm no longer working there or dealing with their "DBAs".
😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 5:05 pm
That was the first thing I did on day 1 here. The application was extremely slow. The MAXDOP was set at 0, so a query would come along and grab all 16 cps. I changed it to 4 and set the threshold to 800 and you would have thought I just cured cancer around here. I was like a god here for a week, until the next wart raised its head. Which, as you might imagine, wasn't long.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply