У меня есть сценарий, в котором каждому пользователю было выделено 2 МБ места в базе данных. Теперь я должен показать процент использования выделенного пространства, для этого мне нужно знать размер одной записи в таблице.
Я пытался использовать sp_spaceused и даже написал собственную процедуру с использованием длины данных ... но они показывают огромную разницу в своих результатах.
Есть ли способ сделать это правильно.
Запустите DBCC SHOWCONTIG с именем вашей таблицы.
dbcc showcontig ('TableName') with tableresults
затем посмотрите на максимальный минимальный и средний размер записи
Следующий запрос сообщит вам процент @SpaceAllotted, который используется данными, посмотрев, сколько страниц выделено объектами базы данных.
Не стоит пытаться измерять использование на уровне строк, поскольку SQL Server выделяет все пространство на уровне экстентов. Каждый экстент состоит из восьми страниц данных по 8 КБ. Таким образом, если в вашей базе данных была только одна строка, и эта строка содержала 4 байта данных, все равно нужно было бы выделить весь экстент для хранения этих 4 байтов (или использовать существующий экстент с нераспределенными страницами. Это называется смешанным экстентом). ).
DECLARE @SpaceAllotted FLOAT
-- 2MB converted to kilobytes...
SET @SpaceAllotted = 2048
SELECT
-- Allocation is done on the extent-level.
-- Each extent contains eight 8KB data pages.
((1 / (@SpaceAllotted)) * CEILING(CAST(SUM([ips].[page_count]) AS FLOAT) / 8) * 64) * 100 AS PercentageUsed
FROM
[sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) ips
-- This will allow us to retrieve the page count of all tables in the
-- current database, regardless of whether or not they have clustered
-- indexes and/or non-clustered indexes.
INNER JOIN
[sys].[indexes] i
ON
[ips].[object_id] = [i].[object_id]
AND [ips].[index_id] = [i].[index_id]
Поскольку у нас есть возможность смешанных экстентов и нет хорошего способа (способы есть, но они не будут красивыми) определения того, какие страницы и каким экстентам отведены, это не так. 100% точно. Кроме того, экстенты могут даже иметь свободные страницы (которые зарезервированы и, следовательно, все еще занимают место на диске), поэтому обычно эта оценка всегда будет низкой. Тем не менее, это, вероятно, лучшее, что вы получите, не написав что-нибудь для проверки базы данных на уровне страницы.
Ах да, глядя на другие ответы, это еще один вариант. Это в основном будет смотреть на все текущие размеры файлов данных на страницах и определять процент пространства, которое они потребляют. Тут тоже есть оговорки...
Надеюсь, что один из них решит вашу проблему.
SELECT
((1 / CAST(SUM([df].[max_size]) AS FLOAT)) * CAST(SUM([df].[size]) AS FLOAT)) * 100 AS PercentUsed
FROM
[sys].dm_io_virtual_file_stats(DB_ID(), NULL) vfs
INNER JOIN
[sys].[database_files] df
ON
[vfs].[file_id] = [df].[file_id]
WHERE
[df].[type] = 0
Поскольку showcontig будет объявлен устаревшим, вы также можете использовать:
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')
Вы можете рассчитать размер строки (количества строк) с помощью довольно сложной формулы. См. книги в Интернете (ms-help://MS.SQLCC.v9). /MS.SQLSVR.v9.en/udb9/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm) для получения более подробной информации.
Короче говоря:
Возьмите количество столбцов и определите размер каждого столбца по его типу данных для типов данных фиксированной длины.
Рассчитайте пространство, используемое для любых столбцов, допускающих значение NULL. Затем рассчитайте пространство строк:
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
Данные сохраняются на диске в каталогах, названных в честь базы данных. Это включает в себя все, включая то, что вы не измеряете (индексы и т. д.). Измерьте это.
