Довольно часто, по мере роста размера базы данных, возникает вопрос - "Откуда?" Тут на помощь придёт обновлённая версия моего запроса, которая формирует по-табличный отчёт о занимаемом пространстве. В полученной таблице прежде всего следует обратить внимание на колонку TotalSize с общим объёмом занимаемым каждой таблицей. Также может оказаться полезной колонка IdxDivData, отображающая соотношение объёма всех индексов таблице к объёму её данных. Таблицы, для которых он более 1, и общий объём значителен точно следует рассмотреть внимательнее.
IF ( OBJECT_ID('tempdb..#svv_tab_row_Count') IS NOT NULL)
DROP TABLE #svv_tab_row_Count
CREATE TABLE #svv_tab_row_Count (
ID INT IDENTITY not null,
Name NVARCHAR(255),
RowsCount INT,
DataSize INT,
IdxSize INT,
TotalSize INT,
IdxDivData MONEY)
DECLARE @tableName NVARCHAR(255)
DECLARE tableCursor CURSOR for
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM tableCursor INTO @tableName
WHILE @@fetch_status=0
BEGIN
INSERT INTO #svv_tab_row_Count
(RowsCount, DataSize, IdxSize)
EXEC sp_MStablespace @tableName
UPDATE #svv_tab_row_Count
SET NAME = @tableName,
TotalSize = DataSize + IdxSize,
IdxDivData = CASE WHEN DataSize = 0 THEN 0 ELSE IdxSize * 1.0 / DataSize END
WHERE ID = IDENT_CURRENT('#svv_tab_row_Count')
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tablecursor
DEALLOCATE tablecursor
GO
SELECT * FROM #svv_tab_row_Count ORDER BY TotalSize DESC
У нас с проекте нашлись таблицы этим коэффициентом большим 2 и, иногда, даже 5. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.
Прекрасно работает
Небольшая доработка в отношении схем
CREATE PROCEDURE [UTILS].pTableSize
AS
BEGIN
IF ( OBJECT_ID('tempdb..#svv_tab_row_Count') IS NOT NULL)
DROP TABLE #svv_tab_row_Count
CREATE TABLE #svv_tab_row_Count (
ID INT IDENTITY not null,
SchemaName NVARCHAR(256),
TableName NVARCHAR(256),
RowsCount INT,
DataSize INT,
IdxSize INT,
TotalSize INT,
IdxDivData MONEY);
DECLARE
@SchemaName NVARCHAR(256),
@TableName NVARCHAR(256),
@TablePath NVARCHAR(512);
DECLARE TableCursor CURSOR FOR
SELECT TABLE_SCHEMA, Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName
WHILE @@fetch_status = 0 BEGIN
SET @TablePath = @SchemaName + '.' + @TableName
INSERT INTO #svv_tab_row_Count
(RowsCount, DataSize, IdxSize)
EXEC sp_MStablespace @TablePath
UPDATE #svv_tab_row_Count
SET
TableName = @TableName,
SchemaName = @SchemaName,
TotalSize = DataSize + IdxSize,
IdxDivData = CASE WHEN DataSize = 0 THEN 0 ELSE IdxSize * 1.0 / DataSize END
WHERE ID = IDENT_CURRENT('#svv_tab_row_Count')
FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName
END;
CLOSE tablecursor;
DEALLOCATE tablecursor;
SELECT *
FROM #svv_tab_row_Count
ORDER BY TotalSize DESC
END
Рад, что моя разработка пригодилась.