Feeds:
Posts
Comments

Archive for the ‘SQL SERVER HEKATON’ Category

How to get list of all tables’ and its index sizes along with row count looks very simple ? We do have the solution for it. Either you can use sp_spaceused or you can use one of my own solutions.

But things became strange when I tried both solutions on SQL Server 2014 having memory optimized table and both solutions return zero for memory optimized tables. Given below is the script and its output in SQL Server 2014.

USE hkNorthwind
GO
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
GO

table and index size of memory optimized table.1.1

As you can see in the above image, all memory optimized tables showing zero result. Oopsssssss. So what is the solution ?
I started my research and developed a solution . Given below is the script.

SOLUTION :

USE hkNorthwind
GO
DECLARE @PageSize float
SELECT @PageSize=v.low/1024.0 FROM MASTER.dbo.spt_values v WHERE v.number=1 and v.type='E'

SELECT
  OBJECT_SCHEMA_NAME(tbl.object_id) as [Schema Name]
, OBJECT_NAME(tbl.object_id) as [Table Name]
, CASE
  WHEN (tbl.is_memory_optimized=0) THEN
		ROUND(TRY_CONVERT(float,ISNULL((SELECT @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
		FROM sys.indexes as i
		JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
		JOIN sys.allocation_units as a ON a.container_id = p.partition_id
		WHERE i.object_id = tbl.object_id),0.0))/1024,3)
  ELSE
		Round(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_table_kb])
		FROM [sys].[dm_db_xtp_table_memory_stats] tms
		WHERE tms.object_id = tbl.object_id), 0.0))/1024,3)
		END
 as [DataSpaceUsed (In Mbs)]
, (CASE
  WHEN (tbl.is_memory_optimized=0) THEN
    ROUND(TRY_CONVERT(float,ISNULL((
    (SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id)
    + ( CASE (SELECT count(*) FROM sys.internal_tables WHERE parent_id = tbl.object_id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222))
        WHEN 0 THEN 0
        ELSE (
            SELECT sum(p.used_page_count)
            FROM sys.dm_db_partition_stats p, sys.internal_tables it
            WHERE it.parent_id = tbl.object_id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id)
        END )
    - (SELECT SUM (CASE WHEN(index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)
        FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id)
    ) * @PageSize, 0.0) )/1024,3)
 ELSE
    ROUND(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_indexes_kb])
    FROM [sys].[dm_db_xtp_table_memory_stats] tms
    WHERE tms.object_id = tbl.object_id), 0.0)) /1024,3)
END)
as [IndexSpaceUsed (In Mbs)]

,(CASE
WHEN (tbl.is_memory_optimized=0) THEN
OBJECTPROPERTYEX(OBJECT_ID, N'Cardinality')
ELSE
(
SELECT ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes as Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2 AND Ind.object_id=tbl.object_id
)
END) [Total No of Rows]

FROM
sys.tables as tbl
--OUPUT

table and index size of memory optimized table.1.2

As you can see in the above script (solution), SQL Server 2014 handles disk based table and memory optimized table space, index space and row count separately. So you need to calculate it separately.

Let me know if you came across the same situation and its solution.

Advertisements

Read Full Post »

I have written multiple articles on memory optimized table and its handling. I was also working on its core area that is its performance. I continued with my research to see whether it really improves the performance or not. And as per my research, I found a massive improvement in the performance (except few limitations).

Before proceeding with the performance, I would like to create a sample to compare the performance between disk based & memory optimized table.

  • Sample For Memory Optimized table :

Let me create sample tables for memory optimized table and insert bulk data in it to measure the performance.
Given blow is the script.

--Given below scripts are compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--Create a memory optimized table
CREATE TABLE [dbo].[tbl_product_Master_MO]
(
	[Product ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
        WITH (BUCKET_COUNT = 100000),
	[Product Name] [nvarchar](100) NULL,
	[Creation Datetime] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
--Insert 65536 records into the memory optimized table
--This script may take few minutes to insert records.

USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_MO
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5

---Create native compiled procedure to give boost to memory optimized table.
CREATE PROCEDURE dbo.usp_product_master
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT [Product ID],[Product Name],[Creation Datetime]
FROM dbo.[tbl_product_Master_MO]

END
GO
  • Sample For Disk Based table :

Let me create sample tables for disk based table and insert bulk data in it.
Given blow is the script.

--Create a disk based table (Normal table)
USE [hkNorthwind]
GO
CREATE TABLE [dbo].[tbl_product_Master_DB](
	[Product ID] [int] NOT NULL,
	[Product Name] [nvarchar](100) NULL,
	[Creation Datetime] [datetime] NULL
) ON [PRIMARY]
GO

--Insert 65536 records into the disk based table.
--This script may take few minutes to insert records.
USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_DB
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5
GO

Let’s proceed with the different categories of performance comparison between normal table and memory optimized table.

  • Query Cost related to the Batch :

Let us start with the query cost related to the batch.
Given below is the script and its output.
Note : Given below results are tested on 300K~ records.

USE hkNorthwind
GO
 --Memory optimzed table
 Select * from [tbl_product_Master_MO]

 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
 --OUTPUT

diskbased_vs_memory_optimized.1.1

As you can see that memory optimized table only took 7% but on the other hand disk based table took 93% for the same task.

  • Time Statistics

Let’s turn on the time statistics and view the performance comparison.
Given below is the script


SET STATISTICS TIME ON
USE hkNorthwind
GO
---Given below is the Memory optimized native compiled stored procedure.
--This stored procedure we used to browse memory optimized table.
--It will give boost to memory optimized table performance.
EXEC usp_product_master
 GO

 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
SET STATISTICS TIME OFF
 --OUTPUT

–For memory optimized
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 1706 ms.

— For disk based table.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 3251 ms.

  • IO Statistics 

Let’s turn on the IO statistics and you will be amazed to view that there is NO IO involvement in memory optimized table
and due to this, it produces a high level performance.
Given below is the script.

SET STATISTICS IO ON
USE hkNorthwind
GO
 --Memory optimzed table
 Select * from [tbl_product_Master_MO]
GO
 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
SET STATISTICS IO OFF
 --OUTPUT

–For memory optimized query
No Result.

— For disk based table query.
(327680 row(s) affected)
Table ‘tbl_product_Master_DB’. Scan count 1, logical reads 1962, physical reads 0, read-ahead reads 1584, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

diskbased_vs_memory_optimized.1.2

Conclusion :
I am very much impressed with the memory optimized table performance. But still it requires some improvement regarding BLOB data types & validations.
Given below is the consolidated summary that will give you a glance performance review of disk based vs memory optimized table.

S.No

Type

Memory Optimized Table

Disk Based Table

1

Query Cost related to the Batch

7%

93%

2

Statistics Time

93 ms

234 ms

3

Statistics IO

NO IO involvement

1962 read

Let me know about your test results.

Read Full Post »

Recently, I was doing some research on memory optimized table and I came across a situation where I needed to keep some temporary data in the tables. So I thought of creating temporary table. Now I have two choices, either to go for disk based temporary table or to go for memory optimized temporary table.
I decided to create a memory optimized temporary table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE #tbl_Product_Master
--GO
CREATE TABLE #tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 92, Line 11
The feature ‘temporary tables’ is not supported with memory optimized tables.

Opssssssss error….

If I am not wrong, you cannot create a memory optimized temporary table because this feature is not supported by memory optimized table. So I started my research and tried to find out the work around that can help me create a memory optimized temporary table. Finally I came to know how to create a temporary table in memory optimized table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)

GO
--OUTPUT

As you can see, I just added one argument (DURABILITY = SCHEMA_ONLY) at the time of memory optimized table and because of this argument, the table is converted to a temporary table.

Let me know if you came across this issue and its solution.

Reference : MSDN

Read Full Post »

Today, I came across a situation where I had to get the total number of records in each Memory optimized table in a particular database.
I remember developing a solution earlier (How to get the number of records in a disk based tables. ). I tried this solution on memory optimized table but it did not work. Ooopssssssss.
Given below is the script that we use to get the number of records in a disk based table. I included the table type in the query to demonstrate the problem.

USE hkNorthwind
GO
SELECT
  OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
,(CASE WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
  And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0
  THEN 'User Table'
  WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
  And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1
  THEN 'Memory Optimized Table'
  ELSE NULL
  END) As [Table Type]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
OR
OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1)
GROUP BY OBJECT_ID
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

