Whether you’ve been working with SQL servers for years or are new to the game there are several updates and new features that have been introduced in 2014 that are worth reviewing.
For starters, the Hekaton in-memory capabilities are being designed to complement the existing in-memory data-warehousing and business-intelligence (BI) capabilities already in SQL Server.
The Hekaton technology will continue to work with traditional SQL Server tables so that users will see performance gains even on existing hardware.
In-memory functions:
- Supports OLTP applications
- Improves performance 10x
- Improves scalability 5x
- Minimizes the latency it takes to retrieve data
Most improve fill on short transactions / high degree of concurrent transactions with many connections
For existing databases using previous versions of SQL you will need:
CREATE TABLE and CREATE PROC statements. Changes have been incorporated into the database engine, so in-memory built-in objects are processed to take advantage of memory processing instead of using the b-tree indexes and buffer pools.
To determine how much memory a given memory-optimized table uses, run the following query:
select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats
CREATE DATABASE RYLTECH
GO
ALTER DATABASE Ryltech ADD
FILEGROUP InMemory CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE RYLTECH ADD
FILE (name=’InMemory_1′, filename=’H:sqldataInMemory_1′)
TO FILEGROUP InMemory
GO
ALTER DATABASE RYLTECH SET
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
USE RYLTECH
GO
CREATE TABLE AIMBETTER (
ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
KEYID INT NOT NULL INDEX IX_AIMBETTER_KEYID NONCLUSTERED
HASH WITH (BUCKET_COUNT=1000000),
KEYDATE DATETIME2 NOT NULL,
KEYVALUE INT
) WITH (MEMORY_OPTIMIZED=ON)
GO
On these blog you can get more information on in-memory including what it does and does not support
msdn.microsoft.com/en-us/library/dn133186.aspx
msdn.microsoft.com/en-us/library/dn133079.aspx
blog.sqlauthority.com/2014/04/30/sql-server-faster-application-performance-with-in-memory-oltp-microsoft-sql-server-2014-and-safepeak-dynamic-caching/
RYLTECH uses 2014 with the new OS & SQL Monitoring Tool
Ryltech.co.il
Aimbetter.com