For those of you who use table partitioning, you know that you need to define a partitioning scheme and function prior to applying partitioning to an index. Personally, I tend to build the function for a couple of years out, and I tend to create them through the end of a calendar year. Now, if I failed to expand a partition range at the end of the year, then come January 1st, all of my data would be written to the same partition. Not the end of the world, no, but it causes all kinds of nasty performance and maintenance issues. Thus, as part of my end-of-year / maternity-leave preparations, I’m in the process of examining all partitioned functions to identify those that need to have their partition ranges expanded. For those interested, here’s the script I used:

IFOBJECT_ID('tempdb..#Results')ISNOTNULLDROPTABLE #Results;
CREATETABLE #Results
(
databaseName NVARCHAR(128)
, schemaName NVARCHAR(128)
, functionName NVARCHAR(128)
, data_space_id INT
, maxRangeValue SQL_VARIANT)/* Grab results for each database and store in our temp table.
And no, I don't *need* to select from sys.indexes and perform
left joins, but I'm overly cautious and want to make sure
I'm not accidentally missing any databases. :) */--EXECUTE master.dbo.sp_msforeachdbEXECUTE sp_foreachdb 'USE ?;
INSERT INTO #Results
SELECT DB_NAME() AS databaseName
, sps.name AS schemaName
, spf.name AS functionName
, sps.data_space_id
, MAX(prv.value) AS maxRangeValue
FROM sys.indexes AS i
LEFT JOIN sys.partition_schemes AS sps WITH (NOLOCK)
ON i.data_space_id = sps.data_space_id
LEFT JOIN sys.partition_functions AS spf WITH (NOLOCK)
ON sps.function_id = spf.function_id
LEFT JOIN sys.partition_range_values AS prv WITH (NOLOCK)
ON spf.function_id = prv.function_id
GROUP BY sps.name
, spf.name
, sps.data_space_id;';
/*
sp_foreachdb was written by SQL MVP Aaron Bertrand and can be downloaded
at http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
Alternatively, you can also use sys.sp_MSforeachdb
*//* Make sure we're not missing any major databases */SELECT*FROM sys.databasesWHERE name NOTIN(SELECT databaseName FROM #Results);
/* Retrieve our results */SELECT*FROM #Results
WHERE schemaName ISNOTNULLORDERBY maxRangeValue;

With the max limit of partitions to be 1000 in SQL Server 2008 it is very possible to run against this limit depending on how you partition your table/index. Excellent script for watching those limits. But as people move to SP2 of SQL Server 2008 and SP1 on SQL Server 2008 R2 they can take advantage of 15,000 partitions. If you hate to do annual maintenance due to the 1000 partition limit then you might want to review this paper: http://download.microsoft.com/…/B/E/1/…/Support_for_15000_Partitions.docx

Why do it that way, rather than add a future partition each time partitioning needs to run? We’ve gotten bitten before by yearly processes like that, so we changed them to add a new partition whenever the process runs. Run once a month?

And a separate question: how do you deal with queries/inserts hitting the table that block the ALTER SWITCH? We’ve moved to a two-tier for our inserts: a partitioned table that catches live traffic, and a second partitioned table, fed nightly by the live table, that’s used for queries. We see problems with inserts blocking the switch on the live-traffic table. Even setting DEADLOCK_PRIORITY to high didn’t help; it still wound up the deadlock victim.