sp_spaceused: Determine SQL Server table size

Have you ever wondered which tables take the most disk space in your database? Using the sp_spaceused stored procedure you can determine the amount of disk space (data and index) used by any given table in the current database.

Example 1 (SQL Server table)

Run the following SQL statement from the SQL Server Management Studio. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused N’dbo.orders
GO

Results

  • name: Table name for which space usage information was requested
  • rows: Number of rows existing in the table
  • reserved: Total amount of reserved space for table data and indexes
  • data: Amount of space used by table data
  • index_size: Amount of space used by table indexes
  • unused: Total amount of space reserved for table but no yet used

Example 2 (SQL Server database)

You can also run sp_spaceused without any parameters to display information about the whole database. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused
GO

Results

First Recordset:

  • database_name: Name of the current database
  • database_size: Size of the current database in megabytes. database_size includes both data and log files
  • unallocated space: Space in the database that has not been reserved for database objects

Second Recordset:

  • reserved: Total amount of space allocated by objects in the database
  • data: Total amount of space used by data
  • index_size: Total amount of space used by indexes
  • unused: Total amount of space reserved for objects in the database, but not yet used

Notes

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When table name is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

sp_configure: Limit SQL Server memory usage

Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Run the following SQL statements to limit the amount of server memory used to 64 MB:

 

Enable advanced options:

USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

 

Set the maximum amount of memory to 64 MB:

USE master
EXEC sp_configure 'max server memory (MB)', 64
RECONFIGURE WITH OVERRIDE

 

Display the newly set configuration:

USE master
EXEC sp_configure 'max server memory (MB)'

 

Set ‘show advanced options’ back to default:

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

GO (SQL Server TSQL)

Signals the end of a batch of SQL statements in Microsoft SQL Server utilities. It is not a Transact-SQL command. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

 

Example

SELECT *
FROM table1
INNER JOIN table2 ON table1.c1 = table2.c1
WHERE table1.c1 > 10
ORDER BY table1.c1

GO

Comments (SQL Server)

Comments indicate user-provided text. There are two different types of comments: the — comment and the /*…*/ comment. Comments can be inserted on a separate line, nested (– only) at the end of a SQL command line, or within a SQL statement. The comment is not evaluated by the server. Two hyphens (–) is the SQL-92 standard indicator for comments.

Example

SELECT *
FROM table1 — this is a comment
INNER JOIN table2 ON table1.c1 = table2.c1
/* this is another comment */
WHERE table1.c1 > 10
ORDER BY table1.c1

DBCC CHECKCONSTRAINS: Verify foreign key integrity

There are two scenarios in SQL Server that can leave tables with rows that are not bound by an existing FOREIGN KEY constraint or CHECK constraint:

  1. Newly added constraints ignore existing table data if created using “WITH NOCHECK”.
  2. Re-enabled constraints ignore all table updates and inserts that took place since the constraint was disabled.

Use DBCC CHECKCONSTRAINS to check the integrity of your FOREIGN KEY constraints and CHECK constraints and make sure there are no constraint violations. The command returns a list of rows containing all violations. DBCC CHECKCONSTRAINTS does not return any rows if there are no constraint violations.

 

Syntax

To check all constraints in the current database:

DBCC CHECKCONSTRAINTS

 

To check all constraints on a single table:

DBCC CHECKCONSTRAINTS (‘table1’)

 

DBCC CHECKCONSTRAINTS checks against enabled constraints only unless WITH ALL_CONSTRAINTS is specified. The returned result consists of table name, constraint name and a WHERE clause that identifies the violating row in the table.

UPDATE (SQL Statement)

Description

Changes existing data in a table.

Syntax

UPDATE  { table_name  | view_name }
SET { column_name = expression } [ ,n ]
[ FROM { table_name  | view_name } [ ,n ] ]
[ WHERE < search_condition > ]

Example

UPDATE table1
SET c1 = 9

Detail

UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmindb_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.

SELECT permissions are also required for the table being updated if the UPDATE statement contains a WHERE clause, or if expression in the SET clause uses a column in the table.

Related Articles

SELECT (SQL Statement)

DELETE (SQL Statement)

INSERT (SQL Statement)

 

DELETE (SQL Statement)

Description

Removes rows from a table.

 

Syntax

DELETE { table_name  | view_name }
[ FROM { table_name  | view_name } [ ,n ] ]
[ WHERE < search_condition > ]

 

Example

DELETE table1
WHERE table1.c1 > 10

Detail

DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmindb_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

SELECT permissions are also required if the statement contains a WHERE clause.

SELECT (SQL Statement)

Description

Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables.

 

Syntax

SELECT [ DISTINCT ] [ TOP n [ PERCENT ]] select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

 

Example

SELECT *
FROM table1
INNER JOIN table2 ON table1.c1 = table2.c1
WHERE table1.c1 > 10
ORDER BY table1.c1

DBCC INDEXDEFRAG Improves SQL Server Index Scanning

Fragmented SQL Server indexes can result in performance degradation and longer index scanning time. DBCC INDEXDEFRAG helps improve index scanning performance by defragmenting clustered and nonclustered indexes.

 

Syntax

DBCC INDEXDEFRAG (database1, table1, index1)

Results

DBCC INDEXDEFRAG returns a result set similar to this:

Pages Scanned Pages Moved Pages Removed
————- ———– ————-
359           346         8

Details

DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed.

A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting. The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.