Lead Image © Anna, Fotolia.com

Performance troubleshooting on Microsoft SQL Server

Gridlock

Article from ADMIN 40/2017

By Evgenij Smirnov

Use Microsoft SQL Server diagnostic tools to troubleshoot bottlenecks and other performance problems.

If Microsoft SQL Server in your IT center supports infrastructure services such as virtualization and IP address management (IPAM), as well as databases for applications and business processes, a performance bottleneck in the SQL Server area is almost always a critical issue. For one thing, delays to data queries can be very specifically translated into dollars and cents from the management perspective. Moreover, all areas of IT – server, storage, network, and applications themselves – are involved in accessing SQL Server. As the person responsible for the affected SQL Server, you would thus do well to be proactive from the outset by identifying any problems as quickly as possible and helping others understand any difficulties. However, before you pounce on SQL Server, make sure the problem lies within your sphere of influence.

A typical SQL-based application has several layers – the application itself, with the application layer and the client front end – each of which can be the source of poor performance. A further sticking point could be the network between client and server. If the SQL queries provide a large volume of data, transmitting the data to the client could be a cause of the perceived delay – even if SQL Server has processed the results quickly.

The database also can be the cause of poor performance. If you have a table with many records, incorrect or missing indexing cannot possibly be compensated for by the available hardware resources and will affect the performance of the selection queries. SQL Server makes very complex use of the hardware resources provided to it, and configuration errors could easily occur. Also, a typical SQL Server is likely to provide databases for numerous applications in several instances. What is rarely tested before rolling out a new application or a major upgrade of an existing application is whether the use of resources still leaves enough room to maneuver.

For further diagnostics, it is

...

Use Express-Checkout link below to read the full article (PDF).