segunda-feira, 23 de outubro de 2017

[From Technet] Ask the team that built SQL Server 2017 anything on October 25, 2017

Hi! We’re Travis Wright and Tobias Ternstrom from the Microsoft SQL Server engineering team and we along with other members of the team are hosting a Reddit Ask Me Anything session on /r/Database, Wednesday, October 25, 2017, from 10:00 am to 11:30am PDT.

Why are we doing an AMA?

SQL Server 2017 was made generally available on October 2, on Windows, Linux and Docker containers. With this release, customers have an even wider choice of development languages, data types, and operating systems. We’re excited to connect with you on Reddit to tell you more about our experiences bringing it to market and answering your questions!

We’re also getting ready to join thousands of data professionals in Seattle from October 31 – November 3rd at the PASS Summit 2017. We look forward to this gathering every year. If you’re attending or planning to watch online, this AMA is a great time to catch up beforehand.

Join us! We’re looking forward to having a conversation with you.

from SQL Server Blog

terça-feira, 10 de outubro de 2017

[From Technet] What’s new in SQL Server Management Studio 17.3

This post was authored by Alan Yu, Program Manager, Microsoft SQL Server

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.3!

Download SSMS 17.3 and review the Release Notes to get started.

SSMS 17.3 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.3 comes with two new exciting features:

  • Import Flat File Wizard
  • XEvent Profiler

Import Flat File Wizard

The Import Flat File Wizard streamlines the import experience of flat files (.csv, .txt) with an intelligent framework, requiring minimal user intervention or specialized domain knowledge.

A common task for a user is to import a flat file to a SQL table. The existing SSMS Import Data wizard provides a much broader set of import capabilities and configuration for defining SQL Server Integration Services packages. This can be useful in many cases, but for the simple task of importing a flat file into a table, a more streamlined experience is desirable. There is a need for a tool or technology that simplifies the import experience by reducing the amount of user configuration required to properly import complex flat file structures.

New import technology

To address this need, we are using the Microsoft Program Synthesis using Examples (PROSE) SDK. This SDK allows us to take raw semi-structured data and identify patterns in it to perform predictive file splitting, helping the wizard “learn” the structure of a flat file to infer column names, types, delimiters, and more.

This simplifies the import flat file experience by requiring the user to only provide an input file and unique file name, and the wizard takes care of the rest.

Try it now

Try the Import Flat File Wizard yourself by following a step-by-step tutorial.

XEvent Profiler

The XEvent Profiler is an easy to launch and customizable SSMS feature that quickly displays a live target view of extended events.

In SSMS, the existing extended events functionality already provides a flexible definition of sessions and views, but lacks the “quick view” capability of SQL Profiler. XEvent Profiler generates that view just as fast as SQL Profiler, while also allowing for view customization that can be shared with other SSMS users as a *.viewsettings file.

The diagram below illustrates how quick it is to view extended events in your database.

Try it now

Try the XEvent Profiler yourself by following a step-by-step tutorial.

We challenge you to try out this new feature over SQL Server Profiler. If there are any gaps that require you to use SQL Server Profiler over XEvent Profiler, feel free to comment on this blog. We hope to fill those gaps in future releases.

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases. Please contact us using our form, or tweet to our team’s engineering manager @sqltoolsguy.


from SQL Server Blog

[From Technet] Webinar: Get enterprise-grade features at open-source prices with SQL Server 2017 on Linux

IT pros now have the flexibility of running workloads across Windows, Linux, or a combination of both. By taking advantage of SQL Server 2017 on Linux, you’ll get enterprise-grade features at open-source pricing — all backed by Microsoft support and security. It’s never been easier or more cost effective to make the move.

SQL Server 2017 combines the best of databases and analytics into one product. New enhancements include real-time intelligence due to faster transactions with In-Memory OLTP, new T-SQL enhancements for in-memory tables and natively compiled stored procedures, ColumnStore performance improvements, and support for LOBS.

Join our speakers Debbi Lyons, Sr. Product Marketing Manager and Travis Wright, Principal Program Manager for a webinar on how SQL Server 2017 on Linux provides intelligence on a trusted, industry-leading platform while enabling innovation for all your data.

In this live webinar and Q&A, you’ll learn:

  • What’s new with SQL Server 2017 on Linux and the benefits of building your next Linux project with SQL Server.
  • How to leverage Microsoft’s offer to get a discounted subscription rate.
  • Quick and easy ways to get started with SQL Server 2017, and our suite of free tools available.

