Skip to Content

There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems:


·         Resource bottlenecks:


a) CPU

b) Memory

c) I/O bottlenecks

+ network issues.

·        tempdb bottlenecks:


what is a tempdb? The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server.

Because there is only one tempdb for each SQL Server instance, it can be a performance and a disk space bottleneck. An application can overload tempdb through excessive DDL or DML operations and by taking too much 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 might regress, or a new query might appear to be taking longer than expected. There can be many reasons for this. For example:

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

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

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

__MCE_ITEM__· Excessive blocking can be due to poor application or schema design or the choice of an improper isolation level for the transaction.

The causes of these symptoms are not necessarily independent of each other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or if the query optimizer decides not to use it, the query can slow down; these conditions also put heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently-run query can put pressure on the CPU.

Possible bottleneck area

Effects on the server

Memory usage

Insufficient memory allocated or available to Microsoft SQL Server degrades performance. Data must be read from the disk rather than directly from the data cache. Microsoft Windows operating systems perform excessive paging by swapping data to and from the disk as the pages are needed.

CPU utilization

A chronically high CPU utilization rate may indicate that Transact-SQL queries need to be tuned or that a CPU upgrade is needed.

Disk input/output (I/O)

Transact-SQL queries can be tuned to reduce unnecessary I/O; for example, by employing indexes.

User connections

Too many users may be accessing the server simultaneously causing performance degradation.

Blocking locks

Incorrectly designed applications can cause locks and hamper concurrency, thus causing longer response times and lower transaction throughput rates.

More information:

http://www.sqlserverspecialists.com/2013/06/reasons-for-slowdown-in-sql-server.html

http://www.mssqltips.com/sqlservertip/2316/how-to-identify-sql-server-cpu-bottlenecks

http://sqlserverplanet.com/troubleshooting/sql-server-slowness

http://support.microsoft.com/kb/298475

http://msdn.microsoft.com/en-us/library/cc966540.aspx

http://www.sqlperformance.com/2013/05/io-subsystem/cpu-troubleshooting

Regards,

Marcelo Silva Santos

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Kennedy T

    You Can Use MS-SQL Activity Monitor. to find the expensive Query. Blockage & etc and fine tune those using them….

    Rgds

    Kennedy

    (0) 

Leave a Reply