Неужели так она должна расти?

Есть таблица A(F1 Datetime NOT NULL,F2 int NOT NULL,A_F1 char(11)) в этой таблице не более 300тыс записей. (F1,F2) - PK ClusteredЕсть таблица B(F1 Datetime NOT NULL,F2 int NOT NULL,F3 char(10) NOT NULL,B_F1 int NOT NULL,B_F2 Datetime NOT NULL,B_F3 int NOT NULL) в этой таблице 1.3 Млн. записей. (F1,F2,F3) - PK Clusteredсвязаны эти таблицы один ко многим по полям по F1,F2 и установлено каскадное обновление/удаление.Есть таблицаC(F1 Datetime NOT NULL,F2 int NOT NULL,F3 char(10) NOT NULL,F4 smallint NOT NULL,.........) в ней (F1,F2,F3,F4) - PK Clustered также настроено каскадное обновление удаление с табл. B
rows reserved data index_size unused 
<i>----------- ------------------ ------------------ ------------------ ------------------ 
</i><b>23487240</b> <b>4935936</b> KB <b>3451056</b> KB <b>1484856</b> KB <b>24</b> KB
Есть таблица D(F1 Datetime NOT NULL,F2 int NOT NULL,F3 char(10) NOT NULL,F4 smallint NOT NULL,F5 ******* NOT NULL,F6 ******* NOT NULL,F7 smallint NOT NULL,F8 ******* NOT NULL,.........) она свзязана с таблицей C один ко многим. в ней (F1,F2,...F8) PK Clustered и также есть каскадное обновление/удаление
rows reserved data index_size unused 
<i>----------- ------------------ ------------------ ------------------ ------------------ 
</i><b>141728251</b> <b>6904448</b> KB <b>6871680</b> KB <b>32768</b> KB <b>0</b> KB
так вот меня интересует почему если сделать shrink для tempdbи выполнить это
select top <b>0</b> * into #ta from A
delete t
from A t inner join #ta t2 on t.F1=t2.F1 and t.F2=t2.F2
то tempdb c 3 метров вырастает до 10Gb и отваливаемся по ошибке.Server: Msg 1101, Level 17, State 10, Line 1Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.Мне непонятно ведь ему ничего удалять не надо а он что-то делает такое что у меня просто не укладывается в голове. В настоящий момент убрал все связи с другими таблицами оставил только связи между этими 4 таблицами, убрал индексированные вьюхи результат по прежнему тот же - вырастает tempdb и отваливаемся по ошибке.Пробовал это запустить результат тот же самый.
select top <b>2</b> * into #ta from A
delete t
from A t inner join #ta t2 on t.F1=t2.F1 and t.F2=t2.F2
Скажите это нормально?Меня интересеут мнения по тому каким должен быть размер раздела под tempdb по отношению к размеру базы. Я понимаю что вопрос не совсем корректный, исходя из того что активность пользователей и запросы выполняемые разные, но все же. Хотелось бы знать много ли я хочу или все же надо просить руководство о том, что следует его увеличить.
7 ответов

А если попытаться каскадное удаление/обновление отключить?


Если я убираю связь от таблицы B к таблице C то запрос на удаление отрабатывает мгновенно.


то tempdb c 3 метров вырастает до 10Gb и отваливаемся по ошибке.А вы посмотрите предварительный план выполнения.Наверняка там присутсвует такая замечательная операция как table/index spool.


2 Glory.да присутствует.выполняю это
drop table #tf
go
select top <b>0</b> * into #tf 
from t_Files 
go
select * from #tf
go
set showplan_text on
go
delete tfs
from Accounts.dbo.t_Files tfs INNER JOIN #tf t ON tfs.DateOfRegistrationOfFile=t.DateOfRegistrationOfFile
	and tfs.CodeLPU=t.CodeLPU
go
set showplan_text off
t_Files - это таблица A описанная ранее.результат:
(<b>0</b> row(s) affected)

DateOfRegistrationOfFile CodeLPU FileName 
<i>------------------------------------------------------ ----------- ------------ 
</i>
(<b>0</b> row(s) affected)

StmtText 
<i>---------------------------------------------------------------------------------------------------------------------------------------------------- 
</i>delete tfs
from Accounts.dbo.t_Files tfs INNER JOIN #tf t ON tfs.DateOfRegistrationOfFile=t.DateOfRegistrationOfFile
	and tfs.CodeLPU=t.CodeLPU

(<b>1</b> row(s) affected)

