Friday, November 15, 2019

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.



Sunday, August 4, 2013

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.

CustomerIDCustomerNameLocation
1Customer1Colombo
2Customer2Kandy
3Customer3Colombo
4Customer4Galle
5Customer5Kandy
6Customer6Jaffna

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.


OrderIDCustomerIDOrderDateOrderAmount
117/1/2013120
217/2/2013110
317/3/2013100
417/4/2013120
527/1/201320
627/2/201330
727/3/201345
837/1/201322
937/2/201365
1037/3/201366
1147/1/201332
1247/2/201378
1357/1/201336
1457/2/201394
1557/3/201343
1667/1/201383
1767/2/201353
1867/3/201354
1967/4/201342

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.

CustomerIDCustomerNameLocationOrderIDCustomerIDOrderDateOrderAmount
1Customer1Colombo117/1/2013120
2Customer2Kandy727/3/201345
3Customer3Colombo1037/3/201366
4Customer4Galle1247/2/201378
5Customer5Kandy1457/2/201394
6Customer6Jaffna1667/1/201383


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”.

Tuesday, June 4, 2013

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.