A lightweight video to explain the basics behind the Cost Threshold for Parallelism SQL Server setting, and how to change it:
Thanks for reading!
Configuration
Video: Setting MaxServerMemory
A quick video for the Accidental DBAs in the crowd:
Thanks for watching!
Your SQL Servers are misconfigured
So are some of mine.
So are 90% of the servers I run across, especially in mid-size businesses ( <1000 employees, or 10-20 servers typically).
Its probably not your fault.
There are a bunch of instance level settings in SQL Server that need to be set that are specific to your environment and the workload you are going to run against it. Most of the defaults that are in the SQL Server installer are set up to make sure the performance is acceptable on a very modest server. Until version 2016, the installer had not changed much.
The problem is that in a large number of organizations, the person doing the installation of SQL Server is a Developer, or a SysAdmin running a script. Both are great at their core jobs, but does either of them know what Cost Threshold for Parallelism is? It is not in the installer…it has to be configured post install or left at a very old default number that most likely makes no sense for you.
Likewise, if you are running SQL Server installed from a script that came with a 3rd party software product, you very likely have the “safest” possible settings in place.
Some of the most common things I see everywhere:
- Instant File Initialization not enabled
- Cost Threshold for Parallelism not set correctly
- MAXDOP set to 1 or ALL CPUs
- MAX Memory left at default
- MAX Memory left at default on a multi-instance server
- Default file locations for system databases, or all
- No maintenance processes
That is the short list.
Sales Pitch: I will give you the first hour (one server) free to check on your configuration. This involves me running a set of scripts, analyzing the results and sending back some recommendations. Yes, I want to implement them for you as well.
Alternatively, a lighter weight version of this is the Constant Care offering from Brent Ozar Unlimited. I can take his results and explain what they mean so that we can make intelligent decisions together. We are not affiliated with Brent Ozar Unlimited, other than as part of the SQL Community.
Let’s talk. Click HERE to set a meeting on my Calendar.
Thanks for reading!
Installing the ‘Ola Scripts’…quick and easy database maintenance
I was recently in a conversation about the best way to go about setting up maintenance (Backups, Integrity Checks, Indexes and stats) for a group of SQL Servers, with minimal hassle, and easy to deploy to new servers.
The factors that came into play on this were:
- Supportability
- Cost
- Ease of use
We discussed the following different options (I was not talking to a DBA, but a SQL Developer):
- SQL Server Maintenance Plans
- Ola Hallengren’s scripts
- 3rd party products from Red Gate, Minion, etc.
- Custom scripts
For this customer, in this environment, I decided to recommend Ola’s scripts. The primary drivers were ease of installation and the amazing free support from the hundreds (thousands?) of DBAs that know and love them. Myself included.
But I still have to prove my point to this client no matter what I recommend…so I made this video.
Enjoy:
Skip to 5:00 if you already have SQL Server installed…that first bit is just to show this on a clean instance 🙂
If you have any questions, feel free to comment on the video, or if you need specific help hit up #sqlhelp on Twitter.
Thanks for reading and watching!
Min and Max Server Memory in English
This one is for the new DBAs…
There is a lot of confusion on memory settings in SQL Server. Specifically Min and Max settings found in the Properties of an instance:
There are dozens, if not hundreds of blog postings that will go into beautiful detail about all of the intricacies of SQL Server memory…after all, SQL lives there! Thus, it is very important to have a basic understanding early in your SQL DBA career….especially if you are an accidental DBA.
In the screenshot above, I have set the Min and Max on my laptop to 2GB and 14GB. Its a 16GB laptop, so I left 2GB for the O/S…pretty basic stuff.
Max Server Memory is fairly obvious…when SQL Server reaches this point, it stops allocating and starts paging to disk as necessary.
This is pretty much the same as setting a throttle control on a car. Or a restrictor plate in NASCAR. Without it you can run the car engine all the way to the max if you want, but eventually the other systems are going to fail (Cooling, transmission, brakes, etc.). Same thing if you don’t leave memory for the O/S.
Min Server Memory seems to get the most bad information spread around. SQL Server does NOT automatically grab memory up to the Min setting when it starts. However, once it gets there, it doesn’t give back.
Back to the car analogy…if you start up and head off down the road at 20 mph, you are above the default (0), but not at the max (100+, depending on the car and the tires…). If you set the cruise control, you can accelerate up and down above 20, but you won’t go below that unless you hit the brakes.
So that’s it…by default, SQL installs at idle and full speed ahead. Its your job to turn on the cruise control and not redline the engine until it blows.
There are holes in the analogy if you dig deep enough, but this should give you a real-world base to work from as you grow in your skills.
Kevin3NF