How to Tackle SQL Server's Top Concerns

Between user error and known challenges with SQL Server, working with this database platform can be tricky, if not downright aggravating. It’s not always easy to avoid SQL Server challenges, and those of us who work with SQL know that some problems are not simple fixes. However, identifying problems and solving them as well as making an effort to avoid costly mistakes is a SQL Server best practice. We’ve taken care of the first part for you and have compiled three common SQL Server concerns in this blog post.

#1: Index Issues

Indexes are the bread and butter of SQL Server, but can also pose a challenge for users. Too many indexes? Not enough indexes? Missing indexes? Wrong indexes? Need index maintenance? The list can go on and on. SQL Server indexes well, but the chance for user error is high, especially for those with less experience. Perhaps it goes without saying that creating indexes correctly, organizing them effectively, and maintaining them is a science. Think you have index issues? Employ Dynamic Management Objects (DMOs) to tackle index concerns.

#2: Storage Configuration, CPU and Memory

Many issues often boil down to server hardware performance. A lack of available memory is the greatest factor in SQL Server performance. Although adding more cores to the system can certainly help with memory needs, this isn’t necessarily true for all processors, such as single-threaded processes. Check the memory by diving into some performance counters, such as available bytes (low values point to memory shortage), pages per second (high values might indicate a lack of memory) and buffer cache hit ratios (should be above 90 percent).

Storage can be a cause for concern as well. Performance counters for storage should be employed so you can evaluate if storage is an issue. PhysicalDisk Object: Average Disk Set/Read and Average Disk Sec/Write values higher than 20 milliseconds may indicate a problem with your storage. A PhysicalDisk Object: Average Disk Queue Length with values higher than two during peak usage could indicate low storage. It’s also necessary to identify which queries and stored processes are taking a long time to run and have the most IO and CPU usage. High CPU usage can be caused by high compilations and re-compilations, high use of the temporary table, poorly written queries, and system threads that cause high CPU spikes.

#3: SQL Server Deadlocks

When you’re running into performance issues with SQL Server, you could be looking at conversion deadlocks or cycle deadlocks. When one or more processes do not receive the required resource to complete their execution, this can be a real headache. Mutual exclusion, hold and wait, no preemption, and loop wait condition (circular wait) must all be true for a SQL deadlock to occur. You can learn more about deadlocks, how to avoid them and how to monitor deadlocks by reading our blog post: SQL Deadlocks: Why They Happen and How to Avoid Them.