We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical queries.

At initial try we partitioned the table daily for 10 years, however, noticed the queries that select from single partition are much slower than are expected when the table wasn't partitioned at all or started with only 10 partitions. I was wondering if there's something that needs to be done to allow for a large number of partitions in the single table.

All partitions are using CLUSTERED COLUMNSTORE index on SQL Server 2014 enterprise.

  • How many records are in each 10 year partition? Table definition would be helpful. – Dave Jan 13 '16 at 21:18
  • The short answer: yes. However, whether that impact is a positive, negative, or a more nuanced mix of the two depends entirely on what you are trying to do. If you post the full table definition and example query that you are trying to optimize, we may be able to help. – Geoff Patterson Jan 13 '16 at 21:32
  • Hi @dave, The table was partitioned daily for 10 year period which meant it had 3560 partition with 2 additional one for future needs. – sgojgini Jan 13 '16 at 21:46
  • geoff-patterson: I no longer have the tables around to be able provide exact structure but the query actually was pretty simple at the end. I was trying to select top 100 rows in particular partition by joining to second table which had the value that would limit to single partition select top 100 * from partitioned_table pt join businessdate bd on pt.snapshotdate = bd.snapshotdate The businessdate table contain only single row and column which is the date query is running for and table is partitioned daily using snapshotdate column. – sgojgini Jan 13 '16 at 21:49

Your Answer

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

Browse other questions tagged or ask your own question.