5 ways SQL Server looks at space usage

Workaround:

Method 1, sp_spaceused

Method 2, DBCC SQLPERF

Method 3, DBCC SHOWFILESTATS

Method 4, DBCC SHOWCONTIG

Method 5, Sys.dm_db_partiton_stats

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------

Method 1,

Execute sp_spaceused; Returns the overall usage of the current database space without parameters.

Execute sp_spaceused @objname = ' dbo. Nums '; Returns the space usage information for the specified object

Caution: The results returned by sp_spaceused are statistical information from the system, but if the statistics are not updated, this will make the sp_spaceused return inaccurate.

Because of this sp_spaceused also added a parameter @updateusage;

So the full format of the sp_spaceused is sp_spaceused @objname = ' objname ', @updateusage = ' updateusage '; remember that adding a second parameter will affect

performance of SQL Server. It's best to do this when you're free.

Method 2,

DBCC SQLPERF it is only for log files (log information for all databases that return the current instance of SQL Server)

DBCC SQLPERF (LOGSPACE);

Method 3,

DBCC SHOWFILESTATS returns the spatial usage of individual data files in the database (no envelope log file)

DBCC SHOWFILESTATS;

Method 4,

DBCC SHOWCONTIG returns the spatial usage information for the object as measured in extents (extent)

DBCC SHOWCONTIG (' dbo. Nums);

Method 5, Sys.dm_db_partition_stats, returns a row for each partition of the object (not extent, such as the partition in the ' Table partition ')

Summary:

It is also possible to write the returned information to a custom table, which can be easily estimated in the future. The use of space.

5 ways SQL Server looks at space usage