Enterprise TechSQLSQL Server 2016 New Performance Features February 24, 20168237 views0Share By IG ShareTable of ContentsNote: If you buy something from our links, we might earn a commission. See our disclosure statement. ToggleSQL Server 2016 New Performance FeaturesOperational AnalyticsUsing Availability Groups instead of data warehouseSupport for Index MaintenanceIn-memory OLTP enhancementsSupport for Transparent Data EncryptionQuery StoreNatively supporting Java Script Object Notation (JSON)TemporalHow system-time works?SQL Server 2016 New Performance FeaturesI 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 BenefitsNo or minimal data latencyNo 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.CHALLENGESAnalytics queries are highly resource intensive and can cause blocking issues.Minimizing impact on operational workloadsSub-optimal execution of analytics on relational schemaExamplesEnterprise resource planning (ERP): Inventory, orders, and salesMachine or Factory data from operations on the factory floorOnline stores such as Amazon, Flipkart or Snapdeal etc.Stock market & derivatives trading Data.Using Availability Groups instead of data warehouseYou 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 replicaSupport for Index Maintenance source: https://msdn.microsoft.com/en-us/library/dn935013(v=sql.130).aspxIn-memory OLTP enhancementsALTER supportSQL 2016 now provides full schema change support: add/alter/drop column/constraintAdd/drop index supported is now supported.Surface area improvementsAlmost full T-SQL coverage including scaler user-defined functionsImproved scaling and bigger hard limits. In-memory OLTP engine has been enhanced to scale linearly on servers up to 4 socketsIncreased size allowed for durable tables; more socketsOther improvementsMARS 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 stringLightweight migration reportsSupport for Transparent Data EncryptionIn SQL Server 2016 CTP2, the storage for memory-optimized tables will be encrypted as part of enabling TDE on the databaseThe process to enable is the same. Simply follow the same steps as you would for a disk-based databaseManagement Data Warehouse (MDW) is now depreciated and now has been replaced with lightweight reports available in SSMS without setting up MDW.Query StoreQuery 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 regressedeasily force better plan from history with a single line of T-SQLsafely do server restart or upgradeHere is the management dashboardNatively 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 JSONYou 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=9898458Temporal Query back in timeHow system-time works? Source: https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspxYou can query point in time:SELECT * FROM Department FOR SYSTEM_TIMEAS OF ‘2006.01.01’In the next post, I will talk about the security enhancements in SQL Server 2016. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...