I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.

So we have Subcriber and SubscriberPartitioned tables.

Tables structure is:

SubscriberId | Name | Email | Telephone | UserId (partition column)

Data: I'm using SQL Data Generator:

  • to fill the tables with 10 million rows each
  • the UserId range goes from 1-200

Partitioning: I have partitioned the table SubcriberPartioned in 2 according to the UserId, which goes from 1 to 200. So we have approx 5 million rows in each partition.

Performance measure: I'm using SQL Server Profiler to measure the query times.

  1. Why does SQL Server differ so much in query times?

    For example the query

    select * from subcriber where SubscriberId = 1000 

    ... will the first time take like 40 seconds, and if I re-execute it will take less than a second. If I try with another Id it also will take less than a second. But the first time took a lot of time.

    So, is there any optimization or caching being done automatically in the background by SQL Server?

    I was expecting the same query to take the same time if repeated over time...

  2. Which do you think would be the best queries to run and how to measure them in order to test the partitioning of the table and to see the optimization gained with the partition strategy instead of the normal table.


  1. After answers on this post I made some cold start tests, and found that the non partitioned table was performing faster than the partitioned table.

Specially this query caught my attention:

select * from [table] where IdUser = 100

Why doesn't the partitioned table improve the performance on it? The above query includes a filter by the partition column, wouldn't that focus the engine to scan only half of the records on the partitioned approach?


I have performed the tests again. Assigning the second partition a different file group, and I have reached a performance improvement of 50 % in the mentioned query.

  • Try something bigger - at least 100GB of data or maybe 3-5x the RAM on your system, whichever is greater. Your data set is probably small enough for the DMBS to cache the working set, which will distort your resolts. Commented Feb 6, 2012 at 12:47

3 Answers 3


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:

  • Table in production could reach 1 billion rows. Regarding this 10 million row tests. Now I have done some tests with cold start. And have found that partitioned table its slower than non partitioned. I would have thought that running a query based on the partitioned column will perform better as It would only need to scan a table with half of the rows, What Im a missing here? Query was: select * from [table] where IdUser = 200
    – user1040478
    Commented Jan 22, 2012 at 12:44
  • @user1040478: So 200 UserId x 5 million each? One difference: do you have separate physical disks for each partition?
    – gbn
    Commented Jan 22, 2012 at 12:46
  • 50000 Subcriber each UserId
    – user1040478
    Commented Jan 22, 2012 at 12:49
  • The same disk..
    – user1040478
    Commented Jan 22, 2012 at 12:49
  • Thanks for your update on the reading, Regarding this point: Myth 7: Partitioned tables improve query performance If your query is written in such a way that it can read only the partitions it needs the data from then you will get partition elimination and therefore an equivalent performance improvement. I was performing query using the partiioned column (userId) why didnt I got any performance improvement? Commented Jan 22, 2012 at 13:05
  • Yes, Sql Server uses Cache, named Buffer. But other processing also can cause queries to slow down. To run tests you need to do it on completely idle system and need to run in in cold and hot conditions.

For relatively cold condition try run before measuring:

DBCC freeproccache
dbcc freesystemcache('all')
dbcc dropcleanbuffers

Completely cold condition means in addition to mentioned above - cleanup operation system's cache too.

  • To measure performance of partitioned tables you need two types of queries - which filtering recordset involving various ranges of partitioning key and without.
  • Thanks, Regarding queries: I ve tried select * from table where idUser=100. I got surprised seeing that the non partitioned table was being faster than partitioned. Why is this behavior? Partitioned table its not expected to work faster? As it needs to scan only half of the rows? What Im missing?
    – user1040478
    Commented Jan 22, 2012 at 12:54

The first time you run the query the data is not cached in RAM yet but has to be read from disk. Once it is in RAM it will stay there (hot buffer) until it gets pushed out if it is not frequently requested, so the data that is requested the most will almost always be in RAM (assuming you have enough memory to fit it all in RAM)

If you don't want it in RAM, you can do


or you can restart the instance

  • Can I force sqlserver to not cach the results in RAM? As I want all the tests to be uniform...As maybe sometimes one will give better result according to what is cached...???
    – user1040478
    Commented Jan 22, 2012 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.