SQL Server 2014 及 规画企业资料库系统高.pptx

Microsoft SQL Server 2014新功能?In-MemoryIn-Memory OLTP 主軸SQL Server 2014 新增 In-memory 技術來提升整體 OLTP 效能資料表 索引Memory optimized table and index structures預存程序Native compilation of business logic in stored procedures鎖定與閂Latch-free and lock-free data structures其它應用整合Fully integrated into SQL Server從DB 開發人員看 In-Memory OLTP 甚麼是 Memory optimized tables and indexes ?該如何存取Memory optimized tables交易該如何配合Memory optimized tablesIn Memory OLTP 架構 : 早期 DBCC PINTABLEIn Memory OLTP 架構演變前端應用程式存取TDS (Client Server Communications) Handler and Session Management注意顏色SQL Server.exeParser, Catalog, Optimizer現有SQL SERVER元件T-SQL Query ExecutionNatively Compiled SPs and SchemaNative Compiler新的In-memory元件Non-durable tableQuery InteropMemory Optimized Tables IndexesBuffer Pool for Tables Indexes產生的.dllT1T2T3TablesIndexesIn-Memory 優化檔案群組交易記錄檔案一般資料檔案T1T2T3T3T1T2In Memory OLTP 如何提升效能前端應用程式存取TDS (Client Server communication) Handler and Session ManagementSQL Server.exe注意顏色維持原樣communication stack, parameter passing, result set generation 現有SQL SERVER元件Parser, Catalog, OptimizerT-SQL Query ExecutionNative Compiler新的In-memory元件Natively Compiled SPs and Schema10-30倍數提升產生的.dllQuery Interop減少lock and latchBuffer Pool for Tables IndexesEngine for Memory_optimized Tables Indexes減少交易使用量 Latency*交易記錄檔案(都適用)一般資料檔案In-Memory 優化檔案群組使用sequential IOIn-Memory optimized 資料表與索引 sys.fn_helpcollations() 如何建立In-memory資料庫使用 CREATE DATBASE 指令(注意定序 Collate)CREATE DATABASE TicketReservationsGO-- 新增 memory_optimized_data 檔案群組ALTER DATABASE TicketReservations ADD FILEGROUP [TicketReservations_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA;-- 新增對應群組的檔案(可以分散到多硬碟)ALTER DATABASE TicketReservations ADD FILE (NAME = [TicketReservations_fs_dir], FILENAME = C:\Data\TicketReservations_fs_dir) to FILEGROUP [TicketReservations_hk_fs_fg]GO檢視建立後In-memory 資料庫新增的檔案群組與對應類似串流檔案建立In-memory資料表與索引建立資料表時候就需要馬上一起定義僅支援NONCLUSTERED indexesCREATE TABLE [Customer]( [CustomerID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), [Na