In my post last week, How To Estimate Data Utilization, I said that it may be my last post for a while. Well… apparently I lied. 🙂
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:
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results; CREATE TABLE #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_msforeachdb EXECUTE 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.databases WHERE name NOT IN (SELECT databaseName FROM #Results); /* Retrieve our results */ SELECT * FROM #Results WHERE schemaName IS NOT NULL ORDER BY maxRangeValue; |
Example Results:
databaseName schemaName functionName data_space_id maxRangeValue ------------------- ------------------------------- ------------------------------------- --------------- ------------------------- HistoricalMart dailyRangeDate_ps dailyRangeDate_pf 65609 2011-12-31 00:00:00.000 AdventureWorks yearlyRangeSmallDateTime_ps yearlyRangeSmallDateTime_pf 65605 2012-01-01 00:00:00.000 dbaTools monthlyRangeDateTime_ps monthlyRangeDateTime_pf 65604 2012-12-01 00:00:00.000 |
End of year? The last project our team did, my instructions were to have enough partitions to last until we retire. But yes, we will monitor them anyway. 🙂
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.
Pingback: Something for the Weekend – SQL Server Links 04/11/11