This is going to be a multi-part post regarding basic performance troubleshooting in SQL Server. It applies mainly to SQL Server 2008 and beyond but the methodology and general approach to troubleshooting can be applied to any version of SQL Server.
it is all to common to experience slowdowns of a SQl Server Database. The causes can range from poor i/o to poorly designed queries to a misconfigured system. To the best of our abilities we want to proactively prevent such slowdowns, however that is not always possible. When such slowdowns occur you want to be able to quickly identify the cause and take corrective actions. The following posts will provide some guidelines for identifying common performance issues using the standard tools available (SQL Profiler, Performance Monitor, DMVs, Extended Events and Data Collectors). The main goal of which is to provide a basic methodology for diagnosing and troubleshooting performance problems.
Obviously there can be many reasons for a slowdown in SQL Server, but I generally focus on three main symptoms to start diagnosing problems:
· Resource bottlenecks: CPU, memory, and I/O bottlenecks
· tempdb bottlenecks: Because there is only one tempdb for each SQL Server instance, it can be a performance and a disk space bottleneck.
· A slow-running user query: The performance of an existing query might degrade over time, or a new query might be taking longer than expected. There can be many reasons for this here are some of the big ones:
o Changes in statistics
o Missing indexes (over indexing as well)
o Lock escalation
Next Post will examine Resource Bottlenecks: CPU