If you have not already seen in the What’s New in Integration Services list there is a new property for Data Flows that is called AutoAdjustBufferSize. Why is this important and what does it actually do?
First, let’s remember that in SQL 2014 we had a max DefaultBuffersize of 100MB and to try and optimize this we would use calculations to figure out or row size and how to set the DefaultBufferMaxRows. I’ve covered these techniques in the past in this video or you can read about it via Ginger Grant’s Blog.
Now in SQL 2016 we don’t have to worry as much about trying to perform these calculations as setting the AutoAdjustBufferSize to true will take care of this for us. So what is happening when I set this to true. SSIS will now ignore the DefaultBufferSize property and set the buffer size based off of the following calculation of DefaultBufferMaxRows * rowsize. When performing this calculation the rowsize is actually calculated at runtime, while the DefaultBufferMaxRows is a property value you specify either when designing the package or through an expression and can be tied to a parameter.
I do have a big caveat to this and with doing some extensive testing you need to make sure that if you have a wide file and try to match SQL Server max row group size of 1,048,576 you can get an error of “Buffer failed while allocating” due to not enough free memory on your system to auto adjust the buffer size (See Below). So as with anything I recommend you test, test and then test again as I have seen some significant performance increase by changing this setting but also the dangers of not having enough memory.
While I never really enjoy a cross country flight back home my feet are very happy that I am finally sitting down for a few hours. CES 2016 was once again another great year and has proven to me again why it’s a must attend conference that spans across many technologies and reaches almost every industry. This was my 5th time attending and over those years I have represented technology in several different industries, but this was my first focusing on marketing. In most of my previous years I was looking at what technology could be built on top of or integrated into what I was already doing. However, this year the focus was more on different ways to interact with the consumer, along with understanding the consumers buying patterns across all industries.
There certainly was one very common theme that you saw at every turn, “Mobile is king”, and it’s not just the phone that we all can’t live without anymore. It was also the theme in all the auto industries connected cars with Wi-Fi enabled cars, integrated entertainment with Amazon, to self driving vehicles. If the car can drive itself, that gives you more time to spend on your mobile devices or enjoying the entertainment systems. From a content delivery aspect imagine not having to rely on radio and the spoken work to get the attention of those who are driving and delivering targeting advertising based on the location of the vehicle. In my opinion we are not far away from this becoming a reality in the next couple of years. One additional thought here as well is the car becoming even more integrated and say when the gas light comes on, it uses GPS and Wi-Fi to automatically search for the nearest gas station and give you directions.
From a phone aspect there was every gadget you could think of from phones longer battery life, larger screens, devices to give you additional battery life, protect your phone, boost your signal and protect your personal information. All these tell me one thing and that the mobile phone has become a device that most people no longer know how to live without, myself included. In almost every keynote, session, panel discussion or fireside chat I attended they all talked about the importance of or new ways to interact with the consumer, who spends over 14 hours a day with the phone no further than an arms length away and averages around 4 hours actively looking at the screen. There is no doubt that the shift of marketing dollars and content will only increase in the digital space as mobile continues to gain not only additional eyeballs but also a greater share of recreational time compared to TV.
From a tradition TV marketing stand point everyone was in agreement that TV viewership is down and that is a trend that most likely is not going to change. However, DISH network is certainly trying to make sure that not everyone walks away from there TV or cuts their cord. There new DSTB is capable of 4K resolution and more importantly it is integrated with Sling, YouTube and Netflix. Which points to a new trend of, if you can’t beat them join them. Netflix has certainly disrupted the likes of both cable and satellite providers so why not build in Netflix search and try and keep them from cutting the cord completely. It’s also a very smart move as it gets a greater amount of information about the consumer, their watching habits and increases the ability to provide addressable media.
Overall the media landscape and consumer interactions continue to evolve as once again shown this year as CES and this upcoming year will show great strides in perfecting the interaction with the consumer. None of the would be capable thought without the innovations taking place with consumer technology. It may scare some about how much everyone will know about them but I for one will enjoy the day when I am not getting hit with ads, emails, calls, etc.. that have nothing to do with me or what I am looking to buy.