A Fine Slice Of SQL Server
Koen Verbeeck sets up a great weekend project:
SQL Server 2022 has been released! In this blog post, I’ll walk you through how you can install the latest version of SQL Server on your machine. I’ll be using the developer edition of SQL Server 2022, which can be downloaded here.
Click through for guidance on setting up Developer edition.
Comments closedDavid Fowler troubleshoots an issue:
Checking the logs on the secondary, it was littered with ‘Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).’ messages. The primary server wasn’t able to authenticate with the secondary, but why? Everything looked ok as far as I could see.
Click through for the fruits of David’s labor.
Comments closedWilliam Assaf mentions a welcome change to SQL Server 2022:
Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization.
This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether.
The prior default of 10% autogrowth has led to so many problems over the years. I’d like new database files (MDF and NDF) to have a similar default as well.
Comments closedDenny Cherry shares a few thoughts on Page Life Expectancy:
One of the very misunderstood values that you can monitor in Microsoft SQL Server is Page Life Expectancy (PLE). I’ve read online that many people think that the value of Page Life Expectancy is worthless to monitor. And that really isn’t true. While Page Life Expectancy won’t tell you a specific problem by itself, it can be used to point you in the correct direction so that you know where to look.
There was a backlash against PLE, specifically the “PLE should be at least 300” concept, but Denny throws that away and digs into the actual benefit from observing this metric.
Comments closedAndrew Pruski picks up Chekov’s Gun:
Recently I had to create another pacemaker cluster, this time on-premises using VMWare virtual machines. The steps to create the pacemaker cluster and deploy an availability group where pretty much the same as in my original post (minus any Azure marlarkey) but one step was different, creating the STONITH resource.
A STONITH resource is needed in a pacemaker cluster as this is what prevents the dreaded split brain scenario…two nodes thinking that they’re the primary node. If the resource detects a failed node in the cluster it’ll restart that node, hopefully allowing it to come up in the correct state.
Read on to see how Andrew did it.
Comments closedIn this case, the SQL Server has multiple NUMA nodes, with greater than 16 logical processors per node – that’s the last line of the screenshot. In that line, Microsoft says MAXDOP should be half of the number of logical processors with a max of 16 – so 16.
But it’s recommending 8. Hmm.
Read on for the answer.
Comments closedKevin Hill has a video for us:
If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:
Fortunately, Kevin has anticipated me and has put together the “I don’t watch videos” version.
1 CommentKevin Hill has a video for us:
Query multiple SQL Server instances at one time!
I have used registered servers before, though my strong preference is for a Central Management Server. There are limits to how many servers CMS can handle before the initial load gets slow—I know this because we’re over the limit and it causes SSMS to freeze for 3-5 seconds the first time I open CMS. Still, having that server list in a central location means not having to share files around with the rest of the team and trying to figure out whether you have the most recent version of the file.
Comments closedPaloma Garcia Martin asks, Are we there yet? Are we there yet? Are we there yet?
When you start a PITR restore in your Managed Instance, is very useful be able to track how it progresses. Azure Portal does not show too many details about it, but we can use Managed Instance logs to track it.
The way to do it, is quite simple.
Click through for one way to track restore progress.
Comments closedSidney Cirqueira shows how to monitor SQL requests in Azure Synapse Analytics:
Today I would like to share a scenario that I was working on one of my serverless SQL Pool support cases. The customer asked for an advice on how to monitor serverless SQL requests by using log analytics.
The intention of this guide is to help you with choosing the configuration required to easily setup the Synapse Analytics Workspace monitoring and all other considerations about how to monitor serverless SQL requests with Azure Monitor. Spoiler: At the end of this article, I will share the latest version of the serverless workbook posted on the Azure_Synapse_Tool_Box. This includes a really cool way to see query execution information.
Read on for that and definitely check out the Azure Synapse Toolbox if you’re a Synapse user.
Comments closed