4 Tips for Solving SQL Server Architectural Problems

Perhaps the biggest problem with SQL Server architectural flaws is that they commonly lie hidden and dormant until application concurrency increases enough for them to manifest. Troubleshooting these problems is complicated by the fact that they typically arise at seemingly the worst possible time.

Related: Are Your DMVs Lying to You?

Here are four tips to help troubleshoot your SQL Server architectural problems:

1. For initial troubleshooting, querying the sys.dm_os_waiting_tasks DMV is an easy way to get a sense of whether queries are waiting excessively on blocks from other simultaneous queries and operations.

2. You can also use use DMVs to track overall SQL Server wait statistics, which can provide great insight into whether locking and blocking problems are causing excessive waits on the server.

3. If the dreaded #1205 Deadlock error messages start showing up, it's time to start looking at root causes and evaluating options for remediation. Toward that end, one of the best ways to track down deadlocks and begin evaluating what corrections will be needed is to use SQL Server Profiler.

4. If you're having deadlocking problems, it's safe to say the database you're dealing with is mission critical—otherwise, it wouldn't have enough concurrent problems to cause deadlocks. As such, don't be afraid to get outside guidance or consulting help in dealing with concurrency problems if you're uncomfortable with what's going on, because troubleshooting this kind of problem can be disastrously expensive if you get it wrong.

Want to learn more? Check out Michael K. Campbell's in-depth article, "Troubleshooting Common SQL Server Problems."