The application says connecting is slow, but the server query is fast. A network trace shows whether the time went into TCP, pre-login, TLS, login, or the first query.
Choose One Connection Attempt for the Network Trace
Start with a precise client, server, time, and application action. Capture a short trace around one failed or slow attempt. A broad network capture can be enormous and hard to handle. It can also contain sensitive metadata. Use an approved collection process and store the file with restricted access.
I ask for a working attempt from the same client path when possible. Two traces make differences visible. A trace from Management Studio is not a substitute if the application uses another driver and certificate store. Keep the driver version and connection string options beside the capture.
What does the user call “connecting”? It can include name resolution, authentication, and the first query. The trace helps split that single complaint into stages.
Find TCP Before TDS
Locate the TCP connection establishment first. If it never completes, focus on routing, firewall rules, listener address, and port. SQL Server query plans are irrelevant at this stage. A TCP connection that opens quickly only proves a transport path exists; it does not prove login succeeds.
Check whether the client tried several addresses or ports before reaching the server. Name resolution and retries can add delay before any TDS message appears. Compare the chosen endpoint with the application’s intended connection target. An alias that points to an old host can waste time quietly.
I mark the first successful TCP handshake on the timeline. That timestamp becomes the start of the SQL protocol discussion. Without it, every later gap gets blamed on the database engine.
Read the Pre-Login Exchange
TDS pre-login messages negotiate connection options before a normal login. Look for a client pre-login packet and a server response. If the exchange stops there, inspect driver support, server availability, and network devices that can interrupt the conversation.
Do not assume you can decode every field when encryption is in use. The trace still shows packet order and timestamps. That is enough to locate a gap. Pair the trace with client error text and SQL Server logs for the cause.
A slow pre-login response can point to server pressure or a path problem. Compare with a known good attempt and server health at the same time. One network trace is a clue, not a verdict.
Measure TLS Separately
After pre-login, TLS negotiation can occur according to the client and server settings. Check the Client Hello, Server Hello, certificate exchange, and any alert. A certificate trust or name failure belongs here, before SQL authentication. The application can report it as a login problem even though no SQL login was checked.
A new driver can use stricter encryption defaults. Verify the driver build, connection string, and server certificate. Do not solve a production trust problem by permanently turning off validation without review. Fix the certificate chain or name where the design requires it.
I compare the TLS stage against a working client. If one workstation succeeds and another fails, the client trust store or driver can be the difference.
Find Login and Its Acknowledgment
Once the secure channel is ready, the client sends the login information according to the protocol. Look for the server’s response or an error. A failed authentication message points to account, password, integrated authentication, or policy. A long gap before acknowledgment needs server and identity system evidence.
Keep credentials out of any shared trace or screenshot. Packet captures are operational evidence, not blog attachments. Redact addresses and account names before outside discussion.
On a successful session, SQL Server exposes connection metadata. Query it from the relevant session when you can. An established connection query cannot explain an attempt that failed before login, but it can confirm transport and encryption for a successful comparison.
SELECT
session_id,
client_net_address,
net_transport,
net_packet_size,
encrypt_option,
auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;Separate the First Query
After login acknowledgment, the application can run initialization SQL before showing a screen. That work can be slow while connection setup itself was quick. Look for the first request and its response. Compare its timing with SQL Server request and Query Store evidence where available.
A connection pool adds another wrinkle. The user action can reuse an old connection, so no new login appears in the trace. Capture the application event and trace together. If the pool retries failed connections, one click can produce several attempts.
I have seen “slow login” turn into a slow initialization procedure once the timeline was marked. At least that problem has a query plan to inspect.
SELECT
r.session_id,
r.command,
r.status,
r.wait_type,
r.total_elapsed_time,
s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;Compare Network Trace Gaps With a Baseline
Measure each interval from the trace you captured: TCP, pre-login, TLS, login, and first query. Use the actual timestamps. Do not invent a universal acceptable number. Compare with a successful run from the same application and network path.
Clock differences between client, server, and capture machine can confuse cross-system logs. Record time zones and synchronize clocks where your operations standard requires it. Within one trace, relative packet timing remains useful.
A gap can be caused by the client, network, server, or an identity provider. The timeline tells you where to ask next. It does not identify the responsible team by itself.
Turn the Network Trace Into an Action
Write a short finding: last completed stage, delayed interval, supporting packet numbers, client error, and the next check. Keep the raw trace protected. Give another engineer enough detail to reproduce the observation without exposing private traffic.
Change one variable during retest. If you update a driver and certificate at the same time, the result cannot tell you which mattered. Run the same connection action and compare stage timing again.
The useful output of a trace is a narrower problem. It moves the conversation from “SQL is slow” to a specific handoff that can be tested.
Related reading on this blog: How to Find IP Address of All SQL Server Connection? Interview Question of the Week #280 and Network Protocol and IP Address.
A connection trace is not a wall of packets, it is a timeline of where the wait began.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
