A Fine Slice Of SQL Server
With SQL Server 2012 Microsoft introduced the AlwaysOn Availability Group feature, and since then many changes and improvements have been made. This article is an update to our previous article https://www.sqlrx.com/steps-for-installing-sql-server-alwayson-availability-groups/ , and will cover the prerequisites and steps for installing AlwaysOn in your SQL Server 2019 environment.
Click through for a checklist of pre-requisites and installation + configuration steps.
Comments closedMichael J. Swart walks us through spinlock contention:
When that occurred, the number of batch requests that the server could handle dropped significantly. So we saw more CPU use, but less work was getting done.
The high demand kept the CPU at 100% with no relief until the demand decreased. When that happened, the database seemed to recover. Throughput was restored and the database’s metrics became healthy again. During this trouble we looked at everything including the number of spins reported in the sys.dm_os_spinlock_stats dmv.
The spins and backoffs reported seemed extremely high, especially for the category “XVB_LIST”, but we didn’t really have a baseline to tell whether those numbers were problematic. Even after capturing the numbers and visualizing them we saw larger than linear increases as demand increased, but were those increases excessive?
Read on for the answer.
Comments closedGilbert Quevauvilliers shows us how to remove a Power BI On-Premises Gateway from a cluster:
In the past when I had to remove a Power BI Data Gateway from a cluster, I had to use PowerShell with a couple steps which was rather complex.
I recently found out that this can now be easily achieved using the Power BI Admin Centre in the steps below.
Click through for the process.
Comments closedKenneth Fisher has evil afoot:
Unfortunately, if there is a problem with the server / server name the sp_addlinkedserver doesn’t seem to throw an error and sp_addlinkedsrvlogin throws an error that breaks me out of the try block. So after my Google-fu failed me I did what I frequently do and asked on #sqlhelp. And I was given an answer that led me to the solution.
Click through for the solution. But not for the answer as to why he’s growing batches of monsters linked servers.
In this blog post, I discussed some facts about Azure SQL Database Serverless. If someone wants to avoid those limitations and disable ‘Auto-pause,’ how costly is it?
Click through for a sample calculation to understand the trade-off between faster performance and lower bills.
Comments closedGarry Bargsley shows us three methods for creating schema-only clones of databases:
Have you ever been asked to make a schema only copy of a database? What is your preferred method to complete this request? Below we will discuss how to create a schema only database copy.
Here are three approaches that are easy to understand and perform. One approach will use PowerShell and the dbatools module. The second method will use built-in SQL Server functionality, depending on your SQL Server version. The third way will use a third-party (paid) tool from Red-Gate software.
Click through for the three options.
Comments closedDavid Klee continues a video series:
If you followed the last video, I showed you how to set up windows perfmon for ongoing performance metric collection on all of your critical SQL Server machines. However, having this raw data in a portable format doesn’t mean you have an easy means to access that data. In this training video, I show you a PowerShell script that we released that helps you export this data into a SQL Server database table so that you have access to the raw data to perform ongoing performance analysis with whatever favorite tool you prefer.
David walks through the process in a video, so check it out.
Comments closedTaiob Ali gives us a briefing summary on Azure SQL Database Serverless:
Occasionally, load balancing automatically occurs if the machine cannot satisfy resource demand within a few minutes. For example, if the resource demand is 4 vCores, but only 2 vCores are available, it may take up to a few minutes to load balance before 4 vCores are provided. The database remains online during load balancing except for a brief period at the end of the operation when connections are dropped.
Click through for more points along these lines.
Comments closedPaul Randal takes us through a change to SQL Server 2019:
After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.
Read on to see the answer.
Comments closedMichael J. Swart applies Swart’s 10% Rule to maximum simultaneous user connections:
The maximum number of user connections that SQL Server can support is 32,767. That’s it. That’s the end of the line. You can buy faster I.O. or a server with more CPUs but you can’t buy more connections.
I actually mentioned this limit in the post where I introduced Swart’s 10% rule: “If you’re using over 10% of what SQL Server restricts you to, you’re doing it wrong” In that post, I was guarded about that statement as it applied to the user connection limit. But I’d like to upgrade that to elevated.
This is Threat Level Vermillion, people!
Comments closed