Найти несжатый размер всех таблиц в базе данных

В Dynamics AX существует механизм кэширования, в котором таблицы могут быть настроены для загрузки в память и кэширования. Этот кеш ограничен некоторым количеством КБ для предотвращения проблем с памятью. Настройка, о которой я говорю, называется entiretablecache и загружает всю таблицу в память, как только запрашивается одна запись.

До недавнего времени мы полагались на некоторые скрипты, чтобы проверить размер таблиц, которые имеют этот параметр, чтобы увидеть, превышает ли размер таблицы этот предел.

Теперь, однако, вступает в игру сжатие, и такие вещи, как sp_spaceused или sys.allocation_units , похоже, сообщают о пространстве, фактически используемом сжатыми данными.

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

Я знаю sp_estimate_data_compression_savings , но, как говорится в названии, это всего лишь оценка.
 Я бы предпочел, чтобы размер был как можно более правильным.

Единственный способ, которым я мог подумать, - это какой-то запутанный динамический SQL, создающий несжатые таблицы с той же структурой, что и сжатые таблицы, вставка сжатых данных в эту теневую таблицу, а затем проверку размера этой теневой таблицы. Излишне говорить, что это немного утомительно, и требуется некоторое время для работы в базе данных из нескольких сотен ГБ.

Powershell может быть вариантом, но мне не хотелось бы перебирать все таблицы для выполнения select *, чтобы проверить размер в скрипте, так как это просто наполнило бы кеш и, вероятно, займет много времени.

Короче говоря, мне нужен способ получить размер для каждой таблицы, поскольку он будет один раз несжатый и с фрагментацией из уравнения, представленного приложению, если это возможно. Я открыт для разных подходов, предпочтение отдается T-SQL, но я не против Powershell или других творческих подходов.

Предположим, что буфер в приложении - это размер данных. Bigint всегда имеет размер bigint, а тип символьных данных - 2 байта на символ (юникод). Данные BLOB также берут размер данных, перечисление - это в основном int, а числовые данные - числовые (38,12), datetime - размер datetime. Кроме того, нет значений NULL, они либо сохраняются как пустая строка, 1900-01-01 или ноль.

