Для того, чтобы узнать размер всей бд, или, например, одной таблицы, у нас есть команда
'sp_spaceused'.
Но если пойти дальше, то возникает вопрос: а как получить статистику по полям? Как
узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые данные, а в каком
самые легкие?
Вопрос не столько теоретический, сколько практический: есть огромная база, которую
надо проанализировать (постепенно погружаюсь в Data-Mining).
Ответы
Ответ 1
Используйте функцию DATALENGTH()
В следующем примере находится длина столбца ProductName в таблице MyOrderTable.
SELECT DATALENGTH(ProductName) as [SizeInBytes] FROM MyOrderTable
Для справки: DATALENGTH
Ответ 2
Как узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые
данные, а в каком самые легкие?
Вас интересует размер в байтах, занимаемый тем или иным столбцом на диске (видимо
так, раз вы упомянули sp_spaceused)?
Не уверен, что возможно определить его точно. Точно можно узнать, сколько страниц
(блоков по 8Кб, которыми SqlServer хранит данные) занимают данные всей таблицы (или
индекса).
Месту на диске, отведенному под хранение конкретного столбца, по-видимому можно дать
лишь некоторую оценку (которая, впрочем, не всегда будет адекватной). И с datalength
не совсем всё просто. Далее несколько подробнее.
Возьмём запрос
/*1*/ select sum(datalength([Column])) from [Table];
за основу оценки.
Во-первых. Кроме собственно данных столбца всегда есть дополнительная служебная информация,
которая может иметь отношение к столбцу, но её объем между столбцами логически может
делиться непропорционально их количеству в таблице (например данные заголовка строки).
Т.е. оценку размера /*1*/ следует воспринимать как "не менее чем".
Чем меньше в таблице столбцов, и чем короче запись, тем больше издержки на служебные
данные, тем, соответственно, дальше оценка /*1*/ от реальности. Так, для таблицы с
одним коротким столбцом полный размер данных (с учётом служебной информации) может
значительно превосходить "логический" размер данных самого столбца. Сравните, например,
для таблицы
create table tiny_nums (value tinyint);
insert into tiny_nums (value)
select top (1048576) 1
from master.sys.all_columns a cross join master.sys.all_columns b;
результат, возвращаемый запросом /*1*/ с тем, что покажет sp_spaceused.
Во-вторых. Значение, возвращаемое datalength не всегда соответствует действительности.
В частности, если datalength([Column]) возвращает NULL, то физически это может быть
вовсе не ноль.
Дело в том, что типы столбцов делятся на fixed-length (напр. int, char(20), datetime2(0),
uniqueidentifier, и т.п.) и variable-length (напр. varbinary(64), nvarchar(30) и т.п.).
И если для variable-length оценка /*1*/ приблизительно справедлива, то для fixed-length
столбцов резервируется место для хранения значения, даже если само значение NULL.
Т.е. для fixed-length столбцов оценку /*1*/ следует скорректировать, используя вместо
NULL (если они возможны) какое-либо непустое значение, соответствующее типу столбца
(например 0 для int):
/*2*/ select sum(datalength(isnull([IntColumn], 0))) from [Table];
Также нужно учитывать, что для столбцов типа bit возвращаемое datalength значение
равно 1. Однако если в таблице (или индексе) несколько bit столбцов, то SqlServer объединяет
их по 8 в 1 байт.
Также столбцы могут быть sparse, что означает 0 байт на хранение NULL (даже для fixed-length),
но плюс 4 дополнительных байта на хранение значения, если оно не NULL:
/*3*/ select sum(datalength([SparseColumn]) + 4) from [TableName];
В-третьих. Если столбец не просто присутствует в таблице, а ещё и участвует в индексах,
то он "утяжеляется" кратно количеству индексов, в которых он участвует. Если столбец
является ключевым в кластерном индексе, то нужно прибавить оценку кратную количеству
всех некластерных индексов (т.к. в leaf-level страницах некластерных индексов содержатся
значения ключей кластерного индекса). Так в таблице
create table SomeTable
(
PK_ID bigint primary key,
FK_1_ID int,
FK_2_ID int,
...
FK_10_ID int,
UID uniqueidentifier
);
create index IX_1 on SomeTable (FK_1_ID);
...
create index IX_10 on SomeTable (FK_10_ID);
самым "тяжёлым" скорее всего окажется вовсе не UID столбец, а PK_ID, т.к. (помимо
участия в кластерном первичном ключе) значения PK_ID будут присутствовать ещё в 10-ти
некластерных индексах.
Следует учесть также, что если некластерный индекс является фильтрованным индексом,
то соответствующую оценку (/*1*/, /*2*/ или /*3*/) нужно взять не по всей таблице,
а по строкам, соответствующим фильтру такого индекса.
В-четвертых (относится к Enterprise edition). Если применяется сжатие строк или страниц
таблицы
alter table [TableName]
rebuild partition = all with (data_compression = row);
либо индекса
alter index [IX_Name] on [TableName]
rebuild with (data_compression = page);
то оценки с помощью datalength перестают быть адекватными и фактор "не менее чем"
перестаёт работать.
Сравните для таблиц
create table strings (value char(2000));
insert into strings (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;
и
create table strings_page (value char(2000)) with (data_compression = page);
insert into strings_page with (tablock) (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;
значения оценки размера столбца с помощью datalength c тем, что покажет sp_spaceused.
Для первой таблицы "показания" datalength и sp_spaceused будут близки (т.к. строка
таблицы "широкая" и объем служебной информации сказывается мало), а для второй будут
расходиться очень сильно.
В-пятых. Всё что было сказано до этого момента справедливо для SqlServer 2008. В
более поздних версиях появились COLUMNSTORE индексы, которые, из-за особенностей своего
устройства, могут хранить данные в существенно сжатом виде. Для них оценка размера
столбца с помощью datalength также может давать неадекватный результат. Если для таблицы
create table strings_col (value char(2000));
insert into strings_col (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;
create clustered columnstore index ix_clcs on strings_col;
сравнить показания sp_spaceused с datalength, то опять можно наблюдать сильное расхождение.
Полагаю, что данный список факторов, которые следует учитывать при оценке места,
занимаемого тем или иным столбцом, не исчерпывающий.
Закрыт. Данный вопрос необходимо конкретизировать. Ответы
на него в данный момент не принимаются.
Хотите улучшить этот вопрос? Переформулируйте вопрос,
чтобы он был сосредоточен только на одной проблеме, отредактировав его.
Закрыт 3 года назад.
Объясните на пальцах про IoC-контейнер Ninject — как используется и в целом зачем
все это надо?
Читаю литературу, никак не могу понять. Лучше на практике один раз увидеть.
Ответы
Ответ 1
На пальцах вряд ли получится, тема сложная. Возникла она приблизительно тогда же,
когда объектно-ориентированные языки стали применять для разработки многозвенных приложений.
Типичное многозвенное приложение состоит из трёх уровней: уровня представления, уровня
предметной области и уровня доступа к данным.
В классической схеме зависимость между уровнями распространяется сверху вниз: уровень
представления зависит от уровня предметной области, а тот, в свою очередь — от уровня
доступа к данным.
+----------------------------+
| Уровень представления |
+----------------------------+
\/
+----------------------------+
| Уровень предметной области |
+----------------------------+
\/
+----------------------------+
| Уровень доступа к данным |
+----------------------------+
Рассмотрим простой пример с веб-приложением. Пусть, например, у нас есть список заказов,
которые мы хотим показывать пользователю:
public OrderController : Controller
{
[HttpGet]
public ActionResult Index()
{
var userId = GetCurrentUserId();
var repository = new OrderRepository();
var orders = repository.GetAllByUserId(userId);
return View(orders);
}
}
Для рендеринга используем простую разметку:
@model IEnumerable
@{
ViewBag.Title = "Список заказов";
}
@foreach (var order in Model)
{
}
Номер
Дата
Сумма
@order.Number
@order.Date
@order.Amount
Это уровень представления, где конечный пользователь видит список заказов на сайте.
Чтобы получить этот список, мы обращаемся к хранилищу заказов (это класс уровня предметной
области) и получаем заказы (тоже предметная область). Список этих заказов мы отправляем
в движок рендеринга HTML, который и превратит их в веб-страницу.
А вот как выглядит код уровня доступа к данным, если обращается к данным через Entity
Framework:
public OrderRepository
{
public IReadOnlyCollection GetAllByUserId(int userId)
{
using (var dbContext = new MyDbContext())
{
return dbContext.Orders
.AsNoTracking()
.Where(x => x.UserId == userId)
.AsEnumerable()
.Select(x => new Order(x.Id, x.Number, x.Date, x.Amount))
.ToArray();
}
}
}
Мы загружаем из БД сырые данные, и создаём из них классы предметной области, обладающие
также и поведением.
Классы уровня доступа к данным, это, например, MyDbContext и OrderData. Получается
вроде бы всё хорошо и расширяемо, но.
Что нужно, чтобы перейти в таком приложении от веб интерфейса к оконному? В идеале
— написать один новый уровень представления, оконный. Два оставшихся уровня останутся
прежними. Здорово.
А что нужно, чтобы перейти в таком приложении с SQL на или MongoDb или файловое хранилище?
Переписать все уровни, поскольку нижний переписывать всё равно придётся, а вслед за
ним придётся переписывать всё, что от него зависит. Нездорово.
Что можно сделать, чтобы упростить перенос таких приложений на другие хранилища?
Инвертировать зависимость, то есть сделать так, чтобы уровень доступа данных зависел
от уровня предметной области.
+----------------------------+
| Уровень представления |
+----------------------------+
\/
+----------------------------+
| Уровень предметной области |
+----------------------------+
/\
+----------------------------+
| Уровень доступа к данным |
+----------------------------+
Если мы сделаем так, то получится, что предметная область станет центральной. От
неё будут зависеть уровни представления (веб, консоль, десктоп) и уровня доступа к
данным (SQL, MongoDb, XML-файлы). Мы сможем расширять приложение, добавляя модули сверху
и снизу, поскольку они будут зависеть только от уровня предметной области.
Возникает вопрос: но ведь тогда появляется зависимость от центрального уровня предметной
области? Что, если мы захотим переписать её?
Ответ неожиданный: именно предметная область определяет всё приложение. Если это
Word то в предметной области описаны такие штуки, как документы, параграфы, форматирование
и всё остальное. В отличие от предыдущих случаев задача подменить предметную область
просто бессмысленна, у вас получается другое приложение.
Значит, инвертирование зависимости вещь полезная. Но как её осуществить практически?
Практически мы должны описать абстракцию (интерфейс) доступа к данным. Вместо конкретного
класса OrderRepository у нас появляется интерфейс:
public interface IOrderRepository
{
IReadOnlyCollection GetByUserId(int userId);
}
Это интерфейс предметной области. Он используется в контролере OrderController нашего
MVC приложения (то есть он доступен с уровня представления).
public OrderController : Controller
{
private readonly IOrderRepository _orderRepository;
public OrderController(IOrderReposiotory orderRepository)
{
_orderRepository = orderRepository;
}
[HttpGet]
public ActionResult GetOrders()
{
var userId = GetCurrentUserId();
var orders = _orderRepository.GetAllByUserId(userId);
return View(model);
}
}
Код отличается от предыдущего тем, что уже не может создать объект класса OrderRepository
непосредственно, более того, из контроллера этот класс совсем недоступен. Мы имеем
доступ только к абстракции (интерфейсу) и ожидаем получить реализацию через конструктор
контроллера.
Кто-то снаружи должен создать объект OrderRepository и передать его экземпляр в наш
конструктор. Пока отложим рассмотрение вопроса, кто этот «кто-то» и посмотрим, как
репозиторий реализован на уровне доступа к данным.
public OrderRepository : IOrderRepository
{
public IReadOnlyCollection GetAllByUserId(int userId)
{
using (var dbContext = new MyDbContext())
{
return dbContext.Orders
.AsNoTracking()
.Where(x => x.UserId == userId)
.AsEnumerable()
.Select(x => new Order(x.Id, x.Number, x.Amount))
.ToArray();
}
}
}
Здесь интерфейс одного уровня реализуется на другом уровне. Физически зависимость
означает, что из проекта, где реализован OrderRepository должен стоять reference на
проект, где описан IOrderRepository и в данном случае мы видим, что зависимость инвертирована:
уровень доступа к данным зависит от уровня представления.
Теперь, если мы захотим изменить представление, нам не надо менять OrderRepository,
достаточно вместо веб-интерфейса реализовать другой, например, консольный интерфейс.
Если мы захотим изменить реализацию с EF на NHibernate, нам достаточно будет переписать
только репозитории, не трогая весь остальной проект, например, контроллеры.
Остаётся вопрос: а кто же увязывает друг с другом интерфейсы и реализации? Тот самый
IoC-контейнер, в частности, NInject.
В проекте, где создается этот контейнер, сходятся все зависимости, поэтому он называется
корнем композиции. Что нужно сделать? Нужно подменить стандартный IDependencyResolver
из ASP.NET MVC своей реализацией на базе NInject и зарегистрировать в контейнере свои
зависимости. О реализации написано, например, здесь.
Регистрация выполняется в приватном методе AddBindings:
public class NinjectDependecyResolver : IDependencyResolver
{
private readonly IKernel kernel;
public NinjectDependecyResolver()
{
kernel = new StandardKernel();
AddBindings();
}
public object GetService(Type serviceType)
{
return kernel.TryGet(serviceType);
}
public IEnumerable
