Any attempt to update the index (via any DML statement) updates that index usage statistics as part of the query scan setup/initialization, even before the iterator’s GetRow method is invoked (i.e. before the iterator attempts to actually insert...

Even though the Features Supported by the Editions of SQL Server 2014 doesn’t have any indication on which SKUs the multi-subnet FCIs improvement is available, after having confirmed with the program manager who owned the stretch cluster improvement back...

One of the many improvements, shipped with the SQL Server 2014, made to the iterators used in the batch mode processing query execution paradigm applicable to a query that uses at least one columnstore index ( Apollo ) , corresponds to the enhancement...

  My PFE colleague Sam Mesel posted the following information a few days ago on an internal distribution group: I’m testing DELAYED_DURABILITY on TempDB Applying the following change on it does not give me any error message, but I see no performance...

  Went to a customer this week to assist them with the risk assessment and work planning to accomplish the upgrade of several business applications’ databases. Question was, what events and what columns should a .trc trace file include that will...

  “How does RESTORE VERIFYONLY checks for space on destination drives when a drive isn’t specified as part of the statement?” was the question being asked this time. And this was my answer to that one: This is what SQL does: In order to complete...

  A question posted by Greg Low to the MCMs distribution group recently: I was surprised that the data collection system creates a database with a dbcompat level of 100 on both 2012 and 2014. Wouldn’t it make more sense for it to create a database...

My answer to a question asked by Vedran Kesegić asked earlier this week in the MCMs group: Queries accessing non-dynamically bound temp tables, if issued from different connections would recompile to bind to the right temp table. This would cause a...

    About three weeks ago, Kimberly approached me because she was seeking for a way to find a plan that’s in the cache that’s been invalidated such that the next execution will be recompiled. One example script to reproduce the conditions would...

This was a question my admired Greg Low posted on the SQL Server MCMs discussion group a few days back, while he was restoring a 2TB database and noticed that the percent_complete column from sys.dm_exec_requests for the session from which the RESTORE...

I have recently received this question from different sources, so I think it’s worth documenting why some people still see validation error for the default value of a column when it’s defined as a NEWSEQUENTIALID() built-in. The tracking defect on Connect...

No, it’s not. It’s on Standard too. If an attempt is made to enable it on any other edition, you’ll see error 865 written to ERRORLOG “Buffer pool extension is only supported on Standard and Enterprise Editions of SQL Server.” and the same exception is...

A colleague came up with this situation: ************ I am tracing some SQL activity on a SQL 2008 R2, Enterprise SP2 64-bit server. In the profiler trace, I keep seeing Encrypted Text in the textdata column for procedures that are clearly plain text...

Someone recently asked under which circumstances SQL Server 2014 would return false when interrogated for the IsXTPSupported server property . I looked into the source code and this is what I encountered there. For the boxed version of SQL14, that...

One fellow MCM posted the other day the following question about log initialization on Azure Blob storage: ******************** In SQL 2014 (VM hosted in Windows Azure) I've tested creating a DB  on the Azure Blob storage with a statement like this...

This contents is based on the response I gave to Pat Martin, a Principal Premier Field Engineer and good colleague of mine, who asked just this question a while back. This is what I found: The iterator used to scan the catalog for the databases that need...

And I’ve created and produced the first video on the SQL Server track. It is called SQL Server Connectivity Deep Dive and is currently available from this link for Premier customers subscribed to this new offering.

  The short answer is: Those returned by master.dbo.xp_enum_oledb_providers. But let’s dig deeper and try to give you the detailed description you deserve. The triggering reason why I’m writing about this is because a colleague described the following...

Last week I was involved in a discussion about the default transaction isolation level used by the transaction objects in WCF’s services (Serializable) versus the one used by the transaction objects in Entity Framework for SQL Server (ReadCommitted) whose...

Today, a colleague exposed the following case:   In the execution plan I do see some exclamatory marks on Sort, Hash Joins operators but there is no warning information available. I am using SQL Server 2012.  How do I interpret the exclamatory...

I invested some time today investigating what SQL Server does with the initialization of TEMPDB’s log file, because a question was raised last week during Robert Davis session on TEMPDB at SQL PASS Summit in Seattle, and I wasn’t sure about what the answer...

3988 - New transaction is not allowed because there are other threads running in the session. 3930 - The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. If so, I would...

Or the chicken and egg problem when attempting to restore a corrupted model database as it was presented by my MCM colleague Gail Shaw the other day in our MCM discussion group. This is the case he presented us with: Hi all Something I’ve been puzzling...

I just happened to find that when the log manager raises error 9002 “The transaction log for database '%s' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” the State can vary...

Following is the answer I provided to the interesting question asked by my colleague Pat Martin about the insides of crash recovery in SQL Server.   The iterator used to scan the catalog for the databases that need startup and recovery returns them...