CREATE DATABASE Scott;
Starting with SQL Server 2005, execution plans include information about missing indexes and staring with SQL Server 2012 execution plans could include information regarding few warnings (ex. implicit conversions, no join predicates, ). Because both types of information are included in [estimated] execution plans and these plans are stored as XML data we can use following queries that find execution plans with warnings and with missing indexes for TOP(100) queries sorted by average logical reads:
You may change default sort criteria to ORDER BY avg_elapsed_time / avg_worker_time / another column from #QueryPlans table.
On September 1, 2013 SQL Server Customer Advisory Team published three eBooks:
Interesting chapters from SQLCAT’s Guide to Relational Engine:
Encapsulation of several SQL DML statements like INSERT, UPDATE, DELETE, MERGE in a single transaction can be beneficial for performance compared with approach which doesn’t uses explicit transactions.
To test above hypothesis I have created a small table named dbo.SalesOrder and two batches that insert 100.000 rows (per batch):
To measure the performance of every batch I have used two performance counters:
As you can see in this simple test, encapsulation of several DML statement in a single transaction increased the volume of data written to disk / second (Log Bytes Flushed/sec) while the number of physical write operations into database log (Log Flushes/sec) and the execution time decreased.
Note #1: the main reasons for using transactions should be the ACID properties but the way we manage the D(durability) can have significant impact on database performance.
Note #2: this test is simple. Wasn’t my goal to do a full performance test. You should do your own tests.
SQL Server supports a subset of the XQuery language. One of features implemented within SQL Server XQuery are comparison operators. There are two kinds of comparison operators:
Two operators are available for equality comparison: = and eq .
First operator (=) can be used to compare sequences and / or singleton values / operands. Second operator (eq) can be used only with singleton values / operands. When two sequences are compared and both sequences have at least one common value the result is true, otherwise (there is no common value) the result is false.