Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Не использованное место  [new]
Devil_FoX
Member

Откуда: Киев
Сообщений: 39
Всем привет.

С помощью запроса ниже, я обнаружил что очень много таблиц содержат некое не используемое место (поле unusedKB). На данный момент у меня по всем таблицам набежало почти 3 гига. Как с ним бороться, откуда оно берётся и на что таблицы так расходуют драгоценные метры?


DECLARE @pagesizeKB int 

SELECT @pagesizeKB = low / 1024 FROM master.dbo.spt_values 

WHERE number = 1 AND type = 'E' 

SELECT
	table_name = OBJECT_NAME(o.id),
	rows = i1.rowcnt,
	reservedKB = (ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0)) * @pagesizeKB, 
	dataKB = (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0)) * @pagesizeKB, 
	index_sizeKB = ((ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))- (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB, 
	all_kb=((ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0)) * @pagesizeKB)+(((ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))- (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB),
	unusedKB = ((ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0)) - (ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB 
FROM sysobjects o 
	LEFT OUTER JOIN sysindexes i1
		ON i1.id = o.id AND i1.indid < 2 
	LEFT OUTER JOIN sysindexes i2
		ON i2.id = o.id AND i2.indid = 255 
WHERE
	OBJECTPROPERTY(o.id, N'IsUserTable') = 1 --same as: o.xtype = %af_src_str_2 
	OR (
		OBJECTPROPERTY(o.id, N'IsView') = 1
		AND OBJECTPROPERTY(o.id, N'IsIndexed') = 1
		) 
GROUP BY
	o.id,
	i1.rowcnt 
ORDER BY
	7 DESC
13 авг 15, 17:00    [18014353]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Devil_FoX
Как с ним бороться, откуда оно берётся и на что таблицы так расходуют драгоценные метры?


А с ним не надо бороться без особой необходимости. Это нормально.

Судя по вашему вопросу -- вам надо разобраться с тем, что такое fillfactor. Ну и как им управлять и зачем.

https://msdn.microsoft.com/en-us/library/ms177459.aspx
13 авг 15, 17:10    [18014405]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1738
Devil_FoX,

Временное решение - создание и сразу после этого удаление кластерного индекса на таблице. Имеет смысл применять на редко обновляемых таблицах.
Гораздо более выгодно с точки зрения освобождения места в базе - регулярный rebuild / reorganize фрагментированных индексов.

Оба варианта необходимо повторить после транкейта файлов базы данных.
13 авг 15, 17:23    [18014498]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1738
a_voronin
Devil_FoX
Как с ним бороться, откуда оно берётся и на что таблицы так расходуют драгоценные метры?


А с ним не надо бороться без особой необходимости. Это нормально.

Судя по вашему вопросу -- вам надо разобраться с тем, что такое fillfactor. Ну и как им управлять и зачем.

https://msdn.microsoft.com/en-us/library/ms177459.aspx


https://msdn.microsoft.com/ru-ru/library/ms177459(v=sql.105).aspx
13 авг 15, 17:24    [18014510]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Devil_FoX
Member

Откуда: Киев
Сообщений: 39
a_voronin,
Спасибо, почитаю.

DaniilSeryi,

DaniilSeryi
Оба варианта необходимо повторить после транкейта файлов базы данных.

Удаление данных?
13 авг 15, 17:38    [18014563]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
o-o
Guest
DaniilSeryi
Временное решение - создание и сразу после этого удаление кластерного индекса на таблице.

в книгу вредных советов или только что оттуда?
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
If you create a clustered index, all the linkages to the heap records are no longer valid and so
all the nonclustered indexes must be rebuilt automatically to pick up the new clustering key links.
If you drop the clustered index again, all the clustering key links are now invalid so
all the nonclustered indexes must be rebuilt automatically to pick up the new heap physical location links.

In other words, if you create and then drop a clustered index, all the nonclustered indexes are rebuilt twice.
13 авг 15, 19:25    [18015014]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
o-o
Guest


К сообщению приложен файл. Размер - 73Kb
13 авг 15, 19:26    [18015018]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
ALTER TABLE REBUILD, 2012+.
14 авг 15, 15:00    [18018348]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
o-o
Guest
Владислав Колосов
ALTER TABLE REBUILD, 2012+.

да ладно, и на 2008 прокатит.
просто август и всем лень сходить по приведенной выше ссылке.
хорошо, скопирую и это:
Randal
Yes, you can use ALTER TABLE … REBUILD in SQL Server 2008 to remove heap fragmentation, but that is almost as bad as creating and dropping a clustered index!
...
If you think you can use ALTER TABLE … REBUILD in SQL Server 2008 to fix heap fragmentation, you can, but it causes all the nonclustered indexes to be rebuilt as the heap record locations obviously change.

Randal, the best choice
Do yourself a favor and just create the well-chosen clustered index and leave it there
14 авг 15, 16:07    [18018771]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20596
Если возникает мысль о "драгоценных метрах" - значит, их на сервере мало. Следовательно, их недостаточно. Следовательно, можно ожидать краша сервера и, возможно, утраты данных, по причине нехватки свободного пространства. Следовательно, DBA не озаботился необходимой конфигурацией оборудования, в частности, объёмом дисковой подсистемы. Он не справляется со своими обязанностями. Уволить нахрен.
14 авг 15, 16:17    [18018846]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
o-o
Guest
Akina,
а если пишет несчастный разработчик, а админ вообще вне досягаемости?
у нас именно так.
и место жмотят тоже.
так что и не уволишь, и места никогда не получишь.
----
от себя скажу, что кучи жмотят место во всех видах,
и если про DELETE это задокументировано,
то про прерванные инсерты (ROLLBACK) -- нет.
попробуйте сделать большую вставку в кучу инсертом,
прервав его.
после роллбэка данных в таблице не будет, а вот место под строки сожрано.
вот у меня было недавно.
как раз откатилось, тк места в базе не было,
3 гига отката, 3 гига сожрано.
в таблице 0 записей.
и это хорошо, что 0, удалю эту кучу и все,
а вот когда что-то откатилось, а уже строки были, пока не перестроишь/перельешь кучу,
место не вернешь

К сообщению приложен файл. Размер - 98Kb
14 авг 15, 16:35    [18018971]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Devil_FoX
Member

Откуда: Киев
Сообщений: 39
Akina,

Ох, твои бы слова да высшему начальству в уши. :)
Я простой аналитик баз данных, но то DBA не соизволили указать что изменены критические расчёты исходных данных, то тупо забыли залить на сервер, то тупо не оповестили что заводится новый сервер, а старый закрывается (из-за чего в прошлом месяце потеряно 3 нервных дня, простоя работы и переделывание всех Excel файлов на новое имя сервера), то какого то включаются настройки времени на американский лад (итого летят все расчёты, которые настроены на день недели).. и т.д. и т.п.
А насчёт места - приходится самому искать у себя в БД оптимизировать всё. Уже написал историю "веса" и активность (Read и Write) каждой таблицы на каждый день, что бы выявить нарушителя (есть такие коллеги что не оптимизируют таблицы и за раз сжирают по 3-8 гигов) и заброшенные таблицы. Рассылку по почте повесил на все критичные исходные таблицы. ну и т.д.
А насчёт "уволить нахрен" - кум/сват/брат не дадут.


o-o,

Почитаю ссылки на выходных, спасибо. Просто очень занят.
14 авг 15, 16:39    [18019004]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20596
o-o
Akina,
а если пишет несчастный разработчик, а админ вообще вне досягаемости?

Тогда ему следует вспомнить простой, но эффективный метод под названием "Чем больше бумаги, тем чище жопа".
Проанализируй потребности разрабатываемой системы - кто, как не разработчик, должен понимать, что ей потребуется для комфортной бесперебойной работы? Сравни потребные ресурсы с имеющимися. Проанализируй возможные проблемы из-за нехватки ресурсов. На основании этих данных составь грамотную служебную записку - исходные данные, потребность системы, узкие места, возможные проблемы, предлагаемые пути решения проблем и "расшивки" узких мест, включая потребные материально-финансово-человеческие ресурсы,- и пусти официальным порядком.
Если проблемы не случится - ну и пусть себе бумажка благополучно сдохнет в дебрях внутренней бюрократии. Но если случится - то претензии будут не к тебе, ибо всё, что ты мог в пределах своей компетенции и зоны ответственности, ты сделал, и вот тому документальное подтверждение. Трахайте того, кто это предупреждение прогавал.
14 авг 15, 17:11    [18019279]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37067
Модератор: Akina, давайте чуть спокойнее, лады?
14 авг 15, 22:08    [18020472]     Ответить | Цитировать Сообщить модератору
 Re: Не использованное место  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Akina
...


+100 за фразу про бумагу и чистоту. очень понравилось.
14 авг 15, 22:22    [18020505]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить