2

My company is moving to SQL Server 2008 R2. We have a table with tons of archive data. Majority of the queries that uses this table employs DateTime value in the where statement. For example:

Query 1

SELECT COUNT(*) 
FROM TableA 
WHERE 
     CreatedDate > '1/5/2010' 
     and CreatedDate < '6/20/2010'  

I'm making the assumption that partitions are created on CreatedDate and each partition is spread out across multiple drives, we have 8 CPUs, and there are 500 million records in the database that are evenly spread out across the dates from 1/1/2008 to 2/24/2011 (38 partitions). This data could also be portioned in to quarters of a year or other time durations, but lets keep the assumptions to months.

In this case I would believe that the 8 CPU's would be utilized, and only the 6 partitions would be queried for dates between 1/5/2010 and 6/20/2010.

Now what if I ran the following query and my assumptions are the same as above.

Query 2

SELECT COUNT(*) 
FROM TableA 
WHERE State = 'Colorado'

Questions?
1. Will all partitions be queried? Yes
2. Will all 8 CPUs be used to execute the query? Yes
3. Will performance be better than querying a table that is not partitoned? Yes
4. Is there anything else I'm missing?
5. How would Partition Index help?

I answer the first 3 questions above, base on my limited knowledge of SQL Server 2008 Partitioned Table & Parallelism. But if my answers are incorrect, can you provide feedback any why I'm incorrect.

Resource:


Update We do have a Cluster Index on the DB and covering indexes on columns

BarDev

  • Your assumptions appear correct. Partition index would mean parallel seeks in the indexes. – user3914 Feb 24 '11 at 22:14
  • If you haven't already, I would make the clustered index on [Date, Id]. See this excellent video on partitioning: technet.microsoft.com/en-us/sqlserver/gg545008.aspx Also, I assume you're aware that table partitioning is only available in SQL Server Enterprise Edition and higher. – Jon Seigel Mar 5 '11 at 21:45
1
  1. Yes
  2. Possibly, depending on what index is queried and how that index is partitioned.
  3. Possibly, again depending on what index is queried and how that index is partitioned.
  4. A Non-Clustered index could be created on the table, and that index could be partitioned against the State column which would be very quick. If there is an index on another column, and the State column is included then that index could be cheaper for SQL Server to scan.
  5. Probably.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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