THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Q&A


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. Smile )

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.


Published Thursday, October 15, 2015 3:57 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



D. Michael said:

So, you don't want to give up that sidebar space to someone else who's actually blogging, eh?

See you in another three months, I suppose.

October 15, 2015 6:05 PM

Kalen Delaney said:

Thanks for your input D. Michael. A blog post is not like a tweet, which is very ephemeral. There is some content I posted years ago, that I still get comments about and that is still referred to by others. Having my name in this list confirms that this is my blog's home.  And of course, I don't always go 3 months between posts. How often do you think is often enough to actually be said to be blogging? Is one little trivial post a week better than a very technical post every couple of months? I could start treating my blog like FaceBook. Would I be blogging then?

October 15, 2015 6:46 PM

RichB said:

I think you should definitely blog about blogging.  

October 15, 2015 9:46 PM

a.m. said:

I'm glad we have the three month expiration thing because it means that you WILL blog :-)

Speaking of which, I'm not currently listed either. Tough to keep up with these things and do our day jobs and live our lives, all at the same time!

October 16, 2015 10:38 AM

zzyytt said:">">">">

May 1, 2018 7:07 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement