Tag Archives: SQL Interesting Interview Questions

SQL Server 2014 In-Memory OLTP

12 Sunday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server 2014 In-Memory OLTP

Hekaton (also known as SQL Server In-Memory OLTP) is an in-memory database for OLTP workloads built into Microsoft SQL Server. Hekaton was designed in collaboration with Microsoft Research and was released in SQL Server 2014. Traditional RDBMS systems were designed when memory resources were expensive, and was optimized for disk storage. Hekaton is instead optimized for a working set stored entirely in main memory, but is still accessible via T-SQL like normal tables.

The SQL Server 2014 In-Memory OLTP engine (a.k.a. Hekaton) is a huge change for relational databases.

Now consider memory as your new disk

Notes-

• In SQL Server 2014 you can create memory-optimized tables and indexes. Everything will be present in the memory that why we need more memory.

• Here we can also have disk-based tables and indexes. (SQL 2012 & before.)

• A memory-optimized table is a one where SQL Server will always store in memory the whole table and its indexes.

• The storage of memory-optimized tables is different from conventional storage mechanism hence there are no data pages, and no extents. There are just “data rows,” written to memory.

• For memory-optimized tables, SQL Server will never have to acquire latches nor perform I/O in order to retrieve data.

• SQL Server guarantees ACID properties of all transactions without acquiring any locks. Therefore no transaction will ever be blocked, waiting to acquire a lock.

• In the order the transactions occurred, with each row containing an index “pointer” to the next row. All “I/O” is in-memory scanning of these structures.

• Many versions of the same row can coexist at any given time. This allows concurrent access of the same row, during data modifications, with SQL Server making available the row version relevant to each transaction according to the time the transaction started relative to the timestamp values stored in the header of each row version.

Now we will have a small demo of how we can create in memory DB and in memory table.

In memory DB should have MEMORY_OPTIMIZED_DATA filegroup. This filegroup is specially created to store the checkpoint files needed by SQL Server to recover the memory-optimized tables. The syntax for creating the filegroup is almost the same as for creating a regular filestream filegroup, We just have to mention the option CONTAINS MEMORY_OPTIMIZED_DATA.

—


--Create an In Memory Database

/*


FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA

*/

CREATE DATABASE Mem
ON PRIMARY
(
	NAME = Mem,
	FILENAME = 'C:\Mem.mdf', size=2000 MB
)
,	FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA
	(
		NAME = [Mem_dir],
		FILENAME = 'C:\Mem_dir'
	)
	LOG ON (name = [InMem_demo_log], Filename='c:\Mem.ldf', size=1000MB
)
GO

--CREATE an In Memory table


/*
There are some syntactical differences between creating a disk based table 
and a memory optimized table. All memory-optimized table should use the 
MEMORY_OPTIMIZED = ON clause as shown in the Create Table query below.
*/


CREATE TABLE TestMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 10000 ) 
  ,[Name] [int] NOT NULL  
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )


--Insert Data in a In Memory Table
DECLARE @cntr bigint = 1
WHILE @cntr<= 100000
BEGIN
	INSERT INTO dbo.TestMem(Id,Name) VALUES(@cntr,@cntr)
	SET @cntr+=1
END

--Time taken here is -- 15 Sec

—

Now since we heard that this memory things if faster than the old ways to doing things. Lets create a disk based table and insert the same no of rows and checkout the timings.

—-


CREATE TABLE TestNONMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED
  ,[Name] [int] NOT NULL  
)
GO

DECLARE @cntr1 bigint = 1
WHILE @cntr1 <= 100000
BEGIN
	INSERT INTO dbo.TestNONMem(Id,Name) 
        VALUES(@cntr1,@cntr1)
	SET @cntr1+=1
END

--Time taken -- 22 Sec

—

We can see that the Memory optimzed insertion took less time than the old version. See the attached snap below.

Comparisions

Keep Learning. We all need to learn.

http://MSBISkills.com

SQL SERVER 2014 New Features

08 Wednesday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER 2014 New Features

Recently this question has been asked to most of my friends. I have been thinking of writing about the new features from quite some time now. Finally yesterday I started. SO Here are the new features in SQL SERVER 2014.

Ref Links-

https://msdn.microsoft.com/en-us/library/bb510411.aspx

http://www.virtual-dba.com/platforms/ms-sql-server/sql-server-2014-features

This latest release of the SQL Server Database Engine introduces new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems. These are the areas in which the Database Engine has been enhanced.

1. In-Memory OLTP (In-Memory Optimization)

