SQL Server 2014新特性培训

概览•内存数据库(Hekaton)•列存储索引(OLTP)•BufferPool扩展•高可用技术AlwaysOnWhatAreMemory-OptimizedTables?•DefinedasCstructs,compiledintoDLLs,andloadedintomemory•Canbepersistedasfilestreams,ornon-durable•Donotapplyanylockingsemantics•Canbeindexedusinghashindexes•Canco-existwithdisk-basedtables•CanbequeriedusingTransact-SQL•Cannotincludesomedatatypes,includingtext,image,andnvarchar(max)•DonotsupportidentitycolumnsorforeignkeyconstraintsMemory-OptimizedTableScenarios•Optimisticconcurrencyoptimizeslatch-boundworkloads:•Multipleconcurrenttransactionsmodifylargenumbersofrows•Atablecontains“hot”pages•Applicationsshouldhandleconflicterrors:•Writeconflicts•Repeatablereadvalidationfailures•Serializablevalidationfailures•CommitdependencyfailuresCreatingMemory-OptimizedTables•Addafilegroupformemory-optimizeddata•Createamemory-optimizedtableALTERDATABASEMyDBADDFILEGROUPmem_dataCONTAINSMEMORY_OPTIMIZED_DATA;GOALTERDATABASEMyDBADDFILE(NAME='MemData'FILENAME='D:\Data\MyDB_MemData.ndf')TOFILEGROUPmem_data;CREATETABLEdbo.MemoryTable(OrderIdINTEGERNOTNULLPRIMARYKEYNONCLUSTEREDHASHWITH(BUCKET_COUNT=1000000),OrderDateDATETIMENOTNULL,ProductCodeINTEGERNULL,QuantityINTEGERNULL)WITH(MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);Memory-OptimizedTableIndexes•Hashindexes•Assignrowstobucketsbasedonhashingalgorithm•Multiplerowsinthesamebucketformalinkedlist•Rangeindexes•Usealatch-freeBW-TreestructureCREATETABLEdbo.IndexedMemoryTable(OrderIdINTEGERNOTNULLPRIMARYKEYNONCLUSTEREDHASHWITH(BUCKET_COUNT=100000),OrderDateDATETIMENOTNULL,ProductCodeINTEGERNULL,QuantityINTEGERNULLINDEXidx_MemTab_OrderDateNONCLUSTEREDHASH(OrderDate)WITH(BUCKET_COUNT=100000))WITH(MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);QueryingMemory-OptimizedTables•QueryInterop•InterpretedTransact-SQL•Enablesqueriesthatcombinememory-optimizedanddisk-basedtables•NativeCompilation•StoredprocedureconvertedtoCandcompiled•Accesstomemory-optimizedtablesonlyMemory-OptimizedTablesDisk-BasedTablesTab1Tab2Tab3Tab4Transact-SQLSELECTt1.col1,t3.col2FROMTab1t1JOINTab2t2ONt1.Col1=t2.col1;QueryInteropNativeCompilationCREATEPROCEDURE…#define__inHRESULThkp_(…0110101101TranslatetoCCompiletoDLLCreatingNativeStoredProcedures•CREATEPROCEDUREStatement•NATIVE_COMPILATIONoption•SCHEMABINDINGoption•EXECUTEASoption•BEGINATOMICclauseCREATEPROCEDUREdbo.DeleteCustomer@CustomerIDINTWITHNATIVE_COMPILATION,SCHEMABINDING,EXECUTEASOWNERASBEGINATOMICWITH(TRANSACTIONISOLATIONLEVEL=SNAPSHOT;LANGUAGE='us_English')DELETEdbo.CustomerWHERECustomerID=@CustomerIDDELETEdbo.OpenOrdersWHERECustomerID=@CustomerIDEND;WhatAreColumnstoreIndexes?•In-memory,compresseddatainpagesbasedoncolumnsinsteadofrowsProductIDOrderDateCost310200107012171.29311200107011912.15312200107022171.2931320010702413.14datapage1000ProductIDOrderDateCost31420010701333.42315200107011295.00316200107024233.1431720010702641.22datapage1001ProductID310311312313314315316317318319320321datapage2001OrderDate20010701…20010702……20010703…………20010704…datapage2000datapage2002Cost2171.291912.152171.29413.14333.421295.004233.14641.2224.9564.321111.25RowStoreColumnStoreColumnstoreIndexScenarios•Columnstoreindexesaremostsuitablefor:DatabaseswithstarorsnowflakeschemasTableswithlargenumbersofrowsTablesthatcontaindatathatrespondswelltocompressionClusteredandNon-ClusteredColumnstoreIndexes•ClusteredColumnstoreIndexesSQLServer2014Enterprise,Developer,andEvaluationEditionOnlyIncludesallcolumnsinthetableOnlyindexonthetableUpdatable•Non-ClusteredColumnstoreIndexesIncludessomeorallcolumnsinthetableCanbecombinedwithotherindexesRead-OnlyCreatingaColumnstoreIndexCREATECLUSTEREDCOLUMNSTOREINDEXcsidx_FactSalesOrderDetailsONFactSalesOrderDetails;CREATENONCLUSTEREDCOLUMNSTOREINDEXnccsidx_FactSalesOrderONFactSalesOrder(CustomerKey,SalesPersonKey,ProductKey,OrderDateKey,OrderNo,ItemNo,Quantity,Cost,SalesAmount,Shipping,Discount);WhatIstheBufferPoolExtension?Datafiles(Disk)Buffercache(RAM)Buffercacheextension(SSD)PagesCleanPages•Extendsbuffercachetonon-volatilestorage•Improvesperformanceforread-heavyOLTPworkloads•SSDdevicesareoftenmorecost-effectivethanaddingphysicalmemory•Simpleconfigurationwithnochangestoexistingapplications.BufferPoolExtensionScenarios•OLTPoperationswithahighvolumeofreads•Upto32GBofphysicalmemory•BufferPoolExtensionis4xto10xphysicalmemory•BufferPoolExtensiononhighthroughputSSDstorageConfiguringtheBufferPoolExtension•EnableusingALTERSERVERCONFIGURATION•ToReconfigure,disableandthenre-enableALTERSERVERCONFIGURATIONSETBUFFERPOOLEXTENSIONON(FILENAME='E:\SSDCACHE\MYCACHE.BPE',SIZE=50GB);OptionsforHighAvailabilityinSQLServer2014LogShippingDatabaseMirroringWitnessFailoverClusterInstancePassiveActiveAvailabilityGroupWindowsClusterWindowsClusterPrimaryreplicaActiveSecondaryreplicaAlwaysOnTechnologiesinSQLServer2014WindowsServerFailoverClusterAlwaysOnAvailabilityGroupDatabase-levelprotectionWindowsServerFailoverClusterAlwaysOnFailoverClusterInstanceServer-levelprotectionAlwaysOnFailoverClusterInstances•Instance-levelhighavailability•Improvedhealthmonitoringandfailoverflexibility•Sup