Performance Tuning — Essential Tool for DBAs

The SQL Server 2016 release comes with new built-in features along with much needed improvements, making it the biggest leap forward in Microsoft’s data platform history. The SQL Server 2016 has several notable built-in features along with great improvements to some long-neglected capabilities in the previous versions that DBAs rely on. You can download our white paper, Making the Move to SQL Server 2016, which looks at the new and upgraded features offered by the latest addition to the SQL Server family.

Performance Tuning

Performance tuning is an essential tool that represents some of the major improvements made on SQL Server 2016, allowing DBAs access to the enhanced system performance metrics.

“Perhaps the most useful SQL Server 2016 feature for database administrators involves performance tuning, which allows DBAs to monitor and record the full history of query execution plans to diagnose issues and optimize plans. It will be invaluable for upgrades and patching to see where changes have impacted performance.” — Andrew Snodgrass, a research vice president at Directions on Microsoft, TechTarget

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services received a significant makeover with the release of SQL Server 2016. Those include a new and brandable portal with a modern Web app appearance, with new KPI functionality that makes the building of dashboards much easier. It also includes paginated reports and mobile reports, desktop files for Excel, and power BI. The new option of installing reporting service separately outside the SQL Server setup makes the SSRS installation more lightweight, and enables users to configure the database with report server.

Master Data Services (MDS)

The MDS utility enables the management of master data sets of an organization. With MDS, users can create data models, perform access control on the data, organize the data into models, and create rules to update data.

PolyBase

PolyBase provides big data integration by allowing users to query distributed data sets; for example, querying Hadoop or SQL Azure blob storage using Transact SQL statements. It also enables the joining of relational data from SQL using written adhoc queries and optimizing them on the fly against semi-structured data with columnstore indexing.

JSON Support

SQL Server 2016 now has a built-in JSON support that allows users to interchange JSON data between applications and the SQL Server database engine. This feature provides great flexibility in reflecting business data in mobile applications as it allows the parsing and storage of JSON formatted data in a relation format and also allows relational data to be turned into JSON formatted data.

Columnstore Index Performance

SQL Server 2016 has much more mature In-Memory OLTP and columnstore indexes that are easily deployable and provides the ability to combine In-Memory OLTP and columnstore indexes on a single table. Single threading execution has made query execution time much faster. In addition, memory-optimized tables’ size limit has been raised to 2TB with capability of them being edited.

Overall, SQL Server 2016 is quite promising, providing comprehensive and enterprise-level database system that caters to today’s dynamic business needs. It also marks Microsoft’s cloud-first version of SQL Server and its investment in and improvements of the important features that had been long ignored.

To learn more about SQL Server 2016, please contact Datavail today.