An indispensable tutorial for becoming an SQL Server Performance Expert (1)

Is it possible to answer this question in just a few seconds? You may try to view the performance counters in Performance Monitor PerfMon. There are about 1000 counters with different names. You may ask: "Skipped Ghosted Records/sec" number of phantom Records Skipped per second) = 10. Is this value good or bad? The correct answer is that this value may not be important. However, some counters are really important. This article will help you identify such counters and teach you how to troubleshoot the most common performance problems.

To get started, install the SQL Heartbeat tool from the following website:
Http://www.sqlsolutions.com/downloads/activity-monitor/Download.html
After connecting to your server, you will see two types in the server tree structure:

Click the "Online Activity/SQL Heartbeat" node and you will immediately see five different charts. But it is best to click "Historical Data" Historical Data) and wait for a day or two until more accurate metrics are accumulated. Then, what do you want to view on the chart? First, you should pay attention to the Waits chart:

By the way, have you noticed the periodic pattern on this graph? For example, there will be fewer activities during the weekend and more evening activities. To better understand the running status of the database during normal working hours, you can exclude these periods during analysis, especially because these periods may be full backups ). We can exclude this type of data to make our charts smoother:

The next step is to check which category is the worst. If the main color is blue or yellow, it indicates reading or writing), then your server is disk input/output intensive. The following is an example:

Why is it input/output-intensive? Click Physical R/W chart. The problem may be caused by a workload, for example, too many input/output operations:

Frequent input/output activities may be caused by poor high-speed cache hit rate. You can click Cache Hits high-speed Cache hit) to confirm:

Note: Do not use the "Cache Hits Ratio" high-speed Cache hit rate reported by performance monitor PerfMon ). PerfMon only reports "average hospital temperature"-it calculates the average value for weekend and busy hours, night index fragmentation, and daytime operations, using different usage modes.

Sometimes, "high-speed cache hit rate" is good enough, but the delay is very poor. Click "Seek Time") to confirm:

 

50 ms) means that the server can only perform 20 input/output operations per second. This speed is only equivalent to an old-fashioned floppy disk!
Some servers are processor-intensive. The following figure shows a typical processor-intensive Server:

In general, if the server is processor-intensive, the situation is not bad, as long as the absolute value is not very high, remember: Y axis indicates that the server uses all the processors per second, the processor milliseconds, therefore, the number of processors per second can be up to 4000 milliseconds on four processors .) However, if the absolute value of processing usage is very high, click Query Stats Query statistics. You will see the top 10 most problematic queries by processor, read, and write categories. These queries are probably the objects to be optimized.

By the way, you can obtain all these reports by email every day. You only need to configure in SQL Heartbeat: Click Reports Report in the toolbar.