Blog Post

T-SQL Tuesday #7 - StreamInsight



The 7th T-SQL Tuesday is upon us and you can read about it on SQL Chicken’s blog. He’s hosting this month, with a few rule changes (like the picture above) and his call is for posts on new features in SQL Server 2008 or SQL Server 2008 R2.

T-SQL Tuesday is a blog party where we encourage a large number of people to write on a single theme or topic. It’s fun, and I encourage all of you to participate.


A new feature introduced in SQL Server 2008 R2 is StreamInsight. It’s a complex event processing technology that I think is one of the more amazing things to be added to SQL Server in quite some time. Of course I thought the Service Broker was cool in SQL Server 2005, so that shows what I know.

This is based on the work at Streambase, and the work of Michael Stonebreaker. It involves building a query engine, processing that can handle large volumes of data, things that come in streams: price quotes, sensor data, auditing data, any number of continuous data elements.

We commissioned a Stairway Series on this topic, since I think it’s a game changer. The introductory article, What is StreamInsight and why does it matter?, does a great job showing what the technology is and what it can do. It’s a way to filter, process and gather data, find patterns, etc. from a large volume of data, and then get it into another database.

Why does that matter to us? I think this image presents a great view of why:


As much as I have said BI isn’t the next big thing because it’s too complex, I think there’s great value in BI systems that can distill data down to a point where we can work with it. I think StreamInsight can help with this, especially as we get larger and larger volumes of data, more than our SQL Server instances can handle.

I believe the ability to gather information from a variety of streams, in a more timely manner than you might get with something like Integration Services (SSIS), will greatly aid businesses in analyzing the information that can help them.

This is a technology that I can see lots of uses for. Real-time auditing data, going through a stream of information about activity on a server or application and flagging patterns. Learning what is normal, and then raising flags when there’s a deviation from that.

It could be great for monitoring a large volume of data from some source like Twitter, searching for references to your company or product, categorizing them, counting retweets, analyzing those that never get a response, or might need a response. The same thing could be done with a stream of blog entries from a search engine like Google or Bing.

I think the possibilities could be very cool. I look forward to seeing some innovative uses of this new feature.

StreamInsight is available in SQL Server 2008 R2, in two editions. There’s a Standard edition that works with Standard, Enterprise, and Web editions. There’s also a  Premium Edition that is available for Enterprise, Developer, and Evaluation editions. The difference is in the rate of processing that occurs.

If you exceed these levels of stream data, you need the Premium edition:

  • More than 5000 events a second
  • You need to process them inside of 5 seconds (low latency)

These are the basic differences in the editions, and if you can tolerate a delay of more than five seconds (most companies/applications) and you don’t have an extremely high rate of data (5000 events/sec), you can use this technology in Standard Edition.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating