Got a read only database and the contents of sys.dm_db_index_usage_stats.last_user_update contains today’s date. How’s that possible?

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, delete or update a single row.) In the case of a R/O database, it could happen that…

0

Standard edition of SQL Server supports only a 2 node cluster, but will it support multi-subnet clustering or do we need Enterprise edition?

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 in 2008, the answer to the question is: multi-subnet failover clustered instances is available and supported…

0

What’s that HTDELETE wait type?

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 and extension of aggregation. Among other things, it now uses one shared hash table…

0

Applying DELAYED_DURABILITY = FORCED on TEMPDB

  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 improvements. ALTER DATABASE [tempdb] SET DELAYED_DURABILITY = FORCED Is this the expected behavior…

1

What events and columns are analyzed by SQL Server Upgrade Advisor from a SQL Trace .trc file?

  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 be analyzed by the Upgrade Advisor? Couldn’t find the answer to that question documented,…

0

RESTORE VERIFYONLY: How does it check for available space on destination devices?

  “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 the space check, it iterates over all data files, transaction…

0

What’s the optional_spid in sys.dm_exec_plan_attributes?

  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 lot of recompiles that would have a performance impact for a workload that…

2

Finding the list of invalidated plans in cache: Is that even possible?

    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 include these steps:   Create a stored procedure Run it and it…

0

When doing a database restore, what actions is the engine taking after it gets past the 100%?

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 statement was running, reached 100% and thirty minutes later, the restore hadn’t finished…

1