6

This isn't Halloween protection, it's normal UPDATE semantics. Check out this warning from the docs on UPDATE: The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not ...


6

There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found. However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags. There is a good reason the timeouts and limits ...


4

Add TYPE to return XML from the inner query rather than a string DECLARE @AuditParameters XML = ( SELECT 1 AS AccountID, 2 AS CategoryID, 3 AS CategoryAttributeID, '4' AS SyncBatchGUID FOR XML PATH(N'Parameters'), ELEMENTS XSINIL, TYPE )


3

Use TYPE Directive in FOR XML Queries SQL Server support for the xml (Transact-SQL) enables you to optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. -- 1st DECLARE @AuditParameters XML = ( SELECT 1 AS AccountID, 2 AS CategoryID, 3 AS CategoryAttributeID, ...


2

I don't think there's a way to do that in SSMS. You should use Sentry One Plan Explorer for this. It's a free tool that provides an alternative way of viewing SQL Server execution plans. I've run this code on my local copy of the Stack Overflow 2010 sample database: CREATE PROCEDURE #sp_TestQuery AS SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;...


2

Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong? You are reading it wrong in two ways. It doesn't think it will read 1.4 mil rows. It thinks it will start a index scan which if completed will read 1.4 mil rows, but that due to the LIMIT 1 it will get to stop very early on. Apparently it is ...


2

Josh's answer (thanks Josh!) got me thinking about determinism. I can see for the fundamental usage update table set column = value, when there are duplicate source rows, the question must be answered which matching value from the source will be written to the destination. The simplest implementation would be to let the iterators run to completion. ...


2

Automatic query tuning requires that Query Store be enabled first, then you can enable Automatic Tuning. To check Query Store, select * from sys.database_query_store_options. You're looking for actual_state_desc to be READ_WRITE. Then check the tuning options with select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'. You'...


1

You can query one of the DMVs against the database you're interested in - sys.database_automatic_tuning_options. SELECT * FROM sys.database_automatic_tuning_options This will return whether or not the FORCE_LAST_GOOD_PLAN is enabled. If you want to enabled it, then you can run: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); ...


Only top voted, non community-wiki answers of a minimum length are eligible