A Fine Slice Of SQL Server
Phil Seamark dives into storage modes in Power BI:
How to choose the correct storage mode for Power BI Tables.
This article aims to help explain the different storage modes available when designing an aggregation strategy for a Power BI Report. What each storage mode is and when you would use it. Picking the correct storage mode for each table in your model can significantly affect overall performance.
Click through for the tl;dr version, but stay for the whole thing.
Comments closedAnthony Nocentino has a script and some tips for us:
This post is a reference post for retrieving IO statistics for data and log files in SQL Server. We’ll look at where we can find IO statistics in SQL Server, query it to produce meaningful metrics, and discuss some key points when interpreting this data.
Click through for the script, and then a bulleted list of things to keep in mind as you’re reviewing the data.
Comments closedArgenis Fernandez puts SQL Server snapshots on a Pure Storage FlashArray to the test:
Look, I’m not here to fight your religious war about how snapshots should not be called backups. I’m just gonna call them fast-as-fast restores(*) and be done with it. Because let’s be honest, with Pure Storage there’s absolutely nothing faster than a storage snapshot to recover a volume. Or volume(s). You get the idea. It’s about how fast you recover, every time.
Yes, I do understand that there are a million of considerations for something to be called a “backup”. We’ll get to those little by little – don’t expect a thorough post on that debate right now. Today I want to focus on one question: Are Pure Storage FlashArray snapshots stable, trustworthy enough that I can take them without pausing I/O against my database? Can I trust that the database will come online every time from a snapshot?
Read on for the Answer. For additional fun, read the whole article with your mental voice sounding like Argenis.
Comments closedSteve REzhener has a warning for us:
Amazon Web Services Elastic Cloud Computing (a.k.a. EC2) is a service that lets anyone with a credit card rent a virtualized server from Amazon. To cater to different clients’ needs, AWS provides various instance types that are either general instance or specific-purpose instances (focused on CPU, RAM, IO). You can see the different types in Fig 1. This blog post is going to talk about a storage optimized instance. the I3 instance type family, its little-known problem, and the solution in the form of Elastic Block Storage (a.k.a. EBS).
Click through for the warning, more explanation, and what you can do about it. H/T the SQLServerCentral newsletter.
Comments closedAveek Das takes us through the basics of Amazon Athena:
Serverless. Since Amazon Athena is offered as a fully managed cloud service, customers do not need to take the pain of installing and maintaining separate infrastructures for this. You can start by logging into the AWS Web console and proceeded to Amazon Athena.
Pay Per Query. You only pay for queries you execute. This is very cost-effective, as you can easily figure out your monthly expenses based on your usage pattern. On average, users pay 5 USD for each terabyte of data scanned. This can be further optimized by creating partitions or compressing your dataset.
Interactive Performance. We do not need to worry about the resources that work behind the scenes. When a query is executed, Athena automatically runs the query in parallel across multiple resources, bringing the results faster.
Read on to see an example of Athena in action.
Comments closedKristyna Hughes links Power BI to a data source:
The step-by-step process below walks through connecting to data housed in Azure Blob Storage from Power BI using a SAS token. There are many ways to grab your data from Blob Storage, but this is the most efficient, scalable, and secure way that I found (with some security restrictions from watchful DBAs).
Click through for the solution, which is based on using SAS tokens.
Comments closedDavid Klee creates some files:
Do you have quite large MDF files on your database? By large, I mean hundreds of gigabytes (or larger). Have you ever noticed that your SQL Server disk stall metrics for these data files are much higher than the storage latency metrics exhibited on the underlying operating system layer? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Why don’t we do this enough for our user databases as well? It’s easy for a brand-new database from day zero, but what about existing databases that have grown out of control with a single data file attached? Let me show you how to adjust this for existing databases without an outage!
Check it out. This is a part of database administration I’d never really thought much about, so it often ended up being a blind spot for me.
Comments closedLinkedIn runs its big data analytics on Hadoop. During the last five years, the analytics infrastructure has experienced tremendous growth, almost doubling every year in data size, compute workloads, and in all other dimensions. It recently reached two important milestones.
1. LinkedIn now stores 1 exabyte of total data across all Hadoop clusters.
2. Our largest 10,000-node cluster stores 500 PB of data. It maintains 1 billion objects (directories, files, and blocks) on a single NameNode serving RPCs with an average latency under 10 milliseconds, making it one of the largest (if not the largest) Hadoop cluster in the industry.
From the early days of LinkedIn, Apache Hadoop was the basis of our analytics infrastructure. Many teams assisted in this effort to make Hadoop our canonical big data platform.
Read on for different techniques they’ve used, as well as code changes implemented in HDFS to support this data size.
Comments closedSean Gallardy wins the jackpot:
If, somehow, you’ve managed to see this error in your errorlog then congratulations, you’ve won an instance of SQL Server that probably won’t be doing much.
I found out about this message a few months ago, but it has been in the product for years and I went this long without ever even knowing it existed (congrats me!) until I was asked about it and coincidentally ended up finding it in an errorlog the same week. Clearly, I have too much fun packed into my weeks. I asked around, only one other person had ever found this in an errorlog before… that’s either impressive, depressing, or some perfect quantity of both – mellow it out to a smooth melancholy.
Click through to see more information about the 1000000 IO error message and when you might find it.
Comments closedIn my real project, I need to build a Logic App to send email messages with a set of files attached from my Azure Storage Account. I was able to find similar examples from other power platform developers, however, they lacked a critical part that I needed: my set of files had to be dynamic: 2 files, or 102 files – the Logic App should be able to support this.
So, here, I would like to share my brief journey in creating such Azure Logic App:
Read on to see how Rayis solved this.
Comments closed