Join us Thursday, October 12, 2017, at 9:00 AM Pacific Time (UTC-7). Register now for this webinar on how your data ecosystem can benefit from SQL Server 2017 on Linux.

from SQL Server Blog

quinta-feira, 5 de outubro de 2017

[From Technet] SQL Server 2012 Service Pack 4 is now available

The SQL Server team is excited to bring you the final service pack release for SQL Server 2012. The service pack is now available for download on the Microsoft Download Center and will be coming soon to Visual Studio Subscriptions, MBS/Partner Source, and VLSC. As part of our commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2012 deployments.

SQL Server 2012 Service Pack 4 (SP4) contains a roll-up of released hotfixes as well as more than twenty improvements centered around performance, scalability, and diagnostics based on the feedback from customers and SQL community. These improvements enable SQL Server 2012 to perform faster and scale out of the box on modern hardware design. It also showcases the SQL Server product team’s commitment to providing continued value to in-market releases.

SQL Server 2012 Service Pack 4 will include:

  • All fixes and Cumulative Updates (CUs) for SQL Server 2012 up to and including SQL Server 2012 SP3 CU10.
  • Scalability and performance improvements for SQL Server.
  • Additional monitoring capabilities through enhancements in DMV, Extended Events and Query Plans and the ability to clone the database including statistics with DBCC CLONEDATABASE.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some of the improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1.

Find more information on SQL Server 2012 SP4 by reading the detailed Engineering team blog post and the release notes. Download today by visiting the links below:

Customers running SQL Server 2012 can extend their product support lifecycle by six years with Premium Assurance. Learn more about this option and read the Premium Assurance datasheet to explore this opportunity to stay compliant with minimal disruption.

from SQL Server Blog

quarta-feira, 4 de outubro de 2017

[From Technet] Taking your data to the cloud is easier than you thought

Data management is shifting away from on-premises towards the many benefits of the cloud. Data is the fuel that energizes businesses in today’s marketplace, and the cloud amplifies this energy. The question then arises — what’s the simplest way to take your data to the cloud?

In this free webinar, our Azure experts share how and why migration to the cloud is now easier, faster, and more cost-effective. Discover how to:

  • Realize cost savings by preserving your investment in already purchased on-premises licenses.
  • Save four times the investment you made on cores, leverage cloud flexibility for ROI, and uncover other cost savings in your environment.
  • Migrate easily with the new Azure SQL Database Managed Instance, shifting to the cloud without having to rearchitect apps.
  • Prepare for your migration with four important steps.

Tune in to our live webinar, Modernize to the cloud in four quick, easy, and cost-effective steps, on October 5, 2017, at 10:00 AM Pacific Time (UTC-7) to learn how modernizing your data with the cloud is within your reach. You’ll also get the chance to have your questions answered in real time by our team. Register today.

from SQL Server Blog

segunda-feira, 2 de outubro de 2017

[From Technet] Analysis Services Innovations in SQL Server 2017

This post was authored by Christian Wade, Senior Program Manager, Microsoft

Microsoft SQL Server Analysis Services (SSAS) enables IT professionals to build semantic models over large, integrated managed datasets for end-user consumption. SSAS runs on Windows and is part of the larger suite of SQL Server’s BI offerings. SSAS 2017 brings a host of new connectivity and modeling features for comprehensive, enterprise-scale analytic solutions delivering actionable insights.

SSAS 2017 introduces the 1400 compatibility level. Here are just some highlights of the new features:

  • New infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This enables support for a range of additional data sources, and data transformation and mashup capabilities.
  • Support for BI tools such as Microsoft Excel enables drill-down to detailed data from an aggregated report. For example, when end-users view total sales for a region and month, they can view the associated order details.
  • Object-level security to secure table and column names in addition to the data within them.
  • Enhanced support for ragged hierarchies such as organizational charts and chart of accounts.
  • Various other improvements for performance, monitoring, and consistency with the Power BI modeling experience.

To benefit from the new features for models at the 1400 compatibility level, you’ll need to download and install SQL Server Data Tools (SSDT) 17.0 from documentation.

