Built-in sproc to track memory usage?

[ChopinFan]

Is there a built-in sproc I can run to show how much memory a specific table is using and even how much memory the largest record in a table is using? "sp_help TableName" does not show this information.

Thanks!

 

[mrdenny]

By memory, I assume that you mean disk space?

exec sp_spaceused [TableName]

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ChopinFan]

This is very helpful, thanks! I see that it gives data on the table overall, which I needed, but how can I check the size of individual rows? I am adding a lot of columns to a table and I'm concerned that I may be nearing the maximum size for individual rows as well as for the table overall. Is there a way to check the rows?

Thanks!

 

[mrdenny]

You would need to write a select statement to sum up the length of each data field. Columns that are NVARCHAR or NCHAR will need to have there length multiplied by 2 as they require twice the storage. Columns that are number fields (numeric, int, bigint, etc) will need to use the datafield size not the length of the data.

/*This assumes the field PK is the primary key of the table.*/
select PK, sum(len(Col02)+len(col03)+len(04))
from table

When working with row lengths, you will want to keep the possible row length below 8060 as well as the physical row length.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com