{"id":16135,"date":"2016-06-23T10:00:27","date_gmt":"2016-06-23T17:00:27","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/?p=16135"},"modified":"2024-01-22T22:52:23","modified_gmt":"2024-01-23T06:52:23","slug":"powering-mission-critical-performance-with-sql-server-2016","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/06\/23\/powering-mission-critical-performance-with-sql-server-2016\/","title":{"rendered":"Powering mission-critical performance with SQL Server 2016"},"content":{"rendered":"

Data has become the lifeblood of the enterprise. It\u2019s the foundation for keen insight and effective decisions that lead to business growth. One of our primary design goals for SQL Server 2016 was to provide the performance, security, availability and business intelligence that are critical to helping companies manage their data and identify new opportunities.<\/p>\n

Over the next three weeks, we\u2019ll give you a few snapshots of some key features in SQL Server 2016 and how you can use them to drive sustained mission critical performance, get deeper insights from your data and benefit from hyperscale cloud capabilities.\n<\/p>\n

Run queries up to 100 times faster<\/h2>\n

With SQL Server 2016 we enhanced the In-Memory columnstore capabilities, which accelerate highly concurrent workloads by transferring data to memory-optimized tables. With these enhancements in place, you can run queries up to 100 times faster than previously possible.<\/p>\n

In addition, we made memory-optimized tables more scalable\u2014able to store up to 2 TB of data each, and support bigger workloads.<\/p>\n

To get started, evaluate your workloads using a new feature in SQL Server Management Studio (SSMS), the ability to generate migration checklists<\/p>\n

To do this, right-click a database in Object Explorer, point to Tasks, and then select Generate In-Memory OLTP Migration Checklists. This step launches a wizard that displays a welcome page. On the second page of the wizard, specify a location in which to save the checklist and whether to generate a checklist for all tables and stored procedures in the database or for a specific list that you define. After you make this selection, the next page of the wizard includes a Script PowerShell Commands button and a Finish button. If you select the Script PowerShell Commands button, a text file opens to display the following command:<\/p>\n

<\/pre>\n
Save-SqlMigrationReport -Server ‘<Server Instance Name>’ -Database ‘AdventureWorks’ -FolderPath ‘C:\\Users\\<User>\\Documents\\<Path>’<\/span><\/p>\n
When you click the Finish button, the wizard begins to generate a separate checklist for each table and stored procedure specified in the wizard. The status of each checklist is displayed in the table so that you can easily see whether any failed. After the wizard completes the checklists, you can find them as HTML files in the Stored Procedures, Tables, or User Defined Functions folders in the output path that you configured in the wizard.<\/p>\n
Real-time, operational analytics<\/h2>\n
SQL Server 2016 also includes options for real-time analysis of datasets that are more dynamic in nature. With added support for columnstore indexes in memory-optimized transactional tables, you can avoid issues around latency and benefit from real-time analytics capabilities that live up to the name.<\/p>\n
Batch execution mode has also been improved so results can be processed up to 1,000 rows at a time, greatly reducing execution time and the utilization of CPU resources. Use SSMS to get started, or add a clustered columnstore index to a disk-based table using a T-SQL statement such as the sample below.<\/p>\n
\"T-SQL<\/p>\n
Better security built in<\/h2>\n
SQL Server 2016 comes with a number of new security features built-in, helping lock down your data at all levels and states:<\/p>\n