3數(shù)據(jù)倉(cāng)庫(kù)和列存儲(chǔ)索引改進(jìn)

1、數(shù)據(jù)倉(cāng)庫(kù)和列存儲(chǔ)索引改進(jìn)面向關(guān)鍵任務(wù)的高性能平臺(tái) - SQL Server 2014王輝 | MVP | MCT C1C2C3C5C6C4Existing Tables (Partitions)Needed columnsColumnstore Index RepresentationIn-Memory In the Data WarehouseData Stored Row-Wise: Heaps, b-Trees, Key-Value2In-Memory ColumnStoreBoth memory and diskBuilt-in to core RDBMS engineCustomer

2、 Benefits:10-100 x fasterReduced design effortWork on customers existing hardwareEasy upgrade; Easy deployment“By using SQL Server 2012 In-Memory ColumnStore, we were able to extract about 100 million records in 2 or 3 seconds versus the 30 minutes required previously. “- Atsuo Nakajima Asst Directo

3、r, Bank of Nagoya Traditional Storage ModelsData Stored Row-Wise: Heaps, b-Trees, Key-Value3Relational, dimensional, map reduce In-Memory DW Storage ModelData Stored Column-wise4Each page stores data from a single columnHighly compressedMore data fits in memoryEach column can be accessed independent

4、lyFetch only columns neededCan dramatically decrease I/OC1C2C3C5C6C4In-Memory DW Index StructureRow Groups & Segments5A segment contains values for one column for a set of rowsSegments for the same set of rows comprise a row groupSegments are compressedEach segment stored in a separate LOBSegment is

5、 unit of transfer between disk and memorySegmentsC1C2C3C5C6C4Row groupIn-Memory DW IndexProcessing an Example6Horizontally PartitionRow Groups7Vertical PartitionSegments8Compress Each Segment*Some Compress More than Others9*Encoding and reordering not shownFetch Only Needed ColumnsSegment Eliminatio

6、n10Fetch Only Needed SegmentsSegment Elimination11Batch ModeImproving CPU Utilization12Biggest advancement in query processing in years! Data moves in batch through query plan operatorsMinimizes instructions per rowTakes advantage of cache structuresHighly efficient algorithmsBetter parallelismBatch

7、 Mode ProcessingQP Vector Operators13Process 1000 rows at a timeBatch stored in vector formOptimized to fit in cacheVector operators implementedFilter, hash join, hash aggregation, Greatly reduced CPU time (7 to 40X)bitmap of qualifying rowsColumn vectorsBatch objectIn-Memory DW: Clustered & Updatab

8、le14Fast execution for data warehouse queriesSpeedups of 10 x and moreNo need for separate base tableSave spaceData can be inserted, updated or deletedSimpler managementEliminate need for other indexesSave space and simpler managementMore data types supportedTable consists of column store and row st

9、oreDML (update, delete, insert) operations leverage delta storeINSERT ValuesAlways lands into delta storeDELETELogical operationData physically remove after REBUILD operation is performed.UPDATEDELETE followed by INSERT.BULK INSERTif batch 100k, inserts go into delta store, otherwise columnstoreSELE

10、CT Unifies data from Column and Row stores - internal UNION operation.“Tuple mover” converts data into columnar format once segment is full (1M of rows)REORGANIZE statement forces tuple mover to start.Updatable Columnstore IndexC1C2C3C5C6C4ColumnStoreC1C2C3C5C6C4Delta (row)storetuple moverComparing

11、Space Savings101 Million Row Table + Index Space16Structure of In-Memory DWHow It Works17CREATE CLUSTERED COLUMNSTOREOrganizes and compresses data into columnstoreBULK INSERTCreates new columnstore row groupsINSERTRows are placed in the row store (heap)When row store is big enough, a new columnstore

12、 row group is createdColumnStoreDeletedBitmapRow StorePartitionStructure of In-Memory DWHow It Works (contd)DELETERows are marked in the deleted bitmapUPDATEDelete plus insertMost data is in columnstore formatColumnStoreDeletedBitmapRow StorePartitionBatch Mode ProcessingWhats New? 19SQL Server 2014

13、Support for all flavors of JOINsOUTER JOINSemi-join: IN, NOT INUNION ALLScalar aggregatesMixed mode plansImprovements in bitmaps, spill support, Global Batch AggregationWhats New?20Replaces a set of three operators in the query plan Local (partial) batch aggregationRow aggregation, somewhere above i

14、tRepartition exchanges, somewhere between themImproves scenarios with large aggregation outputProcess the same data with less memory than local batch aggregationBetter performance than local batch aggregation, example big hash tablesRemoves the need for row mode aggregation in mostly batch query pla

15、ns, resulting in less data conversion and better management of granted memoryArchival CompressionWhats New?21Adds an additional layer of compression on top of the inherent compression used by columnstoreShrink on-disk database sizes by up to 27%Compression applies per partition and can be set either

16、 during index creation or during rebuildEnhanced CompressionTPCH3.1XTPCDS2.8XCustomer 13.9XCustomer 24.3X* compression measured against raw data fileARCHIVAL CompressionCOLUMNSTORE CompressionDefault compression when creating a table with Clustered Columnstore IndexTypical customer workloads gets 5-

17、7x compression ratiosEnables additional 30% compression for whole table and/or chosen partitions.Going back and forth between columnstore and columnstore_archive compressions.sys.partitions exposes compression info (3 columnstore, 4 columnstore_archive)Table compression options: PRESSION = NONE | RO

18、W | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE Partitioning ColumnStoresThe Basic Mechanism23The motivation is manageability over performanceCREATE TABLE ( )As usualCREATE CLUSTERED COLUMNSTORE INDEX on Converts entire table to Columnstore formatBULK INSERT, SELECT INTOINSERTUPDATEDELETEInsert & Updat

19、ing DataLoad Sizes24Bulk insertCreates row groups of 1Million rows, last row group is probably not fullBut if 100K rows, will be left in Row StoreInsert/UpdateCollects rows in Row StoreTuple MoverWhen Row Store reaches 1Million rows, convert to a ColumnStore Row GroupRuns every 5 minutes by defaultS

20、tarted explicitly by ALTER INDEX ON REORGANIZEBuilding Index in ColumnStoreMaking Them Fast25Memory resource intensiveMemory requirement related to number of columns, data, DOPUnit of parallelism is the segmentLots of segments, lots of potential parallelism Low memory throttles parallelismIncrease the max server memory optionSet min server memory to a large i