1

Topic: delete from a huge table

VSEM PRIVET.
Est ' huge clustered table (~100Gb, 1bln rows +) i 2 nonclutered indexes na ney. Kogda ja delaju

SET ROWCOUNT 100000;
delete t from t inner merge join #t on t. = #t...

server udalaet 100k rows iz tablizi no potom prosto zavisaet na udalenii iz nonclustered indexsov (ja viju eto iz sys.partitions).
Processi chto pishut v etu tablizu v etot moment prosto blocked by etot process. Deadlocks net. Pochemu takoe povedenie? Kuda kopat '?
Microsoft SQL Server 2014 (SP2-CU10-GDR)

2

Re: delete from a huge table

To dig towards sectioning.

3

Re: delete from a huge table

Est ' huge, please use translit.net
a to figeem takoe chitat'

4

Re: delete from a huge table

How to me  sectioning? There is a removal   which captures many sections at  sectioning functions sad
The question in other - why index evolution occupies  a lot of time?

5

Re: delete from a huge table

Hello world! 11 wrote:

a question in other - why index evolution occupies  a lot of time?

In the first, it also can be big enough, in a limit it is necessary to change> 100000 pages, that is 1  casual disk writing, or 100 000 operations IO. On each index.
In the second, be convinced that that does not refer to your table what still the big table then, can, matter is not in evolution of indexes.

6

Re: delete from a huge table

Similar model of behavior if I delete 5 records. There are no objects of type  representations, are not present ,  or  removals.
How it is possible to look with what  there is an evolution ?

7

Re: delete from a huge table

Hello world! 11 wrote:

similar model of behavior if I delete 5 records. There are no objects of type  representations, are not present ,  or  removals.
How it is possible to look with what  there is an evolution ?

select *
from sys.partitions
where
object_id = object_id (' t ')

8

Re: delete from a huge table

Thanks, but I specified in the first post what exactly  representation  for review, and here is how time it not  any  in number of lines  indexes