SQL SERVER Performance bottlenecks & Trouble shooting

To start troubleshooting, you have to define the symptoms first. The most common SQL Server performance symptoms are CPU, memory, network, and I/O bottlenecks, and slow running queries

CPU bottlenecks are caused by insufficient hardware resources. Troubleshooting starts with identifying the biggest CPU resource users. Occasional peaks in processor usage can be ignored, but if the processor is constantly under pressure, investigation is needed. Adding additional processors or using a more powerful one might not fix the problem, as badly designed processes can always use all CPU time. Query tuning, improving execution plans, and system reconfiguration can help. To avoid bottlenecks, it’s recommended to have a dedicated server that will run only SQL Server, and to remove all other software to another machine

Memory bottlenecks can result in slow application responsiveness, overall system slowdown, or even application crashing. It’s recommended to identify when the system runs with insufficient memory, what applications use most of memory resources, whether there are bottlenecks for other system resources. Reviewing and tuning queries, memory reconfiguration, and adding more physical memory can help

Network bottlenecks might not be instantly recognized, as they can at a first glance be considered as SQL Server performance issues caused by other resources. For example, a delay of data sent over a network can look like SQL Server slow response

I/O bottlenecks are caused by excessive reading and writing of database pages from and onto disk. A bottleneck is manifested through long response times, application slowdowns and tasks time-outs. If other applications use disk resources excessively, SQL Server might not get enough disk resources for its normal operation and would have to wait to be able to read and write to disk

Slow running queries can be a result of missing indexes, poor execution plans, bad application and schema design, etc.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s