Wednesday, November 18, 2015

In-Memory OLTP Table Checkpoint Processes Performance Comparison

Checkpoint processes and other internal processes have undergone major changes for In Memory OLTP tables and transactions. The following article of mine compares the performance of checkpoint processes in disk based ( traditional ) tables against In memory tables. I understand that, I have not written about checkpoint processes of In Memory tables here. Will be doing soon. Anyways, sharing the article below. Read n comment if interested

http://www.sqlservercentral.com/articles/SQL+2014/131964/

Friday, November 6, 2015

Hekaton Part 11 :- Finding row count in memory table

Finding a row count in a "in memory" Hekaton table can be a tricky task as the regular commands and DMVs don't give the desired result.

  • sp_spaceused - doesn't work and returns a blank result
  • sys.dm_db_partition_stats - doesn't track in memory tables.

So the  option we are left with is
    
      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.dm_db_xtp_index_stats AS Ind
      CROSS APPLY sys.dm_db_stats_properties
      (Ind.object_id,Ind.index_id)
      WHERE Ind.index_id =2
      ORDER BY OBJECT_NAME(Ind.OBJECT_ID)
 
However, please note that the above query will give correct row counts if and only if statistics are updated for the table. For in memory tables, statistics are not automatically updated manually and hence will have to be updated  before one attempts to find the row count. So, please refer to the query below, to update the statistics for in memory table alone.
 
 
DECLARE @id int,@rowcnt int
DECLARE @in_mem_tbl_name varchar(500)
SET @id = 1
 
CREATE TABLE #in_memory_tbl(id int identity(1,1),in_mem_tbl_name varchar(500))
 
INSERT INTO #in_memory_tbl(in_mem_tbl_name)
SELECT object_name(object_id) FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id > 0
SET @rowcnt = @@ROWCOUNT
WHILE @id <= @rowcnt
BEGIN
 
 
SELECT @in_mem_tbl_name = in_mem_tbl_name
FROM #in_memory_tbl
WHERE id = @id
 
EXEC( 'UPDATE STATISTICS ' + @in_mem_tbl_name + ' WITH FULLSCAN,NORECOMPUTE ' );

SET @id = @id + 1
 
END
 
DROP TABLE #in_memory_tbl
 

Please ensure to run the statistics section above, before attempting to find the row counts on "in memory tables"
 
 


 
 
 

 
 

      
 
 

Monday, November 2, 2015

Hekaton Part 10: Things Hash Indexes Can do

Hash Indexes on "In memory" tables are best suited for equality searches. Reason is obvious, as they are based on hash algorithm which doesn't sort data, hash indexes aren't useful for sorting or non equality searches ( Refer here). But, hashing being a fast and a effective technique, hash indexes are perhaps the best bet for handling equality searches ( where conditions using "=" ). Note that, equality searches are by far the most common filter condition you would see in queries. So, it is one of those things Microsoft has done to address the obvious with best possible solution ignoring the not so common problems :) Besides equality filters other areas where hash indexes can be useful are

Group by clause:

Consider the following query

SELECT count(*),data
FROM [HK_tbl]
GROUP BY Data

Query plan indicates a index scan but no additional sorting or grouping required, as even though data is not sorted by hash index, it is grouped / categorized and hence it is very effective for processing "group by" queries.


Inner Join:

HK_tbl2 is another "in memory" table with hash index on data column.

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
INNER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Outer table is scanned as there is no filtering condition while the inner table makes use of the "Hash Index" . Query plan shown below



 

Left Outer Join:

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
LEFT OUTER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Semi equijoin condition like left outer join also benefits from hash index