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…
Month: July 2014
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…
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,…
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…
Why is the compatibility level of the database that supports Data Collection set to 100 even in SQL Server 2012 or SQL Server 2014?
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 with the same level as the server instance that…
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…
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…