To do checking to the size used in a table in SQL Server, you can do this query :
SAMPLE :
EXEC sp_spaceused ssmaddr, 'TRUE'
note : ssmaddr is the table name
RESULT :
name rows reserved data index_size unused
ssmaddr 25948 218944 KB 32256 KB 72 KB 186616 KB
SYNTAX :
sp_spaceused [[ @objname= ] 'objname' ] [,[ @updateusage= ] 'updateusage' ]
[ @objname=] 'objname'
Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.
If objname is not specified, results are returned for the whole database.
objname is nvarchar(776), with a default of NULL.
[ @updateusage=] 'updateusage'
Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true orfalse. updateusage is varchar(5), with a default of false.
| Column name | Data type | Description |
| name | nvarchar(128) | Name of the object for which space usage information was requested. The schema name of the object is not returned. If the schema name is required, use thesys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information. |
| rows | char(11) | Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue. |
| reserved | varchar(18) | Total amount of reserved space for objname. |
| data | varchar(18) | Total amount of space used by data in objname. |
| index_size | varchar(18) | Total amount of space used by indexes in objname. |
| unused | varchar(18) | Total amount of space reserved for objname but not yet used. |
Like you can see, that the unused space of the table is 2.311.672 KB, and the data itself is only 524.712 KB.
This usually happened when a table didn't use clustered index.
ANALOGY :
Table is like a book, example : debts table.
There is 5 people that owe you some money, then we will note it down in our book ( in this case, our debts table) :
Customer Balance
A 1000
B 2000
C 3000
D 4000
E 5000
Then Customer A give the moeny back to us, what we do is we delete Customer A from our book (debts table)
Customer Balance
B 2000
C 3000
D 4000
E 5000
After that customer F, borrow some money from us, what we do, off course, we note that in our book (debts table)
Now this is the different between clustered index and non-clustered index :
On clustered index :
Customer Balance
F 6000
B 2000
C 3000
D 4000
E 5000
In non-clustered index :
Customer Balance
B 2000
C 3000
D 4000
E 5000
F 6000
On clustered index, the empty part (row), will be re-used
So, the solution is :
SOLUTIONS :
On SQL 2000 before SP4(clustered or non-clustered index), SQL Server 2000 SP 4 and after(non-clustered index):
Copy all the table to temporary table, delete the original table content, then put the content from temporary table, back to original table.
Pada SQL 2000 SP4 & SQL 2005 and after (clustered index) :
SAMPLE :
DBCC DBREINDEX ssmaddr
DBCC INDEXDEFRAG
SYNTAX :
DBCC DBREINDEX ( table_name [ , index_name [ , fillfactor ] ] ) [ WITH NO_INFOMSGS ] table_name
Is the name of the table containing the specified index or indexes to rebuild. Table names must follow the rules for identifiers.
index_name
Is the name of the index to rebuild. Index names must comply with the rules for identifiers. If index_name is specified, table_name must be specified. If index_name is not specified or is " ", all indexes for the table are rebuilt.
fillfactor
Is the percentage of space on each index page for storing data when the index is created or rebuilt. fillfactorreplaces the fill factor when the index was created, becoming the new default for the index and for any other nonclustered indexes rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. This value is stored in the sys.indexes catalog view.
If fillfactor is specified, table_name and index_name must be specified. If fillfactor is not specified, the default fill factor, 100, is used. For more information, see Fill Factor.
WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.
SYNTAX :
DBCC INDEXDEFRAG ( { database_name | database_id | 0 } , { table_name | table_id | 'view_name' | view_id } , { index_name | index_id } ) [ WITH NO_INFOMSGS ]
database_name | database_id | 0
Is the database for which to defragment an index. Database names must conform to the rules for identifiers. For more information, see Using Identifiers. If 0 is specified, then the current database is used.
table_name | table_id | 'view_name' | view_id
Is the table or view for which to defragment an index. Table and view names must conform to the rules for identifiers.
index_name | index_id
Is the index to defragment. Index names must conform to the rules for identifiers.
WITH NO_INFOMSGS
Suppresses all informational messages (with severity levels from 0 through 10).
Result :
Before :
name rows reserved data index_size unused
ssmaddr 25948 218944 KB 32256 KB 72 KB 186616 KB
After
name rows reserved data index_size unused
ssmaddr 25948 6800 KB 6664 KB 48 KB 88 KB
Reference :
http://msdn.microsoft.com/en-us/library/ms188776.aspx
http://support.microsoft.com/kb/924947
http://msdn.microsoft.com/en-us/library/ms181671.aspx
