A Fine Slice Of SQL Server
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 CommentWe 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 closedAjay 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 closedCan I still take storage-array snapshots and if yes, will I lose data in my memory-optimized tables? What about data inside my non-durable tables?
Thankfully, the question was not in the headline. Therefore, Betteridge’s Law of Headlines does not apply and the answer may be either ‘yes’ or ‘no’ depending on the facts. Speaking of which, to find that answer, click through and read Andy’s post.
Comments closedI continue a series on data virtualization in SQL Server 2022:
Today’s post is a fairly short one. Let’s say you want to connect to Azure Data Lake Storage Gen2 from SQL Server 2022. We saw in a recent post how to connect to Azure Blob Storage. When it comes to Azure Data Lake Storage, the story is almost the same, though there are a couple pitfalls you will want to avoid.
Read on for that error, which stymied me for a good 10 minutes.
Comments closedI continue a series on data virtualization in SQL Server 2022:
Several years ago, I wrote a blog post on how to insert data into Azure Blob Storage from SQL Server using PolyBase. That technique used PolyBase V1: the Java connector for Hadoop. With SQL Server 2022 eliminating that connector, we’re going to learn the new method.
This is one of the larger practical differences in data virtualization with SQL Server 2022.
Comments closedI take a look back at the past and forward to the future:
PolyBase used to connect to Azure Blob Storage. Specifically, you could use the
wasbs://protocol and connect to Azure Blob Storage over WebHDFS. Here’s an example of an external data source which would work for SQL Server 2016 through 2019:
SQL Server 2022 changes its mechanisms around Azure Blob Storage a little bit, though I think the changes are sensible.
Comments closedKamil Nowinski runs into some trouble:
Last weekend, I played a bit with Azure Synapse from a way of mounting Azure Data Lake Storage (ADLS) Gen2 in Synapse notebook within API in the Microsoft Spark Utilities (MSSparkUtils) package. I wanted to just do a simple test, hence I followed the documentation from Microsoft: How to use file mount/unmount API in Synapse.
Having an ADLS Account already created in a subscription – should be easy peasy, right?
Read on to understand when things might be a little more complicated than they seem. And more frustrating, once you see the cause of the problem.
Comments closedJosh Darnell provides timeless advice:
As Deborah’s invite post suggests, this is a “that one time at that client” story. I was working at a consulting firm, and we had written an app for a particular client. Part of this application’s workflow involved users uploading images alongside some other information. These were not particularly large images in the grand scheme of things – they were taken by a microscope, and were a few kilobytes each, maybe.
However, this app had been in use for a long time. And as you might have guessed from the title of this post, each of these images was stored in a single table in the database that backed this application
Yeah, that’ll be a problem… Read on for some recommendations on how to avoid the issue. One thing I would add is FileTable, which came out in SQL Server 2012. In that case, the files are actually stored on disk but are queryable via T-SQL. It introduces its own set of problems but I do have some fond feelings about having used FileTable in the past.
Comments closedStorage Accounts are pretty much integrated into so many different designs in Azure, whether you are using Azure Synapse, 3rd party product like Snowflake, or Event Streaming designs – we need it.
When you create a storage account there are 5 different replication types you should know about. These are LRS, ZRS, GRS, RA-GRS and GZRS. Lots of abbreviations here, lets explain further.
Read on for the explanation.
Comments closed