Enterprise TechSQL SQL Server 2016 New Performance Features February 24, 2016922 views0 Share SQL Server 2016 New Performance Features I have already written about SQL Internals improvements on a different post. With SQL Server 2016, Microsoft has continued to invest in three major areas. They have continued to evolve the product on mission critical performance, with SQL Server 2014. Microsoft began to take the lead away from Tier 1 vendors & (Like Oracle, SAP) competitors with in-memory technology built-in. This release (SQL 2016) has further added new innovations across many mission critical components. With investments on data insights, significant investment both on premises and complimentary services via Azure has added to help you gain deeper insights across your data. Finally Microsoft is adding new hybrid capabilities that will compliment your on-prem investments and give you the ability to take advantage of Microsoft’s hyperscale Azure cloud. Operational Analytics Benefits No or minimal data latency No need to setup ETL, directly query SQL Tables without significant performance impact. No additional investments or a need to setup a separate data warehouse for reporting needs. Data Warehouse queries can be run on in-memory OLTP workload with no application changes. These operations have minimal impact on OLTP workload. CHALLENGES Analytics queries are highly resource intensive and can cause blocking issues. Minimizing impact on operational workloads Sub-optimal execution of analytics on relational schema Examples Enterprise resource planning (ERP): Inventory, orders, and sales Machine or Factory data from operations on the factory floor Online stores such as Amazon, Flipkart or Snapdeal etc. Stock market & derivatives trading Data. Using Availability Groups instead of data warehouse You can now Load balance Always On availability groups for Mission Critical Operational Workloads typically configured for High Availability. You can offload analytics to readable secondary replica Support for Index Maintenance source: https://msdn.microsoft.com/en-us/library/dn935013(v=sql.130).aspx In-memory OLTP enhancements ALTER support SQL 2016 now provides full schema change support: add/alter/drop column/constraint Add/drop index supported is now supported. Surface area improvements Almost full T-SQL coverage including scaler user-defined functions Improved scaling and bigger hard limits. In-memory OLTP engine has been enhanced to scale linearly on servers up to 4 sockets Increased size allowed for durable tables; more sockets Other improvements MARS support. SQL Server 2005 introduced support for multiple active result sets (MARS) in applications accessing the Database Engine. Setup MARS connection for memory optimized tables using the MultipleActiveResultsSets=True in your connection string Lightweight migration reports Support for Transparent Data Encryption In SQL Server 2016 CTP2, the storage for memory-optimized tables will be encrypted as part of enabling TDE on the database The process to enable is the same. Simply follow the same steps as you would for a disk-based database Management Data Warehouse (MDW) is now depreciated and now has been replaced with lightweight reports available in SSMS without setting up MDW. Query Store Query store provides DBAs with data insight on query plan choice and performance. Simplified performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. You can use Query plan to automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. Unlike DMV’s which shows you point in time information, Query Store separates data by time dimension, allowing you to see database usage patterns and understand when query plan changes happened on the server. With Query Store you can: get full history of query execution quickly pinpoint the most expensive queries get all queries that regressed easily force better plan from history with a single line of T-SQL safely do server restart or upgrade Here is the management dashboard Natively supporting Java Script Object Notation (JSON) SQL 2016 can now natively accept JSON, easily parse and store it in a relational format. You can export relational easily as JSON You can correlate relational and non-relational data. Azure DocumentDB integration.See TechNet Virtual Lab “Exploring SQL Server 2016 support for JSON data” – http://go.microsoft.com/?linkid=9898458 Temporal Query back in time How system-time works? Source: https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx You can query point in time: SELECT * FROM Department FOR SYSTEM_TIME AS OF ‘2006.01.01’ In the next post, I will talk about the security enhancements in SQL Server 2016. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Enterprise Tech How to use Azure Ad Join on Windows Server 2019? & Gateway Access Just like Microsoft Windows 10, the Microsoft Server 2019 also lets you make use of ...
Enterprise Tech What is VMware VMotion? Limitations & Requirements – Storage & Shared If you are in the IT industry, especially infrastructure professionals, you may have heard the ...
Cloud Computing Differences Between VMWare SRM Standard and Enterprise Licensing Disaster Management is one of the essential aspects of any virtualization technique. VMWare, being one ...
Enterprise Tech Cisco WebEx vs Skype for Business: Differences and Comparisons The workforce today has become completely mobile. With that change in the work strategy, the ...