Press "Enter" to skip to content

Category: Administration

Monitoring Blocked Processes in Azure SQL DB

Published 2022-10-05 by Kevin Feasel

Etienne 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

sqlpackage and Managed Identities

Published 2022-10-04 by Kevin Feasel

Nora Yang provides a guide:

– 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 closed

Troubleshooting High Non-SQL CPU Utilization

Published 2022-10-03 by Kevin Feasel

Ajay 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 closed

Tracking Bulk Insert Usage with Extended Events

Published 2022-09-30 by Kevin Feasel

Grant 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 closed

High Memory Grants in SQL Server

Published 2022-09-30 by Kevin Feasel

Ajay 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 closed

Triggering Dumps for Specific Errors and States

Published 2022-09-29 by Kevin Feasel

Bob Dorr has a plan:

I 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 closed

In-Place SQL Server Upgrades

Published 2022-09-28 by Kevin Feasel

Garry 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

Using the DAC with Dockerized SQL Server

Published 2022-09-27 by Kevin Feasel

Joey D’Antoni needed to use the Dedicated Administrator Connection:

Because as shown in the image above, the table in question is a system_table, in order to query it directly, you need to use the dedicated administrator connection (DAC) in SQL Server. The DAC is a piece of SQL Server that dedicates a CPU scheduler, and some memory for a single admin session. This isn’t designed for ordinary use–you should only use it when your server is hosed, and you are trying to kill a process, or when you need to query a system table to answer a twitter post. The DAC is on by default, with a caveat–it can only be accessed locally on the server by default. This would be connected to a server console or RDP session on Windows, or in the case of a container, by shelling into the container itself. However, Microsoft gives you the ability to turn it on for remote access (and you should, DCAC recommends this as a best practice), by using the following T-SQL.

Read on to see how, as well as what else you’d need to do to get it working.

Comments closed