2 replaced http://sqlblog.com/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx with http://web.archive.org/web/20180422160838/http://sqlblog.com:80/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx
source | link

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency characteristics for bulk insert. Make sure to read SQL Server clustered columnstore Tuple Mover and you achieve a sufficiently high BCP rate to create directly compressed segments. If not, staging, bcp+rebuild in staging and then partition switch is a must.

With the layout you have I would have much preferred having clustered index on timestamp. As is now, your best hope is to leverage partition elimination. There are many way this can misfire (Why Doesn’t Partition Elimination Work?Why Doesn’t Partition Elimination Work?) and even when it works it still causes concurrency problems. Specially since most times the user queries concern recent data, this leads to blocking against the BCP inserts.

I would make sure my BPC does not escalate to table X locks. The Data Loading Performance Guide is mandatory reading, but be aware that the whitepaper is rather obsolete when it comes to columnstores so only applies to the current structure. Pay special attention to the 'Bulk Loading a Partitioned Table' chapter.

I would consider a much coarser partitioning granularity, perhaps 1 partition per day. Having too many partitions brings significant runtime cost.

And, as with any performance issues, measure.

I would not be shy in considering radical different approaches. Cassandra for instance can ingress data at very high rate, and has the advantage of a "throw more hardware at the problem" scale-out solution. Queryability is not Cassandra's forte, but once you buy into that ecosystem there is Spark+Hive+ORC/Parquet and these can query data pretty fast.

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency characteristics for bulk insert. Make sure to read SQL Server clustered columnstore Tuple Mover and you achieve a sufficiently high BCP rate to create directly compressed segments. If not, staging, bcp+rebuild in staging and then partition switch is a must.

With the layout you have I would have much preferred having clustered index on timestamp. As is now, your best hope is to leverage partition elimination. There are many way this can misfire (Why Doesn’t Partition Elimination Work?) and even when it works it still causes concurrency problems. Specially since most times the user queries concern recent data, this leads to blocking against the BCP inserts.

I would make sure my BPC does not escalate to table X locks. The Data Loading Performance Guide is mandatory reading, but be aware that the whitepaper is rather obsolete when it comes to columnstores so only applies to the current structure. Pay special attention to the 'Bulk Loading a Partitioned Table' chapter.

I would consider a much coarser partitioning granularity, perhaps 1 partition per day. Having too many partitions brings significant runtime cost.

And, as with any performance issues, measure.

I would not be shy in considering radical different approaches. Cassandra for instance can ingress data at very high rate, and has the advantage of a "throw more hardware at the problem" scale-out solution. Queryability is not Cassandra's forte, but once you buy into that ecosystem there is Spark+Hive+ORC/Parquet and these can query data pretty fast.

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency characteristics for bulk insert. Make sure to read SQL Server clustered columnstore Tuple Mover and you achieve a sufficiently high BCP rate to create directly compressed segments. If not, staging, bcp+rebuild in staging and then partition switch is a must.

With the layout you have I would have much preferred having clustered index on timestamp. As is now, your best hope is to leverage partition elimination. There are many way this can misfire (Why Doesn’t Partition Elimination Work?) and even when it works it still causes concurrency problems. Specially since most times the user queries concern recent data, this leads to blocking against the BCP inserts.

I would make sure my BPC does not escalate to table X locks. The Data Loading Performance Guide is mandatory reading, but be aware that the whitepaper is rather obsolete when it comes to columnstores so only applies to the current structure. Pay special attention to the 'Bulk Loading a Partitioned Table' chapter.

I would consider a much coarser partitioning granularity, perhaps 1 partition per day. Having too many partitions brings significant runtime cost.

And, as with any performance issues, measure.

I would not be shy in considering radical different approaches. Cassandra for instance can ingress data at very high rate, and has the advantage of a "throw more hardware at the problem" scale-out solution. Queryability is not Cassandra's forte, but once you buy into that ecosystem there is Spark+Hive+ORC/Parquet and these can query data pretty fast.

1
source | link

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency characteristics for bulk insert. Make sure to read SQL Server clustered columnstore Tuple Mover and you achieve a sufficiently high BCP rate to create directly compressed segments. If not, staging, bcp+rebuild in staging and then partition switch is a must.

With the layout you have I would have much preferred having clustered index on timestamp. As is now, your best hope is to leverage partition elimination. There are many way this can misfire (Why Doesn’t Partition Elimination Work?) and even when it works it still causes concurrency problems. Specially since most times the user queries concern recent data, this leads to blocking against the BCP inserts.

I would make sure my BPC does not escalate to table X locks. The Data Loading Performance Guide is mandatory reading, but be aware that the whitepaper is rather obsolete when it comes to columnstores so only applies to the current structure. Pay special attention to the 'Bulk Loading a Partitioned Table' chapter.

I would consider a much coarser partitioning granularity, perhaps 1 partition per day. Having too many partitions brings significant runtime cost.

And, as with any performance issues, measure.

I would not be shy in considering radical different approaches. Cassandra for instance can ingress data at very high rate, and has the advantage of a "throw more hardware at the problem" scale-out solution. Queryability is not Cassandra's forte, but once you buy into that ecosystem there is Spark+Hive+ORC/Parquet and these can query data pretty fast.