Joey on SQL Server

5 SQL Server Components You Should Be Using

Microsoft's rapid-fire release cycle for SQL Server means it's easy for database admins to miss useful new features. Here are some recent additions that might otherwise fly under the radar.

• By Joey D'Antoni
• 09/04/2018

Modern versions of SQL Server get released almost every 18 months. This frequent release cycle means that it is easy to miss major enhancements that may not make marketing headlines, but offer both performance and productivity enhancements. Here are a handful of examples.

(NB: Microsoft has started including feature releases in cumulative updates, so it is always important to install those. Also, if you are on an older version of SQL Server Standard or Express, you should make every effort to move to SQL Server 2016 SP1, which brings several enterprise features to all editions of SQL Server. All of the features mentioned below are available in all editions of SQL Server 2016 SP1 and higher.)

1. Data Compression
Data compression was introduced as an Enterprise edition feature in SQL Server 2008, part of the great SQL Server 2016 SP1 patch that is included in all editions of SQL Server, including Azure SQL Database and Managed Instance.

Data compression compresses your data pages on disk, and they remain compressed within SQL Server's memory. Your data pages are still 8KB, but they will now have more rows of data on each page.

The biggest benefit of this is that the same queries that run against compressed tables and indexes require fewer I/Os to complete. The other benefit is that you get better utilization of your memory, as there are fewer data pages stored in the buffer pool.

The trade-off with compression is slightly higher CPU use, but it's nearly always worth it. The best part about compression is that you can enable independent software vendor (ISV) applications without impacting their source code.

2. Group Managed Service Accounts
This is not a SQL Server feature per se, but a Windows feature that SQL Server supports.

If you work in any regulated industry and have to change your service account passwords on a rolling basis, you know how painful it can be to take downtime to do service restarts -- even after you have automated the password change process. What group managed service accounts do is allow Active Directory to own the service account password process. You delegate permissions to a server to use the service account and how frequently you want to change the password, and let Active Directory take it from there.

Group managed service accounts were added to Windows Server 2012 and have support in SQL Server, starting with 2014. (If you are using Failover Cluster Instances or Always On availability groups, you will need SQL Server 2016.)

3. Query Store
This is a new feature in SQL Server 2016 and up. I don't think it's underutilized by the folks who are using those releases of SQL Server, but a lot of organizations are stuck on older versions of the database engine.

The query store collects execution plan and runtime information about workloads against a specific database, and highlights things like execution plan changes. This can be incredibly useful to a database administrator, especially when a user reports a performance problem that happened three days ago.

I'll share a personal story on this feature. A customer called me at 6 a.m. complaining that their CPU use had gone way up overnight. After some coffee and about five minutes of troubleshooting, the query store let me quickly identify the root cause of the issue: The query plan that had been in use for the application's main query suddenly started consuming way more CPU about six hours earlier. I was able to force the last good execution plan, and CPU utilization immediately plummeted.

4. Trace Flags
Microsoft develops SQL Server to support a wide-ranging array of workloads. Different customers have dramatically different demands of their databases, whether it's an e-commerce site pushing a million requests per second or a 20TB data warehouse that needs to deliver reporting with low latency. This means the software is optimized for the middle of most workloads.

Sometimes, you need to tell the database engine to do a certain task a certain way for the benefit of your workload. For that, Microsoft provides both documented and undocumented trace flags.

Over time, the SQL Server team has tried to integrate many of these behaviors into the engine by default. For example, two trace flags that I would universally recommend for SQL Server 2016 or higher are 1117 and 1118, which affect file growth and TempDB page allocations, respectively. They are built into the database engine from SQL Server 2016 and up. The other trace flag I always recommend is 3226, which turns off "backup successful" messages from your SQL Server error log files, making them easier to read.

Be careful with trace flags. While the ones I mentioned above have minimal side effects, others do require rather significant trade-offs to make a specific behavior happen.

5. Always Encrypted
For this article, I wanted to avoid features that required changes to application code, but I couldn't help myself with Always Encrypted.

Data breaches are far too common and hacking threats are everywhere. What's the easiest way to protect your data? By encrypting it and keeping the keys away from the people who have full access to the data: the database administrators and system admins.

Always Encrypted works by storing encryption keys in your application's client, storing only encrypted data in the database (and in transit) and, in some cases, allowing you to index and join on encrypted data. The only real changes that are required to your application are to use a modern SQL Server client and .NET 4.6 or higher.

Always Encrypted was released as an enterprise-only feature, but as of SQL Server 2016 SP1, it is available in all editions.

As can you might have gathered, most of these features are available in newer versions of SQL Server -- think of this as Microsoft's way of encouraging you to upgrade your applications. Microsoft's support model allows you to use compatibility levels to upgrade your applications to take advantage of some of the new features, while reducing the risk of change impacting your performance or application.