An Introduction to Server Performance Monitoring and Profiling

All topics about maintaining SQL Server from a DBA perspective.
Post Reply
Posts: 17
Joined: Thu Jun 20, 2019 6:41 pm

Mon Jun 24, 2019 2:13 pm

There are many ways to define performance such as queries per second, CPU utilization, scalability, and so forth. One description that I like to use is that performance is measured by the time required to complete a task, or simply put it is the response time. This is an essential basic principle. We determine performance by tasks and time, not by resources. A database server's function is to execute SQL statements, so the tasks we care about are queries or statements-the We've observed that many people, when trying to optimize something, spend the bulk of their time changing things and very little time measuring. In contrast, we aim to spend most of our time-perhaps upwards of 90%-measuring where the response time is spent. If we don't discover the answer, we might possibly not have calculated correctly or fully. Whenever we collect complete and adequately scoped measurements concerning server activity, performance issues generally can't hide, and the solution often becomes trivially obvious.

Testing can be a challenge, however, and it can also be hard to know what to do with the results once you have them-measuring where the time is spent is not the same thing as comprehending why time is spent. We described proper scoping, but what does that mean? An adequately scoped measurement is one that measures only the activity you want to optimize. There are two common ways that you can capture something irrelevant: wrong start and end points, and failing to specifically target the activity itself.

A standard mistake is to monitor a slow query, and then look at the whole server's behavior to try to discover what's incorrect. If the problem is slow, then it's best to measure the query, not the whole server. And it's better to measure right from the start of the query towards the end, not prior to or after. Time required to execute an activity is spent possibly executing, or waiting around.

The best method to reduce the time necessary to execute is to recognize and measure the actual tasks, and then do a number of of the subsequent: eliminate tasks totally, make them occur less often, or make sure they are happen more effectively. Reducing waiting is really a more complex physical exercise, because waiting could be caused by "collateral damage" from alternative activities on the program, and thus there might be interaction between the duty and other tasks that could be contending for use of resources like the disk or CPU. Various techniques or resources may be used, depending on if the time is invested executing or waiting around. In the previous paragraph we said you'll want to identify and enhance tasks. But that's a good oversimplification. Infrequent or brief tasks might lead so little in order to overall response period that it's not worth your time and effort to optimize all of them. How do a person determine which tasks to focus on for optimization? For this reason profiling was created.
Post Reply

Social Media