Delete records in SQL Server. What is happening behind?

I have seen many people questioning on how to reclaim space from the database. Lets do some analysis and see what is happening with data files behind the seen.

First I create an empty database with default values as below.

Lets execute "sp_spaceused" & see the output.

As per these, size of the database is 16MB. Log file is 8 MB & Data file is 8 MB. For our analysis what is important here is the size of the data file which is 8 MB.

As per the output above reserved space is 2386 KB. It is the sum of data, index & unused space.
That is
Reserved = data + index_size + unused
2368 KB = 960 KB + 1040 KB + 268 KB.

This can also be called as the allocated space. Then, size of the data file can be found as below

reserved space + unallocated space = datafile size
2368KB(2.31MB) + 5.69 MB = 8 MB.

Above calculations are useful to understand the data file size & usage. Now lets create a table, insert some data & see how this behaves.
Below table shows how the space is allocated based on each step.

First row in the table shows the sizes at the beginning. Then I insert 1000 records & then delete 200 records. After deleting 200 records, unused space has been increased to 1808 from 376. Then when I shrink, this space is not released but the unallocated space. Then i delete another 500 records & unused space is increase up to 4736.

Now the question is how to release this space. I rebuilt the index. After rebuilding, SQL Server arranges pages & released the unused space.

I hope this explanation clarifies on how the space in the data file behaves when data is deleted.

CROSS APPLY

The “CROSS APPLY” is a very important key word that can be used to simplify complex queries. It is not only simple to write, but also better in performance. To have a clear idea, let’s take two data sets as below.

CustomerID	CustomerName	Location
1	Customer1	Colombo
2	Customer2	Kandy
3	Customer3	Colombo
4	Customer4	Galle
5	Customer5	Kandy
6	Customer6	Jaffna

This record set is from a table named “Customer” and it contains six records for six customers. Below records were taken from a table called “Order” it has different orders for the above customers.

OrderID	CustomerID	OrderDate	OrderAmount
1	1	7/1/2013	120
2	1	7/2/2013	110
3	1	7/3/2013	100
4	1	7/4/2013	120
5	2	7/1/2013	20
6	2	7/2/2013	30
7	2	7/3/2013	45
8	3	7/1/2013	22
9	3	7/2/2013	65
10	3	7/3/2013	66
11	4	7/1/2013	32
12	4	7/2/2013	78
13	5	7/1/2013	36
14	5	7/2/2013	94
15	5	7/3/2013	43
16	6	7/1/2013	83
17	6	7/2/2013	53
18	6	7/3/2013	54
19	6	7/4/2013	42

Now I will execute below two queries to compare the results and execution plan. One was written with an INNER JOIN and the other was written with an CROSS APPLY key words.

SELECT *
FROM dbo.[Order] a CROSS APPLY
    dbo.Customer b
WHERE a.CustomerID = b.CustomerID

SELECT *
FROM dbo.[Order] a INNER JOIN
    dbo.Customer b ON a.CustomerID = b.CustomerID

Both the queries looks same and also not only the results, but also the execution plan is same. The execution plan can be seen below.

According to the image above, all the operators and the cost of each operator are identical. Then what is the difference?
Let’s have a look at the below query. In general terms, this will return orders with highest amount for each customer.

SELECT        a.CustomerID, CustomerName, Location, c.*
FROM            dbo.Customer AS a
    CROSS APPLY ( SELECT TOP 1 OrderID, CustomerID, OrderDate, OrderAmount
                    FROM dbo.[Order] AS b
                    WHERE a.CustomerID = b.CustomerID
                    ORDER BY OrderAmount DESC ) c

If this written, without the “CROSS APPLY” key word, we have to do little more work since the line “WHERE a.CustomerID = b.CustomerID” in above query is not supported with “INNER JOIN”.
The result of the above query looks as below which can’t be achieved with “INNER JOIN” simply.

CustomerID	CustomerName	Location	OrderID	CustomerID	OrderDate	OrderAmount
1	Customer1	Colombo	1	1	7/1/2013	120
2	Customer2	Kandy	7	2	7/3/2013	45
3	Customer3	Colombo	10	3	7/3/2013	66
4	Customer4	Galle	12	4	7/2/2013	78
5	Customer5	Kandy	14	5	7/2/2013	94
6	Customer6	Jaffna	16	6	7/1/2013	83

In summary, with the “CROSS APPLY”, the right table is evaluated for each row in left table and it is not the case with “INNER JOIN”.

Locks placed by queries on SQL Server on selected Isolation Levels.

