The Bakers Dozen Business Intelligence 13 Productivity Tips
The Baker’s Dozen Business Intelligence 13 Productivity Tips for In-Memory OLTP Enhancements in SQL 2016 Kevin S. Goff www. Kevin. SGoff. net Microsoft SQL Server MVP
Kevin S. Goff – Brief BIO • Developer/architect since 1987 / Microsoft SQL Server MVP • Columnist for Co. De Magazine since 2004, “The Baker’s Dozen” Productivity Series”, 13 tips on a SQL/BI topic • Wrote a book, collaborated on a 2 nd book • Frequent speaker for SQL Server community events and SQL Live!360 Conferences • Email: kgoff@kevinsgoff. net • My site/blog: www. Kevin. SGoff. Net (includes SQL/BI webcasts) • Releasing some SQL/BI video courseware in the future
Overview • This is an introductory session • Assumes you have no specific knowledge of In-Memory OLTP databases and want to learn • Having said that, I will talk about where Microsoft has enhanced this in SQL 2016 over what’s in SQL 2014 • In early SQL 2014, In-Memory optimized OLTP was called Hekaton • Will cover the basics and provide links to other sources for further research • Some demos to give an idea where In-Memory OLTP boosts performance • Will also cover what In-Memory OLTP doesn’t do/isn’t for
Topics 1. What specifically is In-Memory OLTP? When should we use? 2. Some demonstration benchmarks 3. Creating In-Memory OLTP databases 4. Collation Sequences in In-Memory OLTP Databases 5. Creating In-Memory OLTP Tables and Indexes 6. In-Memory OLTP Tables versus Normal tables and In-Memory OLTP table restrictions 7. Supported Data Types 8. What you should know about Bucket Counts 9. Stored Procedures and In-Memory OLTP 10. Limitations of Native Compilation Stored Procedures 11. In-Memory OLTP and Table Types 12. AMR Tool to Profile if you can use In-Memory OLTP 13. Reading recommendations
1 -What is In-Memory OLTP? • New in SQL Server 2014 Enterprise, significantly enhanced in SQL Server 2016 Enterprise • New In-Memory OLTP optimized row format for specific tables, performance gains potentially several times over standard tables. Uses a new buffer pool targeting a memory-optimized structure • Has been in the works for years • Data in server memory – Filestream used for storage persistence – some data type restrictions • Still access with T-SQL statements (some limitations) • No row locking/latching that you have with normal tables, uses Optimistic Concurrency – much less overhead • Two types of indexes: Hash and Range • Two types of Storage/Durability Modes: SCHEMA_AND_DATA, or SCHEMA_ONLY • Lower overhead / faster performance / better CPU utilization • Index restrictions that we’ll see later (mainly in 2014) • Not all OLTP applications will use this for all tables – must think about strategy • AMR Tool to profile OLTP application, to see which tables will benefit • You build In-Memory-enabled Databases and then In-Memory-enabled tables • Stored Procedures using Hekaton tables are compiled as native C code and executed internally as DLLs for performance (restrictions on functions in procs)
1 -In-Memory OLTP: when to use? • Not all SQL applications will benefit from In-Memory OLTP • Best Applications/Application segments: • • • Staging tables in ETL scenarios (don’t need to persist data) Holding SQL Session State data (very fast inserts/reads) Heavy random reads Bulk Inserts and sometimes bulk updates Reporting applications and possibly some OLAP scenarios • SQL 2014: Is there any overlap between In-Memory OLTP and the Columnstore Index? • Not really…. two great features but different uses • Columnstore index more for Data Mart/Data Warehouse Fact tables (improved in SQL 2014, is now a read-write index), for queries that scan/aggregate over a large number of rows • Columnstore index uses x. Velocity compression techniques, no compression in Hekaton in-memory optimized tables • You could not create a columnstore index on an in-memory OLTP table • Unfortunately, if you JOIN tables using In-memory OLTP with tables using Columnstore index, you lose batch mode/parallelism • SQL 2016: Is there any overlap between In-Memory OLTP and the Columnstore Index? • Possibly! You might be able to use columnstore indexes and in-memory OLTP on certain fact tables • You can create a table that’s both in in-memory table AND contains a columnstore index (Clustered only) • You can join in-memory OLTP tables with tables containing columnstore indexes, and get batch mode (parallelism)
2 -Demonstrations Demo Hekaton table (SCHEMA_ONLY) (SCHEMA_WITH_DATA) Normal table Insert 10 million rows from CSV using SSIS data flow 27 sec 31 sec 72 sec Bulk Insert of 10 million rows from CSV 22 sec 27 sec 79 sec Populate Date Calendar rows in a loop . 273 sec Key points/Best uses: • When inserting large # of rows (far less CPU utilization and overhead) • Reading rows can be faster, though not always by as high a factor • Remember, no IO Statistics – everything is in memory! • SCHEMA_ONLY fastest for large inserts • Remember to UPDATE STATISTICS manually on In-Memory tables 3. 13 sec
3 -Creating In-Memory Databases First, use interface to create new database Third, go to File. Groups tab and create Memory Optimized Data File. Group Second, set Collation Sequence to Latin 1_General_100_BIN 2 Will explain later – but don’t forget this step!!! Needed for character-based indexes Fourth, go back to General Tab and add File Type Reference to the new Memory Optimized Data File Type
3 -Creating In-Memory OLTP Databases Script syntax New syntax
3 -Creating In-Memory Databases • Can add Memory Optimized tables to existing database: • ALTER DATABASE Adventure. Works 2012 ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA; • GO • ALTER DATABASE Adventure. Works 2012 ADD FILE (NAME='hk_mod', FILENAME='c: datahk_mod') TO FILEGROUP hk_mod; • GO
4 -Collation Sequences in In-Memory OLTP • Default Collation Sequence will not support JOIN statements on Hekaton tables • Hekaton tables must use a BIN 2 collation on all character columns used in indexes Don’t forget this step!!! • Natively compiled procedures only support BIN 2 collations for comparisons, sorting, and grouping. • Don’t forget this step – you can’t go back later and change collation sequences on Hekaton databases! Not required in SQL 2016! (Though using other collation sequences when joining to character-based indexes might not perform as well)
5 -Creating In-Memory Tables • Note the Hash with Bucket Count • Note the WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) • Still keeps physical copy in the Memory Optimized File. Group 2 index types: Hash and Range, can create in-line Indexes are pointers to memory locations Bucket count should be 1. 5 – 2 X times the expected cardinality (number of expected unique index key values). Excess will not help and will only waste memory Must create indexes inline. Cannot create table and add indexes later • No support for clustered indexes (indexes point to rows, but don’t duplicate) • No support for adding a columnstore index • No data or index page – just rows • Max row size: 8, 060 bytes (hard limit, no off-page storage) • No IDENTITY support (use SEQUENCE object instead) None of the Betas/CTPs of SQL 2014 had IDENTITY support in Hekaton But was included in final RTM, only if you use IDENTITY(1, 1) Many websites haven’t updated that IDENTITY now supported in Hekaton
5 -Creating In-Memory Tables • DURABILITY MODE • WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) (default) • Performs IO with File. Groups, data persists if server crashes or SQL Service is restarted • WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) • Often better performance, but data is lost if Service restarted • Best for staging tables • INDEXES • Hash Indexes • Best for single item lookups on exact matches • Static in size, based on Bucket Count (1. 5 to 2 x the cardinality of the primary key) • Because there’s no heap or clustered index, an INDEX SEEK is often all you’ll get in the execution plan • Range Indexes • For data such as Dates (range of values)
6 -In-Memory OLTP Tables vs Normal Tables • In-memory, but uses File. Stream to persist on physical storage • No b-tree indexes, no concept of a heap or clustered index, no need for Covering Indexes • Implemented as Hash Indexes • Data Types: no LOBs, no identity columns, must have non-clustered index • No clustered indexes, no UNIQUE indexes (except for Primary) See prior slide – now supported in RTM , but only for IDENTITY (1, 1) • Maximum of 8 indexes (including primary key index), cannot specify one as a columnstore index • No DML triggers, no unique indexes, no PK/FK or check constraints • No support for MERGE statement, no support for compression • No VARCHAR(MAX), XML, CLR Data Types, no IDENTITY, no FKs, no CHECK constraints • Cannot TRUNCATE a memory optimized table (just DELETE all rows) • No locks, latches (no overhead for lock managers), logging, fragmentation • Atomic unit is the row. No data pages – access at the row level, not the page level • A more efficient row store, less overhead - max row size – 8060 bytes • Memory Limit: 256 GB in SQL 2014, 2 TB in SQL 2016 • Cannot be used with TDE (Transparent Data Encryption) • Memory optimized tables don’t support auto_update_statistics - update manually • UPDATE STATISTICS <Table. Name> WITH FULLSCAN, NORECOMPUTE
7 -Supported Data Types • Supported In-Memory Table Data Types • • • All integer types: tinyint, smallint, bigint All money types: money, smallmoney All floating types: float, real Bit (logical) Date/time types: datetime, smalldatetime, datetime 2, date, time Numeric and decimal types All non-LOB string types: char(n), varchar(n), nvarchar(n), sysname Non-LOB binary types: binary(n), varbinary(n) Uniqueidentifier 2016 supports varchar(max) • Two types Of Indexes: • HASH Indexes (good for single lookups, not very good for ranges, can’t use LIKE) • RANGE Indexes (similar to B-Tree), good for ranges
8 -Bucket Counts • Too few Hash Buckets will negatively impact performance • Too many Hash Buckets results in wasted memory • Again, general rule of thumb: bucket count = no more that twice the expected unique index values • If # of buckets is unknown for a particular column or if searches will be on a range of values, create a range index instead of a hash index. • In SQL 2014, if you need to adjust bucket count, must recreate table and index bucket count • In 2016, you can do an index rebuild • Good link from MSDN: • http: //msdn. microsoft. com/en-us/library/dn 465866(v=sql. 120). aspx
9 -Stored Procedures and In-Memory OLTP • Code that reads from/writes to Hekaton tables can be abstracted as stored procedures • Stored Procedures can be designated for native code (C language) compilation for additional performance • Can only be done on Hekaton tables – will not work on hybrid of Hekaton tables and non-Hekaton tables • Overall will increase performance Required syntax, reason for EXECUTE AS OWNER is so SQL Server doesn’t need to check user’s execution rights (can’t use EXECUTE AS CALLER)
10 -Limits of Native Compilation • Native Compilation will only work for Hekaton tables • Requires BIN 2 Collation Sequence for JOIN statements • No support for following statements/functionality: • Common table Expressions or EXISTS pattern with subqueries • UNION statement • DISTINCT • PIVOT • CURSORs • CASE statements • Many date functions and other functions not supported • The following query shows all table and stored procedure DLLs currently loaded in memory on the server: • SELECT name, description FROM sys. dm_os_loaded_modules WHERE description = 'XTP Native DLL' • For a full list, check here: • http: //technet. microsoft. com/en-us/library/dn 246937(v=sql. 120). aspx
11 -In-Memory OLTP and Table Types/TVP • Table Types – added in SQL Server 2008 • Allow you to pass tables as parameters to stored procedures • Often used in reporting applications in place of passing CSV lists, XML strings, or even using CURSOR objects (passing tables is better than splitting strings apart) • You can create a TABLE TYPE as an in-memory optimized table • Remember that any procedure you wish to designate for native compilation is subject to restricts in the last bullet point • Performance can be 2 x to 3 x faster – but again, mileage will vary • Good 3 -part link in the last slide under reading/references
11 -In-Memory OLTP and Table Types/TVP Step 1: Create the type (can be reused) Step 2: Use it in a stored procedure (note the read only) Step 3: Create an instance of the table type, populate, and pass it as a parameter to a stored procedure Can also be passed as ADO. NET datatable
11 -In-Memory OLTP and Table Types/TVP Step 1: Create the type (can be reused) Step 2: Use it in a stored procedure (note the read only) Step 3: Create an instance of the table type, populate, and pass it as a parameter to a stored procedure
12 -AMR – Analysis Migration Report • AMR Tool can profile whether your application can utilize Hekaton In-Memory OLTP tables • Won’t build migration code, but will help to determine if Hekaton will benefit you • With tool, you create a new database to hold AMR Data Collection Sets • Then generate some database activity, then go back to AMR Database, select “Reports…. Management Data Warehouse…then Transaction Performance Analysis Overview” • Will list recommended tables and projected performance gains • http: //sqlmag. com/sql-server-2014 -analysis-migrate-report-tool-amr • http: //Benjamin. Nevarez. com/2013/07/a-tour-of-the-hekaton-amr-tool
12 -AMR – Analysis Migrate Report Run twice…first to configure MDW, and then to Configure Data Collection
13 - Additional Reading/References • Very good You. Tube videos by EPC Group. net on SQL 2014 and In-Memory Databases • Good general reference by James Serra: • http: //www. toadworld. com/platforms/sql-server/b/weblog/archive/2014/01/07/sql-server-2014 -in-memory-oltp-hekaton-limitations. aspx • Good three-part blog post on SQLPerformance. com on TVPs: • • • http: //www. sqlperformance. com/2013/11/t-sql-queries/hekaton-twist http: //www. sqlperformance. com/2013/12/t-sql-queries/hekaton-twist-2 http: //www. sqlperformance. com/2014/01/t-sql-queries/hekaton-twist-3 • Bob Beauchemin on SQLSkill. com has a good post on Collation Sequences: • http: //www. sqlskills. com/blogs/bobb/thinking-about-hekaton-then-think-about-collations/ • Kalen Delaney’s White Paper on Hekaton: • http: //download. microsoft. com/download/F/5/0/F 5096 A 71 -3 C 31 -4 E 9 F-864 E-A 6 D 097 A 64805/SQL_Server_Hekaton_CTP 1_White_Paper. pdf • T-SQL constructs not supported in Hekaton: • http: //technet. microsoft. com/en-us/library/dn 246937(v=sql. 120). aspx • Hekaton and Bucket Counts: • • http: //www. sqlpassion. at/archive/2013/11/23/choose-your-hash-bucket-count-very-wisely-in-hekaton/ http: //msdn. microsoft. com/en-us/library/dn 465866(v=sql. 120). aspx • AMR (Analysis, Migration, and Reporting tool) Link: • http: //www. benjaminnevarez. com/2013/07/a-tour-of-the-hekaton-amr-tool/ • Bradley Ball’s Website: • http: //www. sqlballs. com/
- Slides: 24