SQL Server daki tablolalarım disk üzerinde ne kadar yer kaplıyor

Bugün, database'imizde bulunan tüm tabloların datalarının ve indexlerinin ne kadar yer tuttuğunu gösteren bir stored procedure yazacağız. Aşağıdaki kod ile sistemdeki tüm tabloların bilgilerini görebiliriz.

SELECT * FROM sys.tables

sp_MStablespace @TableName sistem prosedürü SQL Server 2000'de de vardı. SQL Server 2005'de de çalışıyor. Çalıştırıldığında tek satırlık 3 kolonluk bir resultset dönüyor.

• Rows: Tabloda kaç satır var


• DataSpaceUsed : Tablodaki veriler ne kadar yer kaplıyor


• IndexSpaceUsed :Tablonun index leri ne kadar yer kaplıyor

Bize örnek oluşturması için bir test tablosu yaratalım içine biraz data ekleyelim.

CREATE TABLE Test(ID int identity(1,1),TextVal varchar(200))
GO
DECLARE @i int

SET @i=1

WHILE @i<=100 BEGIN
-- Rastgele birşeyle oluşturalım
INSERT INTO Test(TextVal)
SELECT Replicate('ab',@i)
SET @i = @i+ 1
END


SELECT * FROM Test

ID	TextVal
1	ab
2	abab
3	ababab
4	abababab
5	ababababab
6	abababababab
7	ababababababab
8	abababababababab
9	ababababababababab
...	....

100. satırda, 100 kere 'ab' içeren bir datamız olacak.

Yarattığımız tabla ne kadar yer kaplıyormuş bakalım:

exec sp_MStablespace 'Test'

Rows	DataSpaceUsed	IndexSpaceUsed
100	16	8

Bütün tabloların boyutlarını öğrenmek istersek sys.tables view sistem view ini kullanarak tüm tabolar için bunu çalıştırabiliriz

CREATE TABLE #TableSize(TableName nvarchar(255),Rows int,DataSpace int, IndexSpace int)

DECLARE @Table nvarchar(255)


DECLARE oCur CURSOR READ_ONLY FAST_FORWARD FOR

SELECT name

FROM sys.tables

ORDER BY name


OPEN oCur

WHILE (1=1) BEGIN

FETCH NEXT FROM oCur INTO @Table

IF @@FETCH_STATUS <> 0 BREAK


INSERT INTO #TableSize(Rows,DataSpace,IndexSpace)

EXEC SP_MSTABLESPACE @table


-- Hangi tablo olduğu bilgisi proc. den dönmüyor. Null olarak yazdık. Şimdi güncelleyelim

UPDATE #TableSize SET TableName = @Table WHERE TableName is null

END

CLOSE oCur

DEALLOCATE oCur

SELECT * FROM #TableSize

Şimdi bunu başka bir yöntem ile yapalım.
Tablonun boyutunu öğrenmek için kullanılabilecek bir sistem prosedürü daha var : sp_spaceused @TableName
Bu procedure biraz daha farklı bir sonuç döndürüyor ancak dönen resultset içerisinde tablo nun ismi de var.

exec sp_spaceused 'Test' sonucu:

name	rows	reserved	data	index_size	unused
Test	100	24 KB	16 KB	8 KB	0 KB

Buradan dönen değerleri yine bir tabloya yazalım ama bu sefer cursor yerine farklı bir yöntem kullanalım.




Daha çok tüm tabloların indexlerini yeniden yaratmak için kullandığım bir sistem prosedürü var.

EXEC sp_MSforeachtable @command1='Command String'


@command1 e verilen sql ifadesini çalıştırır. Tablo parametresi geçmek için command içerisinde tablo adı yerine ? geçilir

CREATE TABLE #TableSize
(
[name] nvarchar(255),
[rows] int,
[reserved] varchar(20),
[data] varchar(20),
[index_size] varchar(20),
[unused] varchar(20)
)
-- Mevcut database deki tüm tablolar için sp_spaceused proc. ü çağırılıp, dönen recordset #TableSize tablosu eklenir
EXEC sp_MSforeachtable @command1=
"INSERT INTO #TableSize
EXEC sp_spaceused '?'"

SELECT * FROM #TableSize