資料庫調校思路分享2 資料表筆數、容量(大小)

在資料庫調校思路分享有了一些思路,接下來要評估線上資料表數量,評估Solution是否可行.

Required Information:

資料表筆數、容量(大小)資訊

SELECT t.NAME AS TableName,  
i.name as indexName,  
sum(p.rows) as RowCounts,  
sum(a.total\_pages) as TotalPages,  
sum(a.used\_pages) as UsedPages,  
sum(a.data\_pages) as DataPages,  
(sum(a.total\_pages) * 8) / 1024 as TotalSpaceMB,  
(sum(a.used\_pages) * 8) / 1024 as UsedSpaceMB,  
(sum(a.data\_pages) * 8) / 1024 as DataSpaceMB  
FROM sys.tables t  
INNER JOIN sys.indexes i ON t.OBJECT\_ID = i.object\_id  
INNER JOIN sys.partitions p ON i.object\_id = p.OBJECT\_ID AND i.index\_id = p.index\_id  
INNER JOIN sys.allocation\_units a ON p.partition\_id = a.container\_id  
WHERE t.NAME NOT LIKE ‘dt%’   
AND i.OBJECT\_ID > 255   
AND i.index\_id <= 1  
GROUP BY t.NAME, i.object\_id, i.index\_id, i.name  
ORDER BY object\_name(i.object\_id)

Table Size with Index Size

--GO  
CREATE TABLE #MyTblInfo  
(  
[name] nvarchar(256),--資料表名稱  
[rows] int,--現有資料列數量  
[reserved] varchar(18),--資料表磁碟保留空間大小，資料庫中的物件所配置的空間總量。  
[reserved\_int] int default(0),--資料表磁碟保留空間大小整數值，資料庫中的物件所配置的空間總量整數值。  
[data] varchar(18),--資料表實體資料使用磁碟空間大小，資料所用的空間總量。  
[data\_int] int default(0),--資料表實體資料使用磁碟空間大小整數值，資料所用的空間總量整數值。  
[index\_size] varchar(18),--資料表索引使用磁碟空間大小，索引所用的空間總量。  
[index\_size\_int] int default(0),--資料表索引使用磁碟空間大小整數值，索引所用的空間總量整數值。  
[unused] varchar(18),--保留給資料表未使用的磁碟空間大小，資料庫中保留給資料庫物件的空間但尚未使用的空間總量。  
[unused\_int] int default(0)--保留給資料表未使用的磁碟空間大小整數值，資料庫中保留給資料庫物件的空間但尚未使用的空間總量整數值。  
);  
GO  
EXEC sp\_MSforeachtable "INSERT INTO #MyTblInfo ([name],[rows],[reserved],[data],[index\_size],[unused]) EXEC sp\_spaceused '?'";  
GO  
--從[reserved]、[data]、[index\_size]、[unused]取出數字並更新到對應的整數值欄位  
UPDATE #MyTblInfo  
SET [reserved\_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS int),  
[data\_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS int),  
[index\_size\_int] = CAST(SUBSTRING([index\_size], 1, CHARINDEX(' ', [index\_size])) AS int),  
[unused\_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS int);  
GO  
--顯示資料表狀態資料依照檔案大小由大至小排序  
SELECT [name],[rows],[reserved],[reserved\_int],  
[data],[data\_int],[index\_size],[index\_size\_int],  
[unused],[unused\_int],  
CAST([reserved\_int]/1024.0 AS VARCHAR) + ' MB' AS 'TABLESIZE',  
[reserved\_int]/1024.0 AS 'TABLESIZE\_FLOAT'  
FROM #MyTblInfo  
ORDER BY data\_int DESC;  
GO  
--資料表大小  
SELECT (CAST(SUM(reserved\_int)/1024.0 AS VARCHAR) + ' MB') AS 'RESERVED\_FILE\_SIZE',  
(CAST(SUM(data\_int)/1024.0 AS VARCHAR) + ' MB') AS 'DATA\_FILE\_SIZE',  
(CAST(SUM(index\_size\_int)/1024.0 AS VARCHAR) + ' MB') AS 'INDEX\_FILE\_SIZE',  
(CAST(SUM(unused\_int)/1024.0 AS VARCHAR) + ' MB') AS 'UNUSED\_FILE\_SIZE',  
(CAST(SUM(reserved\_int)/1024.0 AS VARCHAR) + ' MB')AS 'TOTAL\_TABLE\_SIZE'  
FROM #MyTblInfo;  
GO  
DROP TABLE #MyTblInfo;  
GO

參考來源:

https://goodlucky.pixnet.net/blog/post/47863749-%5Bms-sql%5D%E6%9F%A5%E8%A9%A2%E8%B3%87%E6%96%99%E8%B3%87%E6%96%99%E5%BA%AB%E5%85%A7%2C%E8%B3%87%E6%96%99%E8%A1%A8%E7%AD%86%E6%95%B8%E3%80%81%E5%AE%B9%E9%87%8F%28

https://kirby86a.pixnet.net/blog/post/90128264

• ← 英文學習--不知道對方是誰的email，怎麼回覆
• SQL Server--資料庫調校思路分享 →