Table monitoring & Table informations in SQL-Server databases

If you are a database admin or developer you always should know and take care about, where tables are stored, how much space they consume and how data grows in the tables. One of the golden rules in SQL-Server database development is not to use the PRIMARY file group as the default location for user-tables in your projects.

Better create a new custom file group, and set it as the default file group. From my opinion, this is the point, where a good database design starts: “With the implementation of additional and customized file groups”. In this article you will find a fast example how to do that. I had to mention file group separation first, before we can move on, because this was actually my motivation to write that query and this blog post.

If you never think about it, or don’t even have an idea where the tables in the database are located, how big they are and how much rows your tables contains. You better take care of it…NOW!

Therefor i wrote a quick management query, especially aligned for that case.  If  you run that query in your database, you will get an overview of the current situation in the database. The result provides you the necessary informational output as you see in the screenshot below.

common table informations
Example result of the user table information query


Now you can decide about the next steps what to do (moving tables to a another file group) or talk to your developers about the necessary changes to stay online and keep your database in a healthy and in a performant state.

Management query to gather table information

### Author: Schoen, Roland (c)2014
### Created: 2014-08-14
### Changed: 2014-08-16
### Description: displays the following table informations:
###				- object_id
###				- schema location
###				- Heap or Non-Heap Table
###				- File group location
###				- Number of rows in the table
###				- Total and used page space in KB

SELECT t.object_id AS o_id
     ,                              AS sch_name
     ,                                AS o_name 
     , ISNULL(, 'Heap Table' )      AS idx_name
     , idx.type_desc                         AS idx_type
     , fg.data_space_id                      AS fg_id
     ,                               AS fg_name
     , CAST( part.rows AS FLOAT )            AS num_rows
     , SUM( au.total_pages ) *8              AS total_pages_kb
     , SUM( au.total_pages ) *8 /1024        AS total_pages_mb
     , SUM( au.used_pages ) *8               AS used_pages_kb
     , SUM( au.used_pages ) *8 /1024         AS used_pages_mb

FROM sys.tables AS t

INNER JOIN sys.indexes AS idx
        ON idx.object_id = t.object_id
       AND ( idx.index_id = 0 OR idx.index_id = 1 )	-- ### Heap-Tables = 0 | Clustered Indexes = 1

INNER JOIN sys.partitions AS part
        ON part.object_id = t.object_id
       AND part.index_id = idx.index_id

INNER JOIN sys.filegroups AS fg
        ON fg.data_space_id = idx.data_space_id

INNER JOIN sys.allocation_units AS au 
        ON au.container_id = part.partition_id
       AND au.data_space_id = fg.data_space_id

INNER JOIN sys.objects AS obj
        ON obj.object_id = t.object_id

INNER JOIN sys.schemas AS sch
        ON sch.schema_id = obj.schema_id

WHERE NOT LIKE 'sys%'					--- ### Exclude sys%-Tables (sysDiagrams)

GROUP BY t.object_id
       , idx.type_desc
       , fg.data_space_id
       ,, part.rows

ORDER BY total_pages_kb DESC;

Creating a additional file group

/* ### Create additional file group */
USE [master];
ALTER DATABASE [projectDB] ADD FILEGROUP [fg_userdata];

/* ### Create a data file for the new custom filegroup */
ALTER DATABASE [projectDB] ADD FILE (   NAME = N'data_file_01'
                                      , FILENAME = N'D:\mssql\data\projectDB\data_file_01.ndf' 
                                      , SIZE = 1048576KB 
                                      , FILEGROWTH = 524288KB 
                                    ) TO FILEGROUP [fg_userdata];

/* ### Set [fg_userdata] as default file group */
USE [projectDB];

Yeah… that’s all for today. Hope you like this post  and help’s you managing your databases in the daily business. Stay tuned – Comments are welcome!

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