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