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

Откуда: Minsk
Сообщений: 59
Добрый день всем.
Передо мной поставили задачу:
Разработать механизм для оценки роста объема базы в день, на основании бизнес условий: числа агентов/сервисов/сессий в день.
Пользователь задает эти 3 параметра, а на выходе должен получать ориентировочный объем прироста в день.

В моем распоряжении будет бэкап, за какой-то период (пока не знаю какой) в каждой таблице есть поле CreateDateTime, на основании которого можно говорить о времени добавления записи.

Я предполагаю начать с оценки количества строк, добавленных в каждую таблицу за день
Затем вычислить количество сессий, по тем же дням
Вычислить среднюю длину строки по таблице (тут сразу предчувствую проблему учета размера индексов и ключей).

На основании сопоставления этих даных выполенять оценки роста.

Подскажите пожалуйста как правилно решать поставленную задачу.
19 авг 13, 11:15    [14724425]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
saycale
Member

Откуда: Москва->Сидней
Сообщений: 32
Как один из вариантов, достаточно грубую оценку размера базы данных можно получить на основе анализа архивного копирования этой базы, если архивное копирование выполнялось регулярно. Таблица [msdb].[dbo].[backupset], поле [backup_size]
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

SELECT TOP 100
	 tBS.[database_name]                                                                         AS [DatabaseName]
	,tBS.[type]                                                                                  AS [BackupType]
	,tBS.[backup_finish_date]                                                                    AS [BackupFinishDate]
	,CONVERT([NUMERIC](20, 2), tBS.[backup_size] / (CONVERT([NUMERIC](20, 2), 1024.0 * 1024.0))) AS [DatabaseBackupSizeMB]
FROM
	[msdb].[dbo].[backupset] tBS
WHERE
	tBS.[backup_finish_date] IS NOT NULL
	AND tBS.[type] = 'D'
ORDER BY
	tBS.[backup_finish_date] DESC;
Возможно использовать данную статистику для проверки работы на основе бизнес условий.
19 авг 13, 12:24    [14724899]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
saycale
Member

Откуда: Москва->Сидней
Сообщений: 32
Если из базы данных происходит удаление данных, то это тоже нужно учитывать. Размер одной записи можно оценить исходя из размера таблицы и числа записей в этой таблице.
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

DECLARE
	@intPageSize [NUMERIC]
;

SELECT TOP 1
	@intPageSize = CONVERT([INTEGER], tSV.[low] / 1024)
FROM
	[master].[dbo].[spt_values] tSV
WHERE
	tSV.[number] = 1
	AND tSV.[type] = N'E';

SELECT
	 tSS.[name]                                                   AS [SchemaName]
	,tSO.[name]                                                   AS [ObjectName]
	,tSO.[type_desc]                                              AS [ObjectType]
	,tSO.[create_date]                                            AS [ObjectCreateDate]
	,SUM(
		CASE
			WHEN (tSP.[index_id] IN (0, 1)) AND (tSAU.[type] = 1) THEN
				tSP.[rows]
			ELSE
				0
		END
	)                                                             AS [ObjectRowCount]
	,SUM(tSAU.[total_pages] * @intPageSize)                       AS [ObjectTotalKb]
	,SUM(tSAU.[used_pages] * @intPageSize)                        AS [ObjectUsedKb]
	,SUM(
		CASE
			WHEN (tSP.[index_id] IN (0, 1)) AND (tSAU.[type] = 1) THEN
				tSAU.[used_pages] * @intPageSize
			ELSE
				0
		END
	)                                                             AS [ObjectDataKb]
	,SUM(
		CASE
			WHEN (tSP.[index_id] IN (0, 1)) AND (tSAU.[type] = 1) THEN
				0
			ELSE
				tSAU.[used_pages] * @intPageSize
		END
	)                                                             AS [ObjectIndexKb]
	,SUM((tSAU.[total_pages] - tSAU.[used_pages]) * @intPageSize) AS [ObjectUnUsedKb]
FROM
	[sys].[objects] tSO
	INNER JOIN [sys].[schemas] tSS ON
		tSS.[schema_id] = tSO.[schema_id]
	INNER JOIN [sys].[partitions] tSP ON
		tSP.[object_id] = tSO.[object_id]
	INNER JOIN [sys].[allocation_units] tSAU ON
		tSAU.[container_id] = tSP.[partition_id]
WHERE
	tSS.[name] = N'имя моей схемы'
	AND tSO.[name] = N'имя моей таблицы'
GROUP BY
	 tSS.[name]
	,tSO.[name]
	,tSO.[type_desc]
	,tSO.[create_date];
19 авг 13, 12:29    [14724936]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
design21, бугуа... успехов. Зависимость между агентов/сервисов/сессий вряд ли будет линейной...
19 авг 13, 12:52    [14725102]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
saycale,

Спасибо за советы, буду копать.
19 авг 13, 13:44    [14725460]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
baracs
Member