memory optimized table record count1.1

As you can see in the above example, it only returned the total number of records in disk based tables and returned zero for memory optimized table. So I started doing my research and found the solution. Given below is the script.

SOLUTION :

USE hkNorthwind
GO
SELECT
  OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0)
GROUP BY OBJECT_ID

UNION

SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS [Schema Name]
,OBJECT_NAME(Ind.OBJECT_ID) As [Table Name]
,ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes AS Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

memory_optimized table_record_count.1.2

Note : Before executing the above query make sure that all your tables’ stats are up to date.

Let me know if you came across this issue and its solution.

Read Full Post »

The operation ‘ALTER TABLE’ is not supported with memory optimized tables is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

Let’s discuss this in detail:
Message Number: 10770

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Given below is the script that I tried to execute but it resulted with the following error :

ALTER TABLE tbl_sample ADD [CreationDateTime] datetime NULL
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables do not support alter table statement. So, instead of alter, you need to drop and create the memory optimized tables.

Lets rewrite the above statement using drop and create. Given below is the script.

USE Sample_DB
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL,
[CreationDateTime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Read Full Post »

Statistics is very helpful when it comes to performance because query optimizer uses these statistics to create query plans to improve query performance. I recently implemented the statistics on memory optimized table and given below are my findings.

S.No

Disk based tables

Memory Optimized tables

1

Statistics are updated automatically.

Statistics are NOT  updated automatically

2

It uses by default sampled statistics.

It has no default statistics, you must specify fullscan option.

3

It supports sampled statistics & fullscan options.

It supports ONLY fullscan option.

Following are the steps you must be cognizant of while implementing statistics in memory optimized tables :

  1. First of all create memory optimized tables and indexes.
  2. After that, insert (update, delete) data into the memory optimized tables.
  3. Once you are done with the data manipulation, update statistics on the memory optimized tables. (Do not do this step in the peak hour)
  4. The last step is to create natively compiled stored procedures that access the memory tables.

Given below is the script to update memory optimized table statistics.

USE hkNorthwind
GO
UPDATE STATISTICS dbo.tbl_Product_Master WITH FULLSCAN, NORECOMPUTE

Read Full Post »

Memory optimized table is a new concept to maintain records in table with high performance. I already discussed this new type of table earlier in my article. So, I kept on researching for this new feature and came across an issue. The issue is, I once created a memory optimized table and inserted few records in it and did other research work on the test database and once I was done, I just shut down my machine. Next morning when I tuned on my machine, I found no data in the memory optimized table. First I thought I deleted the data by mistake and forgot it. So I repeated the process but the following morning same thing happened to me again. I could not comprehend what on earth was going on !!  I was puzzled and started doing my research and finally resolved it.

Let me explain it step by step. (Never apply these steps on production database)

Step 1 :
Create a memory optimized table in a memory optimized table enabled database.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

Step 2 :
Insert records in the memory optimized table.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
INSERT INTO tbl_Product_Master VALUES (1, 'SQL Server 2012',GETDATE())
INSERT INTO tbl_Product_Master VALUES (2, 'SQL Server 2014',GETDATE())
GO

Step 3 :
Browse the memory optimized table.

--This script is compatible with SQL Server 2005 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.1

Step 4 :
Now either restart the database server or shutdown your test machine and turn it on.
Never do this exercise on production server without taking proper precautions.
Browse the table again.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.2

Step 5 :
Opsssss, now you can see the data gone and the reason behind this is, just a parameter that we passed at the time of memory optimized table creation and this is DURABILITY because if you make DURABILITY = SCHEMA_ONLY the schema will be durable but not the data. Once you restart the database server, you will lose your data. Remember, whenever you create a memory optimize table, to  keep the durable data (permanent data). Always use WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA).

Note : If you create the memory optimized table without passing DURABILITY parameter, by default it will be DURABILITY = SCHEMA_AND_DATA.

Lets recreate the memory optimize table. Given below is the script. Do the same process again without step 1 and your data will remain with you.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Please let me know if you come across this issue and its resolution.

Reference : MSDN

Read Full Post »

Older Posts »