Finding What Is Blocking Your Query in SQL Server

Posted by Kyle Hankinson August 25, 2026

An UPDATE that normally takes milliseconds has been sitting there for two minutes. Or your application just threw "timeout expired" on a query you know is fine. Nine times out of ten nothing is slow at all: your session is waiting for a lock that another session holds and has not released.

That is blocking, and it is worth separating from its noisier cousin. A deadlock is two sessions each waiting on the other; SQL Server detects that cycle within seconds, kills one side, and raises error 1205. Blocking is one-directional and SQL Server will happily let it continue forever, because as far as the engine is concerned, everyone is just politely queueing. Nobody gets an error. Things simply stop.

Everything below is plain T-SQL against dynamic management views, so it works from any client on any platform, on SQL Server 2012 and later plus Azure SQL Database. You need the VIEW SERVER STATE permission. To verify the queries, I reproduced a real blocking chain on SQL Server 2022: one session opened a transaction, updated a row, and held the transaction open without committing, then two more sessions ran conflicting updates against the same row.

Step 1: is anything blocked right now?

One query answers it:

SELECT session_id, blocking_session_id, wait_type, wait_time, command
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Against my stuck sessions this returned:

Read it as: session 66 has been waiting 10 seconds (wait_time is milliseconds) for an exclusive lock, and session 65 is the one holding it. LCK_M_X is an exclusive lock wait; LCK_M_S (shared) and LCK_M_U (update) are the other common ones. Empty result set: no blocking at this instant, and your problem lies elsewhere.

Step 2: who is blocked, and what are they running?

Join to sys.dm_exec_requests' sibling views to turn session numbers into people and SQL:

SELECT r.session_id       AS blocked_session,
       r.blocking_session_id AS blocked_by,
       r.wait_type,
       r.wait_time / 1000 AS wait_seconds,
       s.login_name, s.host_name, s.program_name,
       t.text             AS blocked_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

In my repro this showed the waiting UPDATE statement along with the login and hostname it came from. Small heads-up: the SQL text often comes back in its parameterized form (UPDATE [dbo].[orders] SET [amount] = [amount]+@1 WHERE [id]=@2), which is the plan's view of the statement, not the literal text the user typed.

Step 3: find the head blocker (it is probably idle)

Here is the trap that makes people miss the culprit. The session at the head of the chain frequently is not running anything. It executed its UPDATE long ago inside an explicit transaction, never committed, and went idle. Since it has no active request, it does not appear in sys.dm_exec_requests at all. It only shows up as a number in other rows' blocking_session_id.

First, isolate the head: a session that blocks others but is not itself blocked.

SELECT DISTINCT blocking_session_id AS head_blocker
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
  AND blocking_session_id NOT IN (SELECT session_id
                                  FROM sys.dm_exec_requests
                                  WHERE blocking_session_id <> 0);

With three sessions queued (67 waiting on 66, 66 waiting on 65) this correctly returned just 65. Then pull what that session last executed, plus whether it is sitting on an open transaction:

SELECT s.session_id, s.status, s.login_name, s.host_name,
       tr.open_tran_count,
       t.text AS last_sql
FROM sys.dm_exec_sessions s
JOIN (SELECT session_id, COUNT(*) AS open_tran_count
      FROM sys.dm_tran_session_transactions
      GROUP BY session_id) tr ON tr.session_id = s.session_id
JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id
                       FROM sys.dm_exec_requests
                       WHERE blocking_session_id <> 0);

The most_recent_sql_handle trick is what makes idle blockers visible: it retrieves the last statement the connection sent even when nothing is currently running. In my case it returned the offending batch verbatim, BEGIN TRAN and the UPDATE holding the lock, with open_tran_count = 1 confirming the transaction was still open.

You may know sp_who2, which still works and shows blocking in its BlkBy column, but it cannot show you the SQL text, which is usually the piece you need. For ongoing monitoring rather than live firefighting, the community procedure sp_whoisactive and the blocked process report are the standard next steps; Microsoft's blocking troubleshooting guide covers both.

Deciding what to do about it

The diagnosis usually lands in one of a few buckets: an application that opened a transaction and forgot to close it (my repro, and the classic), a legitimately long-running write, a query doing a full scan under the covers and locking far more rows than it should, or lock escalation turning row locks into a table lock. If the blocker belongs to a colleague's forgotten session, a message to them beats any T-SQL.

When the session must die, KILL does it:

KILL 65;

