Solved

Updating SQL partition schemes & partition functions

Posted on 2010-11-16
6
711 Views
Last Modified: 2012-05-10
My client's IT dept had set up SQL Server 2005 filegroups on a particular database for each fiscal quarter: fg2006Q4, fg2007Q1, fg2007Q2, ... up to fg2010Q4. I noticed today that the fg2010Qx files had not been used, still at 1MB each.

Upon further digging, I found the Partition Schemes and Partition Functions folders in Mgmt Studio.

One Scheme: CREATE PARTITION SCHEME [psEvent] AS PARTITION [pfEvent] TO ([fg2006Q4], [fg2007Q1], [fg2007Q2], [fg2007Q3], [fg2007Q4], [fg2008Q1], [fg2008Q2], [fg2008Q3], [fg2008Q4], [fg2009Q1], [fg2009Q2], [fg2009Q3], [fg2009Q4])

Corresponding Function: CREATE PARTITION FUNCTION [pfEvent](int) AS RANGE RIGHT FOR VALUES (1000300, 5493523, 25712235, 45243827, 67741212, 88804361, 106935897, 121343754, 122508529, 122512053, 139523161, 154878083)

Q1: I'd like to create filegroups for fg2011Qx... then can I simply ALTER PARTITION SCHEME with fg2010Q1 through fg2011Q4 added to the list?

Q2: I believe I understand the function ranges, but wondering where/how these ID's would have been generated (likely)? And more fundamentally, how would SQL know when to move onto the next filegroup (assuming it had existed when needed)?

Q3: Is there a safe & easy way to "re-balance" the filegroups... so that fg2010Qx's are used, and the fg2009Q4's aren't so big. I suppose not a huge deal if not, but it'd look cleaner if so.

Thanks,
Jeff
0
Comment
Question by:jdallen75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:Sander Stad
ID: 34153577
You can add an additional filegroup to the scheme with the following command:

ALTER PARTITION SCHEME psEvent
NEXT USED fg2011Qx;
GO

Than you can use the following script to alter the partition function:

IF EXISTS (SELECT * FROM sys.partition_functions
    WHERE name = 'pfEevent')
DROP PARTITION FUNCTION pfEvent;
GO
CREATE PARTITION FUNCTION pfEevent (int)
AS RANGE RIGHT FOR VALUES  (1000300, 5493523, 25712235, 45243827, 67741212, 88804361, 106935897, 121343754, 122508529, 122512053, 139523161, 154878083, xxxxxxx);
GO
0
 
LVL 9

Accepted Solution

by:
Sander Stad earned 500 total points
ID: 34153622
Q2:
The numbers in the function are not ID's but are the boundaries of rows. This website shows you what you want to know: http://msdn.microsoft.com/en-us/library/ms187802(v=SQL.90).aspx

Q3: What do you mean by rebalance the filegroups? Do you want to change the number of records that are saved in the different filegroups?
0
 

Author Comment

by:jdallen75
ID: 34154843
Sstad,

Q3: I mean that, for instance, the files in fg2009Q1, Q2, and Q3 each total 10GB. However, since fg2009Q4 was the last listed in the partition scheme, it has been used since that point in time, and is now 80GB. fg2010Qx files are basically empty because they have not been used this year.

Thanks on the other points - I will look into them.

JA
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:jdallen75
ID: 34155331
I think I understand now... it looks as though the boundary values in the partition function list are SSID's of the rows in the tables. That part isn't all that intuitive, and more importantly does not cleanly divided up the data by calendar quarters. What seems to be cleaner would be to choose a datetime column from the table, and define its boundaries accordingly. "20090101, 20090401, 20090701..." is a heck of a lot clearer!
0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 34157157
That could work but make sure your column is in date or int format because I don't know how it works with text in this case.
0
 

Author Comment

by:jdallen75
ID: 34158841
I discovered the common problem in that the partition column has to be part of the primary key, which in this case it isn't. There are a couple of workaround solutions, be we've decided in this case (since it is an archive DB), to go with one partition over several filegroups.

Thanks for your guidance.
JA
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question