Kenneth Fisher has a good case study on tuning with the help of Rob Farley:
Here we are looking at the difference between the estimated and actual number of rows for an element of the plan. To look at this information you can either mouse over the element or right click and open the properties tab. In this case you will see that the estimated number of rows (what the optimizer thought would happen) is fairly low (117) particularly compared to what actually happened (1494900). When you see a big difference like that in a query plan there is something wrong.
This is a really nice and detailed walkthrough in which Rob plays Socrates and Kenneth your favorite of the group (Thrasymachus anyone?).
Paul White shows us a scenario in which attempts to update a writable partition could fail:
The where clause is exactly the same as before. The only difference is that we are now (deliberately) setting the partitioning column equal to itself. This will not change the value stored in that column, but it does affect the outcome. The update now succeeds (albeit with a more complex execution plan):
The optimizer has introduced new Split, Sort, and Collapse operators, and added the machinery necessary to maintain each potentially-affected nonclustered index separately (using a wide, or per-index strategy).
Read on for the reason why this happens, as well as a few solutions.
Very quick and simple and hopefully of use to people, this could easily be turned into a function. The full script is below and also available here on the Powershell gallery or by running Save-Script -Name Set-ExtendedEventsSessionstoAutoStart -Path <path>
This is indeed a quick and easy script, and quite useful when checking across a large number of instances.
Thomas Rushton walks us through determining if there’s memory pressure on an instance:
If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.
This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?
More importantly, he shows us how bad the situation is: is this something that happened for a couple of seconds, or is it persistent? This is a great walkthrough.
Matt Smith links to Analysis Services auditing details and includes an audit table:
There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing.
Today I am looking at the SSAS auditing – you can find out more about it onTechNet.
Olaf Helper has published some TSQL code for querying the audit data -on theScript Center.
Just because it’s in a cube doesn’t mean we shouldn’t be able to audit it.
Avi Singh notes that Power BI will soon allow you to analyze data sets in Excel:
As a Modern Excel enthusiast (Modern Excel = Excel + Power Pivot + Power Query = Magic!), I found myself hesitant in embracing the new world of Power BI. Many of those inhibitions have shed away as Microsoft has continued to innovate and deliver an outstanding experience with Power BI. But I could not get over the feeling that going from Excel to Power BI felt like a one-way street.
You could upload/import an Excel Power Pivot model into Power BI desktop or onto PowerBI.com. But then you could not get it back. You could not get it back in Excel. In thefirst blog post on the site (yes the very first) Rob called us gridheads, and that we are.
Excel is still the top tool for business users. Anything you can do to make life easier for your Excel users makes life easier for you as well.
Steph Locke notes that the R Consortium has agreed to support satRdays:
I’m very pleased to say that the R Consortium agreed to the support the satRday project!
The idea kicked off in November and I was over the moon with the response from the community, then we garnered support before submitting to the Consortium and I must have looped the moon a few times as we had more than 500 responses. Now the R Consortium are supporting us and we can turn all that enthusiasm into action.
This is great. I’m looking forward to this taking off and being a nice complement to SQL Saturdays in cities.
Steph Locke has created an R package to query Troy Hunt’s Have I Been Pwned? site:
The answer in life to the inevitable question of “How can I do that in R?” should be “There’s a package for that”. So when I wanted to query HaveIBeenPwned.com (HIBP) to check whether a bunch of emails had been involved in data breaches and there wasn’t an R package for HIBP, it meant that the responsibility for making one landed on my shoulders. Now, you can see if your accounts are at risk with the R package for HaveIBeenPwned.com, HIBPwned.
This is a nice confluence of two fun topics, so of course I like it.
Ginger Grant has some pointers on getting started with the Hortonworks Data Platform sandbox:
Previously, spinning up a virtual machine meant purchasing software. No more, as there is now an open source application. In the example shown here, the Linux operating system will be installed, you can put any operating system you want on your virtual machine, provided of course you have a license for it. If you don’t feel comfortable installing non-released versions of code like SQL Server 2016, on your pc, a virtual machine is a great way to test it out. You will need to provide your own operating system, but there are trial versions you can use for limited periods of time as well. The open source virtual machine Oracle VM Virtual Box is the only open source version of a virtual machine software. You can download it here. This software is needed prior to installing the Hortonworks Sandbox. Obviously Hortonworks is not the only version of Hadoop available, Cloudera has a Hadoop VM too, which you can download as well. Personally I am not a use fan of the Cloudera Manager, which is why I prefer Hortonworks, but either will work with polybase.
I’m personally a fan of VMware Player for VMs, but either will work well for the task.
If you have a non-aligned index on the table, you’ll see an error like this:
Msg 3756, Level 16, State 1, Line 1
TRUNCATE TABLE statement failed. Index ‘ix_FirstNameByBirthDate_pt_BirthYear_FirstNameId_nonaligned’ is not partitioned, but table ‘FirstNameByBirthDate_pt’ uses partition function ‘pf_fnbd’. Index and table must use an equivalent partition function.
This isn’t a bug, and it makes total sense from a logical point of view. “Non-aligned” indexes are not partitioned like the base table is– by definition they are either partitioned differently, or not partitioned at all. The chunk of data that you’re trying to truncate isn’t all in an easily identifiable partition that can be quickly marked as “data non grata”. There’s just no way to do a simple truncate when the data’s scattered all around.
Ideally, all indexes on a partitioned table would be partition-aligned, as it makes maintenance a lot easier. That’s not always possible, though, so keep this in mind.
Kevin Feasel
2016-03-28
Query Tuning