Two cautions, both learned by watching it happen. First, KILL does not stop work, it undoes work: SQL Server rolls back the session's open transaction, and rolling back a large transaction can take as long as, or longer than, the work already done. Killing a session that has been updating rows for an hour buys you up to another hour of rollback, during which the locks are still held. You can watch the progress:

KILL 65 WITH STATUSONLY;

which in my test reported: SPID 65: transaction rollback in progress. Estimated rollback completion: 10%. Estimated time remaining: 0 seconds. Second, KILL is a hammer, not a fix. If the blocker was an application connection, the app will reconnect and do the same thing tomorrow; the durable fix is committing or shortening the transaction in the code. The KILL documentation lists further restrictions, including that it requires ALTER ANY CONNECTION and cannot target your own session or system sessions.

One related scenario deserves its own playbook: when blocking sessions are what stops you from dropping a database, see how to drop a database with active connections.

A workflow note

This investigation is naturally multi-window: the stuck query in one place, the DMV queries in another, so you can re-run the diagnostics and watch wait_time climb. Since everything here is an ordinary query, any client works, from sqlcmd (which I used for the repro above) to a GUI like SQLPro for MSSQL, whose multiple query tabs and side-by-side result sets fit the pattern of holding one session open while diagnosing from another. Keep the step 2 query saved somewhere close at hand. When you need it, something is on fire, and pasting beats remembering.

Tags: Microsoft SQL Server

SQL NULL traps: = NULL, NOT IN, and sort order

Posted by Kyle Hankinson August 7, 2026

A query that returns zero rows with zero errors is harder to debug than one that fails loudly, and NULL is behind more of those silent empties than everything else combined. The root cause is always the same: SQL comparisons involving NULL do not evaluate to true or false but to a third value, UNKNOWN, and a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped without comment.

That one rule produces three distinct traps. All of the MySQL, PostgreSQL, and SQLite behavior below was run against MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6; the SQL Server behavior is cited from Microsoft's documentation, linked where used.

Two tables are enough to demonstrate everything:

CREATE TABLE customers (id int, name varchar(20));
INSERT INTO customers VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

CREATE TABLE orders (customer_id int);
INSERT INTO orders VALUES (1), (NULL);   -- one order has no customer

Trap 1: WHERE col = NULL matches nothing

The natural way to find the orphaned order reads fine and returns nothing:

SELECT count(*) FROM orders WHERE customer_id = NULL;   -- 0
SELECT count(*) FROM orders WHERE customer_id IS NULL;  -- 1

Identical results on MySQL, PostgreSQL, and SQLite: the = version finds zero rows even though a NULL row is sitting right there. NULL means "unknown", and asking whether an unknown value equals another unknown value can only be answered "unknown", so the comparison never becomes TRUE for any row. SQL Server follows the same logic under its default settings; Microsoft's NULL and UNKNOWN page states that comparisons between two null values, or between a null value and any other value, return unknown, and directs you to IS NULL / IS NOT NULL.

When you genuinely want NULL-tolerant equality, where NULL equals NULL and nothing else, every engine has an operator for it, they just disagree on the spelling:

MySQL's comparison operators documentation notes that <=> is equivalent to the standard IS NOT DISTINCT FROM, so all four spellings mean the same thing.

Trap 2: one NULL turns NOT IN into an empty result

Now the trap with real teeth. Find the customers who have no orders:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

You would expect bob and carol. On MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6 alike, this returns zero rows. Not a wrong list, an empty one, and the same three-valued logic explains it in SQL Server as well.

The subquery produces the list (1, NULL), and id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second comparison is UNKNOWN for every row in the table, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. One stray NULL in the subquery quietly vetoes the entire result. This is the nastiest variety of NULL bug because the query works perfectly in development and then returns nothing in production the day the first NULL shows up in that column.

Two fixes, both verified to return bob and carol on all three engines. The direct one is to keep NULLs out of the list:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL);

The better one is to stop using NOT IN for this job entirely:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

NOT EXISTS asks "is there a matching row?" instead of comparing values, so NULLs in the subquery cannot poison it. It states the anti-join intent directly (the same family of shapes covered in SQL joins explained), and it is safe to use as a habit even on columns that are NOT NULL today, because columns have a way of becoming nullable later.

Trap 3: every engine sorts NULLs somewhere different

The first two traps behave identically everywhere. The third is where the engines part ways. Sort three values, one of them NULL:

