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.
Database Bits And Pieces
Friday, November 15, 2019
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.
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.
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.
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”.
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”.
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.
Subscribe to:
Posts (Atom)