Press "Enter" to skip to content

Author: Kevin Feasel

SQL Server Versions: Choose Your Own Adventure

Published 2019-01-22 by Kevin Feasel

Brent Ozar has a guide to help you choose which version of SQL Server to install:

Wait! Before you install that next SQL Server, hold up. Are you sure you’re using the right version?
I know, management wants you to stay on an older build, and the vendor says they’ll only support older versions, but now’s your chance to make your case for a newer version – and I’m gonna help you do it.
I’m going to go from the dark ages forward, making a sales pitch for each newer version.

My branch logic is easier: if you need the data today, SQL Server 2017. If you need the data later this year, SQL Server 2019. If you hate your company and yourself, SQL Server 6.5.

Comments closed

Resource Semaphore Waits

Published 2019-01-22 by Kevin Feasel

Arthur Daniels explains what the RESOURCE_SEMAPHORE wait type is with an example:

So according to this DMV, there’s only 3 queries with memory grants, while the remaining 5 queries have to wait for space in this semaphore. This is where the wait type comes in. When a query is sitting as a waiter, it will display the wait type RESOURCE_SEMAPHORE.

This is one of the biggest performance-killing waits you can find, and there are several ways to tackle it in SQL Server, as well as adding more hardware.

Comments closed

SQL Server Data File Size Limit: 16 TB

Published 2019-01-22 by Kevin Feasel

Dave Mason learned the hard way that data files max out at 16 TB in SQL Server:

I could see that most of the stored proc execution calls were coming from the same server, which was a processing server running a web service. The customer decided to shut down the service and have a quick discussion with his dev team to see if there had been any recent changes deployed. We called it a night. I got a text the next morning. The server was rebooted, and performance had improved noticeably. None to my surprise, I got another call later that day: performance had deteriorated again.
I got back online and looked at all the same things I’d looked at before and still was puzzled.

I think this is a case where Swart’s Ten Percent Rule is easier to violate than most: terabyte-sized databases are fairly common these days, though most of them probably have multiple data files to help with piecemeal recovery.

Comments closed

P-Hacking and Multiple Comparison Bias

Published 2019-01-21 by Kevin Feasel

Patrick David has a great article on hypothesis testing, p-hacking, and multiple comparison bias:

The most important part of hypothesis testing is being clear what question we are trying to answer. In our case we are asking:
“Could the most extreme value happen by chance?”
The most extreme value we define as the greatest absolute AMVR deviation from the mean. This question forms our null hypothesis.

Give this one a careful read and try out the code. This is an important topic for anyone who analyzes data to understand.

Comments closed

SPLIT And FLATTEN In Snowflake DB

Published 2019-01-21 by Kevin Feasel

Koen Verbeeck takes us through a couple of Snowflake functions which behave quite differently from their SQL Server equivalent:

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.
Let’s start with SPLIT. Splitting string is something most of us have to do from time to time. In SQL Server, we had to wait until SQL Server 2016 when the table-valued function STRING_SPLIT came along. The syntax of both functions is exactly the same. You specify the string to split and the delimiter.

It turns out that SPLIT doesn’t quite split the same way that STRING_SPLIT does in SQL Server. It gets closer when you add FLATTEN but someone coming from a SQL Server background will still need to pay attention.

Comments closed

Get Windows Failover Cluster Errors

Published 2019-01-21 by Kevin Feasel

John Morehouse walks us through the Get-ClusterLog cmdlet in Powershell:

Sometimes you know that a problem occurred, but the tools are not giving you the right information.  If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen.  The user interface won’t show you any errors, but you KNOW there was an issue.  This is when knowing how to use other tools to extract information from the cluster log becomes useful.
You can choose to use either Powershell or a command at the command prompt.  I tend to lean towards Powershell. I find it easier to utilize and gives me the most flexibility.

Click through for an example, including of a method which filters out everything but error messages.

Comments closed

Updating Power BI Reports Based On Website Changes

Published 2019-01-21 by Kevin Feasel

Kasper de Jonge shows how you can scrape a webpage using an M function and check for specific updates:

A quick hack today. Got this question from someone who needed to be able to update a report and show users that something had changed. This is easy when you have access to a database and can add data to it but in this case that was not possible.
So I came up with a hacky (and great :P) way to do this. and wanted to share it in case it came handy in your box of tricks :).

It is a little bit hacky, but much less so on a website with a last updated date visible someplace.

Comments closed

Azure Databricks Security

Published 2019-01-21 by Kevin Feasel

Tristan Robinson looks at what’s currently available in terms of security on Azure Databricks:

You’ll notice that as part of this I’m retrieving the secrets/GUIDS I need for the connection from somewhere else – namely the Databricks-backed secrets store. This avoids exposing those secrets in plain text in your notebook – again this would not be ideal. The secret access is then based on an ACL (access control list) so I can only connect to Data Lake if I’m granted access into the secrets. While it is also possible to connect Databricks up to the Azure Key Vault and use this for secrets store instead, when I tried to configure this I was denied based on permissions. After research I was unable to overcome the issue. This would be more ideal to use but unfortunately there is limited support currently and the fact the error message contained spelling mistakes suggests to me the functionality is not yet mature.

To be charitable, there appears to be room for implementation improvement.

Comments closed

The Thought Behind Metrics

Published 2019-01-21 by Kevin Feasel

Shannon Holck takes a book as a jumping-off point for failure by metric success:

There were some great use cases in the book.  Doctors that stopped taking cases that were difficult because it would ruin their surgical success metric.  Police that stopped responding to calls because it would ruin their case closure rate if they couldn’t solve it.

Muller states “The problem is not measurement, but excessive measurement, and inappropriate measurements – not metrics, but metric fixation.”

Shannon’s case study and recommendations were interesting.

Comments closed

Data Lake Organization Tips

Published 2019-01-21 by Kevin Feasel

Melissa Coates has some great advice for people working with data lakes:

Q: Partitioning by date is common. Where should the dates go in the folder hierarchy?

Almost always, you will want the dates to be at the end of the folder path. This is because we often need to set security at specific folder levels (such as by subject area), but we rarely set up security based on time elements.

Optimal for folder security: \SubjectArea\DataSource\YYYY\MM\DD\FileData_YYYY_MM_DD.csv

Tedious for folder security: \YYYY\MM\DD\SubjectArea\DataSource\FileData_YYYY_MM_DD.csv

Click through for all of Melissa’s advice in FAQ form.

Comments closed