CREATE TABLE s (v int);
INSERT INTO s VALUES (2), (NULL), (1);
SELECT v FROM s ORDER BY v ASC;

MySQL and SQLite treat NULL as smaller than every value, so it leads an ascending sort. PostgreSQL treats NULL as larger, so it trails. SQL Server sides with MySQL: the ORDER BY clause documentation states that NULL values are treated as the lowest possible values. The practical consequence: port a "latest items first, blanks at the bottom" query from MySQL to Postgres and the blanks migrate from bottom to top with no error and no warning.

Where the standard syntax exists, pinning the position is trivial. Verified on PostgreSQL 16 and SQLite 3.50.6:

SELECT v FROM s ORDER BY v ASC NULLS LAST;   -- 1, 2, NULL

On MySQL 8.4 that syntax is a hard error (ERROR 1064), but a boolean sort key does the same job, verified to return 1, 2, NULL:

SELECT v FROM s ORDER BY (v IS NULL), v;

v IS NULL is 0 for values and 1 for NULLs, so NULLs sink to the end. SQL Server lacks the syntax too; the equivalent trick there is ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, using the conditional ordering pattern shown in the same ORDER BY documentation.

Chasing this class of bug across engines is considerably less painful when you can run the identical script against MySQL, PostgreSQL, SQL Server, and SQLite connections in one place and compare the grids, which is precisely the sort of side-by-side work SQLPro Studio exists for.

The habits that make all three traps a non-issue: write IS NULL rather than = NULL always, reach for NOT EXISTS rather than NOT IN under a subquery, and never let a query's correctness depend on where the engine happens to put NULLs in a sort. Declare it with NULLS LAST or a boolean sort key, and the query means the same thing everywhere.

Tags: MySQL PostgreSQL Microsoft SQL Server SQLite

"The certificate chain was issued by an authority that is not trusted"

Posted by Kyle Hankinson July 31, 2026

You update a driver, a NuGet package, or a client tool. You change nothing on the database server. And a connection that has worked for years suddenly fails with:

[Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate
chain was issued by an authority that is not trusted.

The wording shifts with the platform. On macOS and Linux, where ODBC Driver 18 validates through OpenSSL, I reproduced the same failure against SQL Server 2022 as:

SSL Provider: [error:0A000086:SSL routines::certificate verify failed:
self-signed certificate]

and Go-based tools like modern sqlcmd report it as a TLS handshake failure with an x509 error naming a certificate called SSL_Self_Signed_Fallback. Different words, one problem.

What actually changed

Starting with ODBC Driver 18, OLE DB Driver 19, and Microsoft.Data.SqlClient 4.0 (which is why Entity Framework Core 7 upgrades trip this too), Microsoft flipped the encryption default. Older drivers assumed Encrypt=no unless told otherwise; the new ones assume Encrypt=yes. And once the client requests encryption, it must validate the certificate the server presents. Microsoft documents the change and its remedies in its certificate chain troubleshooting article.

Here is the part that catches people: a SQL Server that was never given a certificate still encrypts on request. It generates its own self-signed certificate at startup (that SSL_Self_Signed_Fallback name above) so encryption is always possible. But no client trusts that certificate's issuer, because the issuer is the server itself. Old driver: never checked. New driver: checks, and refuses.

So the error is not telling you encryption failed. It is telling you the client could not verify who it encrypted to. That distinction decides which fix is appropriate.

Fix 1: trust the server certificate (the dev-loop unblock)

Every Microsoft driver has a switch that says "encrypt, but skip issuer validation":

I verified the sqlcmd form directly: connecting to a fresh SQL Server 2022 container fails without -C and succeeds with it:

sqlcmd -S localhost,1433 -U sa -P 'YourStrongPassword' -C -Q "SELECT @@VERSION;"

Understand the tradeoff before you paste this into anything permanent. The connection is still encrypted, but you skipped the step that proves the server is the machine you meant to reach. Anything that can intercept your traffic can present its own certificate and read everything, including your SQL login credentials. For a container on localhost or a throwaway dev VM, that risk is theoretical and TrustServerCertificate is the pragmatic answer. For a production connection string crossing a network you do not fully control, it is the wrong answer, and it has a way of surviving in config files for years once added.

Fix 2: turn encryption back off

The other quick option is restoring the old behavior: Encrypt=Optional (or Encrypt=no) in ODBC 18 terms, Encrypt=False in ADO.NET terms. The connection then proceeds without certificate validation because the data channel is not encrypted at all.

You can see the difference from the server side. After connecting each way, I checked:

SELECT encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

That FALSE means login packets are still protected during the handshake, but your queries and result sets travel in plain text. Between fixes 1 and 2, prefer fix 1: an encrypted channel to an unverified server still beats an unencrypted one. Fix 2 is mostly useful for legacy servers whose TLS stack is too old to negotiate with modern drivers at all.

Fix 3: the production fix, a certificate the client can verify

The reason the error exists is that Microsoft wanted encrypted-and-verified to be the default posture. Matching that posture means giving SQL Server a certificate that chains to an authority your clients already trust:

• A certificate from a public or corporate CA. Per Microsoft's encryption configuration documentation, the certificate must be issued for server authentication, and its subject or subject alternative name must match the hostname clients use to connect. It is installed via SQL Server Configuration Manager on Windows, followed by a service restart.
• If you run an internal CA, the alternative is distributing that CA's root certificate to client machines, so certificates it issues validate everywhere in your organization.

Server certificate installation is a server-admin task and the steps above summarize Microsoft's documentation rather than something you can do from a client tool. But it is the only fix on this list that removes the error without weakening anything.

If you see this against Azure SQL

Azure SQL Database presents a certificate chained to a public CA, so a default-configured client validates it fine. Hitting this error there usually means something is intercepting the connection, most often a corporate proxy or firewall doing TLS inspection, or a connection string pointed somewhere unexpected. Do not reach for TrustServerCertificate on Azure; find out what is actually terminating your TLS first.

Fixing it in a GUI client

Desktop clients surface the same driver options as checkboxes. In SQLPro for MSSQL the connection editor exposes the encryption mode and a server-certificate trust setting per connection, which maps exactly onto the table above: trust the certificate for a dev container, and leave validation on for anything production-facing. The same reasoning about tradeoffs applies no matter which client the checkbox lives in.

If you are setting up a Mac SQL Server workflow from scratch, our guide to connecting to SQL Server from a Mac covers the client options and where this error fits among the usual first-connection failures.

The short version

The error reads like the server broke. It didn't. Your client got stricter, and the fastest fix, trusting blindly, is also the one worth removing again once a real certificate is in place.

Tags: Microsoft SQL Server

How to Connect to SQL Server from a Mac (No SSMS Required)

Posted by Kyle Hankinson July 14, 2026

For years the stock answer to "how do I work with SQL Server?" was SQL Server Management Studio. That answer has never worked on a Mac: SSMS is Windows-only, and Microsoft has shown no sign of porting it. The usual fallback, Azure Data Studio, is gone too. Microsoft retired it on February 28, 2026, and it no longer receives updates or security fixes. We covered that retirement and the migration paths in an earlier post.

So what does a Mac developer actually use in 2026? There are three good options, and none of them involve a Windows VM. Everything below was tested from an Apple Silicon Mac against SQL Server 2022 (CU25).

Before you connect

Whichever tool you pick, you need the same four things:

• The server's hostname or IP address, and its port (1433 by default).
• Network reachability. Your Mac must be able to open a TCP connection to that port. If the server sits on a private network, that means a VPN or an SSH tunnel (covered below).
• Credentials: a SQL Server login and password, or Microsoft Entra ID for Azure SQL.
• One syntax quirk worth memorizing: SQL Server tools separate host and port with a comma, not a colon. It is db.example.com,1433, never db.example.com:1433.

Option 1: sqlcmd in Terminal

Microsoft ships a modern, Go-based rewrite of its classic command-line tool, and it installs cleanly through Homebrew:

brew install sqlcmd

Connecting takes a server (-S), a login (-U), and a password (-P):

sqlcmd -S db.example.com,1433 -U sa -P 'YourStrongPassword'

That opens an interactive prompt where you type T-SQL and execute it with GO. For one-off commands, -Q runs a query and exits:

sqlcmd -S localhost,1433 -U sa -P 'YourStrongPassword' -Q "SELECT @@VERSION;"

One encryption detail to know up front. Most SQL Server installs, and every Docker test container, present a self-signed certificate. In my tests, go-sqlcmd connected to such a server without complaint, because by default it negotiates without validating the certificate. The moment you request encryption with -N, validation kicks in and the connection fails unless you either install a trusted certificate or pass -C (trust server certificate). The older ODBC-based sqlcmd, and most current GUI drivers, validate by default, which is where the widespread "certificate chain was issued by an authority that is not trusted" error comes from. The sqlcmd utility documentation spells out the flag behavior, including a heads-up that SQL Server 2025's sqlcmd makes encryption mandatory by default.

sqlcmd is the right tool for scripts, health checks, and CI. It is a poor place to read a 40-column result set.

Option 2: VS Code with the mssql extension

Microsoft's official recommendation after the Azure Data Studio retirement is the mssql extension for Visual Studio Code. Per Microsoft's documentation it covers query execution with IntelliSense, a results grid with export, object browsing, schema tools, and backup/restore operations, and existing Azure Data Studio queries and projects open in it without conversion.

It is free, and if you already live in VS Code it adds no new application to your dock. The tradeoffs are the ones you would expect from a database tool grafted onto a text editor: connection management, results, and object exploration all live inside VS Code's panel system, and the experience leans toward developers who primarily write application code and sometimes touch the database.

Option 3: a native Mac client

If SQL Server is where you spend a large part of your day, a purpose-built client is worth the money. SQLPro for MSSQL is a native client for macOS (with iOS and Windows versions) built specifically around SQL Server: a connection editor that exposes the encryption and certificate trust settings mentioned above, an object browser for databases, tables, and views, a query editor that handles multiple result sets from a single batch, result export to CSV, JSON, or XML, and built-in SSH tunneling so remote servers work without a manual tunnel.

The honest comparison: sqlcmd and the VS Code extension are free and official; a native client is commercial and, in exchange, treats the database as the main event rather than a sidebar. Plenty of developers use both a GUI for exploration and sqlcmd for automation.

Reaching a remote server: the SSH tunnel

Production databases should not expose port 1433 to the internet, and mostly they don't. If you can SSH into any machine that can reach the database, you can forward a local port through it:

ssh -L 11433:database-host:1433 you@jump-host.example.com

While that session stays open, your Mac's localhost,11433 behaves like the remote server's port 1433, and any of the three tools above can connect to it. Note the tunnel carries the database protocol as-is; whether the connection is encrypted end to end is still decided by the SQL Server driver settings.

The first-connection errors you are most likely to meet

• "The certificate chain was issued by an authority that is not trusted." The driver defaulted to encryption and the server presented a self-signed certificate. You either trust the certificate explicitly (the -C flag, or a trust checkbox in a GUI client) or put a proper CA-issued certificate on the server.
• "Login failed for user" (error 18456). Wrong password, a login that doesn't exist, or a server configured for Windows authentication only. The client-side message is deliberately vague; the server's error log holds the specific reason.
• A long pause, then a timeout. TCP 1433 is not reachable: a firewall in the way, the wrong port, a named instance listening on a dynamic port, or a server with TCP/IP connections disabled. Test reachability first with nc -z db.example.com 1433.

Summary

A Mac has been a perfectly good SQL Server workstation for a while now; the retirement of Azure Data Studio just changed which tools fill the gap. Pick the one that matches how much of your day the database occupies, and keep the comma-not-colon port syntax in mind for your first connection.

Tags: Microsoft SQL Server

SQL Window Functions: SUM, AVG, LAG, and LEAD

Posted by Kyle Hankinson January 25, 2026

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into groups like GROUP BY does. They are one of the most powerful features in modern SQL.

The OVER() Clause

Every window function uses OVER() to define which rows to include:

SELECT name, department, salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

This adds a total_salary column with the sum of all salaries — without grouping. Every row still appears individually.

PARTITION BY

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing):

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each row shows the department total and average alongside the individual salary.

ORDER BY in OVER()

Adding ORDER BY creates a running calculation:

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG and LEAD

Compare the current row to previous or next rows:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

LAG(col, n) looks back n rows (default 1). LEAD(col, n) looks forward n rows.

Default Values

Avoid NULLs for the first/last row:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- returns 0 instead of NULL

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

Percent of Total

SELECT name, department, salary,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Moving Average

Use a frame specification to average over a sliding window:

SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

Frame Types

Combining Multiple Window Functions

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_company
FROM employees
ORDER BY department, salary DESC;

WINDOW Clause (Reusable Definitions)

Avoid repeating the same OVER clause:

SELECT
    order_date, amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM orders
WINDOW w AS (ORDER BY order_date);

Supported in PostgreSQL, MySQL 8.0+, and SQLite 3.28+. Not supported in SQL Server or Oracle.

Database Compatibility

Tags: MySQL PostgreSQL Microsoft SQL Server