• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Tag Archives: SQL Interesting Interview Questions

SQL Server 2014 In-Memory OLTP

12 Sunday Apr 2015

Posted by Pawan Kumar Khowal in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

In memory oltp, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, puzzle sql developer, QL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL 2014 new features, SQL In memory oltp, SQL Interesting Interview Questions, SQL Interview Questions, SQL New Features, SQL Server, SQL SERVER 2014, SQL SERVER 2014 New Features, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Tricky question, SQLSERVER, Tricky Questions, TSQL, TSQL Interview questions, TSQL Queries


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

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL SERVER 2014 New Features

08 Wednesday Apr 2015

Posted by Pawan Kumar Khowal in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, puzzle sql developer, QL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Interesting Interview Questions, SQL Interview Questions, SQL New Features, SQL Server, SQL SERVER 2014 New Features, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Tricky question, SQLSERVER, Tricky Questions, TSQL, TSQL Interview questions, TSQL Queries


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

 

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Blog Stats

  • 1,097,352 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

June 2023
M T W T F S S
 1234
567891011
12131415161718
19202122232425
2627282930  
« Oct    

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.