In SSDT, you can select the new 1400 compatibility level when creating new tabular model projects. Alternatively, you can upgrade an existing tabular model by selecting the Model.bim file in Solution Explorer and setting the Compatibility Level to 1400 in the Properties window. Models at the 1400 compatibility level cannot be downgraded to lower compatibility levels.

New infrastructure for data connectivity

1400 models introduce a new infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This is based on the similar Power Query functionality in Power BI Desktop and Microsoft Excel 2016. For more information, please refer to the Analysis Services Team blog.

Detail Rows

A much-requested feature for tabular models is the ability to define a custom row set contributing to a measure value. Multidimensional models achieve this by using drillthrough and rowset actions. This allows end-users to view information in more detail than the aggregated level.

For example, the following PivotTable shows Internet Total Sales by year from the Adventure Works sample tabular model. Users can right-click the cell for 2010 and then select the Show Details menu option to view the detail rows.

By default, all the columns in the Internet Sales table are displayed. This behavior is often not meaningful for the user because too many columns may be shown, and the table may not have the necessary columns to show useful information such as customer name and order information.

Detail Rows Expression Property for Measures

1400 models introduce the Detail Rows Expression property for measures. It allows the modeler to customize the columns and rows returned to the end user. The following example uses the DAX Editor in SSDT to define the columns to be returned for the Internet Total Sales measure.

With the property defined and the model deployed, the custom row set is returned when the user selects Show Details. It automatically honors the filter context of the cell that was selected. In this example, only the rows for 2010 value are displayed.

Further information on Detail Rows is available our blog post, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services.

Object-Level Security

Roles in tabular models support a granular list of permissions, and row-level filters to help protect sensitive data. 1400 models introduce table- and column-level security allowing sensitive table and column names to be protected in addition to the data within them. Collectively these features are referred to as object-level security (OLS).

The following example uses the Role Manager in SSDT to protect sensitive employee information. Unauthorized users cannot access the selected columns using client tools like Power BI and Excel Pivot Tables. Additionally, such users cannot query the columns using DAX or MDX, or measures that refer to them.

Further information on OLS is available in a recent post, titled, What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.

Ragged Hierarchies

Tabular models with previous compatibility levels can be used to model parent-child hierarchies. Hierarchies with a differing number of levels are referred to as ragged hierarchies. An example of a ragged hierarchy is an organizational chart. By default, ragged hierarchies are displayed with blanks for levels below the lowest child. This can look untidy to users, as shown by this organizational chart in Adventure Works:

1400 models introduce the Hide Members property to correct this. Simply set the Hide Members property to Hide blank members.

With the property set and the model deployed, the more presentable version of the hierarchy is displayed.

Other Features

Various other features such as the following are introduced with the 1400 compatibility level. For more information, please refer to the Analysis Services Team blogs, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services and What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.

  • Transaction-performance improvements for a more responsive developer experience.
  • Dynamic Management View improvements enabling dependency analysis and reporting.
  • Date relationships to easily create relationships to date dimensions based on date columns.
  • DAX enhancements to make DAX more accessible and powerful. These include the IN operator, and table and row constructors.

from SQL Server Blog

[From Technet] SQL Server 2017 on Windows Linux and Docker is now generally available

This post was authored by Travis Wright, Principal Program Manager, SQL Server Engineering

Today, October 2nd, we are excited to announce that SQL Server 2017 is generally available for purchase and download! The new release is available right now for evaluation or purchase through the Microsoft Store, and will be available to Volume Licensing customers later today. Customers now have the flexibility for the first time ever to run industry-leading SQL Server on their choice of Linux, Docker Enterprise Edition-certified containers and, of course, Windows Server. It’s a stride forward for our modern and hybrid data platform across on-premises and cloud.

In the 18 months since announcing our intent to bring SQL Server to Linux, we’ve been focused on making SQL Server perform and scale to the industry-leading levels customers expect from SQL Server, making SQL Server feel familiar yet native to Linux, and ensuring compatibility between SQL Server on Windows and Linux. With all the enterprise database features you rely on, from Active Directory authentication, to encryption, to Always On availability groups, to record-breaking performance, SQL Server is at parity on Windows and Linux. We have also brought SQL Server Integration Services to Linux so that you can perform data integration just like on Windows. SQL Server 2017 supports Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu.

