sql.ru

По материалам статьи Брайан Найт (Brian Knight) на sqlservercentral.com: " The Basics of Sizing a SQL Server Database"

Выбор оптимального размера базы данных является непростой задачей, которую должны решать администраторы, аналитики или разработчики. Анализ базы данных занимает много времени, прежде чем правильное решение будет найдено. В данной статье рассказывается, как следить за размером базы данных после ее создания и содержится несколько советов по выбору оптимального размера базы данных до ее создания.
Итак, какое оборудование необходимо приобрести для вашей базы данных? От вашего ответа зависит будет ли это SAN или достаточно несколько RAID 1 дисков. К счастью, существует несколько продуктов, которые достаточно точно определяют размер базы данных до ее создания. Например, вы можете использовать ERwin (продукт компании CA) для определения размера вашей базы данных через X месяцев. Вы также можете заполнить базу произвольными данными используя продукты, сходные с Datatect 1.6. Те, кто чувствует в себе смелость, могут выполнить вычисления самостоятельно, но будте внимательны с индексами. Когда большинство людей рассчитывают размер баз данных без помощи специальных продуктов, они забывают учитывать место, необходимое для индексов, вторичных ключей и констрейнтов.
Легче всего вычислить размер базы данных с помощью программ моделирования баз данных, таких как ERwin. ERwin, как и большинство программ моделирования баз данных, имеет панель размера БД, которая позволяет вам указать множество параметров при создании таблицы. В ERwin вы можете указать:

- начальное количество строк
- количество ежемесячно добавляющихся строк
- будет ли столбец содержать NULL

Сочетание этих трех факторов может дать специализированному продукту возможность сгенерировать достоверные отчеты о размере базы данных через некоторое время. Эти отчеты могут быть выполнены в HTML формате, и их даже можно просматривать с помощью обьектов. Большинство программ моделирования баз данных учитывают индексы, вторичные ключи и констрейны. Единственная вещь, которую не учитывает Erwin, это индексированные представления, которые могут занимать существенное место, если они основываются на больших таблицах.
Когда вы имеете общее представление о том, какого размера будет ваша база данных, вы можете проверить вашу теорию с помощью такого продукта, как Datatect 1.6(www.datatect.com). Datatect позволяет вам создать реалистичные произвольные данные для заполнения вашей БД. При этом также учитывается, что загружаемые данные имеют вторичные ключи. После того, как автор статьи перепробовал все другие генераторы данных, он склонен находить, что Datatect 1.6 оказался самым удобным средством для загрузки значительного обьема произвольных данных в его БД. Автор статьи считает, что другие генераторы данных работают неустойчиво и могут легко нарушить работу CPU.
Итак, почему желательно заполнять БД тестовыми данными? Несколько месяцев назад автор использовал ERwin для проектирования базы данных нового продукта, размер которой через 6 месяцев эксплуатации должен был составлять 50 Гб. Разработчики продукта использовали для тестовую БД, содержавшую всего несколько сотен простых записей и не могли понять особенностей реализации для баз такого размера. После заполненния БД с использованием Datatect они получили возможность найти дефекты в приложении, которые могли бы привести к краху сервера через 6 месяцев, когда БД уже будет в промышленной эксплуатации.
С помощью Datatect я заполнил 10 Гб таблицу реалистичными, тестовыми данными за 12 часов. Та же задача с помощью подобного продукта другой, более крупной фирмы, заняла больше 8 дней. Перед загрузкой данных убедитесь, что вы убрали все кластерные индексы. В противном случае время загрузки значительно возрастет. Как только вы имеете тестовую БД вы можете использовать такие программы, как WinRunner, которые позволяют измерить производительность, что бы увидеть, как приложение работает с таким большим количеством записей.
Заключительный совет: следите и изучайте (смотри и учись!). Вы можете пользоваться этим советом, когда хотите достаточно точно определить размер БД и затем воспользоваться полученными прогнозами при установке сервера. Когда автор устанавливает сервер, он создаёт таблицу на каждом сервере, в которой ведёт статистику роста БД. Каждую неделю фиксируется информация о размере каждой БД и копируется в электронную таблицу. Используя данные из электронной таблицы можно построить график для прогнозирования размера БД через год.
Ниже приводится пример хранимой процедуры, с помощью которой вы можете получать необходимые статистические данные:
http://www.sqlservercentral.com/columnists/bknight/usp_database.sql

/* Procedure for 8.0 server */
create proc usp_databases
as
  set nocount on
  declare @name sysname
  declare @SQL nvarchar(600)

  /* Use temporary table to sum up database size w/o using group by */
  create table #databases (
            DATABASE_NAME sysname NOT NULL,
                size int NOT NULL)

  declare c1 cursor for
      select name from master.dbo.sysdatabases
        where has_dbaccess(name) = 1 -- Only look at databases to which we have access

  open c1
  fetch c1 into @name

  while @@fetch_status >= 0
  begin
    select @SQL = insert into #databases
            select N+ @name + , sum(size) from
            + QuoteName(@name) + .dbo.sysfiles
        /* Insert row for each database */
        execute (@SQL)
        fetch c1 into @name
  end
  deallocate c1

  select
        DATABASE_NAME,
        DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */
        RUN_DT=GETDATE()
  from #databases
  order by 1

GO