Rambling thoughts

All About My thoughts…..

My Experiments with SQL 2014 July 27, 2013

Filed under: SQl Server,SQL Server 2014 — KrazySQL @ 7:47 am

Let me share my experiences on the first look of this product

Installation

  • As all of us already know we can’t have this CTP where old versions are already sitting, I too had it in new VM with WS 2012 which have .net 4.0 installed
  • But I don’t think SQL Server cannot survive without .net 3.5 framework, so I had to install and then there were no other identified problems

Installation is pretty similar as 2012 ones, no major changes.

Hekaton:

First thing I tried immediately after installing SQL Server was IN-Memory OLTP , Before I elaborate more let me thank Kalen Delaney for her 1st white paper on In-Memory OLTP from where many things were uncovered

Let’s get going

As we all already know about what the technology is , here is what I have

I first created a database which is In- memory aware.. Remember we need to add separate file group which is memory optimized aware to the existing database or while creating a new database.

CREATE DATABASE HEKATON

ON PRIMARY (Name =HEKATON_DATA,

FILENAME=’C:\MSSQL\hetaon_data.mdf’ ,size=500mb),

filegroup memoryoptimised contains MEMORY_OPTIMIZED_DATA

(Name=inmemory_l,

filename= ‘C: \MSSQL\hekaton_dir’ )

(Name=inmemory_2,

filename= ‘C: \MSSQL\hekaton_dir2′)

log on(name=hekaton_log,filename=’C:\MSSQL\hekaton_log.ldf’,size=500MB)

collate latinl_general_100_bin2

If you observe we haven’t specified .ndf at the end of secondary file in the file group memoryoptimised and the reason is SQL Server stores Memory Optimized data in File Streams rather than normal SQL Server files, we will discuss the reason why it stores in this way.

Next- let me create an Inmemory table

CREATE table inmemoryl

(id int not null primary key nonclustered hash with ( bucket_count=1024),

name varchar(30) null,

city varchar(20) null,

lastmodified datetime not null,

) with memory_optimized=on,durability=schema_and_data

Following are the points of interest from above Create SCRIPT

  • We should specify whether the table is of INMEMORY or not by Memory_optimized option
  • In memory supports two types of durability
    • Schema Only : After a restart or Crash of the system we  will have only SCHEMA of the tables we created in memory
    • Schema and Data: Here Hekaton engine retains both Data of course with Schema
    • We are subjected to create only Non-clustered Index in INMemory (bucket count and why only NCI we will have it in separate blog post)

Let’s input some junk data in to table

declare @i int

set @i=l000000

while (l<@i)

begin

insert into inmemoryl values(@i+1, ‘Aditya’, ‘Hyderabad’ ,getdate())

set @i=@i-l

end

To Compare with Native tables let us create one native table with same syntax and same data

CREATE table outmemoryl

(id int not null primary key nonclustered,

name varchar(30) null,

city varchar(20) null,

lastmodified datetime not null,

)

I am executing following simple queries and let us see what will be the plans look like

select * from inmemoryl

inmemoryqp

select * from outmemoryl

outmemoryqp

  • We can see that Inmemory tables have done an Index Scan and outmemory is atable scan
  • We know why Outmemory does and the reason is because of non presence of clustered index
  • But Inmemory table does this scan because the main data is stored in Index pages itself.

Also I took the output of exec_requests while executing above statements to prove that physical reads and writes from Inmemory are 0 while out memory would be obviously some value.

 Output for Outmemory1 select

select writes,reads,logical_reads,* from sys.dm_exec_requests where session_id=52

outmemoryselect

Output for inmemory1 select

inmemoryselect

 

Yayyy!! We now have data inmemory… So what if I join two inmemory tables would I be having any physical reads… let us take a look

select * from inmemoryl i

inner join inmemory2 i2

on i.id=i2.id

joinexec

Ooops!! I have huge reads!!! And when I went to resource monitor I see tempdb.mdf having more IOPS..

taskamanger

Yes.. The reason is Worktable… No matter what kind of technology I use, if your query went wrong, your performance ought to be wrong.

For this Blogpost we will do a last test..

While this is all happening I want to restart the SQL Server and see how it behaves (remember I am not doing any writes or making buffers dirty anywhere)

So I issued

shutdown with nowait

And started SQL Server …. Now When I am trying to see my object explorer I end up in seeing

error1

And after some time I got hold of SQL Server and this is what I saw in database list and errorlog

errorlog

recovery

From this We can say that the Inmemory data that is loaded in to buffer pool is loaded at the point of startup/recovery

I will sign off here and in next blog post next week, we will discuss

  • Checkpoint process
  • Index storage
  • Bufferpool extension

Thank you so much for reading this blog and let me know your suggestions @ adityafeb22@gmail.com and your comments below :)… Happy Learning

About these ads
 

8 Responses to “My Experiments with SQL 2014”

  1. vamsi Says:

    It’s very thoughtful to put your experience into words in a blog. I almost have felt like 2014 is no difficult than 2012… Thanks Aditya… looking forward to see your next blog.

    Also, for the benefit of starters like me, could you please come up with advantages of 2014 over 2012 that you found from your so far experience? (obviously other than those which I can google out ;) )

    Regards,
    Vamsi Kapa

  2. manu0417 Says:

    Good description and looking forward for more knowledge sharing… :)

  3. Sribhagat Says:

    very nice post…..
    waiting for more experiments…..:)
    One Doubt: Can there be only a partition of a table inMemory when needed??

    • Thanks for the feedback Bhagat… We cant take a partition of table to inmemory as of now.. we need to specify the table as inmemory specific if we want to leverage this.
      But we can join the tables with normal tables as in a disk table can be joined with inmemory table. Here SQL server uses Query Interop.

  4. Yamini Says:

    very well explained Aditya! :)
    I do not have much knowledge on intricacies of SQL server..but still u made it so simple that i could understand it with ease! Thank you!
    And keep posting!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 668 other followers