SQL Server 2016 – Resolving Head Blocker Issues in HP 3PAR Environment

This is an answer assuming MySQL/InnoDB. Visibility rules are slightly different on Oracle:

No, as you say, SET autocommit = 1 (default behavior) is that every single query is like if it has a START TRANSACTION; ... COMMIT; around it, committing after every query. That is different from READ UNCOMMITTED (which doesn't mean that there is not a lock). InnoDB will always lock the row affected during its operation, the difference is that as the transaction commits just after the statement is executed, the lock will be released just afterwards in all cases.

However, even if you are using autocommit=1, rollback area cannot be purged inmediately when using tx_isolation > READ COMMITED, because other long-running transactions may need those values.

To show you the difference, I can show you an example:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Even if the other session is is using autocommit = 1; session 1 can't see the changes of session 2 because a transaction is ongoing. Also:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Session 2 cannot see the new value autocommited in session 2 becase start transaction + select is like if you had executed start transaction with consistent snapshot, so in InnoDB's default level "REPEATABLE READ", it cannot be seen even if it has (auto) commited on the other session.

Of course, if you only use autocommit mode, and no longer transactions in any connection, READ COMMITED and READ UNCOMMITED are equivalent, but not because it changes its visibility, but because you commit every single query. Things also get more complex if you throw other non-transactional engines into the mix.

As a side effect, mysql 5.6 changes InnoDB autocommit SELECT into read-only transactions, boosting its performance a bit.

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by.

The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query actually was.

The estimates are in turn based on statistics and those can be outdated giving you estimates and costs that are wrong.

The estimates for XML queries is always wrong. There is no statistics generated for XML columns and there is certainly no statistics generated for XML parameters or variables.

Have a look at this rather simple XML query.

declare @X xml;

select 1
from @X.nodes('*') as T(X);

Estimated query plan

SQL Server assumes there are 10000 elements in the XML and keeps on guessing from there. Using the nodes() function assumes that 200 of those will be returned. Before that there is a Filter operator that checks if @X is not null limiting the number of estimated rows to 66. Pure guesswork and not influenced at all by what data you actually have in your XML.

To know if a query is good enough you should have a look at things like duration, number of reads and allocated memory. Don't use the estimated cost and please don't use the percentages of individual queries to compare performance.

Your XML query could be improved as Mister Magoo suggests in a comment.

SELECT ParamValues.x1.value('(location/text())[1]', 'VARCHAR(200)')
FROM  @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);

If you don't specify the text() node, SQL server have to generate a plan that works with mixed content XML, concatenating all the node values from the sub nodes.