Press "Enter" to skip to content

Category: Storage

Immutable Blobs in Azure Storage

Published 2023-07-05 by Kevin Feasel

Khushbu Gandhi won’t change:

A lot of my customers have a business requirement that –

  • once a document is written in storage account, nobody shall be able to modify or delete it, including the administrators
    AND / OR
  • audit data can be written only once, and nobody shall be able to alter them.

These kind of business requirements are called WORM (Write Once, Read Many) and are common for various industries such as ISVs, financial or healthcare. Have you run into these mission-critical requirements? If yes, then immutable storage would be the solution.

Read on to see how this works, using retention policies. But ash Khushbu notes, if you set an absurd retention policy (up to 400 years), you can’t change the data while that policy is active.

Comments closed

Putting tempdb on an Azure VM Temp Disk

Published 2023-04-11 by Kevin Feasel

Daniel Hutmacher uses a temp disk for a temp database:

Almost all Azure virtual machine sizes come with a temporary disk. The temporary disk is a locally attached SSD drive that comes with a couple of desirable features if you’re installing a SQL Server on your VM:

  • Because it is locally attached, it has lower latency than regular disks.
  • IO and storage are not billed like regular storage.

As the name implies, the temporary disk is not persistent, meaning that it will be wiped if you shut down your VM or if the VM moves to another VM host (as part of maintenance or troubleshooting). For that reason, we never want to put anything on the temporary disk that we need to keep.

I’d say this was a lot more popular several years ago, back when spinning disk was the default for Azure storage. There can still be benefits from doing this, though if you’re using Premium storage with high IOPS, the biggest remaining benefit is around latency.

Comments closed

Troubleshooting Azure Synapse Link for SQL Server Issues

Published 2023-04-06 by Kevin Feasel

Kevin Chant diagnoses an issue:

In this post I want to cover common Azure Synapse Link for SQL storage permission issues. Since I helped a fellow MVP out with this recently.

To be more precise, I want to show how you can fix one of the most common issues I tend to encounter with Azure Synapse Link for SQL. Which is access to the Data Lake Storage Gen2 account.

I have encountered issues like this a few times now. For example, when I was performing my file tests for Azure Synapse Link for SQL Server 2022.

Click through to learn more about a couple of common issues, their causes, and resolutions.

Comments closed

Finding Columnstore Index Storage and Memory Allocations

Published 2023-03-31 by Kevin Feasel

Jose Manuel Jurado Diaz has a few scripts for us:

Today, we got a new question how much is the size used by a columnstore index table at storage level and memory usage.

TSQL to obtain the total number of rows, size per schema, table and index.

Using the view sys.column_store_row_groups (Transact-SQL) – SQL Server | Microsoft Learn we could see the total number of rows and space usage at storage level.

Click through for that script, as well as a few more to learn how much space and memory that columnstore index is taking.

Comments closed

Tagging on Pure Storage Volumes

Published 2023-01-27 by Kevin Feasel

Anthony Nocentino tags all the things:

A tag is a key/value pair that can be attached to an object in FlashArray, like a volume or a snapshot. Using tags enables you to attach additional metadata to objects for classification, sorting, and searching. For example, you can assign a tag to a collection of volumes and then come along later and retrieve a listing of volumes that match a particular key or value. You can use tags to add application context to resources inside FlashArray. Specifically, in the examples in this blog post, I want to tag volumes with the names SQL Server Instances.

Click through to see what you’d need to do, assuming you have an appropriate device running Purity 6.0 or later.

Comments closed

The Unimportance of IOPS

Published 2023-01-09 by Kevin Feasel

Kellyn Pot’vin-Gorman explains that there are things more important than IOPS when considering database choices:

IOPs is Overrated, yeah, I said it.

How many compute, storage area networks, hard drive vendors and storage services have posted their IOPs capabilities in marketing and didn’t include the throughput (MPBs)?  Why when someone sends me IOPs for an Oracle database do I thank them kindly and ask for throughput? 

Read on for more. This post is specific to Oracle but the general principle applies to SQL Server and all other relational databases as well.

1 Comment

Hyperconverged Storage and Trace Flags

Published 2022-11-28 by Kevin Feasel

David Klee has a tip for us:

We all (should) know that running SQL Server in hyperconverged virtual environments, both on-premises and in the cloud, has some interesting trade-offs. The biggest is write latency from the hyperconverged storage platform underneath the database. We find that write latency suffers compared to traditional all-flash storage, even if the hyperconverged layer is all-flash as well, due to how the hyperconverged layer handles the larger block write that the SQL Server engine drops on it.

Read on for a trace flag which could help here.

Comments closed

Troubleshooting I/O Issues in SQL Server

Published 2022-11-15 by Kevin Feasel

Ajay Dwivedi shares some advice:

Storage performance is something that puzzles a lot of SQL Server professionals. So in this blog, I will cover the basic steps I perform to ensure I get the best performance from the underlying storage.

Read on for some thoughts on storage testing prior to SQL Server installation, as well as what to do to ensure your SQL Server instance is up and at them.

Comments closed