Field engineer on Partition Processing dimensions in SSAS.
http://blogs.msdn.com/b/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx
Troubleshooting partition queries.
Partitioning
http://msdn.microsoft.com/en-us/library/ms180767.aspx
In planning the partition scheme, you must decide what filegroup or filegroups that you want to put your partitions on. The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. This is because you can perform backups on individual filegroups. For more information, see Backing Up and Restoring Databases in SQL Server.
Good overview of steps for a database on two filegroups followed by a partition that uses the two file groups for the two partitions and creation of index.
Partitioning enhancements for SQL Server 2008 around the allocation of threads to active partitions in a query.
Note that this makes it all the more important to allocate partitions to filegroups that are spread across many disk spindles, allowing the query on a given partition to be as efficient as possible.
But wait, there’s another improvement to partition query behavior in SQL Server 2008. SQL Server 2005 only allows lock escalation to the table level. Let’s say your query eliminates 75% of a table’s partitions, but scans all of the remaining partitions (1/4 of the table). If SQL Server decides to escalate the lock, all other queries will be locked out even if they are querying completely different partitions. SQL Server 2008 provides a table option to override this default behavior. Note that the default is still to escalate to table locks (at least for now) so this option will need to be changed to take advantage of partition-level lock escalation. Find out more about this, like I did, on über blogger Paul Randall’s post.
http://msdn.microsoft.com/en-us/library/cc280449.aspx
Partitions have compression settings.
http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server
Partitioning best practices
- Consider partitioning big fat tables into different file groups where each file inside the file group is spread into separate physical disks (so that the table spans across different files in different physical disks). This would enable the database engine to read/write data operations faster.
- For history data, consider partitioning based on “Age”. For example, suppose a table has order data. To partition this table, use the Order date column to split the table so that a partition is created to contain each year’s sales data.
- Add user defined file groups to the database
Use the following SQL command to create a file group:
Collapse | Copy CodeALTER DATABASE OrderDB ADD FILEGROUP [1999] ALTER DATABASE OrderDB ADD FILE (NAME = N'1999', FILENAME = N'C:\OrderDB\1999.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [1999] http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/ Adding and removing partitions.
Partitioned tables and Indexes
If the index contains the partitioning column then the index is referred to as being ‘aligned’ with the table.
If the index uses the same partitioning scheme as the table and is in the same filegroup then the index must be aligned with the table.
For a non-clustered non-unique index the partitioning column can be included to align the index rather than being indexed.
I think it is best to always explicitly include the partitioning column in your indexes.
Partitioning on multiple columns
Although the partitioning column must be a single column, it does not need to be numeric and it can be calculated so that the range can include multiple columns. For instance it is common to partition on datetime data by month. This will work well, because that data is usually in a single column, but what do you do if you have data for multiple companies and you also want to partition by company? For this you could use a computed column for the partitioning column. This will create a computed column using the ‘company id’ and ‘order month’ which is then used for the partitions. It will partition three companies for the first three months of 2007.
CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) , PartCol AS Company_id * 10000 + CONVERT(VARCHAR(4),OrderDate,112) persisted ) ON MyPartitionScheme (PartCol)
The computed column must be ‘persisted’ to form the partitioning column.
Monthly Data – the sliding range
A common requirement is to partition by month. This means that new month partitions need to be added and possibly old data partitions removed. I will describe the process for the addition of a new partition for later data, to remove an old partition the process is the same except that you swap out two partitions, merge the range and swap in a single table.
We create a partitioned table for data by OrderDate month
CREATE PARTITION FUNCTION MyPartitionRange (datetime) AS RANGE RIGHT FOR VALUES (’20070101′, ’20070201′, ’20070301′, ’20070401′) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE Orders ( OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) ON MyPartitionScheme (OrderDate)
This will give four partitions…
OrderDate < ’20070101′ OrderDate >= ’20070101′ AND < ’20070201′ OrderDate >= ’20070201′ AND < ’20070301′ OrderDate >= ’20070301′ AND < ’20070401′ OrderDate >= ’20070401′
Therefore the data will be split intopartitions by month, and we insert some test data:
–insert Orders select ’19000101′, 1, 1, 1INSERT Orders SELECT ’20070101′, 1, 1, 1INSERT Orders SELECT ’20070201′, 1, 1, 1INSERT Orders SELECT ’20070301′, 1, 1, 1INSERT Orders SELECT ’20070401′, 1, 1, 1INSERT Orders SELECT ’20070402′, 1, 1, 1INSERT Orders SELECT ’20070501′, 1, 1, 1
After the data is loaded into the temporary tables, they are then switched in to become partitions in the full portioned tables. This switch is a metadata operation only – the data does not need to be copied or physically moved to accomplish it. This is a significant piece of the power of partitions, and this technique has been a recommended data loading practice since SQL Server 2005.
