Tag Archives: hekaton

Memory Optimization Advisor – SQL Server 2014 CTP2

Posted on by

SQLServer 2014 CTP2 came with an inbuilt tool called Memory Optimization Advisor which will help you in migrating your normal tables to memory optimized tables.

Where can I find this tool ?

All you need to do is right click the table you want to migrate, and choose the option Memory Optimization Advisor.

MOA1

The tool will launch with a detailed description of what its capable of

MOA2

I decided to play around with this tool, and here are my observations –

Note – This is still CTP, so things can change during RTM/GA phase.

I started of with a normal table named Employee which has 3 Col and 3 rows data on it. Nothing big, pretty simple.

Launched the advisor, and the initial checks were all green. [Be sure to carefully analyze the checks, they are all interesting.]

MOA4

The wizard also has the ability to export a report (Who doesn’t like a report these days !)

I clicked next to proceed, and the wizard gave me some information about the limitations of memory optimized object, and a link which will explain the limitations in detail.

MOA5

I liked these warnings, because its telling me well in advance about the limitations so that I will be more careful on what I’m up to.(Everything has a cost associated with it !)

Next up is some interesting stuff. The wizard is forcing me to select the options for memory optimized objects. I have the option to mention memory optimized file group,name and the file path.

I also have the option to re-name the original table, copy data from the original table to the memory optimized table and a check box to mention if the table needs to be moved with no data durability.(Default being both schema/data durability).

I decided to go with all defaults as this was a test case.

MOA6

One of the other cool option which the above wizard window gave is this value –

MOA7

I presume that this value will be the cost of size in memory which will be needed when the table is moved as a memory optimized object. I might not be right at this point, but I will update this post in case this is not true.

Next screen in the wizard talks about primary key and index creation. I decided to make column ID as the primary key with a NON-CLUSTERED HASH Index and a bucket count of 1024.

MOA8

The final screen provided me a summary of my selections.

MOA9

Yet another cool feature in the wizard is that it allowed me to script everything before I finalize my selections.

I decided to hit Migrate, and wanted to see how it goes.

Viola,all clean and green !

MOA10

The wizard was smart enough to rename the old table,and created a memory optimized one for me.

MOA11

MOA12

I had scripted out everything before hitting migrate and that file looked like this –

MOA12_1

Conclusion

This is a very neat feel good to have tool and those warnings and pre-checks will definitely help users to streamline issues well before they are IN MEMORY !

Thanks for reading and keep watching this space for more !

Advertisement

Monitoring Memory Usage of Memory Optimized Objects – SQL Server 2014

Posted on by

Starting SQL 2014 monitoring memory usage of the memory optimized objects is super important to ensure that your instance don’t run out of memory and cause real bad situations.

One way of ensuring that the memory optimized objects wont utilize a certain amount of memory is by setting up resource governor. This is a great level of control when you have multiple databases running on the same instance and you don’t want memory optimized tables eating up the whole memory.

Is there an easy way to get a quick overview of memory usage of the memory optimized tables in SQL2014?

Yes, you have a real good SSMS report just for this purpose. This one report utilizes DMVs under the hood and provides you with some valuable information.

InmemOLTP Reports

Lets do a quick walk through of what this report gives –

InmemOLTP Reports1

At any point of time I will be interested to see the usage of Table Used Memory counter.

Index Used Memory is based on the bucket count which you mention during non clustered hash index creation.

The query which the report runs under the hood to give you the usage related value is –

SELECT t.object_id, t.name, 
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_unused_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_unused_memory_in_mb
FROM sys.tables t JOIN sys.dm_db_xtp_table_memory_stats TMS 
ON (t.object_id = TMS.object_id)

This query leverages the power of a new DMV dm_db_xtp_table_memory_stats

Conclusion

SSMS reports are great ways to get a quick overview of what is happening, and I expect more and more powerful reports getting incorporated to SQL 2014 during the coming days.

Thanks for reading and keep watching this space for more.

SQL Server 2014 – A new and exciting journey !!! “Into the cloud”

Posted on by

Today Microsoft announced their next major version of SQLServer, SQL Server 2014 at Tech-Ed NA 2013.

This version of SQLServer is indeed a reflection of Microsoft’s vision towards the future computing. A bright and colorful “In to the Cloud” future !

The journey of SQLServer from SQL2000 – SQL2014 is just amazing. On a related note,Its worth reading this post by Quentin Clark which explains this journey with the help of a neat diagram.

So when can I start exploring the new features? Where can I download the same to evaluate it ?

Thinking boy

Here is the answer for you  –

Go to http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx  and choose option Get-Notified >

You will be asked to enter few details and you can sit and wait for that “email” which will eventually allow you to download the bits and do some testing.

What’s new in SQL2014 that I am excited about ? Lets do a quick walk through of my favorite features –

Note- This is not an extensive list of features which SQL2014 will provide you.I will write about that altogether in a different post.These are some of the features which I really like about.
  • In Memory OLTP(Code named Hekaton) – 

This is one of my favorite features that I’m really looking forward to work with. This feature will be called as SQLServer In-Memory OLTP Engine for SQL2014.

SQLServer In-Memory OLTP Engine will boost  OLTP workloads to perform remarkably better. The beauty of this feature is you can now pick tables and define it as memory optimized and these tables are fully transactional.

Anytime I will suggest you to download and read the white paper ‘SQL Server In-Memory OLTP Project “Hekaton” Internals Overview for CTP1’ by Kalen Delaney(B/T) to understand this feature really well.

  • 8 Readable Secondaries –

With SQL2014 we will have the ability to add up-to 8 readable secondaries for our read-only workload(Mainly reporting queries).

  • Always on to Windows Azure Virtual machine – 

This is one feature which I’m really looking forward. This will enable us to add a secondary replica directly in Windows Azure Virtual Machine. This is one feature which the CIOs would love, as you pay per use for Windows Azure VMs.

  • Buffer pool extension using SSDs – 

This can improve query performance by allowing the use of non-volatile solid-state drives to reduce SQLServer memory pressure with no risk of data loss.

Brent Ozar(B/T) has a great post explaining this and you can read it here.  He also have provided a great deal of information about other SQL2014 features too. Always a great read.

  • Performance Data Collector – 

I’m also looking forward for the enhancements which Performance Data Collector module will come with. The details are not yet out on what is new, but I definitely hope that there will be some good changes here and there will be deep integration with SSMS for this module.

Conclusion

With SQL2014 I’m pretty sure that your move towards the cloud will be more easy and a nice experience and I’m really looking forward for it.

Thanks for reading and keep watching this space for more(Lot of SQL2014 for sure !)