Feeds:
Posts
Comments

Posts Tagged ‘memory optimized table’

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.

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 »

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 »