SQL DBA Guru

Hi Salim,
Thanks for putting up your question here. I would say that the performance of any SQL Server is dependent upon a number of factors but more of the kind of workload it has have, and the resources it has been provided with. There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems.

Resource bottlenecks: CPU, memory, and I/O bottlenecks, we do not consider network issues. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can lead to excessive paging that ultimately impacts performance.

Tempdb bottlenecks: Since there is only one tempdb for each SQL Server instance, this can be a performance and a disk space bottleneck. A misbehaving application can overload tempdb both in terms of excessive DDL/DML operations and in space. This can cause unrelated applications running on the server to slow down or fail.

A slow running user query: The performance of an existing query may regress or a new query may appear to be taking longer than expected. There can be many reasons for this. For example:

- Changes in statistical information can lead to a poor query plan for an existing query.

- Missing indexes can force table scans and slow down the query.

- An application can slow down due to blocking even if resource utilization is normal.

Excessive blocking, for example, can be due to poor application or schema design or choosing an improper isolation level for the transaction.

So, there are few tools which are used to resolve a particular resource bottleneck.

- System Monitor (PerfMon): This tool is available as part of Windows. For more information, please see the System Monitor documentation.

- SQL Server Profiler.

- DBCC commands.

- DMVs: See SQL Server Books Online for details.

Thanks,
Sujeet