How SQL configuration can save you money

Why do database servers, like cars, require frequent servicing and health checks? Databases servers, as the name would suggest, host databases and the way they operate depends on those databases. Just like cars, two exactly identical vehicles could be used in two different ways and do different mileage on different roads, and their servicing will be different.

You don’t need more CPU

SQL Server is licensed per CPU core, with the Enterprise edition costing approx. £13000 for two cores with a minimum of 4-core license per each physical processor. This means that if you want to make your SQL Server go faster by adding more CPU, this may cost you £26000 or more.

  • Many clients who believe they need more CPUs run their OS in Balanced power plans meaning SQL Server can’t use the CPU to its full potential. A simple change to a High-Performance power plan often solves many problems.
  • Incorrect MAX DOP and COST THRESHOLD lead to SQL Server consuming more CPU per query than it should, leaving all other queries in a queue, giving an overall impression of a “server being slow.”We take the pain out of SQL Server Management.
    All rights reserved, Sargable Group, sargable.com Page 4 of 8
  • An out-of-date BIOS can cause CPU problems. HP had a BIOS bug that made Windows only use half of the available cores (HPE c04650594)
  • It is common to misconfigure the hypervisor and to create more sockets than cores. SQL Server Standard edition is limited to 24 cores or 4 sockets. If you create 8 sockets with 1 core each, SQL Server will only use 50% of the CPU (but you are likely still licensing all 8 cores)

You don’t need more servers

SQL Server is easy to set up. A typical, Windows-based installer with a friendly GUI and a few clicks to install a brand-new shiny SQL Server. Microsoft want your money, so they make it easy to install and use SQL Server, and as we mentioned before, the defaults are far from best practice.

  • Vendors often force our clients to provision brand-new servers for their new applications because they claim their databases cannot co-exist with any other database. SQL Server has been designed to host hundreds of databases, and there are built-in features that make this happen:
    • Resource governors – databases can be limited in how much resources they can consume.
    • Compatibility levels – databases can be set to behave as they run on an older version of SQL Server, so you can ignore vendors who say their application isn’t compatible with the latest version of SQL Server.
    • Server roles – appropriate security roles can isolate access, so users aren’t even aware of other databases on an instance.
    • Listeners – we can create a dedicated listener with its own DNS, so it looks like we’re connecting to a separate instance.
  • Each production server will come with a Disaster Recovery, Pre-Production and Development instance. Each production server should also be clustered to help with the patching strategy. This means that a single server is suddenly five servers in total, which brings a lot of management and maintenance overhead that often results in hiring additional DBAs at £80000 per year.
  • Even if you have a strict change control in place, the more servers you have, the more fragmented your environment becomes, especially when you run third-party applications and vendors have their own requirements for configuration (which they shouldn’t have). It suddenly becomes challenging to stay on top of it all, and you start looking at spending £15000 per year on monitoring software that no one understands.

Don’t buy Standard Edition

SQL Server Standard Edition is cheaper than Enterprise, but it is a different product. The SQL Server Enterprise Engine has features that make it run faster than the Standard Editions and features that provide high availability and disaster recovery.
Instead of deploying multiple Standard editions (and we may have already established less is better when it comes to database servers), deploying less or even one large Enterprise cluster is often more cost-efficient.

You may not need a new storage

SQL Server relies heavily on memory and storage. SQL Server will try and keep most or all the data in memory if possible. If you haven’t got enough memory (that would be at least the size of all databases), it will have to query the storage to get the data. Even with enough memory, it will still require fast storage for writes and TempDB operations. Poorly configured storage, with incorrect block size and disk layout (yes, this is still important), will contribute to high IO waits and manifest as “server being slow”. Look at setting the storage correctly before spending more money on faster storage.

The storage speed also influences the recovery time objective (RTO), so the slower the storage, the longer it may take to recover from a disaster.

Make sure SQL Server memory is set and utilised correctly to offload storage.

Get your indexes right

Indexes don’t just make queries run faster. Up-to-date statistics allow SQL Server to build better execution plans and correct indexes to reduce CPU and storage utilisation. If you are struggling with SQL Server performance, the first thing to look at are indexes and not adding more CPU.

Most clients who come to us for help have no index or an utterly wrong index strategy. You know, creating useless indexes could be worse than no indexes. Why? Because every index must be maintained, every time you insert data into a table with an index, SQL Server has to update that index. If those indexes do not cover your queries, they are not used for reading but to slow down inserts and updates.

Keep SA access in a circle of trust

Some applications dare to change server settings without telling anyone, and not only does it impact other databases and applications on this instance, but it also upsets your change control. I would be cautious with any vendors insisting on SA access.

Databases running CLR code can create security risks (there are ransomware attacks that utilise CLR to break into your SQL Server and from there to the entire environment). What do you know about CLR? How much could a data breach or a ransomware attack cost you?

If it isn’t broken, fix it – yes, fix it

The “if it ain broken, don’t fix it” and the “we have been doing this for 20 years, so it must be right” are phrases often used by accidental DBAs – sysadmins, developers and other people looking after SQL Server in businesses that have no dedicated DBAs. But with the approach, the wheel would have never been invented. Often “not broken” does not mean right and certainly does not mean as efficient as it could be.

System databases are just that

Stop storing user objects in system databases. They are not part of High Availability, and whatever you create on one instance will never replicate to the other instance. This may stop the failovers and cause an outage.

Finally

Your business heavily relies on your databases. A poorly configured Microsoft SQL Server puts performance, security, reliability, and data at risk. Please make sure yours is as optimised as it can be. If you need help, we offer health checks.

Help us grow, please share this content!

Author

Posted by
Marcin Gminski
November 11, 2022

Let us help you. Get in touch.

Help us grow, please share this content!