StmtText 
<i>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
</i> |<i>--Sequence
</i> |<i>--Table Spool
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Files].[PK_t_Files]))
</i> | |<i>--Top(ROWCOUNT est 0)
</i> | |<i>--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
</i> | |<i>--Nested Loops(Inner Join, OUTER REFERENCES:([t].[CodeLPU], [t].[DateOfRegistrationOfFile]))
</i> | |<i>--Table Scan(OBJECT:([tempdb].[dbo].[#tf_________________________________________________________________________________________________________________0000000000BE] AS [t]))
</i> | |<i>--Clustered Index Seek(OBJECT:([Accounts].[dbo].[t_Files].[PK_t_Files] AS [tfs]), SEEK:([tfs].[DateOfRegistrationOfFile]=[t].[DateOfRegistrationOfFile] AND [tfs].[CodeLPU]=[t].[CodeLPU]) ORDERED FORWARD)
</i> |<i>--Table Spool
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Accounts].[PK_t_Scheta]))
</i> | |<i>--Table Spool
</i> | |<i>--Nested Loops(Inner Join, OUTER REFERENCES:([t_Files].[DateOfRegistrationOfFile], [t_Files].[CodeLPU]))
</i> | |<i>--Table Spool
</i> | |<i>--Clustered Index Seek(OBJECT:([Accounts].[dbo].[t_Accounts].[PK_t_Scheta]), SEEK:([t_Accounts].[DateOfRegistrationOfFile]=[t_Files].[DateOfRegistrationOfFile] AND [t_Accounts].[CodeLPU]=[t_Files].[CodeLPU]) ORDERED FORWARD)
</i> |<i>--Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_CategoriaOfCase]))
</i> | |<i>--Sort(ORDER BY:([t_Cases].[CategoriaOfCase] ASC, [t_Cases].[DateOfRegistrationOfFile] ASC, [t_Cases].[CodeLPU] ASC, [t_Cases].[Account] ASC, [t_Cases].[NumberOfCase] ASC, [Bmk1008] DESC))
</i> | |<i>--Table Spool
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[PK_t_Sluchai]))
</i> | |<i>--Hash Match(Inner Join, HASH:([t_Accounts].[DateOfRegistrationOfFile], [t_Accounts].[CodeLPU], [t_Accounts].[Account])=([t_Cases].[DateOfRegistrationOfFile], [t_Cases].[CodeLPU], [t_Cases].[Account]), RESIDUAL:(([t_Cases].[Date
</i> | |<i>--Table Spool
</i> | |<i>--Index Scan(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_CategoriaOfCase]), ORDERED FORWARD)
</i> |<i>--Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_Birthday]))
</i> | |<i>--Sort(ORDER BY:([t_Cases].[BirthDay] ASC, [t_Cases].[DateOfRegistrationOfFile] ASC, [t_Cases].[CodeLPU] ASC, [t_Cases].[Account] ASC, [t_Cases].[NumberOfCase] ASC, [Bmk1008] DESC))
</i> | |<i>--Table Spool
</i> |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Meduslugi].[t_Meduslugi_PK]))
</i> |<i>--Hash Match(Inner Join, HASH:([t_Meduslugi].[DateOfRegistrationOfFile], [t_Meduslugi].[CodeLPU], [t_Meduslugi].[Account], [t_Meduslugi].[NumberOfCase])=([t_Cases].[DateOfRegistrationOfFile], [t_Cases].[CodeLPU], [t_Cases].[Account], [t_Cases]
</i> |<i>--Clustered Index Scan(OBJECT:([Accounts].[dbo].[t_Meduslugi].[t_Meduslugi_PK]))
</i> |<i>--Table Spool
</i>
(<b>28</b> row(s) affected)


да присутствует.Ну так spool - это создание временного объекта в базе tempdb.Посмотрите сколько записей и какого размера вам предсказывают для каждого spool-а и получите ращзмер необходимого места в tempdb


Посмотрите сколько записей и какого размера вам предсказывают для каждого spool-а и получите ращзмер необходимого места в tempdb
При просмотре графического Estimated Execution Plan'а я кое-где ниже в текстовом плане указал кол-во записей Estimated row Count(ERC) и Estimated row size(ERS), там где не указывал там ERC=1.2 Glory. Я так понял что надо просить винт для увеличения раздела?
StmtText 
<i>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
</i> |<i>--Sequence
</i> |<i>--Table Spool (Estimated row count=1, E. row size=19 )
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Files].[PK_t_Files]))
</i> | |<i>--Top(ROWCOUNT est 0)
</i> | |<i>--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
</i> | |<i>--Nested Loops(Inner Join, OUTER REFERENCES:([t].[CodeLPU], [t].[DateOfRegistrationOfFile]))
</i> | |<i>--Table Scan(OBJECT:([tempdb].[dbo].[#tf_________________________________________________________________________________________________________________0000000000BE] AS [t]))
</i> | |<i>--Clustered Index Seek(OBJECT:([Accounts].[dbo].[t_Files].[PK_t_Files] AS [tfs]), SEEK:([tfs].[DateOfRegistrationOfFile]=[t].[DateOfRegistrationOfFile] AND [tfs].[CodeLPU]=[t].[CodeLPU]) ORDERED FORWARD)
</i> |<i>--Table Spool (здесь ERC=6036, ERS=29)
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Accounts].[PK_t_Scheta]))
</i> | |<i>--Table Spool
</i> | |<i>--Nested Loops(Inner Join, OUTER REFERENCES:([t_Files].[DateOfRegistrationOfFile], [t_Files].[CodeLPU]))
</i> | |<i>--Table Spool (здесь E. row count = 6036, ERS=34)
</i> | |<i>--Clustered Index Seek(OBJECT:([Accounts].[dbo].[t_Accounts].[PK_t_Scheta]), SEEK:([t_Accounts].[DateOfRegistrationOfFile]=[t_Files].[DateOfRegistrationOfFile] AND [t_Accounts].[CodeLPU]=[t_Files].[CodeLPU]) ORDERED FORWARD)
</i> |<i>--Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_CategoriaOfCase]))
</i> | |<i>--Sort(ORDER BY:([t_Cases].[CategoriaOfCase] ASC, [t_Cases].[DateOfRegistrationOfFile] ASC, [t_Cases].[CodeLPU] ASC, [t_Cases].[Account] ASC, [t_Cases].[NumberOfCase] ASC, [Bmk1008] DESC))
</i> | |<i>--Table Spool (здесь ERC=1,ERS=64)
</i> | |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[PK_t_Sluchai]))
</i> | |<i>--Hash Match(Inner Join, HASH:([t_Accounts].[DateOfRegistrationOfFile], [t_Accounts].[CodeLPU], [t_Accounts].[Account])=([t_Cases].[DateOfRegistrationOfFile], [t_Cases].[CodeLPU], [t_Cases].[Account]), RESIDUAL:(([t_Cases].[Date
</i> | |<i>--Table Spool 
</i> | |<i>--Index Scan(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_CategoriaOfCase]), ORDERED FORWARD)
</i> |<i>--Index Delete(OBJECT:([Accounts].[dbo].[t_Cases].[IX_t_Cases_Birthday]))
</i> | |<i>--Sort(ORDER BY:([t_Cases].[BirthDay] ASC, [t_Cases].[DateOfRegistrationOfFile] ASC, [t_Cases].[CodeLPU] ASC, [t_Cases].[Account] ASC, [t_Cases].[NumberOfCase] ASC, [Bmk1008] DESC))
</i> | |<i>--Table Spool 
</i> |<i>--Clustered Index Delete(OBJECT:([Accounts].[dbo].[t_Meduslugi].[t_Meduslugi_PK]))
</i> |<i>--Hash Match(Inner Join, HASH:([t_Meduslugi].[DateOfRegistrationOfFile], [t_Meduslugi].[CodeLPU], [t_Meduslugi].[Account], [t_Meduslugi].[NumberOfCase])=([t_Cases].[DateOfRegistrationOfFile], [t_Cases].[CodeLPU], [t_Cases].[Account], [t_Cases]
</i> |<i>--Clustered Index Scan(OBJECT:([Accounts].[dbo].[t_Meduslugi].[t_Meduslugi_PK]))
</i> |<i>--Table Spool
</i>


В общем вывод сделан следующий (если не прав поправьте):Каскадное удаление на большой базе использовать нельзя, так как будут дикие тормоза (добавил еще один файл для tempdb и удалял он 0 записей 40 минут, конечно то что используется в качестве сервера сервером назвать нельзя, но все равно пусть даже если бы дисковая система была бы в 4-6 раз шустрее то все равно ужас какой-то). Исходя из того что было, при использовании каскадного удаления получается что размер tempdb должен быть примерно таким же как сумма размеров таблиц из которых будет произведено удаление. Может конечно это не всегда так, но в моем случае на данный момент получается что tempdb должна иметь такой размер.Каскадное обновление 1 записи выполнилось конечно не на раз-два, но за 4 минуты при условии что обновлялось первое поле входящее в состав первичного КЛАСТЕРНОГО ключа.