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.
Trusted Tech Team is an accredited Microsoft CSP Direct Bill Partner, carrying multiple Solutions Partner designations and the now-legacy Microsoft Gold Partner competency. Based in Irvine, California, we report trends affecting IT pros everywhere.
If your organization uses Microsoft 365 or Azure, you may be eligible to receive a complimentary savings report from a Trusted Tech Team Licensing Engineer. Click here to schedule a consultation with our team now to learn how much you can save today.
Subscribe to the Trusted Tech Team Blog
Get the latest posts delivered right to your inbox