CREATE DATABASE Scott;
On September 1, 2013 SQL Server Customer Advisory Team published three eBooks:
Interesting chapters from SQLCAT’s Guide to Relational Engine:
“Join us at ITdevConnect, a two day event packed with knowledge about the latest innovations in the field of technology. Find out what’s new in 2015: Visual Studio 2015, C# 6, ASP.NET 5, SQL Server 2014 and try the programmes during a hands-on lab on Windows Azure.”
But this query generates following error:
The cause of this error is that the statement(s) executed by OPENROWSET / OPENQUERY should return at least one resultset (OPENROWSET / OPENQUERY will return only the first one). In this case, the statement is CREATE MINING STRUCTURE and it doesn’t returns a resultset.
The solution is to use EXEC … AT linked_server statement thus:
I have made following changes in sp_DeadlockAnalyzer:
Sample usage: Create a trace with SQL Profiler including [Deadlock graph event]. After intercepting a deadlock event you have to use [Extract event data] command to save on local computer the XML content of [Deadlock graph event] into a *.xdl file (ex. D:\DbEvents\deadlock18.xdl). Then, you have to execute the stored procedure:
Note #1: The type of resource can be keylock or pagelock (at this moment).
Note #2: I’ve tested this procedure only with SQL Profiler – SQL Server 2012.
Note #3: This procedure should be executed on the same SQL instance.
Note #4: Next update will analyze also triggers execution plans. A future version will try to correlate data access operators and type of SQL statements (ex. SELECT -> S locks, UPDATE -> U or X lock on target table, etc.).
Sometimes we need to test our apps for deadlocks. In this case we might need a simple way to simulate this type of error.
According to this, a deadlock occurs when two or more transactions block each other thus:
Resources: here (section Deadlock Information Tools, Resource attributes) and here (section Resource Details) you may find a full list with all resources types. Usually, resources which appear in deadlocks are KEYs and PAGes. A KEY refers to index records and this type of resource is identified by a hash value (hash). A PAGe refers to 8K data pages and this type of resource is indentified by a pair <file_id>:<page_in_file>.
Note: for every PRIMARY KEY constraint SQL Server automatically creates a UNIQUE INDEX (clustered or non-clustered). In this case, SQL Server creates an UNIQUE index (PK_MyTable_Col1) for the primary key constraint.
In order to get information about the hash value of every record within index PK_MyTable_Col1 we could use the following query:
Note: %%lockres%% function is undocumented.
Because this table is small it consumes only one 8K page. We can get information about this page by using sp_AllocationMetadata stored procedure (or by using sys.system_internals_allocation_units view):
As you can see, this page is 283 within file 1.
In this example we will simulate a write – read deadlock thus:
Using SQL Server Management Studio, open a new window (SQLQuery1.sql) and start a transaction T1 thus
At this moment only one resource (8194443284a0) is locked X by transaction T1:
Now, open a new window (SQLQuery2.sql) and initiate a new transaction (T2):
At this moment, UPDATE statement will take an X lock on record 2 B and SELECT will try to take a S lock on record 1 A (which is already locked by T1). Because record 1 A is already locked X by T1, T2 will have to wait till this X lock is released:
Now, we have to return in the first window (SQLQuery1.sql) and execute
In this case, SELECT statement from transaction T1 will try to take a S lock on record 2 B
In this moment the circle is completed and SQL Server deadlock monitor will automatically detect and end this deadlock:
In my case, SQL Server selected transaction T2 as deadlock victim. This means that T2 is automatically cancelled (ROLLBACK) and all locks took by this transaction are released.
Note: transaction isolation level used for this example is read committed (default).
SQL Server Query optimizer can automatically remove joins between parent and child tables if the query’s result remains unchanged. This is optimization is called foreign key join elimination.
Bellow example creates two tables with a simple foreign key defined on a mandatory column:
For the following query
SQL Server generates an execution plan (SSMS: Query > Display estimated execution plan) which includes a single data access operator (Clustered Index Scan on child table dbo.SalesOrder):
In above example, Query Optimizer automatically removes the JOIN between parent table dbo.Customer and child table dbo.SalesOrder and, also, it removes data access operator on parent table dbo.Customer:
This optimization is possible, mainly, because the foreign key constraint is defined on a single mandatory column and this constraint is trusted.
I think it worth mentioning those reasons which prevent Query Optimized to apply this optimization:
The execution plans includes data access operator for both tables:
The execution plan is the same:
And the execution plan shows only one data access operator:
To check what FK constraints allows FK join elimination optimization I wrote this script:
1) PPTX + scripturi SQL + funcție SQL CLR download
Funcția SQL CLR DivideBy este prezentată doar cu titlu de exemplu. Nu este optimizată și nu este pe deplin testată.
2) Exemplu de utilizare a funcției DivideBy: