A Fine Slice Of SQL Server
Thomas Rushton doesn’t have time to sit around and wait:
You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.
Read on for a script which waits for one SQL Agent job to complete before moving on and doing something else.
Comments closedDan Jackson explains the concept of latching in SQL Server:
To start with, a basic definition: ‘Latches are lightweight synchronization objects, that are used by the storage engine of SQL Server to protect the internal memory structures’. Compare this with locks in SQL server, which are a transaction level construct to manage concurrency, latches work at the thread level to maintain data integrity within the internal memory structures. They are not exposed outside of the SQL Server Operating System (SQLOS). They are only managed by SQL Server itself, not by users (unlike locks that can be overridden via lock hints or changing isolation level). It is useful to keep in mind that a single transaction can use multiple threads at the same time.
Latching is a funny concept to me, in that I think people say “This must be a latching problem” far more than there actually is, but when there is a proper latching problem, it usually winds up being a pretty big deal.
Comments closedErik Darling explains some of the shortcomings of the missing indexes DMV:
The problem with relying on any data point is that when it’s not there, it can look like there’s nothing to see.
Missing indexes requests are one of those data points. Even though there are many reasons why they might not be there, sometimes it’s not terribly clear why one might not surface.
That can be annoying if you’re trying to do a general round of tuning on a server, because you can miss some easy opportunities to make improvements.
Read on for a few examples of where the results can betray you.
Comments closedDaniel Hutmacher has a fun assignment:
Virtual machines cost money when they’re powered on. Most servers obviously need to be on 24 hours a day. Others, like development machines, only have to be on when you’re using them. And if you forget to turn them off, they’ll empty out your Azure credits (or your credit card) before you know it.
Today, I’ll show you how to set an Auto-shutdown time to turn a VM off if you forget, as well as have Azure notify you on Slack 30 minutes ahead of time, so you have the option to postpone or cancel the shutdown.
There are a few steps to the process, but everything is straightforward.
Comments closedJames Serra covers business continuity scenarios with Azure SQL Database:
I have wrote a number of blogs on the topic of business continuity in SQL Database before (HA/DR for Azure SQL Database, Azure SQL Database high availability, Azure SQL Database disaster recovery) but with a number of new features I felt it was time for a new blog on the subject, focusing on disaster recovery and not high availability.
Business continuity in Azure SQL Database and SQL Managed Instance refers to the mechanisms, policies, and procedures that enable your business to continue operating in the face of disruption, particularly to its computing infrastructure. In the most of the cases, SQL Database and SQL Managed Instance will handle the disruptive events that might happen in the cloud environment and keep your applications and business processes running.
James takes us through options available for Azure SQL Database as well as managed instances.
Comments closedTaryn Pratt gives us closure on an issue from a few months back:
I suggest reading my other post first, it’ll only take a few minutes. I’ll wait…
However, if you really don’t want to read it, here’s a quick recap on the initial issue.
In early February 2020, a lot of data was deleted from some clustered columnstore indexes in our PRIZM database. Some of the tables were rebuilt, but 11 tables weren’t since we don’t have maintenance windows, and that would involve downtime. The rebuilds would happen once we upgraded to SQL Server 2019, to take advantage of the ability to rebuild those columnstore indexes online.
Taryn now has the full story and I recommend giving it a read.
Comments closedMichael J. Swart has a script to monitor identity columns:
My friend pointed out an interesting RCA by Github where a
database table’s auto-incrementing ID column exceeded [maxint]. When we attempted to insert larger integers into the column, the database rejected the value
This led to a discussion about setting up monitoring for this kind of problem in our software. We have a place for monitoring and health-checks for all our databases. We just need to know how to define them.
So how do I create a script that reports any tables whose current identity values are above a certain threshold? This is what I came up with. Maybe you’ll find it useful too.
Click through for the script, but also read the comments for a simplification. The next step in the process is to figure out which tables can handle it and auto-reseed when you get to a certain threshold. Most commonly, this would be queue tables, where the active set of IDs in use is a relatively small number and there are no ID stragglers hanging around, so resetting is safe.
Comments closedArun Sirpal asks and answers a question:
Question, can you take a built-in backup ( a backup that Microsoft takes as part of the service) and create a new database from it on a NEW server in a different region?
Let’s find out.
Yes, let us.
Comments closedDave Bland shares the results of a lot of effort:
When you click the SQL Server 2012 or newer you will be taken to a report that will return a great deal of information that will be useful when doing an assessment. This is very similar to the report you will be taken to if you click the 2008r2 or older button. Since many of these data points are not options in an Azure SQL DB, that button will take you to the Report Library.
Below are the 21 data points that will be returned in just a few seconds. A number of these will be helpful when doing an assessment for performance reasons. The boxes will also change color to yellow or red if issues are found, just like the image above.
Click through for samples as well as the download link.
Comments closedBy default, SQL Server will grant 20% of available memory to any R or Python scripts running. The purpose of this limit is to prevent you from hurting server performance with expensive external scripts (like, say, training large neural networks on a SQL Server).
Here’s the kicker: this affects you even if you don’t have Resource Governor enabled. If you see out-of-memory exceptions in Python or error messages about memory allocation in R, I’d recommend bumping this max memory percent up above 20, and I have scripts to help you with the job. Of course, making this change assumes that your server isn’t stressed to the breaking point; if it is, you might simply want to offload that work somewhere else.
Click through for the other issue.
Comments closed