4

Results from @@Version

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) 
Sep 21 2011 22:45:45 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Here is the database definition

CREATE DATABASE [Scratch] ON  PRIMARY 
( NAME = N'Scratch_mdf', FILENAME = N'N:\MSSQL\TEST\Primary\Scratch_mdf.mdf' , SIZE = 17664KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [DATA]  DEFAULT 
( NAME = N'Scratch_dat1a', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1a.ndf' , SIZE = 14539584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_dat1b', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1b.ndf' , SIZE = 12016128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [INDEX] 
( NAME = N'Scratch_idx1', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx1.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx2', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx2.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx3', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx3.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx4', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx4.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [PM_G0] 
( NAME = N'PM_data0', FILENAME = N'N:\MSSQL\PDB1\Data\PM_data0.ndf' , SIZE = 768000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'Scratch_log1', FILENAME = N'N:\MSSQL\TEST\Log\Scratch_log1.ldf' , SIZE = 833024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
GO

ALTER DATABASE [Scratch] SET COMPATIBILITY_LEVEL = 100
GO

Here is the code to create the table and to add a clustered index. In both cases I'm specifying filegroup PM_G0 but when I check its on filegroup DATA.

CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0; 
-- Tried the filegroup name with/without brackets just in case

I'm checking the location of the first heap, then clustered index by using this query:

SELECT o.name objectname, fg.name filegroupname, sdf.name as datafile,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.tables t ON o.object_id = t.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = i.data_space_id
INNER JOIN sys.database_files sdf ON sdf.data_space_id=fg.data_space_id
WHERE o.name = 'STUPID_TEST'

Interesting test. I changed the default filegroup to [index]. Confirmed that [index] was in fact the default index in sys.filegroups then dropped and re-created my clustered index and it still went to the [DATA] filegroup. Code below

ALTER DATABASE Scratch MODIFY FILEGROUP [index] DEFAULT;
GO
DROP INDEX dbo.STUPID_TEST.IX_test;
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON [PM_G0];
GO
12
  • Is Scratch.mdf smaller than 17664?? PM_GO will not be used unless DATA is Full as DATA is set to default.
    – eyoung100
    Commented May 20, 2014 at 19:27
  • I should be able to force a table/index to be on a different FG shouldn't I? Commented May 20, 2014 at 19:29
  • Wrong! There is nothing wrong with the way he is creating the clustered index. It should use the filegroup he mentioned. Your sample code works as expected on my machine. Commented May 20, 2014 at 19:38
  • Which begs the question why is it not working on mine. I generated the DB code directly from the DB I'm using and copied the other code directly also. Only thing I changed was the directory the files are going to and got rid of some of the extra files in one of the filegroups (there were about a dozen which seemed like a bit much) Commented May 20, 2014 at 19:47
  • I can't reproduce either - I used the same code (changing only the paths) and the heap and CI both end up on the right FG. What is the exact @@VERSION? I noticed that you used a database prefix on the create table, which makes it possible you're not in the right database for part or all of the test - could you be in the context of another database which also has a table called STUPID_TEST (perhaps model or tempdb)? Commented May 20, 2014 at 19:53

1 Answer 1

3

First thanks to everyone who tried to help me with this here and on twitter. We did however finally find the answer. It turns out that someone created a database trigger on model. The trigger parses every create index/table command and changes it to a DATA filegroup if it's a heap or clustered index and an INDEX filegroup for everything else. We were then able to track the trigger back to a policy.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.