Откуда: Москва
Сообщений: 7206
design21
Добрый день всем.
Передо мной поставили задачу:
Разработать механизм для оценки роста объема базы в день, на основании бизнес условий: числа агентов/сервисов/сессий в день.
...
Вычислить среднюю длину строки по таблице (тут сразу предчувствую проблему учета размера индексов и ключей).
Под "объемом базы" подразумевается только объем данных и индексов или размер файлов БД?
19 авг 13, 14:03    [14725602]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
baracs
design21
Добрый день всем.
Передо мной поставили задачу:
Разработать механизм для оценки роста объема базы в день, на основании бизнес условий: числа агентов/сервисов/сессий в день.
...
Вычислить среднюю длину строки по таблице (тут сразу предчувствую проблему учета размера индексов и ключей).
Под "объемом базы" подразумевается только объем данных и индексов или размер файлов БД?


Под объемом базы подразумевается именно конечный размер файлов.
19 авг 13, 15:43    [14726253]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
baracs
Member

Откуда: Москва
Сообщений: 7206
design21
baracs
Под "объемом базы" подразумевается только объем данных и индексов или размер файлов БД?


Под объемом базы подразумевается именно конечный размер файлов.
Тогда надо учитывать размер автоприращения файлов, модель восстановления БД, смотреть, что происходит с журналом транзакций...

Вообще, размер файлов врядли меняется каждый день. Если такое происходит, то это плохо. Почитайте рекомендации.
19 авг 13, 16:37    [14726653]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
вы хоть читали что автору нужно по т.з. сделать... ? ему нужно прогнозировать рост ваза в зависимости от набора неких параметров... как только он эту зависимость установит - все остальное копеечный вопрос...
19 авг 13, 16:47    [14726739]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
baracs
Member

Откуда: Москва
Сообщений: 7206
buser
вы хоть читали что автору нужно по т.з. сделать... ? ему нужно прогнозировать рост ваза в зависимости от набора неких параметров... как только он эту зависимость установит - все остальное копеечный вопрос...
Где тут про рост ВАЗа?

Вот я и подсказываю, что эта зависимость еще загадочней...
19 авг 13, 17:06    [14726862]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
в общем для установления этой зависимости... предположим, что она есть, нужно накопить сперва статистику по "числу агентов/сервисов/сессий в день/ прирост файлов бд" ну а потом... что там было в разделе "КОРРЕЛЯЦИОННЫЙ И РЕГРЕССИОННЫЙ АНАЛИЗ" ... или как он там... для расчета весов каждого из параметров... ну и копить... данные - усложнять модель. Я, к сожалению, уже все забыл...
19 авг 13, 18:17    [14727203]     Ответить | Цитировать Сообщить модератору
 Re: Оценка роста базы  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Решал задачу следующим путем:
1. За основу взял бэкап существующей продакшн базы.
2. Получил список всех таблиц в базе.
3. Получил размер размер таблиц и число строк в каждой таблице с помощью системной процедуры "sp_spaceused".
4. Вычислил на этом основании средний размер одной строки для каждой таблицы.
5. Выделил основные процессы, влияющие на рост базы (дальше буду пользоваться термином "процесс"). Для меня это оказались - новые сессии, смена статуса агента и различные логи. Для каждого процесса можно выделить список таблиц, в которые записывается соответствующая информация.
6. Проанализировал все таблиц, выделил группы таблиц по каждому процессу и присвоил им типы:
a. Таблица не растет.
b. Таблица растет в результате процесса 1.
c. Таблица растет в результате процесса 2.
и т.д.
7. В качестве исходных данных для вычислений необходимо представить – ежедневное количество итераций каждого процесса. Для меня это количество новых сессий в день или количество смен статуса агента в день - первое вводит пользователь, второе было получено из соотвествующей таблицы базы.
8. Для каждой таблицы можно вычислить коэффициент, который показывает: сколько строк добавляется в данную таблицу, при одной итрации процесса:
Для вычисления коэффициента роста таблицы делим количество строк добавленных за день, на число итераций соответвующего процесса в тот же день.
a. Коэффициент роста равен 0 для таблиц которые не растут.
b. Коэффициент роста больше 1 для обязательных таблиц. Например, каждая 3я сессия обслуживается двумя агентами, поэтому коэффициент роста таблицы «агенты сессий» - 4/3.
c. Коэффициент роста меньше 1, если строки в таблицу добавляются не всегда, а при наличии каких-то особых условий. Например каждая вторая сессия является внешней. В случае внешней сессии добавляется строка в специальную таблиц «внешний номер» - коэффициент роста 1/2.
9. Усредняю за выбранный пользователем промежуток времени (стабильной работы системы).
Все!

Примерно буду считать прирост базы в день, как сумма приростов каждой из таблиц:
Средний размер строки * коэффициент роста * число итераций процесса в день.
1. Перемножение и суммирование таблиц загнал в Excel.
2. Для вычисления исходных данных: коэффициентов роста и среднего размера строк сделал хранимую процедуру.
Предоставил файл вычислений и хранимую процедуру пользователям, теперь буду ждать результатов с полей.
2 сен 13, 17:00    [14785506]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить