May 11, 2011 at 6:19 am
Hello all,
I have a database with a partitioned table. There are indexes on that table which are also partitioned. Both partition schemes use the same partition function. Every month I execute a sliding window stored procedure to switch out the 2 year old partitions (data and index) and set those to be used next. When the new month starts, data is put in the filegroup as it should. But the index data is put in the primary filegroup instead of the filegroup for that month. Does anybody know why the index filegroup isn't used when the new month starts? My workaround at the moment is to rebuild the indexes.
Some additional info: it occured to me that after executing the sliding window script, there wasn't a NextUsedFileGroup set under Storage-> Partition Schemes -> Facets. The sliding window stored procedure does alter the partition schemes to set the next used filegroup.
Add. info (2): the database was upgraded from 2005 to 2008 R2 on a different Windows server last year by my predecessor. When it was on the previous server, the problem I decribed did not exist. So the sliding window stored procedure must be fine. I suspect it has something to do with rights and my guess is that it can be fixed really simple. But how?
Thanks in advance for any solutions, idea's, suggestions, etc...
August 9, 2011 at 8:10 am
I'm also running into this issue - did you find any solution please?
Maybe building the index on the empty partition just after the split using ON PARTITION will force the index onto the correct file group.
August 22, 2011 at 2:30 am
I build an empty table and create the relevant indexes on that filegroup and switch it into the newly created partition (after splitting). Even though I don't switch any data in, the indexes need to be explicitly built on that FG otherwise they will go to PRIMARY.
August 24, 2011 at 5:22 am
I figured out where the sliding window procedure goes wrong: it was a typo in the stored procedure, so it could never have worked completely. It was hard to find, because SQL Server doesn't return an error for that typo.
Pierre, I can't help you if I haven't got your code. If you sent it, I can take a look at it.
August 29, 2011 at 3:46 am
Also, from BOL: "after a split operation is performed, there is no longer a designated NEXT USED filegroup"
So, the property NextUsedFileGroup should be empty after the SPLIT has occurred. Didn't know that before.
All should be good now, just have to wait for the proof next thursday (1st of september)
September 2, 2011 at 7:45 am
Sadly, this month's filegroup for the indexes isn't used 🙁 The positive part is that I'm pretty sure it's not the sliding window mechanism.
The partition scheme's and the partition function are fine too. But still, index data is filling the primary filegroup instead of this month's partition. Could it be due to 2 partition schemes (one for the table, one for the indexes) using the same function?
My workaround is to ALTER INDEX REBUILD PARTITION=25 (which is the current month's partition). Obviously, I don't want to do that every month.
Somebody out there got an idea? Please!
September 2, 2011 at 9:06 am
If you are only swapping out partitions, and then splitting - i.e. not swapping in the data (and indexes!) to the new partition then the indexes for that partition will go to Primary filegroup. I swap in an empty staging table that has the correct indexes created on it. You need to explicitly create the indexes on the new empty partition.
September 2, 2011 at 9:55 am
the indexes must be made upon the partition schema too - here's an a code snippet from one of my tables to illustrate table and secondary index - if you don't define the secondary index this way it uses the primary file group
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ActivityLog_SEP](
[ActivityLogID] [bigint] NOT NULL,
-----------------
[ActivityMonth] [tinyint] NOT NULL,
CONSTRAINT [PK_ActivityLog_SEP] PRIMARY KEY CLUSTERED
(
[ActivityLogID] DESC,
[ActivityMonth] ASC
)WITH (PAD_INDEX = OFF) ON [psch_YearByMonthNumber]([ActivityMonth])
) ON [psch_YearByMonthNumber]([ActivityMonth])
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [idx_ActivityLog_Acode_SEP] Script Date: 09/02/2011 16:52:23 ******/
CREATE NONCLUSTERED INDEX [idx_ActivityLog_Acode_SEP] ON [dbo].[ActivityLog_SEP]
(
[ActivityCode] DESC,
[ActivityDate] DESC,
[UserID] ASC
)WITH (PAD_INDEX = OFF, FILLFACTOR = 100)
ON [psch_YearByMonthNumber]([ActivityMonth])
GO
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 8, 2011 at 6:43 am
Thanks guys. I will alter the procedure to rebuild the indexes on the newly created partition after the SPLIT.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply