So, probably instead of establishing a rigid step-by-step process, putting together a repeatable methodology can lead you to a resolution. This will eliminate blind guesses and at least bring you to an educated guess if the issue is too complicated.

A common workflow for troubleshooting performance issues on SQL Server may involve the following. But again, these are not necessarily phases or stages that you have to follow in a specific order. Real-world troubleshooting rarely happens in a perfect sequence. You may jump between these areas, revisit previous findings, or investigate multiple things simultaneously depending on the complexity of the issue.

1. Define the scope
2. Determine what changed
3. Measure server health
4. Identify bottlenecks
5. Investigate the root cause
6. Validate the fix / Monitor after implementation
7. Document the incident

Scope it first

Every time I get a call about slow performance, the first thing I want to do is qualify the question itself. What does slow mean? Is it a widget that used to run for a few seconds and is now running for five minutes? By the way, this is one of the most common “slow” incidents I’ve seen in my last job at an ISV.

Scoping and discovery are probably the most critical parts of any troubleshooting task. This is the part where you can grasp the scope and nature of the issue at hand. Here, you will probably be able to establish your footing on how to approach the issue. Of course, a batch job running in SSIS or SQL Server Agent will require a different approach than, say, a process triggered by a user in the application’s frontend.

And, again, as I’ve said in my other post, I think it’s important to know when this issue started occurring. That alone might reveal a few important details about the very nature of the issue. Knowing whether you’re dealing with a single bad query or a server-wide issue will prevent you from investigating the wrong layer of the stack. For all you know, it’s not a performance issue at all, but a slowdown caused by the user’s ISP while working from home. I’ve seen this many times before.

My typical goal at the onset is to reproduce or replicate the issue in a test environment. That can give you the most bang for your buck because it allows you to investigate the issue and test potential solutions in a controlled environment.

The one thing we’re trying to prevent in the scoping is getting down the proverbial rabbit hole or chasing that red herring. Yes, I still love using those idioms in real life.

What changed?

Actually, this should be part of scoping, but I think it merits a whole section of its own. Knowing what changed could hold critical information as to the reason behind the issue. Maybe somebody just tweaked some lever on the application side, and that kind of caused a domino effect of some sort.

The main goal here is to find all the possible root causes and not point fingers at whoever might have been involved. There are a ton of reasons why a SQL Server might suddenly run slow. Configuration changes, new procedures, introduction of new indexes, unanticipated workload, network I/O, and even user workflows can cause slowdowns given the right circumstances where SQL Server can really slow down.

I’d start with the most recent change. And, of course, the typical answer to “What changed?” is almost always “nothing.” So, it’s up to you to determine how you surface what actually changed, if anything.

Know your baseline

If nothing has changed at all, then the next step is to determine any other activities that could be affecting the particular issue that you are investigating. It’s possible that the slowdown is caused by another task that is not necessarily related to the slowdown issue but is somehow affecting, for example, server resources.

I’ve seen cases where a third party started to send an amount of data that was not typical for an intra-day workload, which caused a domino effect on normal business operations. Not knowing what a “typical intra-day workload” looks like can complicate your investigation because you wouldn’t know if things are a factor in the issue or not.

For example, high CPU isn’t automatically a problem if the server normally runs at 90% CPU during month-end processing. While a query that normally consumes an infinitesimal amount of CPU and is suddenly taking 30% of the total CPU is almost certainly an issue. Always compare current metrics against a known baseline before concluding that something is abnormal.

Paul can tell you where it hurts

If you haven’t identified a possible root cause or figured out a narrative that may possibly connect the dots at this point, then it’s time to dig deeper. Paul Randal’s wait stats query can surface some clues about the ongoing issue. If you are troubleshooting a live issue, you probably want to combine that with Paul’s waiting task query. Glenn Berry’s Diagnostic queries are a must-have tool for any SQL Server DBA. If you are like me, when I encounter slow issues that are happening live, my instinct is to run Adam Machanic’s sp_whoisactive procedure. For many others running Brent O’s sp_BlitzFirst procedure is their go-to.

Wait stats and waiting tasks can be a superpower when it comes to troubleshooting a performance issue. Here is an example of how I used these two in the past to troubleshoot a live performance issue.

The wait type typically tells you where to go next. For example, blocking and lock waits send you to the head blocker (sp_WhoIsActive does a great job at identifying this). PAGEIOLATCH sends you toward storage I/O issues or queries performing large reads, possibly due to missing indexes. RESOURCE_SEMAPHORE points you toward memory grant pressure, while SOS_SCHEDULER_YIELD points toward CPU pressure. CXCONSUMER can be a benign wait type, but you should still investigate whether it is contributing to a bottleneck in a highly concurrent system.

Wait stats can help you identify the type of bottleneck SQL Server is experiencing, whether it’s related to I/O, CPU pressure, memory pressure, locking, or other resource contention.

Resource utilization

Ok, just like with wait stats, if you need to dig deeper, you should look into system resources as well. There is only so much you can achieve by optimizing indexes and queries. At some point, you need to ask whether the server has enough resources to handle the workload it is being asked to process. A perfectly tuned query can still struggle if the underlying infrastructure is already under pressure.

Here’s what I had to say about Disk IO on a LinkedIn post:

The key here is understanding whether you are dealing with a query problem, a workload problem, or a capacity problem. You can only go so far with index tuning and query optimization if the real issue is resource pressure underneath.

At some point, you have to understand what SQL Server is waiting on and whether the current infrastructure can support the workload. This is where troubleshooting becomes less about applying fixes and more about understanding the behavior of the system as a whole.

Bottleneck at the query level

We’re at the point where we are ruling out server-wide issues and closing in on specific queries that may be contributing to the problem. This is where tools like Query Store become extremely valuable because they allow you to look back at query behavior over time instead of relying only on what is happening at the exact moment of the incident.

One thing that I have been trying to learn lately is Query Store. At the time of writing, I am taking the SQLskills course on Query Store, IEQS. The more I learn about it, the more I realize how useful it can be when troubleshooting query performance issues, especially when dealing with plan regressions or queries that suddenly start behaving differently.

Of course, actual execution plans can give you a lot of information about where the bottleneck is occurring at the query level. Is it a cardinality estimation issue, memory grant pressure, spills, missing indexes, outdated statistics, or even parameter sniffing? The information you can glean from execution plans or Query Store can help you understand why a query is behaving the way it is and guide you toward the right tuning approach.

Is it blocking

If it’s a blocking chain, the first thing you want to do is find the head blocker and understand what it is doing before you start killing sessions. A blocked session is usually just a victim waiting for something else to finish. The real question is: what is holding the lock, and why is it taking so long?

This is where experience and judgment come into play. Just because you found a blocking session does not mean the immediate answer is to kill it. You need to understand what the session is doing, how long it has been running, and what impact terminating it will have. One interesting case I encountered in the past is a Service Broker queue that got stuck in limbo.

Communicate to stakeholders

During an incident, people understand that troubleshooting takes time. What they don’t want is uncertainty and silence while the issue is impacting the business. Keeping stakeholders informed shows that you are engaged, even when the root cause has not been identified yet.

In my experience, you don’t always have to provide a breakthrough every time you communicate. Sometimes a simple update like “we are seeing high I/O waits, we are checking storage latency, and we are looking at the queries involved” is enough to keep everyone aligned. The technical investigation may still be in progress, but at least everyone understands where things stand.

Validate the fix

Once the fix has been implemented, the work is not done yet. This is where you go back and validate if the change actually fixed the problem. Did the waits decrease? Did CPU return to its normal behavior? Did response times improve? Did the blocking clear? And probably the most important question: did the users notice the improvement?

One thing I always try to avoid is assuming that the issue is fixed just because the immediate symptom disappeared. A SQL Server environment is a complex system, and sometimes fixing one problem can expose another one. Keep monitoring after the change and make sure that things continue to behave as expected.

I’ve seen cases where a change looked successful during the initial troubleshooting session, only to find out later that the original problem came back or another issue surfaced. This is why validation and monitoring after the change are just as important as finding the fix in the first place.

Document the incident

Document what happened, what you checked, and what fixed the issue. Include the root cause, symptoms, timeline, diagnostics, fix, and any lessons learned. The next time a similar incident happens, this documentation can save you from starting the investigation from scratch.

Effective SQL Server troubleshooting is less about having the perfect fix ready and more about knowing how to narrow down the possibilities. You start with what you know, gather evidence, validate your assumptions, and slowly eliminate what doesn’t fit.

In my experience, the DBAs who troubleshoot well are not the ones who have memorized every possible solution. They are the ones who know how to ask the right questions, understand what the system is telling them, and make changes based on evidence. Having a repeatable methodology will take you much further than a collection of random tuning techniques.

Well, take it with a grain of salt. It depends. It always is.

The post How I Troubleshoot a Slow SQL Server Live first appeared on SQL, Code, Coffee, Etc..

Provision an Azure SQL Database

Go to you Azure Portal and search for Azure SQL Database

On the upper left-hand of the UI, click on Create and select SQL database (Free offer).

Configuring an Azure SQL Database is pretty much intutive. For the Server option, use an existing SQL Database Server or create a new one.

Click Review + create to finish the setup.

Connect from VS Code on a MacBook

Go to your Resource Group and find your Azure SQL Database. Or, you can simply search for Azure SQL Database in the search bar again and that will take you to your databases.

Copy the Server name.

Now, open your VS Code (install the mssql extension if you haven’t already). Why VS Code? That’s because Mirosoft will never port SSMS to macOS. That’s why.

Create new connection. Look for the plug icon with the ‘+’ sign next to it.

For the Input type, Browse Azure wouldn’t work for me even if I already took care of the networking setting. Let me know in the comment if you made it work. Using Parameters worked for me.

Paste the Server name. Don’t forget to tick the Trust server certificate. Use SQL login and input the sa user and password that you set when you provisioned your SQL Server.

That should be it. Your Azure SQL Database is now ready to use.

The post How to Provision an Azure SQL Database first appeared on SQL, Code, Coffee, Etc..

The problem: proving a migration works shouldn’t require the cloud

If you run Azure SQL Database, you want to know a schema migration will succeed before you apply it in production. But the honest way to be sure has always meant testing against a live Azure SQL Database — which means a subscription, cloud spend, and slow round-trips every time you tweak the migration.

So validation gets rushed, done once instead of continuously, or skipped until staging — and the surprises show up late, mid-release, when they’re most expensive.

What’s been missing is a way to validate a migration against the actual Azure SQL Database engine, locally, repeatably, and for free.

What it is

Azure SQL Developer is the Azure SQL Database engine packaged as a local container. It’s the real thing running on your laptop — what works there deploys seamlessly to Azure SQL Database. More info on SQL Database Developer can be found here. Youtube Demo provided by MSFT can be found here.

azsql-migration-test automates the validation loop against it:

1. Pull and start the Developer container.
2. Extract your source schema (with sqlpackage).
3. Deploy it into the container — the real compatibility test: if the engine won’t accept the schema, the deploy fails and so does validation.
4. Replay your queries against the deployed schema.
5. Tear everything down.

No cloud account. No cloud cost. Zero-to-answer in a couple of minutes — and because it’s the same engine you deploy to, a clean pass means a clean deploy.

The three commands

validate — the full pass

Extract the source schema, deploy it into the container (the compatibility test), then replay your queries against it:

azsql-migration-test validate \
--source "Server=;Database=;User Id=;Password=" \
--queries ./queries.sql

azsql-migration-test validate \
--source "Server=;Database=;User Id=;Password=" \
--queries ./queries.sql

✓ Schema deployed to Azure SQL Database Developer (compatible)
✓ Query replay complete: 12 passed, 0 failed
Migration validation completed successfully

If the schema is rejected or a query fails, it exits non-zero — so it drops straight into a script or a CI job.

compare — a non-destructive deployment dry-run

Report exactly what deploying your schema would do — every operation sqlpackage would run against the Azure SQL Database engine — without applying anything:

azsql-migration-test compare \
--source "Server=;Database=;User Id=;Password="

azsql-migration-test compare \
--source "Server=;Database=;User Id=;Password="

✓ Schema comparison complete

You get a report.md summary and the raw schema-diff.xml:

Schema
Changes: 4 (breaking: false)
DeployReport: schema-diff.xml

Against a fresh target this is the full create plan; against a database that already has a schema (e.g. from a prior validate --keep) it’s the incremental diff — creates, alters, and drops. It exits non-zero when the plan contains a breaking operation — a drop or a data-loss alert — so it makes a clean pre-merge gate.

It exits non-zero when the diff contains a breaking operation — a drop or a data-loss alert — so it makes a clean pre-merge gate.

replay — run a query set

Run a set of GO-separated batches against a database and get per-batch pass/fail and timing:

azsql-migration-test replay --queries ./smoke.sql --database MyDb

azsql-migration-test replay --queries ./smoke.sql --database MyDb

✓ Query replay complete: 8 passed, 0 failed

A failing batch exits non-zero and the SQL error is captured in the report:

[FAIL] SELECT * FROM dbo.Orders_old; (168ms)
Msg 208, Level 16, State 1 — Invalid object name 'dbo.Orders_old'.

Where it fits: use cases

• A pre-deploy gate on your laptop. Before you open the PR, confirm the migration will be accepted by the Azure SQL Database engine.
• A CI/CD check. Every command exits non-zero on failure, so you can wire it into a pipeline that blocks a merge or a deploy on a migration that won’t apply.
• Repeatable validation across a migration project. Instead of a one-time cloud assessment, re-run it on every change — locally, for free.
• Vetting third-party or generated migration scripts. Point it at a schema you didn’t write and see whether the engine accepts it before it reaches production.
• Fast iteration. Change the migration, re-validate in a couple of minutes, no cloud round-trip.

Honest about what it does

Good tooling tells you where it stops. The breaking-change flag is derived from the sqlpackage DeployReport (drops and data-loss alerts); the full XML report is always written, so you can inspect the deployment plan yourself. As with any pre-deploy check, keep your normal staged rollout — but you’ll catch the schema problems that otherwise surface at deploy time, right on your laptop.

Try it in two minutes

Download a binary from the latest release (https://github.com/MarlonRibunal/azsql-migration-test/releases) (each ships with checksums), or:

go install github.com/MarlonRibunal/azsql-migration-test@latest

go install github.com/MarlonRibunal/azsql-migration-test@latest

Point it at an Azure SQL Database Developer container image you have access to (and its registry credentials, if needed) via environment variables, then run validate. Full quickstart and flags are in the README (https://github.com/MarlonRibunal/azsql-migration-test#readme).

You’ll need Docker and sqlpackage on your PATH. (sqlcmd runs inside the container, so you don’t need it on the host.)

Contribute — it’s small on purpose

azsql-migration-test is a compact, dependency-free Go CLI. That’s deliberate: it’s easy to read, easy to build, and easy to contribute to. If you work with Azure SQL migrations, there’s low-hanging fruit here:

• Richer DeployReport parsing and smarter breaking-change classification.
• A first-class GitHub Action wrapper for the CI use case.
• More output formats (JSON, SARIF) for pipeline integration.
• Real-world schema fixtures and edge cases worth validating against.

Issues and PRs are welcome — see CONTRIBUTING (https://github.com/MarlonRibunal/azsql-migration-test/blob/main/CONTRIBUTING.md). If it saved you a bad deploy, a on the repo helps others find it.

→ https://github.com/MarlonRibunal/azsql-migration-test

azsql-migration-test is an independent, community project. It is not affiliated with, endorsed by, or sponsored by Microsoft. “Azure” and “Azure SQL Database” are trademarks of Microsoft Corporation.

The post Introducing azsql-migration-test: Test and Validate Azure SQL Database Migrations first appeared on SQL, Code, Coffee, Etc..

The idea behind Azure SQL Developer is for anyone to build, iterate, and test locally at no cost. This means NO Azure subscription to deal with and NO credit card required! Everything will work seamlessly when you deploy your application to an Azure SQL Database backend!

It’s exactly like the SQL Server Developer Edition that we all love. As of this writing, it’s in Private Preview. And I got the chance to get into the preview program. Find more information about it here. There’s a short intro on youtube here.

I’d like to build something on top of Azure SQL Developer. But for now, let’s set it up on my dev environment:

Laptop: Macbook Pro M5 Pro
Code Editor: Zed 1.11.3
Terminal: iTerm2
Container App: OrbStack (my preferred container app on mac)
Database: Local Azure SQL Developer container
AI Coding Agent: Claude Code Max 5x

By the way, same steps should apply to Windows.

Authenticate to the Preview Registry

Like I said, Azure SQL Developer is currently in Private Preview. I was given a user and password to authenticate to the registry to pull the latest image.

docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io -u private-preview-reader

docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io -u private-preview-reader

Input the password that was sent to you when you get admitted to the private preview. Hopefully, pulling the image would be much more convenient when this goes public.

You should get a Login Succeeded message.

Pull the Azure SQL Developer image and deploy container

Let’s pull the image and deploy the Azure SQL Developer container to OrbStack.

docker run --platform linux/amd64 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='YourStrongPassword' -p 1433:1433 --name azuresqldb --hostname azuresqldb -d sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest

docker run --platform linux/amd64 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='YourStrongPassword' -p 1433:1433 --name azuresqldb --hostname azuresqldb -d sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest

Normally, I would pull the image first then deploy. But you can also directly deploy and docker should be able to pull the image first (duh).

Test connection to our local Azure Database Developer

Let’s send a query to the database to make sure we can establish a connection to it.

docker exec azuresqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'YourStrongPassword' -C -Q 'SELECT @@VERSION;'

docker exec azuresqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'YourStrongPassword' -C -Q 'SELECT @@VERSION;'

And if you’ve followed the steps correctly, you should now be able to connect to the local version of Azure SQL Database.

As of this writing, as you can see in the screenshot below, the current version of Azure SQL Database Developer is 12.0.2000.8, July 12, 2026

Enjoy! I’ll build something on top of this. Come back for that later!

The post Getting Started with Azure SQL Developer on Macbook first appeared on SQL, Code, Coffee, Etc..

Download the Backup

For this exercise we want to download the full backup of Wide World Importers. Go ahead and download it from the github repo which you can find it here. And of course, take note of where you saved the .bak file.

Copy the Backup File into the Container

First, let’s create our backup directory in our cotainer. From your favorite terminal, run the following:

docker exec -it sql2025 mkdir -p /var/opt/mssql/backup

docker exec -it sql2025 mkdir -p /var/opt/mssql/backup

Then copy over the file to the directory we just created above

docker cp /Users/marlonribunal/Downloads/WideWorldImporters-Full.bak sql2025:/var/opt/mssql/backup/

docker cp /Users/marlonribunal/Downloads/WideWorldImporters-Full.bak sql2025:/var/opt/mssql/backup/

You terminal should now say that the backup file was successfully copied.

Use VS Code to Restore the Database

Since Microsoft will never ever port SSMS to the macOS, I think VS Code with the mssql extension is a pretty much decent tool for doing work in a SQL Server.

Check the in-memory data, database, and log files contained in the backup, so we know what logical files to map to their physical paths in the container:

RESTORE FILELISTONLY
FROM DISK = N'/var/opt/mssql/backup/WideWorldImporters-Full.bak';
GO

RESTORE FILELISTONLY
FROM DISK = N'/var/opt/mssql/backup/WideWorldImporters-Full.bak';
GO

You should see all the contained in the backup file of the WideWorldImporters database. Take note of those for the next step.

Then restore the database. Please take note that if you don’t map the path of the files to where you want to store them, the restore proccess will assume that you are restoring the files into the same directory the backup files were stored in the source. Chances are you will then get an error because those original directory don’t exist in the target container.

RESTORE DATABASE [WideWorldImporters]
FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Full.bak'
WITH 
MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf',
MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters_Log.ldf',
MOVE 'WWI_InMemory_Data_1' TO '/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',
REPLACE;
GO

RESTORE DATABASE [WideWorldImporters]
FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Full.bak'
WITH 
MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf',
MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters_Log.ldf',
MOVE 'WWI_InMemory_Data_1' TO '/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',
REPLACE;
GO

That took about 8 seconds to complete in my container:

That’s it! Enjoy querying!

The post Restore SQL Server Database to an OrbStack Container first appeared on SQL, Code, Coffee, Etc..