There is a new column with this information in the sys.dm_os_sys_info DMV. – Identification of the “Lock Pages In Memory” for the SQL Server Service Account. DBCC CLONEDATABASE in SQL 2016 SP1 allows you to create pure schema only clone without statistics or query store. In SQL 2016 Service Pack 1, DBCC CLONEDATABASE also supports cloning of CLR, Filestream/Filetable, Hekaton and Query store objects. The description of the SQL Server 2016 SP1 functionality can be found in the respective KB: Incredible, we do not have to do custom sum on the executed operations – a very simple but extremely useful improvement!ĭatabase Cloning – Clone database is a new DBCC command added that allows power users to trouble shoot existing production databases by cloning the schema and metadata, without the data. Total logical reads 6534, physical reads 3014, writes 0.
Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 6161, lob physical reads 4, lob read-ahead reads 13344.
– Total IO from the executed query operations. Notice that the DMV sys.dm_exec_valid_use_hints is also already available on Azure SQL Databases for some time, but at the moment of writing of this article, it appears to be empty with no hints available. I expect that all of this options to be documented as the SQL Tiger team have been doing an amazing job in regarding of the improvements and documentation of the SQL Server. On my current test instance I can see the following enabled query hintsĪSSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES Which would actually disable the Row Goals for the specified query.įor finding out all available query hints, you can query the DMV sys.dm_exec_valid_use_hints, which will contain the name of the available hints for the current instance and version of SQL Server. Option ( recompile, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')) Select top 10 ProductKey, sum(sales.SalesAmount) This functionality is logically similar to OPTION(QUERYTRACEON) functionality, but there are no more “magic” numbers, but descriptive hints, which as already mentioned allows you to avoid sysadmin privileges requirement. A brand new option on the query level is available in SQL Server 2016 SP1 that allows to control the query optimizer behaviour, that was controllable with the trace flags requiring SysAdmin access rights previously. – USE HINT query option without SysAdmin privileges. Now we can modify and deploy objects like Stored Procedures, Triggers, User-Defined Functions, and Views without any fear. Now, I can finally have future project deployments of those who are not using SSDT running with much less problems. I was asking, begging, threatening, crying for years to get this in the SQL Server. I almost cried when I found out that it was implemented. – Optimised Alter Index operation for the In-Memory Columnstore Indexes! We can add or remove a Columnstore Index with the same effectiveness as a Hash or Nonclustered BW-Tree one! The detailed blog post will follow. With the release of the most epic Service Pack 1 for SQL Server 2016, the biggest attention of everyone should be drawn to the programmability surface that was brought on the same level for Enterprise, Standard, Web & Express Edition! Plus, where possible, even the LocalDB has a hugely extended set of the programability features!īut here are other Service Pack improvements for the engine that are hugely appreciated:
0 kommentar(er)
