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.