One of the issues users face is sporadic high CPU usage by their SQL Server, which leads them into a frenzied search of the queries creating this problem.
If you’ve come across such a situation, you may have considered starting up Perfmon (Windows Performance Monitor). Perfmon displays each occurrence of said spikes along with the exact CPU usage in each instance.
SQL Performance Monitoring Using Perfmon
It won’t display the code causing such spikes, but that’s not much to worry about, especially if you want SQL performance monitoring. This is because you already have a free graphical tool that can display both - the CPU usage spikes and the code that’s causing them.
Take a look at the following steps to use this ‘free’ tool -
Step 1: Correlate data. A correlation can be performed between Perfmon output and trace information to get the view required to fix this issue. This proves quite useful because when you click anywhere on the lower graph, you jump to the corresponding code in the upper tabular trace data being executed in that moment.
You will also find buttons to take the user to the topmost and bottommost values for any perfmon counter in particular. These are particularly helpful in database query optimization.
Step 2: Create the Perfmon counter log and Profiler trace file. However, doing all this requires the creation of a Perfmon counter log as well as a Profiler trace that must run simultaneously. The Profiler trace file has to have the “startdate” column along with the metrics you need.
Note: Although DBAs usually have experience with traces, in case you are just starting out, you can simply go for a database query optimization tool straightaway.
Step 3: Load the Perfmon file into Profiler. To do this, just open Profiler and load the trace file created in Step 2. From there, choose the option that says “Import Performance Data” in the File menu. You will have to locate the newly created Perfmon file.
Tip: Once you get to this point, you will be able to choose all the Perfmon metrics you want to view in graphical form. However, choosing only a few at a time is recommended, because the graphic window is rather small to fit more, and it can get too congested and unreadable if you try to display too many metrics. Also, you need to visit Import Performance Data again in order to adjust your selection.
Final Thoughts
Experts always find it surprising when they visit client sites only to find a considerable number of otherwise-knowledgeable SQL professionals unaware of this interoperability between such popular and extensively used monitoring apps.
Therefore, as a DBA, you may want to follow the three tips given below and avoid such ignorance:
●
Inculcate
the fundamentals into your development environment. Ensure that both - the app
developers as well as the Database Administrators comprehend the steps
mentioned above to use SQL performance tuning to its fullest.
●
Begin
application construction with database performance in mind.
● Make sure you own a convenient, automated method of locating and resolving poorly performing SQL queries.
These are as simple as they sound, which is why it’s surprising when many companies fail at these three simple things.
