SQL Server 2017: Fast, faster, and the fastest database everywhere you need it
This post was authored by Bob Ward, Principal Architect, Database Systems Group
“I feel the need, the need for speed”. That is a quote from the character Maverick, played by Tom Cruise, in one of my favorite movies, Top Gun. It makes me think of one of the top reasons why someone chooses a database engine. It must be fast and perform to the needs of all types of workloads accessing data.
SQL Server 2017 sets the standard when it comes to speed and performance. Based on the incredible work of SQL Server 2016 (See the blog series It Just Runs Faster), SQL Server 2017 is fast: built-in, simple, and online. Maybe you caught my presentation at Microsoft Ignite where I demonstrated 1 million transactions per minute on my laptop using the popular tool HammerDB¹ by simply installing SQL Server out of the box with no configuration changes (with the HammerDB client and SQL Server on the same machine!)
Consider for a minute all the built-in capabilities that power the speed of SQL Server. From a SQLOS scheduling engine that minimizes OS context switches to read-ahead scanning to automatic scaling as you add NUMA and CPUs. And we parallelize everything! From queries to indexes to statistics to backups to recovery to background threads like LogWriter. We partition and parallelize our engine to scale from your laptop to the biggest servers in the world.
Like the enhancements we made as described in It Just Runs Faster, in SQL Server 2016, we are always looking to tune our engine for speed, all based on customer experiences. Take, for example, indirect checkpoint, which is designed to provide a more predictable recovery time for a database. We boosted scalability of this feature based on customer feedback. We also made scalability improvements for parallel scanning and consistency check performance. No knobs required. Just built-in for speed.
One of the coolest performance aspects to built-in speed is online operations. We know you need to perform other maintenance tasks than just run queries, but keep your application up and running, so we support online backups, consistency checks, and index rebuilds. SQL Server 2017 enhances this functionality with resumable online index builds allowing you to pause an index build and resume it at any time (even after a failure).
SQL Server 2017 is faster than you think. SQL Server 2017 was designed from the beginning to run fast on popular Linux distributions such as Red Hat Enterprise Linux, SUSE Linux Enterprise, and Ubuntu whether that is on your server or a Docker Container. Don’t believe it? Check out our world record 1TB TPC-H benchmark result (non-clustered) for SQL Server on Red Hat Enterprise Linux. Even though this is our first release on Linux, we know how to configure and run SQL Server on Linux for maximum speed. Read our best practices guide for performance settings on Linux in our documentation. We know it performs well because our customers tell us. Read the amazing story of dv01 and how SQL Server on Linux exceeded their performance expectations as they migrated from PostgreSQL
One of the key technologies to achieve a result like this is columnstore indexes. This is one of the most powerful features of SQL Server for high-speed analytic queries and large databases. Columnstore indexes boost performance by organizing columnar data in a new way than traditional indexes, compressing data to reduce memory and disk footprint, filtering scans automatically through rowgroup elimination and processing queries in batches. SQL Server runs at warp speed for data warehouses and columnstore is the fuel. At Microsoft Ignite, I demonstrated how columnstore indexes can make PowerBI with Direct Query against SQL Server faster handling the self-service, ad-hoc nature of PowerBI queries.
SQL Server also excels at transaction processing, the heart of many top enterprise workloads. Got RAM? Not only does columnstore use in-memory technologies to achieve speed, but our In-Memory OLTP feature focuses on optimized access to memory-optimized tables. This feature is named OLTP, but it can be so much more. ETL staging tables, IoT workloads, table types (no more tempdb!), and “caching” tables. One of our customers was able to get a throughput of 1.2M batch requests/sec using SCHEMA_ONLY memory-optimized tables. To really boost transaction processing, also consider using SQL Server’s support for Persistent Memory (NVDIMM-N) and our optimization for transaction log (get ready for WRITELOG waits = 0!) performance. SQL Server 2017 supports any Persistent Memory technology supported on Windows Server 2016 and later releases.
Many customers I talk to have great performance when they first deploy SQL Server and their application. Keeping SQL Server fast and tuned is more of the challenge. SQL Server 2017 comes with features to keep you fast and tuned automatically and adaptively. Our Query Processing engine has all types of capabilities to create and build query plans to maximize the performance of your queries. We have created a new feature family in SQL Server 2017 to make it smarter, called Adaptive Query Processing. Imagine running a query that is not quite the speed you expect because of insufficient memory grants (which is a thorn in the side of many SQL Server users, as it can lead to a spill to tempdb). With Adaptive Query Processing, future executions of this query will have a corrected calculated memory grant avoiding the spill, all without requiring a recompilation of the query plan. Adaptive Query Processing handles other scenarios such as adaptive joins and interleaved execution of Table Valued Functions.
Another way to keep you tuned is the amazing feature we added in SQL Server 2016 called Query Store. Query Store provides built-in capabilities to track and analyze query performance all stored in your database. For SQL Server 2017, we made tuning adjustments to Query Store to make it more efficient based on learnings in our Azure SQL Database service where Query Store is enabled for millions of databases. We added wait statistics so now you have an end-to-end picture of query performance. Perhaps though the most compelling enhancement in SQL Server 2017 is Automatic Tuning. Parameter Sniffing got you down? Automatic Tuning uses Query Store to detect query plan regressions and automatically forces a previous plan that used to run fast. What I love about this feature is that even if you don’t have it turned on, you can see recommendations it has detected about plan regressions. Then you can either manually force plans that you feel have regressed or turn on the feature to have SQL Server do it for you.
SQL Server 2017 is the fastest database everywhere you need it. Whether it is your laptop, in your private cloud, or in our Azure public cloud infrastructure. Whether it is running on Linux, Windows, or Docker Containers, we have the speed to power any workload your application needs.
As I mentioned above, back in April, we announced our world record TPC-H 1TB data warehousing workload (non-clustered) for SQL Server 2017 running on a HPE ProLiant DL380 Gen9 using RedHat Enterprise Linux².
Perhaps you missed the announcement in June of 2017, of a new world record TPC-E benchmark result³ on SQL Server 2017 on Windows Server 2016 running on a Lenovo ThinkSystem SR650 continuing to demonstrate our leadership in database performance. This benchmark running on a 2 socket system using Intel’s Xeon Scalable Processors has set a new standard for price and performance, becoming the first TPC-E benchmark result ever to be under $100/tpsE.
We continued to show our proven speed for analytics by announcing in July of 2017 a new TPC-H 10TB (non-clustered) world record benchmark result4 of 1,336,109 QppH on Windows Server 2016 using a Lenovo ThinkSystem SR950 system with 6TB RAM and 224 logical CPUs.
While benchmarks can show the true speed of SQL Server, we believe it can perform well with your workload and maximize the computing power of your server. Perhaps you caught the session at Ignite where my colleague Travis Wright showed how we can scan a 180 Billion row table (from a 30TB database) in our labs in under 20 seconds powering 480 CPUs to 100% capacity. And if you don’t believe SQL Server is deployed in some of the biggest installations and servers in the world, I recently polled some of our field engineers, SQL Customer Advisor Team, and MVPs asking them for their largest SQL Server deployments. Over 30 people responded, and the average footprint of these installations was 3TB+ RAM on machines with 128 physical cores. Keep in mind that SQL Server on can theoretically scale to 24TB RAM on Windows and 64TB on Linux. And it supports the maximum CPUs of those systems (64 sockets with unlimited cores on Windows and 5120 logical CPUs on Linux). Look for more practical and fun demonstrations of the speed of SQL Server in the future.
It could be that you are consolidating your deployments and want to run SQL Server using Azure Virtual Machine, but not sure if the capacity is there for your performance needs. Consider that Azure Virtual machine has the new M-Series, which supports up to 128 vCPUs, 2TB RAM, and 64 Data Disks with a capacity of 160,000 IOPS. It could be that in your environment you want to scale out your read workload with Availability Group secondary replicas but don’t want to invest in Failover Clustering. SQL Server 2017 introduces the capability of read-scale availability groups without clustering supported both on Windows and Linux. Two other very nice performance features new to SQL Server 2017 are SSIS Scale Out, for those with data loading needs, and native scoring, which integrates machine learning algorithms into the SQL Server engine for maximum performance.
SQL Server 2017 brings to the database market a unique set of features and speed. A database engine that is fast, built-in with the power to scale, and even faster when taking advantage of technologies like columnstore Indexes and In-Memory OLTP. An engine that provides automation and adapts to keep you fast and tuned. And the fastest database everywhere you need it.
Stay tuned for future blog posts providing more details on SQL Server performance both on this blog and the bobsql blog.
Resources
- ¹HammerDB is an open-source performance tool. The demo and results shown are not official TPC benchmark results and all testing was done with a workload derived from the TPC-C benchmark.
- ²1TB TPC-H benchmark result: Hewlett Packard Enterprise; TPC, as of September 27th, 2017.
- ³TPC-E benchmark result: Lenovo Press; TPC as of September 27, 2017.
- 410TB TPC-H benchmark result: Lenovo Press; TPC, as of September 27, 2017.
