I’ve done it again… I’ve neglected my blog so long that my name has been removed from the list of SQLBlog contributors on the right-hand side of the main page. So I need to fix that.
I have been accumulating a list of questions that have come up during my 5-day SQL Server Internals classes, that I cannot immediately answer. Sometimes I can figure out the answer myself with just a bit of research or a simple test, and sometimes I can ask one of my Microsoft contacts for help. (Other times, I actually know the answer, but I just space out on it when asked, after being on my feet talking for 5 straight days. )
I kept hoping that I would eventually get answers to ALL the questions on the list, but I realized I should wait to post what I have. And that will get me back up on this blog site.
1. Can memory-optimized tables use BPE (Buffer Pool Extensions)
No, BPE is only for data read from disk and stored in memory buffers. Memory optimized tables do not use pages or buffers.
2. What if a plan guide references a table that is specified in a query without a schema name, and there are two different tables, in different schemas, with the same name?
For a plan guide of type ‘SQL’, all that matters is the TSQL-statement that is included in the plan guide. The same plan guide could be invoked when accessing different tables with the same name.
3. Why does the Native Compilation Advisor complain about the EXEC command?
In SQL Server 2014, EXEC is never allowed in Natively Compiled Procedures. In SQL Server 2016, we can EXEC a Natively Compiled Procedure or function from within a Natively Compiled Procedure, but we can’t EXEC a non-native module, and we can never EXEC a string.
4. Can we replicate a memory-optimized table?
Memory-optimized tables can be subscribers for transactional replication, but that is the only supported configuration. See this MSDN page for more details:
5. Does auditing work on memory-optimized tables?
6. Are the values in sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats reset when an index is rebuilt?
YES, just as if you had restarted your SQL Server Service.
7. When do I need to use the option ‘scan for startup procs’?
This configuration option is related to the procedure sp_procoption, that allows you to mark a stored procedure as a ‘startup’ procedure, to be executed every time your SQL Server service starts. However, the configuration option to tell SQL Server to scan for these procedures doesn’t see too useful. When the first procedure is marked for startup, this option is turned on automatically, and when the last one is unmarked, it is turned off. So there is really no need for the configuration option, unless you suspect a problem and want to inhibit the startup procedures.
8. When are SQL Trace and the SQL Profiler going away?
They’re still available in SQL Server 2016 CTP 2.4, so I assume they will still be in SQL Server 2016 RTM. After that, I think no one knows yet.
That’s all I’ve got for now.