We recently convened a Twitter chat with a panel of SQL Server and database performance experts to discuss the myths and realities of SQL Server database performance (read Part 1 here), and recommendations for improving performance (read Part 2 here), and features of SQL Server 2014 that can help improve performance.
To answer questions about new features in SQL Server 2014 aimed at performance, we convened a Twitter chat with a panel of SQL Server and database performance experts, including: Tim Chapman (SQL Server Dedicated Support Engineer (PFE) at Microsoft, Microsoft Certified Master), Joey D’ Antoni (Solutions Architect and Anexinet), Chris Bell (Founder, WaterOx Consulting), Robert Davis (SQL Server 2008 Certified Master and Principal DBA at OuterWall), Steve Karam (Oracle ACE and Technical Manager at Delphix), Karen Lopez (SQL Server MVP and Sr. Project Manager / Architect at Infoadvisors), and Thomas LaRock (SQL Server MCM and MVP, and Technical Evangelist at SolarWinds).
In-memory OLTP (sometimes referred to by as Hekaton)
Tom LaRock and Tim Chapman both agreed that one of the most exciting new features of SQL Server 2014 is in-memory OLTP (sometimes referred to by its code name ‘Hekaton’):
Traditionally, database architecture focused on designing for optimized disk I/O, because memory was expensive. Now, very inexpensive memory and disk hardware are readily available, and database architecture has evolved to better optimize for the available resources. According to Microsoft here, in-memory OLTP provides a memory-optimized database engine that has been integrated into the SQL Server engine. This engine has been optimized for OLTP and promises significant performance and scalability improvements for OLTP applications.
Tom LaRock has compiled a very useful list of links related to Hekaton here.
Clustered columnstore indexes
Joey D’Antoni shared that he is expecting that clustered columnstores can contribute to improved performance.
Columnstore indexes were introduced in SQL Server 2012 and offered, according to Microsoft, the potential for 10x performance improvements and 7x compression over traditional tables. But there were significant restrictions to their use. The clustered columnstore index introduced in SQL Server 2014 overcomes those limitations, allowing for highly efficient column-ordered data while still allowing the table to operate normally for DUI operations.
Cardinality Estimator changes for the query optimizer
Tim Chapman highlighted the much anticipated cardinality changes associated with the Cardinality Estimator for the query optimizer:
Microsoft conducted extensive research into modern-day workloads and completely redesigned the Cardinality Estimator in SQL Server 2014 to improve the quality of query plans, which in turn improves overall query performance. Specific details of these changes are explored here with Microsoft’s SQL Server team.
SQL Server 2014 promises new opportunities for performance improvement
Though this list is limited, our panel was excited about the many features of SQL Server 2014 aimed at improving performance for the workloads SQL Server must handle today. Be sure to read Part 1 of this conversation with experts for a discussion on why hardware is rarely the cause of performance issues, and Part 2 about how to accurately assess and plan to address performance issues.
[…] experts to discuss the myths and realities of SQL Server database performance (read Part 1 here and Part 3 here) and recommendations for improving performance, which we’ll share in this […]