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.
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
, sch.name AS sch_name
, t.name AS o_name
, ISNULL( idx.name, 'Heap Table' ) AS idx_name
, idx.type_desc AS idx_type
, fg.data_space_id AS fg_id
, fg.name 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 t.name LIKE 'sys%' --- ### Exclude sys%-Tables (sysDiagrams)
GROUP BY t.object_id
, sch.name
, t.name
, idx.name
, idx.type_desc
, fg.data_space_id
, fg.name, part.rows
ORDER BY total_pages_kb DESC;
Creating a additional file group
/* ### Create additional file group */
USE [master];
GO
ALTER DATABASE [projectDB] ADD FILEGROUP [fg_userdata];
GO
/* ### 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];
GO
/* ### Set [fg_userdata] as default file group */
USE [projectDB];
GO
ALTER DATABASE [projectDB] MODIFY FILEGROUP [fg_userdata] DEFAULT;
GO
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