Locks placed by queries on SQL Server on selected Isolation Levels.

When a query executes, SQL Server places locks on resources. In this article I am going to analyze in detail, what are the locks placed, types of locks, number of locks based on the query executed against the database depending on the isolation level.

First I’ll open a new query window against the adventure works database and executes below query. It will show the current locks placed against this database. Please note that at the moment there are no any current activities on this database and the default isolation (READ COMMITTED) level is set.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

 

Results looks as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

According to the results, this is a database level shared lock. This lock was requested by a shared transaction workplace which is a session. This session was created by the query window I opened to execute above query.

Now I’ll open another query window to execute a query. Before executing the query, I execute the above query again to see the current locks.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

Now we can see two shared database locks with two different session ids. This is because two query windows I have opened. Let’s execute a simple SELECT query and observe how locks are placed.

SELECT COUNT(1) FROM Production.TransactionHistory

 

Even though it is included in a transaction, it seems no locks have been applied. This is because the SELECT statement commits just after it is executed regardless of the transaction. So we can’t see the lock applied by the SELECT query.

Let’s change the transaction Isolation Level to a higher level by issuing following command

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

And execute the SELECT query again and see the locks results are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT
OBJECT	S	LOCK	53	TRANSACTION	GRANT

We can see that there is an additional SHARED lock on an object. Actually it is the table in the query and therefore it is a TABLE LOCK. The lock has been requested by a transaction which is the transaction I started with the SELECT query.

Now I will try to execute an INSERT and an UPDATE statement against the same table. Before that I’ll change the query to check the locks to filter out the locks created by sessions which are not important to our context.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

WHERE request_owner_id <> 0

 

Then I try to execute below UPDATE query.

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1

 

Once this is executed an additional lock can be seen as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

This is the lock requested by the UPDATE statement and resource cannot be granted because of the SHARED lock already placed. So it is in the WAIT status. So this query has placed an INTENT EXLUSIVE lock since it can’t place to prevent EXCLUSIVE placing exclusive locks by any other requet. Let’s try with an INSERT statement in another window.

INSERT INTO  Production.TransactionHistory(ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)

VALUES (316, 100000, 1000001, GETDATE(), 'I', 100, 1000, GETDATE())

 

 

Locks looks like below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

Even the INSERT statement is waiting.

Let’s change the SELECT query to access only a set of records as below. I’ll start it with a transaction to hold the locks.

BEGIN TRANSACTION

 

SELECT * FROM Production.TransactionHistory WHERE TransactionID BETWEEN 150001 AND 150010

 

This query returned 10 rows and locks are placed as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
PAGE	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT

 

This shows that there are 10 SHARED locks on the 10 rows selected and two INTENT SHARED locks on the page and on the table. Now I’ll execute an UPDATE statement which will try to update set of records from the selected 10 records and some additional records.

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150008 AND 150015

 

Results for the locks applied looks below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	WAIT	(c28af39ea70b)

 

Here I have included an additional column “resourse_description” to see what are the resources acquired by the queries. Other than the locks applied by the SELECT query, there are two INTENT EXCLUSIVE locks applied by the UPDATE query and it is WAITIING for an ECLUSIVE lock on one KEY.  So the UPDATE statement doesn’t intend to apply locks on other KEYs until the lock is granted to the first KEY.

It looks like, it tries to apply the locks on the order of the KEY. If the UPDATE statement range started, below 150001 in WHERE condition, Locks will be placed to the values below 150001 and WAITING to apply lock on the KEY 150001. If the range is totally out of the SELECT statement’s range, it will apply locks to all the KEYS as below. I change the query to …

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150011 AND 150015

 

… and the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	GRANT	(03e3af802e72)
KEY	X	LOCK	54	TRANSACTION	GRANT	(22bedd1163b7)
KEY	X	LOCK	54	TRANSACTION	GRANT	(1a9bea980fc2)
KEY	X	LOCK	54	TRANSACTION	GRANT	(e3d7810feace)
KEY	X	LOCK	54	TRANSACTION	GRANT	(faafc417cb7e)

 

Here we can see that there are additional EXCLUSIVE locks for the five rows that are trying to UPDATE.

 

For the above same queries, locks are different if the TRANSACTION_ISOLATION_LEVEL is SERIALIZABLE. Then the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(22bedd1163b7)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	X	LOCK	54	TRANSACTION	WAIT	(22bedd1163b7)

 

According to MSDN “A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction”. But it is not only INSERT but also UPDATES are prevented.

In this analysis, we can clearly understand how locks applied differently in different isolation levels.