A Fine Slice Of SQL Server
Taiob Ali takes us through a new DMV:
There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.
If you run the example query as-is from the above documentation some of the columns do not make sense.
Taiob includes a better query which provides the type of information you’re used to in on-premises SQL Server.
Comments closedDavid Fowler looks for the biggest, meanest database on a SQL Server instance:
I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.
In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.
Read on for one way to do this, assuming that the instance has been up long enough to give you reliable results.
Comments closedEitan Blumin automates creation and deletion of partitions in SQL Server:
Before we begin, there are a few “ground rules” we should understand first:
1 – Partition Functions define the partition ranges
This means that whenever we want to eliminate an old partition range or add a new partition range, the PARTITION FUNCTION is the object that we actually need to modify.
Click through for Eitan’s entire process and a couple of scripts. This is an area that SQL Server could have made a lot easier, especially for periodic processes, by including options like “Daily” or “Monthly” or “Weekly(start on Monday)” for intervals rather than making people specify every partition separately.
Comments closedThe two main requirements around high availability are commonly known as RTO and RPO.
RTO – stands for Recovery Time Objective and is the maximum allowable downtime when a failure occurs. In other words, how much time it takes for your databases to be up and running.
RPO – stands for Recovery Point Objective and is the maximum allowable data-loss when a failure occurs. Of course, the ideal scenario is not to lose any data, but a more realistic (and also ideal) scenario is to not lose any committed data, also known as Zero Committed Data Loss.
With those definitions out of the way, read on to learn more.
Comments closedArun Sirpal feeds the mogwai after midnight:
I mentioned before that you could use the idea of data persistency to rebuild your data from total failure. There are two types. RDB and AOF.
RDB – persists a snapshot of your cache in a binary format. The snapshot is saved in an Azure Storage account. AOF – saves every write operation to a log. The log is saved at least once per second into an Azure Storage account.
I’m a big proponent of using Redis as a caching service. I’m not a big proponent of using Redis as a persisted database, mostly because I’ve had a lot of bad experiences with persistent Redis…
Comments closedArun Sirpal shows off how to scale an Azure Redis Cache instance:
Let’s assume that you started off with the lower editions and capacity with Redis and your developers tell you to scale the Redis cluster. Well first to use a cluster you need premium
Click through for a walkthrough of how you can scale out Azure Redis.
Comments closedTom Collins has some guidance for us:
-The Hashicorp database secrets engine (database secrets engine) supports a plugin for SQL Server
-It supports root credential rotation, dynamic roles , static roles , username customization
-Vault needs to be configured a user that at minimum :
Click through for those configuration notes.
Comments closedRoel Hogervorst diagnoses trouble:
When you are in panic mode you focus on what is right in front of you and make suboptimal decisions. Here is some I have made.
Read on for a couple stories as well as a practical implementation of debugging as an OODA loop. Something that Sean McCown mentioned before has always stuck with me: it’s amazing just how few people know how to troubleshoot issues. Our inclination seems to be one of two things: adduce a conclusion from the first piece of evidence (usually just a flimsy error message) or immediately give up.
Comments closedDavid Klee answers a question for the ages:
Have you ever wondered why Windows Server doesn’t show disk performance metrics in Task Manager, but your Windows 10/11 OS does? It’s a really silly difference.
I have indeed wondered this and David has a good solution to the problem.
Comments closedChad Callihan looks at buffer pool extension:
Perhaps you started out with X amount of memory when your SQL server was brought online and over time, with additional load and activity on that SQL server, users are not quite getting the type of performance they’re used to getting. Sure, you can buy more memory. What if that’s not an option?
If you’re running low on memory and need a little boost, enabling buffer pool extension can take advantage of an SSD as an “extension” for the buffer pool.
This is one of those interesting features that probably help a small number of customers but shouldn’t be generally useful. That’s because even with SSD performance improvements, memory is still a couple orders of magnitude faster, so as long as you have the ability to increase RAM, that brings much better performance.
Comments closed