Senaste inläggen 

Taggar 

parameters     virtuell     XP_cmdshell     central management server     CMS     data warehouse     temp table     0xC0202009     HEAP     package load     CU3     constraint     DTA     SSIS     Cluster     connection     2011     SQL Server 2012     CTP1     0xC0010014     SSRS     access denied     clean up     #am_get_querystats     sp_MSForEachDB     SQL2008     HADR     parallelism     CTE     SSRS 2008     SSAS     filter     function     sql browser     SQL Denali     Trace Flag     page splits     platsannons SQL utvecklare     profile     feedback     Business Intelligence     reorganize index     DECIMAL     security     sql 2005     history     Logins     SQL server codename Denali     resource governor     performance     Techdays     Page life expectancy     Activity Monitor     2005     bugs     concatenation     transactions     Datawarehouse     CU1     create index     gratis verktyg     2000     improve     features     Säkerhet     T-SQL     Reports     error     sp1     dbmail     rebuild     undocumented procedures     2008     BOL     SQL Server     sql 2008     login error     Microsoft     connect     AcquireConnection     Extended Event

SQL Server mystery: Hekaton tables and Hekaton Procedures

Skrivet den 12 juni 2012 i T-SQL, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, Level 500, SQL Server maintenance, Steinar Andersen, sv, en

Have you ever heard of Hekaton Tables and Hekaton Procedures in SQL Server? Neither had I until recently. And it seems neither have Google. I happened to stumble upon the mention of these mysterious object, and tried to find out more, but had no luck. But I wanted to share what I have seen of is so far, and ask you, the readers of our blog, if you might have some light to shed on this? If so, please comment on this blogpost.

To find the mention of Hekaton on your own SQL Server instance, run the following code:

select * from syscomments where text like '%hekaton%'

That should generate an output that indicates that sp_updatestats, sp_createstats and sp_recompile contains the word Hekaton.

The relevant lines of code are as follows:

from "sp_helptext sp_createstats":

   -- filter out local temp tables, Hekaton tables, and tables for which current user has no permissions

   -- Note that OBJECTPROPERTY returns NULL on type="IT" tables, thus we only call it on type='U' tables

   if (

      (@@fetch_status <> -2) and

      (substring(@tablename, 1, 1) <> '#') and -- temp tables

      ((@table_type<>'U') or (0 = OBJECTPROPERTY(@table_id, 'TableIsInMemory'))) and -- Hekaton table

from "sp_helptext sp_updatestats"

   -- filter out local temp tables and Hekaton tables

   -- Note that OBJECTPROPERTY returns NULL on type="IT" tables, thus we only call it on type='U' tables

   if ((@@fetch_status <> -2) and

      (substring(@table_name, 1, 1) <> '#') and  -- temp tables

      ((@table_type<>'U') or (0 = OBJECTPROPERTY(@table_id, 'TableIsInMemory')))) -- Hekaton tables

from "sp_helptext sp_recompile"

   -- Hekaton procedure cannot be recompiled

   -- Make them go through schema version bumping branch, which will fail

       ObjectProperty(@objid, 'ExecIsCompiledProc') = 0)

None of that really tells us exactly what Hekaton tables and Hekaton procedures are, but might give some clues. The undocumented OBJECTPROPERTY(@table_id, 'TableIsInMemory') call (another mystery...) seems to indicate that it could possibly be connected to memory, caching or even the old and removed feature of DBCC PINTABLE? That would be a real surprise in that case.

The comments tells us the following: Hekaton Procedures cannot be recompiled using sp_recompile Hekaton tables can not update or create stats using sp_updatestats and sp_createstats

I know that this is not much, but if you have more information about this little mystery, please comment it on this blogpost or mail me at firstname.lastname@sqlservice.se

 

Skriv en kommentar

  • Lookup SQL Server 2014 In Memory OLTP. Hekaton is the codemanme for this incarnation of in memory SQL database that is coming with SQL Server 2014

    By tester