Presentation idea - Using Columnstore Indexes to Store and Analyze Billions of Torques

Sat 22 July 2017 | tags: 208
This is an abstract I wrote for a local area users group, only to find out that they had stopped meeting. Toledo doesn't seem to be able to keep many computer-based user interest groups alive. I'm posting it here for my own records.


Torque tools (impact wrenches, electronic motors, rotary torque sensors) produce lots of data as a vehicle is being built. Torque data is used to control the movement of the production lines and to do quality control analysis. During production, traditional row-based storage makes the most sense, using the VIN and torque tool ID as the primary key in a table optimized for insertion speed and VIN-based lookups. However, post-production it makes more sense to switch to column-based storage for quality control analysis because aggregation functions (mean, std. dev.) need to be computed over large ranges of data (regardless of VIN), and many torque values are similar. Columnstore indexes are a good solution to the latter storage and analysis problem, and they have gotten much better in SQL Server 2016. I'll describe how they're useful for this problem for analyzing how well the torque tools are working using simple statistical techniques.