There are a number of new features for SQL Server that we think make this the best release ever. Here are just a few:

  • Container support seamlessly facilitates your development and DevOps scenarios by enabling you to quickly spin up SQL Server containers and get rid of them when you are finished. SQL Server supports Docker Enterprise Edition, Kubernetes and OpenShift container platforms.
  • AI with R and Python analytics enables you to build intelligent apps using scalable, GPU-accelerated, parallelized R and now Python analytics running in the database.
  • Graph data analysis will enable customers to use graph data storage and query language extensions for graph-native query syntax in order to discover new kinds of relationships in highly interconnected data.
  • Adaptive Query Processing is a new family of features in SQL Server that bring intelligence to database performance. For example, Adaptive Memory Grants in SQL Server track and learn from how much memory is used by a given query to right-size memory grants.
  • Automatic Plan Correction ensures continuous performance by finding and fixing performance regressions.

Above and beyond these top-line features, there are more enhancements that you haven’t heard as much about, but we hope will truly delight you:

  • Resumable online index rebuild lets you stop and start index maintenance. This gives you the ability to optimize index performance by re-indexing more frequently – without having to wait for a long maintenance window. It also means you can pick up right where you left off in the event of a disruption to database service.
  • LOB compression in columnstore indexes. Previously, it was difficult to include data which contained LOBs in a columnstore index due to size. Now those LOBs can be compressed, making LOBs easier to work with and broadening the applicability of the columnstore feature.
  • Clusterless availability groups enable you to scale out reads by building an Always On availability group without having to use an underlying cluster.
  • Continued improvement to key performance features such as columnstore, in-memory OLTP, and the query optimizer to drive new record-setting performance. We’ll share some even more exciting perf and scale numbers soon!
  • Native scoring in T-SQL lets you score operational data using advanced analytics in near real-time because you don’t have to load the Machine Learning libraries to access your model.
  • SQL Server Integration Services (SSIS) scale-out enables you to speed package execution performance by distributing execution to multiple machines. These packages are executed in parallel, in a scale-out mode.
  • Many enhancements were made to SQL Server Analysis Services including:
    • Modern “get data” experience with a number of new connectors like Oracle, MySQL, Sybase, Teradata, and more to come. New transformations enable mashing up of the data being ingested into tabular models.
    • Object-level security for tables and columns.
    • Detail rows and ragged hierarchies support, enabling additional drill-down capabilities for your tabular models.
  • Enhancements were made to SQL Server Reporting Services as well, including:
    • Lightweight installer with zero impact on your SQL Server databases or other SQL Server features.
    • REST API for programmatic access to reports, KPIs, data sources, and more.
    • Report comments, enabling users to engage in discussion about reports.

In addition to the ability to upgrade existing SQL Server to 2017, there are a few more benefits to renewing your software assurance:

  • Machine Learning Server for Hadoop, formerly R Server, brings R and Python based, scalable analytics to Hadoop and Spark environments, and it is now available to SQL Server Enterprise edition customers as a Software Assurance benefit.
  • SQL Server Enterprise Edition Software Assurance benefits also enable you to run Power BI Report Server. Power BI Report Server enables self-service BI and enterprise reporting, all in one solution by allowing you to manage your SQL Server Reporting Services (SSRS) reports alongside your Power BI reports. Power BI Report Server is also included with the purchase of Power BI Premium.
  • Lastly, but importantly, we are also modernizing how we service SQL Server. Please see our release management blog for all the details on what to expect for servicing SQL Server 2017 and beyond.

Thanks for joining us on this journey to SQL Server 2017. We hope you love it! Going forward, we will continue to invest in our cloud-first development model, to ensure that the pace of innovation stays fast, and that we can bring you even more and improved SQL Server features soon.

Here are a few links to get started:

from SQL Server Blog

sexta-feira, 29 de setembro de 2017

[From Technet] View on-demand: Microsoft Data Platform – SQL Server 2017 and Azure Data Services

Did we miss you at Microsoft Ignite in Orlando this year? That’s OK! You can still stream the Data General Session, Microsoft Data Platform – SQL Server 2017 and Azure Data Services on-demand.

In this session Rohan Kumar, General Manager, Data Systems Group, discusses all the latest news — from upcoming SQL Server 2017 general availability, to the new Azure Hybrid Benefit for SQL Server, to serverless computing with Azure Cosmos DB and Azure Functions. Hear how to take your SQL Server Integration Services to the cloud with Azure Data Factory’s new hybrid functionality, plus learn about the latest performance and scale enhancements for Azure Data Services. This 75-minute session is packed with demonstrations and customer testimonials from the likes of ASOS, Caradigm, and dv01.

