A Fine Slice Of SQL Server
Jason Russell takes us through a SQL Server 2019 feature:
Included in SQL Server 2019 (and also the upcoming SQL Server 2022), Accelerated Database Recovery (ADR) improves database availability. Microsoft outlines the main benefits of ADR as:
– Fast and consistent database recovery
– Instantaneous transaction rollback
– Aggressive log truncation
Click through for an overview of how it works.
Comments closedLukas Vileikis begins a series on MySQL backups with the classic option:
mysqldumpis one of the most popular database backup tools in the MySQL world.The tool is prevalent partly because it’s very basic and quite powerful –
mysqldumpdatabase backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.
Click through to see how it works.
Comments closedAjay Dwivedi continues a series on high CPU utilization:
In the last blog post Live Troubleshooting High CPU On SQL Server – Part 1, we worked on a scenario where we saw a high CPU on SQL Server due to some external OS level task. In this blog, we are going to explore a scenario where a high CPU issue is present because of the workload running on SQL Server.
Just like in the last blog post scenario, when I have to troubleshoot a “slow” SQL Server, if my SQL Server is baselined with the SQLMonitor tool, then I first visit my Monitoring – Live – All Servers dashboard which displays all the metrics of specific SQL Servers that need DBA help.
As normal, we see a scenario with SQLMonitor, as well as other options including sp_BlitzFirst.
Comments closedEtienne Lopes wants to see what the hold-up is:
Blocked processes (and deadlocks) are often one of the main factors responsible for performance issues in the databases, as such, it’s really important to monitor them effectively and if they exist then understand where, what, why, how often, duration, etc. Having that information will greatly help in the following fine tuning process.
In this post I’ll show one way to easily monitor blocked processes in an Azure SQL database.
Click through to learn how.
Comments closed– Enable AAD auth on Azure SQL server
– Conn to Azure SQL database via AAD admin
– Create contained user for the managed identity (using Azure VM name as contained username)
create user <vmname> from external provider;
alter role db_owner add member <vmname>;
Read on for the full set of steps.
Comments closedAjay Dwivedi finds out it wasn’t the database:
Since SQL Server is not a cheap application and would cost a lot of money if we need to scale it horizontally. So it is not a good practice to allow any other resource-consuming processes to run alongside with SQL Server.
In this blog, we are going to troubleshoot a high CPU scenario when the CPU issue is caused by non-SQL Server process. Let’s say, I receive a call from the monitoring team where the application team is complaining about a “slow” SQL Server.
Ajay first uses SQLMonitor to perform this troubleshooting and then shows how to do this without SQLMonitor.
Comments closedAjay Dwivedi tracks down memory grants:
Very often, specially on mixed workload enviroment where server deals with oltp & olap queries together, concurrent sessions allocated with High Memory Grants cause entire SQL Server workload to suffer.
To detect such a situation, I like to watch my critical metrics dashboards. For example, dashboard Monitoring – Live -All Servers of free SQLMonitor tool highlights all these metrics.
Click through for an example of the dashboard as well as a diagnostic query which helps find high-memory grant queries.
Comments closedGrant Fritchey wants to see if you’re bulking up:
Wouldn’t it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don’t know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event.
Click through for an overview of what this event provides, as well as what it doesn’t provide.
Comments closedI had an inquiry about dbcc dumptrigger today and realized that state filtering was added to dbcc dumptrigger but not well known.
You can collect a process dump (SQL Dumper) when a specific error occurs using XEvent (error_reported/ex_raise2 events with dump capture action) or dbcc dumptrigger.
The common use of dbcc dumptrigger is: dbcc dumptrigger(‘set’, 208) to produce a dump when error 208 is encountered.
This is pretty useful, especially if you’re troubleshooting a bug in the database engine.
Comments closedGarry Bargsley rolls the dice:
In my experience, two options exist to get the desired result. One, create a new server, install the latest supported version of SQL Server, and migrate your data. Two, upgrade SQL Server on the existing server.
There are pros and cons to each of these options. My preference is to go with option number one as it allows you more flexibility in your migration plan. However, many smaller shops might not have the hardware resources for this option, so they are forced to option number two.
In-place upgrades have improved considerably, though certain ancillary services (like Machine Learning Services) have breaking changes between versions, so you may be forced into the first route regardless.
Comments closed