https://msdn.microsoft.com/en-us/library/dn535766.aspx

In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine. In-Memory OLTP is optimized for OLTP. Now there are two types of tables – Memory optimized table and Disk based tables.


--CREATE In MEMORY Table

CREATE TABLE TestMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED HASH
   WITH ( BUCKET_COUNT = 10000 )
  ,[Name] [int] NOT NULL
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

--

MEMORY_OPTIMIZED=ON indicates that the table type is memory-optimized

This feature is getting the most attention from SQL Server lovers, as it speeds up SQL Server response times for frequently-accessed data.

The new Memory-Optimized table feature of SQL Server 2014 allows us to create tables in memory for improved performance. A second copy of the table exists on disk and is updated shortly after transactions are updated. These tables are also versioned so that concurrent reads and writes are supported with reduced locking.

A non-durable, memory only table is also supported – but in case of a crash all data in these tables will be lost. This feature can improve performance substantially but there is a small window where a crash may cause data loss.

This feature is only available in Enterprise edition of SQL Server 2014 and require a 64 bit installation of SQL Server.

2. SQL Server Data Files in Windows Azure

This feature enables native support for SQL Server database files stored as Windows Azure Blobs. This feature allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

Here you can run our SQL instance locally, but place our data files into the Azure blobs. This can offer advantages for projects such as migrations, however there is a performance impact, every transaction will be pushed across the internet and this will impact your application.

This option can be used for archives that you have to keep because of some audit or government restriction, and you just want your data live and available for compliance.

This feature allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

3. Host a SQL Server Database in a Windows Azure Virtual Machine

We can now host a database from an instance of SQL Server in a Windows Azure Virtual Machine.

4. Backup and Restore Enhancements

  • SQL Server Backup to URL
  • SQL Server Managed Backup to Windows Azure
  • Encryption for Backups

For details please visit – https://msdn.microsoft.com/en-us/library/bb510411.aspx#Backup

5. New Design for Cardinality Estimation

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance.

The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads

How to use new Cardinality Estimation

 
--
ALTER DATABASE JyotiBubla
SET COMPATIBILITY_LEVEL = 110
GO
EXEC [dbo].[GetEmpDetails]
GO
--

6. Delayed Durability

SQL Server 2014 introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

How to Enable Delayed Durability for the database

--
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED
GO
--

7. Updateable Column Store Indexes

Columnstore indexes can be updated with SQL SERVER 2014. This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables. Not only that, but updateability also means you may be able to look at columnstore indexes for certain OLTP applications.

Note – The catch is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren’t supported.

Additional compression of Columnstore indexes is now available by using Alter Index Rebuild with the COLUMNSTORE_ARCHIVE option. The use of Columnstore indexes requires Enterprise Edition of SQL Server 2014.


--

CREATE CLUSTERED COLUMNSTORE INDEX Ix_CstoreEMP ON dbo.EMP

INSERT T1
SELECT 'Avika Khowal' , 'Fin' , 'Address - 1' , 122001

--

8. Incremental Statistics & Partition Enhancement

Now we can create unique statistics for each partition by using the INCREMENTAL option. This allows for improved performance when distribution of data varies between partitions. Partitions are only supported on Enterprise Edition of SQL Server 2014.

Whenever statistics need to be rebuilt, you have to update all the rows. This means if a table has 200 million rows and only 40 million changes need to update, all 200 million rows will be updated in order to pick up those changes.

INCREMENTAL = { ON | OFF }

When ON, the statistics created are per partition statistics. When OFF, stats are combined for all partitions. The default is OFF.

--

CREATE INDEX Ix_ID ON TestMem(ID)
WITH ( ONLINE = ON , Statistics_Incremental = ON ) 

--

CREATE STATISTICS  statsMem ON TestMem(ID)
WITH FULLSCAN, INCREMENTAL = ON

--

9. Buffer Pool Extension to Solid State Drives (SSDs). 

The Buffer Pool Extension 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.

Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they’re needed again, they’ll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.

http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html#slide10

9. Managing Locks in Online Index

In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.

http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html#slide10

10. Always On Improvements

SQL Server 2014 contains the following enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups:

•An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups. For more information, see Use the Add Azure Replica Wizard (SQL Server).
•The maximum number of secondary replicas is increased from 4 to 8.
•When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
•Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks. For more information, see AlwaysOn Failover Cluster Instances (SQL Server).
•A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
•The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.

11. Resource Governor Enhancements

The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool.

I will explain all the above new features in detail in coming weeks.

Keep Learning

MSBISkills.com