Table size (MB)

| 2 Comments | 19,734 views
Categories:

VN:F [1.9.17_1161]
Rating: 4.3/5 (7 votes cast)

This metric is useful if you want to know exactly how much physical space a particular table is taking up, including the size of its indexes. If a database is growing quickly within a short time and you suspect a certain table is responsible, you can monitor its actual size, or the rate at which it is increasing.

If you have a particularly large table affecting performance, you may consider:

Table size (MB), 4.3 out of 5 based on 7 ratings

Metric definition

Metric Name:
Table size (MB)
Description:
The size in Megabytes of a specific table.
Enter the T-SQL query that will collect data:
-- Replace 'dbo.MyTable' with the name of the table you want to measure.
 
DECLARE @SpaceUsed TABLE
  (
   [name] NVARCHAR(128),
   [rows] CHAR(11),
   [reserved] VARCHAR(18),
   [data] VARCHAR(18),
   [index_size] VARCHAR(18),
   [unused] VARCHAR(18)
  );
 
INSERT INTO @SpaceUsed
    EXEC sp_spaceused 'dbo.MyTable';
 
SELECT (CAST(LEFT(data, LEN(data) - 3) AS BIGINT)
    + CAST(LEFT(index_size, LEN(index_size) - 3) AS BIGINT)) / 1024.00
  FROM @SpaceUsed;
Note: Replace 'dbo.MyTable' with the name of the table you want to measure.
Select instances to collect from:
the instance that contains the database you’re interested in
Choose databases to collect from:
Specify databases and type
the database that contains the table you’re interested in
Set collection frequency:
Collect data every hour
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Table size
Description:
Raised when the size of a table exceeds a specified threshold.
Raise an alert when the metric value goes:
above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:2000
Medium:1000
Low:10
Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 4.3/5 (7 votes cast)
  • Kirt Carson

    Great post. Found out my year old table would consume 1.7GB with every query. Created a historic table and moved old data to speed up everything

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Steve Mangiameli

    I drafted a script years ago to run adhoc. It’s similar but uses sp_msforeachtable to load a temp table that you can maniplate for better readability.

    IF object_id(‘tempdb..#tableSizes’) IS NOT NULL
    DROP TABLE #tableSizes

    CREATE TABLE #tableSizes
    (
    NAME sysname
    , ROWS VARCHAR(255)
    , reserved VARCHAR(255)
    , DATA VARCHAR(255)
    , index_size VARCHAR(255)
    , unused VARCHAR(255)

    )

    INSERT #tableSizes
    EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)