SQL Server – How to Find When a Partition Was Created

What do you expect your real life volume of data to be?

For 10 million rows, I wouldn't bother with partitioning. The overhead far outweighs the benefits: partitioning isn't a silver bullet to cure performance issues.

To answer,

Point 1: on the first run, data needs loaded into memory ("buffer pool") and will stay cached until evicted based on memory pressure and usage. Personally, I'd test with the cache filled because you'd expect your app to require that data very often, especially if you think partitioning is the solution to some problem

For point 2, what queries do you expect to run in production? The queries should be representative of this production load. However they should test different realistic filter combinations with and without partition key at least.

Edit, some reading, after comments below:

• http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/
• http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/ (point 7 especially)

There are two advantages to partitioning a table in the same filegroup:

1. Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance. Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
2. Leveraging partition elimination and (possibly) partition level locking to improve query maintenance. I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

• If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
• To avoid performance issues, you should align your partitions. This means all your indexes should include your partition key, whether as an include or as part of the index itself.
• Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012). If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it. Kendra Little has an excellent list of resources where you can get started.