A Fine Slice Of SQL Server
Reza Rad points out some SSIS 2016 catalog changes:
There are 17 new stored procedures in SSIS Catalog 2016 CTP 3.0. Most of them is to cover changes related to AlwaysOn and Customized Logging Levels, however there are some others. Let’s see.
There are also procedures to support incremental package deployment, so check out the post.
Comments closedThere might be a theme to today’s posts…
Matt Allington shows us compression in Power Pivot:
Power Pivot would end up storing a table that looks more like the black table above (rather than the blue one), keeping just the minimum amount of information it needs to rebuild the real table of data on the fly when and if required. If the black RLE table ended up taking more space than the original column of data, then there would be no benefit of RLE and the original column of data would be stored. Power Pivot may use one or more of the other compression techniques used as well as, or instead of RLE – it all depends on the specifics of the actual data.
This is a very interesting look at ways the Power Pivot team optimize data storage.
Comments closedChris Webb shows us how to work with gzip files using PowerQuery:
For example, given a gzip file that contains a single csv file, here’s an example M query showing how the Binary.Decompress() function can be used to extract the csv file from the gzip file and then treat the contents of the csv file as a table:
He goes on ot show how Binary.Decompress is used to read Excel XLSX files.
Comments closedBill Anton shows three separate methods for parsing SQL Server Analysis Services Extended Event data:
Warning, this method involves the use of .NET – proceed with caution! That said, please proceed because the results are certainly worth it.
The same 32MB file that took 5 minutes using the first method, 2 minutes using the second method, now only took ~3 seconds using this method. Also, the processing time is essentially linear – so even when we bump it up to 4 files (or ~128MB of data) its only going to take ~12 seconds. This would have brought us down to under 5 minutes to process all 3GB of data.
Again, I’m not a .NET expert (not really even a novice) so the code I’m sharing is most likely terrible…but it works (at least for me) and its fast. So here it is…read through it and feel free to heckle me in the comments 😉
Don’t heckle Bill; thank him.
Comments closedJovan Popovic shows us the COMPRESS and DECOMPRESS functions in SQL Server 2016:
These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side.
This seems potentially useful if you’re going to store blobs of data you don’t need to parse.
Comments closedErik Darling shows how to make filtered indexes a bit more robust:
But why oh why didn’t SQL use my filtered indexes for even smaller subsets of the filter condition? It seemed insane to me that SQL would know the filter for the index is on (x > y), but wouldn’t use them even if (z > x).
The solution was to put the filtered column in the include list. This lets SQL generate statistics on the column, and much like getting rid of the predicate key lookup, allows you to search within the filtered index subset for even more specific information.
Filtered indexes are as useful as they are mercurial.
Comments closedMatt Slocum ran into a production issue with a cluster name that changed:
Next is just a simple matter of updating the registry value. I launched Registry Editor (RegEdit.exe) and navigated to HKLM > Software > Microsoft > Microsoft SQL Server > MSSQL12.MSSQLSERVER > Cluster
Note: MSSQL12.MSSQLSERVER will vary based on SQL version (SQL 2012 is “MSSQL11”) and instance name (“MSSQLSERVER” is the default instance and named instances are the actual instance name instead of “MSSQLSERVER”).Within the Cluster key I updated the value of ClusterName to match what was specified in Failover Cluster Manager
This is a good diagnosis and resolution of an issue.
Comments closedReza Rad has a three-part series on applying BI tools (specifically, Power BI) to Fitbit.
So for this post we are going to build that dashboard (not all of that obviously, because we don’t have the data required for all of that), but most part of it with Power BI. You will see how easy and powerful is Power BI in this kind of scenarios, and you will see how you can be the BI Developer of Fitbit in a few steps of building this demo.
Part 2:
Unfortunately Power Query or let’s say Power BI doesn’t have a loop structure, and that is because of the functional structure of this language. However there are data structures such as Table and List that can be easily used with each singleton function to work exactly as a loop structure does. Here in this post I will get you through the process of looping into files in a directory and processing them all, and finally combining them into a large big table. You will also learn some Power Query M functions through this process.
Part 3:
Fitbit calculates based on my current weight and age (I assume) how much calories I have to spend each day. I don’t know that calculation, So I create a static measure with the value of 2989 for the amount of calories I have to spend each day. I also create StepsCap measure with 12000 value showing that I have to walk 12000 steps a day, and another one for FloorCap with the value of 10. I created a Calories HighEnd measure with 5000 calories as value (I will die if I burn more than that!). You can create all these measures easily in Data tab.
This is a nice combination of work and play, building an interesting system with a data set interesting to the author and freely available.
Comments closedKoen Verbeeck on using sp_BlitzIndex:
What’s great is that this script also provides you with the URLs to knowledge articles on the Brent Ozar website. If you don’t understand one of the results, you can immediately look it up and read about it.
By focusing on the results of sp_BlitzIndex script, I could boost performance in just a few hours of work. This near real-time data warehouse is the source for a reporting application used by dozens of people in the field, and you could immediately tell it worked a lot faster. Awesomesauce.
There are several interesting scripts in the suite; check them out if you’ve not already.
Comments closedDenny Cherry thinks Windows Server 2016 pricing changes won’t matter that much:
So looking at this chart we can see that any machine with less than 8 cores per socket and two sockets or less will cost you exactly the same as it will today. Also any machine with one socket and less than 16 cores will also cost the same as it costs today.
I’ll admit that I don’t know much about licensing, but rumblings at my company are that our server licensing costs are going to go up significantly. As always, check with one of the four people on Earth who understand Microsoft licensing.
Comments closed