table deletes are very slow

Question

Hi All,

Have some questions LOB data. One of the table with below schema has reached to 500GB.
Application team is trying to delete some data from this table and it is taking more time.
We aren’t sure what is really happening. The deletes are getting successful but the number of rows are not getting reduced nor space is getting released.

We are on SQL Server 2017 RTM. application team is deleting data based on date range condition.
Other thing we noticed is database is part of AG and it is in FULL recovery model. Also, read_committed_snapshot is ON for this database.

create table test
(
c1 bigint not null,
c2 int not null,
c3 ntext,
c4 int,
c5 int,
c6 int,
c7 int,
c8 int,
c9 int,
c10 nvarchar(255),
c11 ntext,
c12 int,
c13 bigint,
c14 ntext,
c15 int
)

Questions

1) What are the options available to clean up LOB data
2) Does lob data stored in single 8kb page or somewhere else?
3) If I am selecting data from disk in what type of pages the LOB data is read into inside sql server memory?
4) Why LOB row deletions are slow?
5) How can calculate the row size of above table?
6) Even though we are deleting the data, why the row count and space is not reclaimed?

Thanks,
Bob

Answer 1

Hi @Bob sql ,

There is a detailed introduction in this article,hope it can solve your confusion.
Please refer to:
SQL Server Storage Engine: LOB Storage

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 2

Hi @Bob sql ,

The link below may help you：
SQL Server - Deleting/Updating LOB data in a Heap

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][2] to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 3

Large Object data is stored in special LOB data pages that live in separate LOB data allocation units. Those pages are of type 3 (TEXT_MIX_PAGE). Any partition of any table or index that holds a column with a LOB data type automatically receives a LOB_DATA allocation unit
https://sqlity.net/en/2496/text-mix-page/#:~:text=Summary,receives%20a%20LOB_DATA%20allocation%20unit.

Answer 4

Hi All,

I am still didn't understand in what type of pages will the LOB data get stored ? apart from 8k data page are there any other type of pages to LOB data in buffer pool?

Regards,
Bob