Статьи

Расчет таблицы SQL и физических размеров строк

Ноябрь 22, 2018

Клиент заметил внезапное и резкое увеличение потребления диска базы данных. Тревогой, которая сработала, был монитор низкого места на диске. По-видимому, в рассматриваемой базе данных осталось всего несколько свободных ГБ на диске. Заинтересованный клиент открыл галочку, задав два вопроса: нормальный и ожидаемый рост базы данных? и каковы средние физические требования к памяти на строку?

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

Google возвращает довольно много результатов ( 1 , 2 , 3 , 4 и 5 ). Похоже, что для MS SQL практически все полагаются на хранимый процесс sp_spaceused . В SQL есть недокументированный sproc sp_msforeachtable, который работает над каждой таблицей в базе данных и выполняет sproc, передавая имя каждой таблицы в качестве параметра. Хотя это совсем не сложно сделать вручную (циклически перебирая sys.Tables — едва ли подвиг), вызывать этот однострочный текст все равно очень удобно. Поэтому неудивительно, что практически все образцы онлайн просто делают это.

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

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

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

-- Copyright (c) 2011, Ashod Nakashian
-- All rights reserved.
--
-- Redistribution and use in source and binary forms, with or without modification,
-- are permitted provided that the following conditions are met:
--
-- o Redistributions of source code must retain the above copyright notice,
-- this list of conditions and the following disclaimer.
-- o Redistributions in binary form must reproduce the above copyright notice,
-- this list of conditions and the following disclaimer in the documentation and/or
-- other materials provided with the distribution.
-- o Neither the name of the author nor the names of its contributors may be used to endorse
-- or promote products derived from this software without specific prior written permission.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
-- EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
-- OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT
-- SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
-- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
-- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
--
-- Show physical size statistics for each table in the database.
--
SET NOCOUNT ON

-- Update all page and count stats.
-- Comment for large tables on production!
DBCC UPDATEUSAGE(0) 

-- Total DB size.
EXEC sp_spaceused

-- Per-table statistics.
DECLARE @t TABLE
(
    [name] NVARCHAR(128),
    [rows] BIGINT,
    [reserved] VARCHAR(18),
    [data] VARCHAR(18),
    [index_size] VARCHAR(18),
    [unused] VARCHAR(18)
)

-- Collect per-table data in @t.
INSERT @t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

-- Calculate the averages and totals.
INSERT into @t
SELECT 'Average', AVG(rows),
    CONVERT(varchar(18), AVG(CAST(SUBSTRING([reserved], 0, LEN([reserved]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), AVG(CAST(SUBSTRING([data], 0, LEN([data]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), AVG(CAST(SUBSTRING([index_size], 0, LEN([index_size]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), AVG(CAST(SUBSTRING([unused], 0, LEN([unused]) - 1) AS int))) + ' KB'
FROM   @t
UNION ALL
SELECT 'Total', SUM(rows),
    CONVERT(varchar(18), SUM(CAST(SUBSTRING([reserved], 0, LEN([reserved]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), SUM(CAST(SUBSTRING([data], 0, LEN([data]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), SUM(CAST(SUBSTRING([index_size], 0, LEN([index_size]) - 1) AS int))) + ' KB',
    CONVERT(varchar(18), SUM(CAST(SUBSTRING([unused], 0, LEN([unused]) - 1) AS int))) + ' KB'
FROM   @t

-- Holds per-row average kbytes.
DECLARE @avg TABLE
(
    [name] NVARCHAR(128),
    [data_per_row] VARCHAR(18),
    [waste_per_row] VARCHAR(18)
)

-- Calculate the per-row average data in kbytes.
insert into @avg
select t.name,
    CONVERT(varchar(18),
        CONVERT(decimal(20, 2),
            (CAST(SUBSTRING(t.[data], 0, LEN(t.[data]) - 1) AS float) +
             CAST(SUBSTRING(t.[index_size], 0, LEN(t.[index_size]) - 1) AS float))
            / NULLIF([rows], 0))) + ' KB',
    CONVERT(varchar(18),
        CONVERT(decimal(20, 2),
            (CAST(SUBSTRING(t.[reserved], 0, LEN(t.[reserved]) - 1) AS float) +
             CAST(SUBSTRING(t.[unused], 0, LEN(t.[unused]) - 1) AS float))
            / NULLIF([rows], 0))) + ' KB'
from @t t

-- Join the two tables using the table names.
select t.name, t.rows, t.reserved, t.data, t.index_size, t.unused, a.data_per_row, a.waste_per_row
from @t t, @avg a
where t.name = a.name

Существует немало преобразований и приведения данных, которые не обязательно очень производительны, но здесь нет большого выбора, и дальнейшая оптимизация, вероятно, не нужна. Но поскольку существует много разных способов получить один и тот же результат, я оставлю любые варианты читателям. Предложения и улучшения приветствуются. Пожалуйста, используйте комментарии, чтобы поделиться своими альтернативами.

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

С http://blog.ashodnakashian.com/2011/07/calculation-sql-table-and-row-physical-size/

62
Share: