Next stop on the ChatGPT series brings us to common problems we see in SQL Server as DBAs. In this career, there are some common issues we tend to see; slowness, deadlocks, performance bottlenecks, blocking, connection errors, and much more. The goal of this post is to act as a high level guide on how to recognize when you’re experiencing these common problems, and some solutions as to how to resolve them.

Deadlocks

What are deadlocks?

Deadlocks occur when two or more transactions block each other by holding locks on resources the other transactions need. SQL Server then kills the transaction that is less expensive.

How to identify deadlocks?

There are many ways to identify the transactions involved in the deadlocks. You could use any of the following tools and more:

• Extended Events
• Monitoring tools like SQL Sentry
• Community developed scripts

How to troubleshoot and resolve deadlocks?

Resolving deadlock issues can sometimes be complex and difficult. However, here are some of the approaches you could take to alleviate this issue:

• Indexing
• Implementing Read Committed Snapshot Isolation (RCSI)
• Adjusting application code to batch data modification transactions
• Using retry logic

Performance Bottlenecks

What are performance bottlenecks?

Performance bottlenecks can occur when system resources (CPU, memory, disk, network) are overutilized, leading to slow performance for queries and other processes.

How to identify performance bottlenecks?

There are lots of tools out there to help you identify performance bottlenecks. The tools you can use include but are not limited to:

• Performance monitoring software like DPA, SQL Sentry, Datadog, etc.
• Open source scripts like The First Responder Kit, sp_WhoIsActive, sp_HumanEvents, etc.
• Native Dynamic Management Views (DMVs) to SQL Server

How to troubleshoot and resolve performance bottlenecks?

At a high level, some ways you can resolve performance bottlenecks include:

• Better indexing
• Query tuning
• Updating statistics
• Analyzing server configuration
• Investigating hardware improvements or changes

Slow Running Queries

What are slow running queries?

Slow running queries are queries that take longer to run than their expected execution times. This can happen for several reasons, but at a high level, some form of query regression occurs. This can include data change causing the regression, code change, and other newly implemented processes negatively impacting existing queries.

How to identify slow running queries?

Some ways to identify these slow running queries include but are not limited to:

• Query store
• Performance monitoring tools
• Open source scripts (sp_WhoIsActive, sp_BlitzWho, sp_BlitzFirst)
• Custom alerts

How to troubleshoot and resolve slow running queries?

Resolving slow running queries can be a multi faceted process. Some approaches you can take are:

• Identify the wait types the slow running query is waiting on
• Implement better indexing strategies
• Find out if there have been recent code changes that could have caused the slow down
• See what is actively running on the server to determine if it is a blocking situation
• Rule out any parameter sniffing possibilities

Blocking

What is blocking?

Blocking is something that happens when one transaction or query has a lock on an object that another transaction needs as well. The other transaction is stuck in a waiting, or blocked state until the first transaction releases that lock.

How to identify blocking?

There are some good ways out there to identify blocking. The way I like to quickly identify blocking is using Adam Machanic’s stored procedure sp_WhoIsActive. This is a light weight stored procedure that you can run to quickly get a look at what is currently running on your SQL Server. There are some tools that come natively with SQL Server such as Activity Monitor, and sp_Who2, but sp_WhoIsActive gives you much more readable detail about what queries are running, waiting on, blocking, how long queries have been running for, and much more.

There are other procedures out there like sp_BlitzWho that function in a similar manner, but sp_WhoIsActive is still a great go to for me. Monitoring tools like DPA, and SQL Sentry are another great solution to see what queries are facing blocking on your server.

How to troubleshoot and resolve blocking?

Blocking can be alleviated and resolved by using the following:

• Have enough indexes to make your queries fast, but not so many that they slow down DUIs, making them hold more locks for longer times
• Tune your transactional code
• Use the right isolation level for your app’s needs
• Consider implementing Read Committed Snapshot Isolation (RCSI)

Connection Errors

What are connection errors?

Connection errors occur when a user or application have trouble establishing or maintaining a connection to your SQL Server. This can obviously be inconvenient and cause issues for development and business to continue.

How to identify connection errors?

Well, connection errors are pretty straight forward to identify. One or more of the following will usually happen:

• You get pinged by someone saying they can’t connect
• You your self cannot connect
• Applications start failing and throw connection related errors
• You are unable to ping the server

These are just some of the ways that you can identify, or be made aware of that there are connection issues. However, someone pinging you screaming that they “can’t hit the database” will be your most common encounter.

How to troubleshoot and resolve connection errors?

This is an “it depends” kind of answer. However, here are some common things to check to help resolve connectivity issues to a SQL Server:

• Make sure the OS is up and running
• Make sure the server is pingable
• Check to see if the SQL Server Service is running in SQL Server Configuration Manager
• Make sure the SQL Server actually exists
• Check firewall rules and ensure that port 1433 is added as an inbound firewall rule
• Check firewall rules for port 1434 if it is a named instance and make sure that an inbound UDP rule is set up for that
• If there are multiple named instances on the same server, make sure there is a dynamic TCP inbound firewall rule set up for each port of the named instance(s)
• Make sure TCP and UDP protocols are enabled for the SQL Server in SQL Server Configuration manager
• Hit up your network team to check to see if they can identify any networking issues

Let’s Wrap it Up

I understand that “common problems” are relative to the environment you’re working in, so I know I missed some that you were maybe hoping to see, however, I wanted to approach this from what I thought was a general enough standpoint to hit some topics that we as DBAs commonly see out in the wild. I’d love to know what common problems you see out there and let me know what you do to resolve them.