QWERTY SQL: августа 2012

Сегодня я хотел бы поговорить о SQL Express, а именно о лимите в 10ГБ на файлы данных. Многие разработчики выбирают эту СУБД для своих проектов. Она имеет достаточно шрокий функционал. Но рано или поздно, они сталкиваются с ограничениями которые не позволяют дальше пользоваться бесплатной версией. Одно из ограничений это лимит на файлы данных. Сегодня я постараюсь ответить на вопрос можно ли как-то обойти это ограничение.

Давайте сначала вспомним что бесплатная Express версия имеет следующие ограничения:

Может использовать только 1 Физический процессор (но все ядра)
Использует только 1 ГБ оперативной памяти (на компьютере может быть установлен любой объем)
Имеет ограничение 10ГБ на файлы данных (начиная с версии SQL Server 2008 R2)

С процессором и оперативной памятью все понятно. Давайте подробнее остановимся на файлах данных.

Ограничение касается только фалов данных, но не файлов логов;
Ограничение распространаяется только на одну базу данных, т.е. вы можете иметь несколько баз на одном инстансе SQL Express;
Ограничение не распространяется на FileStream.

О FileStream я напишу в следующем посте. А сейчас я расскажу и продемострирую как мы можем распределить данные по нескольким базам существено не меняя наше приложение, тем самым уйдя от 10ГБ лимита. Речь пойдет о базе данных в которых ода или несколько таблиц существено больше остальных, и они занимают львиную долю всего пространства в базе данных.

К примеру у нас в базе имеется таблица dbo.Data следующей структуры, и она занимает в БД более 80% места.

use master;
go

create database BigDb;
go

use BigDb;
go

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (Id)
)

-- заполним базу данных данными, чтобы было с чем работать
;with cte ( id )
as (
    select 1
    union all
    select id + 1
    from cte
    where id < 500000
)
insert into BigDb.dbo.Data 
(
    Id
  , CreatedDate
  , PlaceHolder
)
select
    id
  , dateadd(minute, id, '20120826')
  , 'cte data'
from cte
option ( maxrecursion 0 )

Давайте распределим эту таблицу к примеру на 4 базы. Вообще выбор на сколько частей можно разделить таблицу зависит от баланса между сложностью поддержки мнжества баз, и получаемым в результате объемом хранения. Т.е. не стоит создавать 100 баз данных, потому что их будет сложно поддерживать, да SQL Server будет тормозить (не забывайте про другие ограничения).

Теперь наша задача найти подходящий столбец, по которому возможно было бы разделить данные. Если у вас есть подходящий столбец можно использовать его. Хорошим вариантом был бы какой нибудь тип записи в таблице RecordTypeId, в котором храняться значения от 1-8, тогда можно распределить данные по 8 базам.

В нашем примере у нас такого столбца нет, поэтому нам придется добавить суррогатный тип записи. Итак приступим. Создаем 4 дополнительные базы данных и в них таблицы аналогичной структуры.

use master;
go

create database BigDb1;
create database BigDb2;
create database BigDb3;
create database BigDb4;
go


use BigDb1;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 1 )
)
go

use BigDb2;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 2 )
)
go

use BigDb3;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null     
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 3 )
)
go

use BigDb4;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 4 )
)
go

Обратите внимание на несколько важных вещей. Все они нужны для того чтобы мы смогли создать секционированное представление.

Мы добавили столбец DB tinyint not null - это будет наш сурогатный тип записи (разделитель)
Мы добавили в структуру таблицы ограничение, чтобы в каждой из таблиц мог храниться только определенный тип записи CH_CreatedDate check ( DB = 4 ) например.
Мы включили столбец DB в главный ключ таблицы PK_Data primary key clustered (id, DB)

Далее нам нужно объединить записи из этих таблиц. Это делается c помощью представления dbo.vData. Создаем его в нашей основной базе данных BigDb.

use BigDb;

create view dbo.vData
as  
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb1.dbo.Data d

    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb2.dbo.Data d

    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb3.dbo.Data d
 
    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb4.dbo.Data d 
go

А теперь можно перелить данные через представление.

insert into dbo.vData ( Id, DB, CreatedDate, PlaceHolder )
select
    d.Id
  , d.Id % 4 + 1
  , d.CreatedDate
  , d.PlaceHolder
from dbo.Data d

Обратите внимание как мы заполняем поле DB, это нам придется делать всегда при вставке данных. Если бы у нас было поле типа записи, нам бы не пришлось этого делать, и данные бы вставлялись как обычно.

Теперь, можно проверить как данные распределились по базам

select *
from BigDb1.dbo.Data

select *
from BigDb2.dbo.Data

select *
from BigDb3.dbo.Data

select *
from BigDb4.dbo.Data

Теперь старую таблицу нужно удалить, и работаеть с данными через представление dbo.vData, для простоты можете дать представлению тоже название что имела таблица т.е. dbo.Data.

Хорошо давате теперь поэкспериментируем с типичными запросами к данным, и посмотрим на их планы выполнения.

use BigDb;

select * 
from dbo.vData

Как видите были просканированы 4 таблицы которые находятся в разных базах, после чего результаты были объеденены

use BigDb;

select *
from dbo.vData
where id between 100 and 1000

Тожесамое произошло и здесь, только в место сканирования мы имеем поиск в кластерном индексе

Также можно ограничивать и используемые базы

use BigDb;

select *
from dbo.vData
where id between 100 and 1000
  and DB in ( 1, 3 )

Тут произведен поиск в кластерных индексах только двух таблиц

А как насчет не кластерных индексов, они работают? Давайте проверим. Создаем индексы на поле CreatedDate для каждой таблицы.

use BigDb1;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb2;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb3;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb4;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb;

select
    Id
  , DB
  , CreatedDate
  , PlaceHolder
from dbo.vData
where CreatedDate between '20120827' and '20120828'

Некластерные индексы используютя в плане

Применив такой подход вы конечно же столкнетесь с дополнительными усилиями по сопровождению ваших данных. Ведь теперь у вас не одна база данных а несколько :). Но тем не менее возможно это поможет вам сэкономить пару лишних баксов.

QWERTY SQL

воскресенье, 26 августа 2012 г.

Как хранить более 10 ГБ в SQL Express

Общее·количество·просмотров·страницы

воскресенье, 26 августа 2012 г.

Как хранить более 10 ГБ в SQL Express

Общее·количество·просмотров·страницы

воскресенье, 26 августа 2012 г.