<img src="https://track.adform.net/Serving/TrackPoint/?pm=268420" width="1" height="1" alt="">

Filegroup confusion

21 min. læsetid
1. november 2013 Skrevet af: Martin Schmidt Del med en ven     

Where did I put my Sales table?

Filegroups? What can I say! Sometimes I love them and sometimes I just hate them. In theory this should be quite simple and give you benefits – but in my years as a consultant, I have seen so many bad file group designs where the simplicity is traded in for an over complex layout that looks good on paper, but is a nightmare to maintain.

A scenario that I often come across is misplaced tables and indexes. This happens mostly if the person that created the database layout is not responsible for creating objects afterwards, or if the end-users does not get the proper instructions on how to do things the right way. When this is the case, things can end up messy. I will show you how to query the Meta data, so that you can check if your data is placed in the right filegroup.

Here is the database layout that I’m about to create:

 Drawing7

In addition, here is the code used to create the database

<span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">[Inspari]
 </span><span style="color:blue;">ON  PRIMARY
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'System01'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPSystem01.mdf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'System02'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPSystem02.ndf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">),
 </span><span style="color:blue;">FILEGROUP </span><span style="color:teal;">[Data]
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Data01'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPData01.ndf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Data02'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPData02.ndf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">),
 </span><span style="color:blue;">FILEGROUP </span><span style="color:teal;">[Index]
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Index01'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPIndex01.ndf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Index02'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPIndex02.ndf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">)
 </span><span style="color:magenta;">LOG </span><span style="color:blue;">ON
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Inspari_log'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:FusionIO_MPInspari_log.ldf' </span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>100<span style="color:teal;">Mb
</span><span style="color:gray;">)
</span><span style="color:blue;">GO
</span>

Let us create a table, pay attention to the ON [Filegroup] keyword

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">RandomPeople
</span><span style="color:gray;">(
    </span><span style="color:teal;">Id </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">) </span><span style="color:blue;">PRIMARY KEY</span><span style="color:gray;">,
    </span><span style="color:teal;">DateCreated </span><span style="color:blue;">DATETIME2 DEFAULT </span><span style="color:magenta;">SYSDATETIME</span><span style="color:gray;">(),
    </span><span style="color:teal;">Name </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">),
    </span><span style="color:teal;">Salary </span><span style="color:blue;">BIGINT </span><span style="color:gray;">not null
)
</span><span style="color:blue;">ON </span><span style="color:teal;">[Data]
</span><span style="color:blue;">GO
</span>

Let us create an index on the table again pay attention to the ON [Filegroup] keyword.

<span style="color:blue;">CREATE INDEX </span><span style="color:teal;">idx_RandomIndex </span><span style="color:blue;">ON </span><span style="color:teal;">RandomPeople
</span><span style="color:gray;">(
    </span><span style="color:teal;">Name</span><span style="color:gray;">, </span><span style="color:teal;">Salary
</span><span style="color:gray;">)
</span><span style="color:blue;">ON </span><span style="color:teal;">[Index]
</span><span style="color:blue;">GO
</span>

Now imagine that you have a massive database where multiple people have created tables and indexes, the odds for everything being placed in the right file group is close to not existing. Some object will eventually end up in the wrong file group. Here is a T-SQL script that queries the meta data to show You which objects are stored in which file groups.

<span style="color:blue;">SELECT
  </span><span style="color:magenta;">OBJECT_NAME</span><span style="color:gray;">(</span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:magenta;">object_id</span><span style="color:gray;">) </span><span style="color:blue;">as </span><span style="color:teal;">tablename</span><span style="color:gray;">,
  </span><span style="color:teal;">t3</span><span style="color:gray;">.</span><span style="color:teal;">name </span><span style="color:blue;">AS </span><span style="color:teal;">indexName</span><span style="color:gray;">,
  </span><span style="color:teal;">t3</span><span style="color:gray;">.</span><span style="color:teal;">type_desc</span><span style="color:gray;">,
  </span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:teal;">index_id</span><span style="color:gray;">,
  </span><span style="color:teal;">t4</span><span style="color:gray;">.</span><span style="color:teal;">name </span><span style="color:blue;">AS </span><span style="color:teal;">FileGroupName</span><span style="color:gray;">,
  </span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:blue;">rows AS </span><span style="color:teal;">RowsInObject</span><span style="color:gray;">,
  (</span><span style="color:teal;">total_pages </span><span style="color:gray;">* </span>8<span style="color:gray;">) / </span>1024 <span style="color:blue;">AS </span><span style="color:teal;">SpaceUsedInMB
</span><span style="color:blue;">FROM </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">partitions </span><span style="color:teal;">t1
</span><span style="color:gray;">INNER JOIN </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">allocation_units </span><span style="color:teal;">t2 </span><span style="color:blue;">ON </span><span style="color:gray;">(</span><span style="color:teal;">t2</span><span style="color:gray;">.</span><span style="color:teal;">container_id </span><span style="color:gray;">= </span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:teal;">hobt_id</span><span style="color:gray;">)
INNER JOIN </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">indexes </span><span style="color:teal;">t3 </span><span style="color:blue;">on </span><span style="color:gray;">(</span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:magenta;">object_id </span><span style="color:gray;">= </span><span style="color:teal;">t3</span><span style="color:gray;">.</span><span style="color:magenta;">object_id</span><span style="color:gray;">) AND (</span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:teal;">index_id </span><span style="color:gray;">= </span><span style="color:teal;">t3</span><span style="color:gray;">.</span><span style="color:teal;">index_id</span><span style="color:gray;">)
INNER JOIN </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">filegroups </span><span style="color:teal;">t4 </span><span style="color:blue;">ON </span><span style="color:gray;">(</span><span style="color:teal;">t4</span><span style="color:gray;">.</span><span style="color:teal;">data_space_id </span><span style="color:gray;">= </span><span style="color:teal;">t2</span><span style="color:gray;">.</span><span style="color:teal;">data_space_id</span><span style="color:gray;">)
</span><span style="color:blue;">WHERE </span><span style="color:teal;">t1</span><span style="color:gray;">.</span><span style="color:magenta;">object_id </span><span style="color:gray;">in
(
    </span><span style="color:blue;">SELECT </span><span style="color:magenta;">object_id
    </span><span style="color:blue;">FROM </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">objects
    </span><span style="color:blue;">WHERE </span><span style="color:teal;">is_ms_shipped </span><span style="color:gray;">= </span>0
<span style="color:gray;">)
</span><span style="color:blue;">ORDER BY </span>1

filegroup_blogpicture1

Is your data stored correct? If that is the case, then congratulations – good job. If not, then there is work to do. The good news is that you might discover that your Sales table is located on slow disks, and by moving it to the faster disks, you’ll improve system performance immediately.