Нет документации о том, как это реализовано, но предположения основаны на некоторых тестах и ​​сценариях, используемых PFE и командой поддержки (которые также игнорируют сжатие, по-видимому, поскольку проверка встроена в приложение, и приложение может 't определить, сжаты ли базовые данные), которые также проверяют размеры таблиц. Эта ссылка содержит:

  

Избегайте использования кешей EntireTable для больших таблиц (в AX 2009 более 128   KB или 16 страниц, в AX 2012 по заявлению «весь размер кэша таблицы»   установка [по умолчанию: 32 КБ или 4 страницы]) - вместо этого перейдите к кэшированию записей.

12 голосов | спросил Tom V 27 +03002017-10-27T13:08:15+03:00312017bEurope/MoscowFri, 27 Oct 2017 13:08:15 +0300 2017, 13:08:15

2 ответа


7
  

Мне нужен фактический размер, который будет иметь несжатые данные.
  ...
  Я предпочел бы, чтобы размер был как можно более правильным.

Хотя стремление к этой информации, безусловно, понятно, получение этой информации, особенно в контексте «правильной, насколько это возможно», сложнее, чем все ожидают из-за ошибочных предположений. Независимо от того, упоминается ли в заявке идея несжатого теневого стола в вопросе, или предложение @ sp_BlitzErik в комментарии о восстановлении базы данных и разжатии там для проверки, не следует предполагать, что размер несжатой таблицы == размер указанных данных в памяти на сервере приложений:

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

    Вопрос обновлен, чтобы указать: да, все строки кэшируются.

  2. Накладные расходы структуры

    1. На стороне БД:
      Страница и ряд строк на стороне БД: количество строк, расположенных на странице, определяется многими факторами, которые могут сбрасывать оценки. Даже с помощью FILLFACTOR из 100 (или 0), вероятно, останется еще некоторое неиспользованное пространство на странице, достаточно для целой строки. И это в дополнение к заголовку страницы. Кроме того, если включена функция блокировки моментальных снимков, будет, по-моему, дополнительно 13 байт на строку, занятых номером версии, и это приведет к смещению оценок. Существуют другие мелодии, относящиеся к фактическому размеру строки (NULL битмап, столбцы переменной длины и т. Д.), Но упомянутые выше элементы должны сделать только одну точку.
    2. На стороне сервера приложений:
      Какой тип коллекции используется для хранения кэшированных результатов? Я предполагаю, что это приложение .NET, так что это DataTable? Общий список? A SortedDictionary? Каждый тип коллекции имеет разное количество подслушивания. Я бы не ожидал, что какой-либо из параметров обязательно отразит накладные расходы страницы и строки на стороне БД, особенно в масштабе (я уверен, что небольшое количество строк может не иметь достаточно разных вопросов, но вы не ищете различий в сотни байт или всего несколько килобайт).
  3. Datatypes
    1. На стороне БД:
      CHAR /VARCHAR хранится в 1 байт на символ (без учета двухбайтовых символов на данный момент). XML оптимизирован, чтобы не занимать почти столько места, сколько подразумевалось в текстовом представлении. Этот тип данных создает словарь имен элементов и атрибутов и заменяет фактические ссылки на них в документе соответствующими идентификаторами (на самом деле это красиво). В противном случае строковыми значениями являются все UTF-16 (2 или 4 байта на «символ»), точно так же, как NCHAR /NVARCHAR. DATETIME2 находится между 6 и 8 байтами. DECIMAL составляет от 5 до 17 байт (в зависимости от точности).
    2. На стороне сервера приложений:
      Строки (опять же, предполагая .NET) всегда являются UTF-16. Оптимизация для 8-битных строк отсутствует, например, VARCHAR. НО, строки также могут быть «интернированы», что является общей копией, на которую можно ссылаться много раз (но я не знаю, работает ли это для строк в коллекциях или если это так, если она работает для всех типов коллекций). XML может храниться или не сохраняться одинаково в памяти (мне придется это посмотреть). DateTime всегда 8 байтов (например, T-SQL DATETIME, но не нравится DATE, TIME, или DATETIME2). Decimal всегда 16байт .

Все это говорит о том, что на стороне БД вы почти ничего не можете сделать, чтобы получить точный объем памяти размером справедливо на стороне сервера приложений. Вам нужно найти способ опроса самого сервера приложений, после загрузки определенной таблицы, так что знайте, насколько она велика. И я не уверен, что отладчик позволит вам увидеть размер времени выполнения заполненной коллекции. Если нет, то единственный способ приблизиться - пройти через все строки таблицы, умножая каждый столбец на соответствующий размер .NET (например, INT = * 4, VARCHAR = DATALENGTH() * 2, NVARCHAR = DATALENGTH(), XML =

ответил Solomon Rutzky 27 +03002017-10-27T18:01:14+03:00312017bEurope/MoscowFri, 27 Oct 2017 18:01:14 +0300 2017, 18:01:14
6

Из вашего вопроса кажется, что у вас максимальный размер кеша S, и вы не хотите загружать таблицы в кеш которые превышают этот размер. Если это правда, вам не нужно знать точный размер каждой таблицы. Вам просто нужно знать, больше или меньше таблицы, чем максимальный размер кеша S. Это значительно проще в зависимости от определений столбцов таблиц и количества строк.

Я согласен с замечательным ответом Соломона Руцки на то, что смотреть на несжатые данные - это не путь, и было бы трудно найти хорошее приближение для истинного размера таблицы в кеше. Тем не менее, я собираюсь работать в рамках вопроса и предположить, что вы можете разработать формулу, достаточно близкую на основе определений столбцов для статических типов данных и фактической длины ваших динамических столбцов.

Если у вас есть сопоставление типов данных с размером кеша, вы можете оценить некоторые таблицы, даже не глядя на данные в них:

  1. Если таблица имеет только статические типы данных (без строк или капли), вы можете приблизиться к количеству строк, просмотрев sys.partitions и вычислить размер таблицы, используя определения столбцов.
  2. Если таблица с большим количеством строк имеет достаточно столбцов статического типа данных, вы можете устранить ее как слишком большую, не глядя на свои данные. Например, таблица с 10 миллионами строк и столбцами 5 BIGINT может иметь размер этих данных размером 10000000 * (8 + 8 + 8 + 8 + 8) = 400 Мбайт, что может быть больше, чем ограничение размера кеша S. Не имеет значения, имеет ли он также столбцы столбцов.
  3. Если таблица с несколькими строками достаточно мала, вы можете подтвердить, что она ниже предела, просто предположив, что каждый динамический тип данных имеет максимально возможный размер. Например, таблица из 100 строк с столбцом BIGINT и NVARCHAR(20) не может превышать 100 * (8 + 2 * 20) = 4800 байт.
  4. Может быть, верно, что если таблица имеет сжатый размер в SQL Server, который больше по коду S, что это крайне маловероятно для установки в кеш. Вам нужно будет провести тестирование, чтобы выяснить, существует ли такое значение.
  5. Вам может повезти, что у всех динамических столбцов есть статистика по ним. Статистика содержит информацию о средней длине и может быть достаточно точной для ваших целей.

Возможно, вам придется запрашивать данные таблиц, которые не соответствуют ни одному из вышеуказанных критериев. Есть несколько трюков, которые можно использовать для минимизации влияния производительности. Я бы сказал, что у вас есть два конкурирующих приоритета здесь: вы цените точность, но также не хотите сканировать все данные в своей базе данных. Возможно, вы сможете добавить какой-то буфер в свои расчеты. Я не знаю, является ли более приемлемым исключить таблицу, которая немного ниже максимального размера кеша S или включить небольшую таблицу выше максимального размера кеша.

Вот некоторые идеи для быстрого выполнения запросов, которые смотрят на данные таблицы:

  1. Для больших таблиц вы можете использовать TABLESAMPLE, если размер вашего образца достаточно велик.
  2. Для больших таблиц с кластеризованным ключом может быть полезно обрабатывать их партиями кластеризованного ключа. К сожалению, я не знаю, как вычислить SUM(), который рано заканчивается на основе значения этого агрегата. Я только видел эту работу для ROW_NUMBER(). Но вы можете сканировать первые 10% таблицы, сэкономить расчетный размер данных, сканировать следующие 10% и т. Д. Для таблиц, которые слишком велики для кеша, вы можете сэкономить значительную часть работы с этим подходом, уйдя раньше.
  3. Для некоторых таблиц вам может быть достаточно повезло, чтобы иметь индексы покрытия для всех динамических столбцов.В зависимости от размера строки или других факторов сканирование каждого индекса за раз может быть быстрее, чем сканирование таблицы. Вы также можете выйти из этого процесса раньше, если размер таблицы слишком велик после прочтения индекса в одном столбце.
  4. Средние длины ваших динамических столбцов со временем могут сильно не меняться. Возможно, было бы целесообразно сэкономить средние длины, которые вы вычисляете, и использовать эти значения в своих вычислениях некоторое время. Вы можете сбросить эти значения на основе активности DML в таблицах или на основе какой-либо другой метрики.
  5. Если вы можете запустить тесты по всем таблицам для разработки алгоритма, вы сможете использовать шаблоны в данных. Например, если вы обрабатываете таблицы, начиная с наименьшего первого, вы можете обнаружить, что после того, как вы обработаете 10 (я сделал этот номер вверх) таблицы в слишком большой для кеша строке, очень маловероятно, что любые большие таблицы будут вписываться в кэш. Это может быть приемлемым, если можно исключить несколько таблиц, которые могли бы поместиться в кеше.

Я понимаю, что в этот ответ я не включил SQL-код. Дайте мне знать, было бы полезно написать демо-код для любой из идей, которые я обсуждал здесь.

ответил Joe Obbish 29 +03002017-10-29T08:44:15+03:00312017bEurope/MoscowSun, 29 Oct 2017 08:44:15 +0300 2017, 08:44:15

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132