Stretch Database in SQL Server 2016
Contributions, Microsoft Azure, SQL Server 2016

Overview of New Feature- Stretch Database in SQL Server 2016

SQL Server 2016 has the new feature to store cold data transparently and securely to the Microsoft Azure cloud. This new feature is known as Stretch Database and the feature will be beneficial to those needing to keep transactional data for long periods of time and those looking to save money on storage. Being able to seamlessly migrate data to the Microsoft Azure Cloud will give you the ability to archive data without having to change the way your applications query the data. Stretch Database migrates entire tables.

If your database is already set up to store archive data in separate tables from current data, you will be able to easily migrate the archive data to Azure. Once you enable Stretch Database, it will silently migrate your data to an Azure SQL Database. Stretch Database leverages the processing power in Azure to run queries against remote data by rewriting the query. You will see this as a “remote query” operator in the query plan.

Continue Reading…

Overview of Query Store Feature - SQL Server 2016
Contributions, SQL Server 2016

Overview of Query Store Feature – SQL Server 2016

Query Store automatically captures and retains a history of queries, query execution plans, and runtime execution statistics for troubleshooting performance problems caused by query plan changes. This feature helps a great deal in simplifying performance troubleshooting by helping us to quickly find performance differences.  Query Store is available in all the editions of SQL Server 2016.

We can find information captured by query store even after a server restart. This is because the information persists inside internal tables of the database, not in the memory. When the Query Optimizer compiles a new Execution Plan for a query that performs much worse than the previous Execution Plan for that same query, it allows us to choose a specific execution plan to use, rather than the default plan. It is similar to force plans in earlier versions.

Continue Reading…