Calculating resource usage and capacity planning for SQL server databases can be a time consuming business and there are many factors to take into consideration. Over time we plan to provide some tips and resources that we’ve come across over the years to help out in this area.
We’re often asked by clients who are using our shared database servers to provide them with information related to the performance of their database. We provide a number of statistics by default but a fairly common request is for a breakdown on the storage usage of their database. The script below is an example of how you can use T-SQL to query the database itself to provide a breakdown on the space used for each table in the database. It also provides the total allocated space to each table and an approximate row count.
T-SQL script to display table size summary:
<SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],>
<CAST( CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Allocated (MB)’,>
<CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Used (MB)’,>
<CAST(CAST((reserved – dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Unused (MB)’,>
<rowcnt AS ‘Row Count (approx.)’>
<FROM sysindexes WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE ‘sys%’ AND OBJECT_NAME(id) NOT LIKE ‘dt%’>
<ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)>
The script above, along with others that we will post in the future, can be really useful in helping to gain a clearer picture about how your database is being used and can provide pointers to what you might want to look at when attempting to optimise performance.
Marks is a director at Secura hosting and one of our resident Network specialists.
Tweet me at:
@securacloud