Include Filtered Index Columns

Erik 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.

Cluster Name Changes

Matt 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.

Fitbit BI

Reza Rad has a three-part series on applying BI tools (specifically, Power BI) to Fitbit.

Part 1:

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.

Tuning With BlitzIndex

Koen 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.

More On Server 2016 Pricing

Denny 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.

Select Failures

Paul Randal walks us through a complicated scenario involving corruption:

An interesting corruption problem cropped up on the MCM distribution list yesterday and after I figured it out, I thought it would make a good blog post in case anyone hits a similar problem.

In a nutshell, the problem was corruption such that a simple SELECT * query failed, but a SELECT * query with an ORDER BY clause worked.

This is interesting, but hopefully you don’t encounter these types of problems very often in your environment.

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031