sql數(shù)據(jù)庫(kù)培訓(xùn)其他相關(guān)4server in-memory概述

1、In-Memory OLTP 概述面向關(guān)鍵任務(wù)的高性能平臺(tái) - SQL Server 2014王輝 | MVP | MCT SQL Server 2014 investmentsIn-memory TechnologiesEnhancedHigh AvailabilityNew HybirdScenariosIn-Memory OLTP520 x performance gain for OLTP integrated into SQL ServerIn-Memory Data Warehouse525x performance gain and high data compression U

2、pdatable and clusteredSSD Buffer Pool Extension410 x RAM and up to 3x performance gain transparently for appsAlwaysOn EnhancementIncreased availability and improved manageability of active secondariesOnline Database OperationsIncreased availability for index and partition maintenanceBackup to AzureE

3、asy to implement and cost effective disaster recovery solution to Windows Azure StorageHigh availability to Azure Virtual MachinesEasy to implement and cost effective high availability solution with Windows Azure Virtual MachinesDeploy to AzureDeployment wizard to migrate database2Better together wi

4、th Windows ServerWindows Server 2012 ReFS supportOnline resizing VHDXMicrosoft Hyper-V replicaWindows “Blue” supportExtending Power ViewEnable Power View on existing analytic models and support new multidimensional modelsOther investmentsIn-memory technologiesIn-memory technologiesIn-Memory OLTP520

5、x performance gain for OLTP integrated into SQL ServerTransactional workloads, such as concurrent data entry, processing, and retrievalDecision support workloads, such as large scans and aggregatesDisk-based transactional workloads, such as large working (data) setsIn-Memory Data Warehouse525x perfo

6、rmance gain and high data compression Updatable and clusteredSSD Buffer Pool Extension410 x of RAM and up to 3X performance gain transparently for appsWhy In-memory OLTPMarket need for higher throughput and predictable lower latency OLTP at a lower costHardware trend demands architectural changes on

7、 RDBMS to meet those demandsIn-memory OLTP: High performanceMemory-optimized OLTP engineIntegrated into SQL Server Built for modern hardware trendsDecreasing RAM costMoores Law on total CPU processing power holdsCPU clock rate has stalled in parallel processingHardware trendsSQL Server integrationSa

8、me manageability, administration, and development experienceIntegrated queries and transactionsIntegrated high availability and backup/restoreMain-memory optimizedOptimized for in-memory dataIndexes (hash and range) exist only in memoryNo buffer pool, B-treesStream-based storageHigh concurrencyMulti

9、-version optimistic concurrency control with full ACID supportCore engine uses lock-free algorithmsNo lock manager, latches, or spinlocksT-SQL compiled to machine codeT-SQL compiled to machine code via C code generator and VCInvoking a procedure is just a DLL entry-pointAggressive optimizations at c

10、ompile-timeSteadily declining memory price, NVRAMMany-core processorsStalling CPU clock rateTCOHardware trendsBusinessIn-memory OLTP architecture pillarsHybrid engine and integrated experienceHigh-performance data operationsFrictionless scale-upEfficient, business-logic processingCustomer benefitsIn

11、-Memory OLTP tech pillarsDriversIntroduction to In-Memory OLTPDemo (Part I)Memory-optimized table filegroupData filegroupSQL Server.exeIn-memory OLTP engine for memory-optimized tables and indexesTDS handler and session managementIn-Memory OLTP integration and application migrationNatively compiled

12、SPs and schemaBuffer pool for tables and indexesProc/plan cache for ad-hoc T-SQL and SPsTransaction logQuery interoperabilityNon-durable tableT1T4T3T2T1T4T3T2T1T4T3T2T1T4T3T2TablesIndexesInterpreter for TSQL, query plans, expressionsT1T4T3T2T1T4T3T2Checkpoint and recoveryAccess methodsParser, Catalo

13、g, Algebrizer, OptimizerIn-Memory OLTP CompilerIn-Memory OLTP componentKeyExisting SQL componentGenerated .dllClient appPerformance gainsIn-Memory OLTP CompilerIn-Memory OLTP componentMemory-optimized table filegroupData filegroupSQL Server.exeIn-Memory OLTP engine for memory-optimized tables and in

14、dexesTDS handler and session managementNatively compiled SPs and schemaBuffer pool for tables and indexesProc/plan cache for ad-hoc T-SQL and SPsClient appTransaction logInterpreter for TSQL, query plans, and expressionsQuery interopAccess methodsParser, Catalog, Algebrizer, Optimizer10-30 x more ef

15、ficientReduced log bandwidth and contention; log latency remainsCheckpoints are background sequential IONo improvements in communication stack, parameter passing, or result set generation KeyExisting SQL componentGenerated .dllSuitable Application Characteristics10Application is suited for in-memory

16、 processingAll performance critical data already fits in memoryTransaction locking or physical latching causing stalls and blockingApplication is “OLTP-Like”Relatively short-lived transactionsHigh degree of concurrent transactions from many connectionsExamples: Stock trading, travel reservations, or

17、der processingApplication porting simplified ifStored procedures usedPerformance problems isolated to subset of tables and SPsSQL Server row-store and column-store scenariosRowstore for OLTP is mainly for operational transactions with minimum reporting and a shorter period of time ConsiderationsIM r

18、owstoreIM updatable columnstoreData size and currencyData sizeDesigned to address bottlenecks in hot data (for V1, less than 256 GB) Designed for cold and archival data (greater than 256 GB)Read patternsPoint select and ad-hoc query for operational reportIdeal: Key design points for non-blocking hig

19、h performance data accessNot ideal: Minimum scan set is 1 million rows + delta rowstoreLarge scan set with aggregatesNot idealIdeal: Key design pointsStar schema and related DW type of complex joinsNot idealIdeal: Key design pointsWrite patternsHeavy updates and deletesIdeal: Key design points for c

20、ontention free data operationsFunctional but not optimized (change happens to on-disk row store and gets merged into columnstore in batches)Heavy ETL and data ingestionIdeal: Key design pointsFunctional but not optimized (same as above)Relational cache scenarioIdeal (with NDT)Not idealColumnstore for DW is mainly for the reporting of transaction history over a longer period of timeComparing In-Memory OLTP PerformanceDemo (Part 2) 2013 Microsoft