A Fine Slice Of SQL Server
Joe Obbish busts out the slide rule:
Lately I’ve been doing some exploratory performance testing on Azure SQL Managed Instances in preparation for a migration to that platform. This blog post documents some storage testing results and may even have practical advice near the end. All testing was done on a gen5 general purpose instance with 8 vCores.
Read on for Joe’s findings. Spoiler alert: there is practical advice at the end.
Comments closedAlex Woodie discusses a new storage solution:
In the hunt for the optimal long-term storage, media comes and media goes. From cave paintings and daguerreotypes to tape and optical disk, humans continue to search for new ways to preserve information for use by subsequent generations. Now an Upstate New York company is touting a new type of archive that leverages an element with the ultimate in longevity: gold.
Totenpass today announced the launch of its novel storage solution that’s made of gold. Data is written directly to the surface of the gold cartridge (which features some nickel) using a laser etching process. The data takes the form of shrunken down human-readable images or documents, or machine-readable data that can be encrypted and read with a smartphone app (which is still in development).
This makes perfect sense to me. If you write it in metal, that means Ruin can’t read or modify it.
Comments closedAnthony Nocentino has some practices for us, the best practices:
The intent of this post is a quick reference guide based on the recommendation made on Pure Storage Support page in the Microsoft Platform Guide . The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server on physical machines on Pure Storage.
Click through for a checklist of recommendations.
Comments closedPhil 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 closed