Most Valuable Yak (Rob Volk) Blog

<br/>e:\sql11_main_t\sql\ntdbms\hekaton\sqlhost\sqllang\matquery.cpp 

<br/>e:\sql11_main_t\sql\ntdbms\hekaton\sqlhost\sqllang\sqlmeta.cpp 

<br/>e:\sql11_main_t\sql\ntdbms\hekaton\sqlhost\sqllang\resultset.cpp</font></p>

Interesting!  The first 4 entries (in red) mention parameters and "slow code".  Could this be the foundation of the mythical DBCC RUNFASTER command?  Have I been passing my parameters the slow way all this time?

And what about those sp_xxxx_hekaton_database procedures (in blue)? Could THEY be the secret to a faster SQL Server? Could they promise a "hundredfold" improvement in performance?  Are these special, super-undocumented DIB (databases in black)?

I decided to look in the SQL Server system views for any objects with hekaton in the name, or references to them, in hopes of discovering some new code that would answer all my questions:

SELECT name FROM sys.all_objects 
    WHERE name LIKE '%hekaton%'
SELECT name FROM sys.all_objects 
    WHERE object_definition(OBJECT_ID) LIKE '%hekaton%'

Which revealed:

name
------------------------

(0 row(s) affected)

name
------------------------
sp_createstats
sp_recompile
sp_updatestats

(3 row(s) affected)

Hmm.  Well that didn't find much.  Looks like these procedures are seriously undocumented, unknown, perhaps forbidden knowledge. Maybe a part of some unspeakable evil?

(No, I'm not paranoid, I just like mysteries and thought that punching this up with that kind of thing might keep you reading.  I know I'd fall asleep without it.)

OK, so let's check out those 3 procedures and see what they reveal when I search for "Hekaton":

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

OK, that's interesting, let's go looking down a little further:

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

Wellllll, that tells us a few new things:

1. There's such a thing as Hekaton tables (UPDATE: I'm not the only one to have found them!)
2. They are not standard user tables and probably not in memory UPDATE: I misinterpreted this because I didn't read all the code when I wrote this blog post.
3. The OBJECTPROPERTY function has an undocumented TableIsInMemory option

Let's check out sp_recompile:

--  (3) Must not be a Hekaton procedure.

And once again go a little further:

if (ObjectProperty(@objid, 'IsExecuted') <> 0 AND
    ObjectProperty(@objid, 'IsInlineFunction') = 0 AND
    ObjectProperty(@objid, 'IsView') = 0 AND
    -- Hekaton procedure cannot be recompiled
    -- Make them go through schema version bumping branch, which will fail
        ObjectProperty(@objid, 'ExecIsCompiledProc') = 0)

And now we learn that hekaton procedures also exist, they can't be recompiled, there's a "schema version bumping branch" somewhere, and OBJECTPROPERTY has another undocumented option, ExecIsCompiledProc.  (If you experiment with this you'll find this option returns null, I think it only works when called from a system object.)

This is neat! Sadly sp_updatestats doesn't reveal anything new, the comments about hekaton are the same as sp_createstats.  But we've ALSO discovered undocumented features for the OBJECTPROPERTY function, which we can now search for:

SELECT name, object_definition(OBJECT_ID) FROM sys.all_objects 
    WHERE object_definition(OBJECT_ID) LIKE '%OBJECTPROPERTY(%'

I'll leave that to you as more homework.  I should add that searching the system procedures was recommended long ago by the late, great Ken Henderson, in his Guru's Guide books, as a great way to find undocumented features.  That seems to be really good advice!

Now if you're a programmer/hacker, you've probably been drooling over the last 5 entries for hekaton (in green), because these are the names of source code files for SQL Server!  Does this mean we can access the source code for SQL Server?  As The Oracle suggested to Neo, can we return to The Source???

Actually, no.

Well, maybe a little bit.  While you won't get the actual source code from the compiled DLL and EXE files, you'll get references to source files, debugging symbols, variables and module names, error messages, and even the startup flags for SQL Server.  And if you search for "DBCC" or "CHECKDB" you'll find a really nice section listing all the DBCC commands, including the undocumented ones.  Granted those are pretty easy to find online, but you may be surprised what those web sites DIDN'T tell you! (And neither will I, go look for yourself!)  And as we saw earlier, you'll also find execution plan elements, query processing rules, and who knows what else.  It's also instructive to see how Microsoft organizes their source directories, how various components (storage engine, query processor, Full Text, AlwaysOn/HADR) are split into smaller modules. There are over 2000 source file references, go do some exploring!

So what did we learn?  We can pull strings out of executable files, search them for known items, browse them for unknown items, and use the results to examine internal code to learn even more things about SQL Server.  We've even learned how to use command-line utilities!  We are now 1337 h4X0rz!  (Not really.  I hate that leetspeak crap.)

Although, I must confess I might've gone too far with the "conspiracy" part of this post.  I apologize for that, it's just my overactive imagination.  There's really no hidden agenda or conspiracy regarding SQL Server internals.  It's not The Matrix.  It's not like you'd find anything like that in there:

Attach Matrix Database
DM_MATRIX_COMM_PIPELINES
MATRIXXACTPARTICIPANTS
dm_matrix_agents

Alright, enough of this paranoid ranting!  Microsoft are not really evil!  It's not like they're The Borg from Star Trek:

ALTER FEDERATION DROP
ALTER FEDERATION SPLIT
DROP FEDERATION

#tsql2sday