TSQL Masters

Native stored procedures in SQL Server 2014 CPT2   Procedure loaded into memory in native code consisting of processor instructions that can be executed directly by the CPU, without the need for further compilation or interpretation.   Syntax: Eg: Memory-Optimized Tables Create table dbo.T1 (   C1 int not null  primary key NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000)   )   with (MEMORY_OPTIMIZED=ON) Natively Compiled Stored Procedures Native stored procedures Create procedure dbo.p1 WITH  NATIVE_COMPIALATION, SCHEMABINDING, EXECUTE AS OWNER AS        BEGIN ATOMIC with (TRANSACTION ISOLATION LEVEL=snapshot,                   LANGUAGE=N'us_english')        Select 1 END

Buffer pool extension (BPE) Memory is one of the key assets available for the SQL Server and especially buffer pool memory, which plays key role in storing data pages and in turn reducing the I/O demand. The Buffer Pool Extension feature (BPE) provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.   Solid-state drives (SSD) have some advantages over direct memory : ·          the higher flexibility of storage options over memory options; ·          is the greater price efficiency of available storage vs. memory.   ·          Buffer Pool Extension offer: ·          Performance gains on OLTP workloads, mostly on the read-heavy OLTP. ·     ...

  Memory optimized OLTP database engine for SQL Server      Memory-optimized OLTP database engine ( In-Memory OLTP )  support tables which reside in memory ( Memory-Optimized Tables )  and  Natively Compiled Stored Procedures ( Create Procedure code translated into native machine code and compiled into a DLL and loaded into memory ) SQL Server 2014 Advantages:    Memory-Optimized Tables ·          High Performance increase : Unlike the usual Disk-based tables, all the data is stored in memory, all the time. Pages do not need to be read into cache from disk   when the memory-optimized tables are accessed. ·          For memory-optimized tables, there are no locks acquired, and thus no waiting because of blocking. ·          Concurrency improvements : When accessing memory-optimized tables, SQL Ser...

What's New in Microsoft SQL Server 2014   CTP2  SQL Server 2014 1.             Memory optimized OLTP database engine -    (#Performance gains)   2.             Windows Azure Storage Integration    3.             Backup and Restore Enhancements 3.1           SQL Server Backup to URL 3.2           SQL Server Managed Backup to Windows Azure 3.3          Encryption for Backups 4.             Delayed Durability-    (#Performance gains) 5.             Partition Switching and Indexing-    (#Performance gains) 5.1 ...