In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014

Today, Dussehra Or Vijayadasami is considered to be the most auspicious day to start learning something new.Let us take this opportunity to start learning about InMemory OLTP in SQL Server.

In-Memory OLTP is one of the most fascinating features in SQL Server 2014.

First off, if you are going to work with InMemory OLTP, then you need to know about Memory optimized data Filegroup. In short, for any database, where memory optimized table(In Memory tables) needs to be created, there should be provided with separate FileGroup called MEMORY_OPTIMIZED_DATA. This filegroup is extremely important for the feature to store the Checkpoint information and delta files for SQL Server during the disaster(Any restart/crash).Let us look at the syntax to create the database and filegroup.

Syntax:


CREATE DATABASE [TestDB]
 ON  PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\data\TestDB.mdf' )
 LOG ON ( NAME = N'ManTest_log', FILENAME = N'D:\data\TestDB_log.ldf')
    
ALTER DATABASE [TestDB] 
    ADD FILEGROUP TestDB_MO CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE TestDB
	ADD FILE ( NAME = TestDB_MO , FILENAME = N'D:\data\Datasample_database_1') 
TO FILEGROUP TestDB_MO 

If you have not created memory optimized data file group and you try to create a memory optimized table, you may be facing the below error message.

Msg 41337, Level 16, State 0, Line 9
Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.

Another important point is you can only create MEMORY_OPTIMIZED_DATA filegroup only in 64 bit SQL Server. Otherwise, you may end up with the below error message

Msg 41356, Level 15, State 1, Line 1
Filegroups with MEMORY_OPTIMIZED_DATA can only be created in 64-bit installations of SQL Server.

As the error message clearly suggests In-Memory OLTP will only support in 64 bit SQL Server.
You can use @@VERSION global variable to know about your SQL Server bit version.

Once you created the database successfully, you will be able to create Memory optimized table as below:


CREATE TABLE dbo.SampleTable
(
   id INT NOT NULL,
   item_id INT NOT NULL,
   CONSTRAINT PK_SampleTable PRIMARY KEY NONCLUSTERED (id,item_id)
) WITH (MEMORY_OPTIMIZED=ON)

Now, we covered very basic requirements for In Memory OLTP concept in SQL Server.To summarize the above

1. make sure you have installed SQL Server 2014 with 64 bit
2. create a memory optimized data filegroup for the database

Next post, We will cover more on this topic…until then happy Dussehra.

Advertisements

Workload Group Stats counters and SQL Server Edition

Problem Statement

During one of our performance evaluation, we wanted to assess the resource utilization for one of servers using the Perfmon Counters.

If you are not familiar with perfmon counters, please have a look at the below link for the improtance and significance of perfmon counter in performance evaluation.

https://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx

In our assessment, in addition to the usual counters, we wanted to collect Workload information from SQL Server.Hence, we enabled the below counters:


SQLServer:Workload Group Stats/ CPU Usage %
SQLServer:Resource Pool Stats/ CPU Usage %

After configuring the above counters in the perfmon, it is observed that the above counters are not collecting the information from the server. However, when we configured it locally, it was collecting the counter values.

These counter values can also be queried in SSMS using the below queries:


Select * From [master].[sys].[dm_os_performance_counters]
Where 
(object_name ='SQLServer:Workload Group Stats' and counter_name ='CPU Usage %') OR
(object_name ='SQLServer:Resource Pool Stat' and counter_name ='CPU Usage %')

Cause/Reason:

On further analysis, we identified that SQLServer:Workload Group Stats object contains performance counters that report information about Resource Governor workload group statistics.

As Resource Governor can only be supported for Datacenter, Enterprise, Developer and Evaluation editions, those counters can not be collected information on any other editions, eventually we have Standard edition in our environment.

Lesson Learned:

When you configure perfmon counters, always check the associated features and its supporting version.

How to Identify Parameter information of a procedure in SQL Server

Problem statement:

Of late, one of my colleague was looking for a script to identify the procedures without any parameters.

Script:

The below script will identify the procedures without any parameter.


SELECT SCHEMA_NAME(A.SCHEMA_ID) AS SCHEMA_NAME
    ,A.NAME AS OBJECT_NAME
    ,A.TYPE_DESC,A.TYPE
FROM 
SYS.OBJECTS A
LEFT JOIN (SELECT O.OBJECT_ID FROM SYS.OBJECTS AS O 
INNER JOIN SYS.PARAMETERS AS P ON O.OBJECT_ID = P.OBJECT_ID AND O.TYPE='P') O ON A.OBJECT_ID= O.OBJECT_ID
WHERE O.OBJECT_ID IS NULL AND A.TYPE='P'
ORDER BY SCHEMA_NAME, OBJECT_NAME;

For any chance, if you are looking for identifying information about parameters for procedures, you can use the below script:



SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id and o.type='p'
/*************SEARCH SCENARIO*************/
--WHERE o.object_id = OBJECT_ID('schemaname.procedurename')
/*************SEARCH SCENARIO*************/
ORDER BY schema_name, object_name, p.parameter_id;