Your SQL Servers Are Misconfigured...
... and it’s probably NOT your fault.
I was talking to a CIO last week about her SQL Server performance, which was horrible. It is a very current server, with plenty of RAM, CPU and SSDs. SQL 2014 Enterprise.
I asked her who did the install. “The 3rd party software vendor’s team.”
Common scenario. Too common. Like “90% of the servers I see” level of common.
The issue boiled down to the installation being done with all Microsoft out-of-the-box defaults, and zero post-install configuration. This is pretty standard when non-DBAs do SQL Server installs. Nothing wrong with that, but you may find you are robbing yourself of a LOT of free performance.
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.
There are a number 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.
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? Or “Optimize for Ad Hoc Workloads”? Or if they should be tweaked?
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:
· 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, not in order of priority.
Back to the CIO…we made some very basic changes, restarted the SQL Instance and BAM! Massive performance gains without changing hardware, upgrading SQL, etc. Just a few hours of my time.
If you don’t have a Database Admin and your SQL Server is struggling to keep up, this may be the issue. It may not, but it is worth looking into. Its even a good idea from time-to-time to have someone else come in and run a Health Check. Even the best DBA can miss things.
.NET Developer en EO Soluciones
6 年Uuuuh yeah!!
Good points Kevin - couldn't agree more about the gap between Sysadmins and application Developers. Unfortunately this happens very frequently at organizations - rarely either role has the expertise to tune SQL configuration settings or understand how queries work and improve their performance.?
SQL Dev/ Data Engineer / Data Analyst / Data Integration
6 年A great post. Those config "tricks" that a person with a experience as DBA know, can make a great impact on performance aspect. I always run a check list over the new databases that of which I'm in charge, from Server config up to the state of the indexes. Some times there are good config, some time not, some times the problem is related to programming or business rules, that is another subject more complicated to solved in my experience
Sr. Manager Data & Analytics @ Baker Hughes | Data Analytics , ESG Reporting and Platform Lead
6 年Absolutely Correct Kevin. Thanks for sharing your experience
President | Vulnerability Management, Enterprise Resilience and Corporate Certification
6 年It was great reading this string and I have gained an insight that may support my request to have a professional SQL DBA review our environment.