Join us now:

from SQL Server Blog

quinta-feira, 28 de setembro de 2017

[From Technet] Enhancing query performance with Adaptive Query Processing in SQL Server 2017

This post was authored by Joe Sack, Principal Program Manager, Data Systems Group, Microsoft

SQL Server 2017 and Azure SQL Database introduce a new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. For this first version of this adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions.

Legacy optimization behavior

During optimization, the cardinality estimation process is responsible for estimating the number of rows processed at each step in an execution plan. Cardinality estimation uses a combination of statistical techniques and assumptions and when estimates are accurate (enough), we make informed decisions around the order of operations and physical algorithm selection.

When estimates are inaccurate, the query optimizer may make poor decisions regarding algorithm selection and order of operations. The cost of incorrect estimates can result in:

  • Slow query response time due to inefficient plans
  • Excessive resource utilization (CPU, Memory, IO)
  • Spills to disk due to insufficient memory grant requests
  • Reduced throughput and concurrency due to excessive memory grant sizes

There are several reasons why incorrect cardinality estimates occur, from missing or stale statistics to inadequate statistics sample rates, bad parameter sniffing scenarios or cases where SQL Server does not have visibility to cardinality estimates and must make fixed-estimate guesses (e.g. multi-statement table valued functions, table variables, XQuery references).

Prior to SQL Server 2017, if we make poor assumptions due to bad cardinality estimates, we do not change our query plan execution strategy during execution.

Adapting to your workloads

The new adaptive query processing feature family in SQL Server 2017 and Azure SQL Database introduces three new techniques for adapting to your application workload characteristics.

Batch Mode Adaptive Joins

The query optimizer has three physical operator options for processing a join operation: nested loop, merge, and hash match. Which algorithm is appropriate or necessary, is highly dependent on the cardinality estimates of the join inputs. If we have inaccurate input cardinality estimates, this can result in the selection of an inappropriate join algorithm.

The new batch mode adaptive join feature enables the choice of a hash join or nested loop join method to be deferred until after the first input has been scanned. The adaptive join operator defines a threshold that is used to decide when to switch to a nested loop plan. Your plan can then dynamically switch to a better join strategy during execution using a single cached plan.

Batch Mode Memory Grant Feedback

A query’s execution plan in SQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. When memory grant sizes are inadequate or excessive, performance suffers. Excessive grants result in wasted memory and reduced concurrency. Insufficient memory grants cause expensive spills to disk.

Addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. When an identical query statement is executed, the query uses the revised memory grant size, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk.

Inadequate grant scenario:


Excessive grant scenario:

Interleaved execution for multi-statement table valued functions

Interleaved execution for multi-statement table valued functions (MSTVFs) changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. During optimization, if we encounter a candidate for interleaved execution, which for SQL Server 2017 is currently multi-statement table valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations.

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and an estimate of “1” for earlier versions.

Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with multi-statement table valued functions. With interleaved execution, we will use the actual row counts from the MSTVF to make plan optimizations downstream from the MSTVF references. The result is a better-informed plan based on actual workload characteristics.

Enabling adaptive query processing

Adaptive query processing is enabled in SQL Server 2017 and Azure SQL Database under compatibility level 140. To learn more about this compatibility level, see Public Preview of Compatibility Level 140 for Azure SQL Database.

Learning more about adaptive query processing

For a comprehensive overview of all three features, see the Books Online topic Adaptive query processing in SQL databases.

To see the adaptive query processing feature family in-action, see the video interview with Scott Klein on Channel 9:

Just getting started

We have several other opportunities in the query processing space to further add and enhance workload-adaptation mechanisms and strategies. Continued investment in the adaptive query processing feature family will help support and meet the requirements of a wide range of workload characteristics, from high-throughput transaction processing workloads to petabyte-scale relational data warehousing databases. Have feedback related to the query processor or other areas in SQL Server? If so, please submit feedback via the Microsoft Connect website.


from SQL Server Blog

quarta-feira, 27 de setembro de 2017

[From Technet] 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 resultof 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.


  • ¹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 PressTPC as of September 27, 2017.
  • 410TB TPC-H benchmark result: Lenovo Press; TPC, as of September 27, 2017.

from SQL Server Blog