“To write it, it took three months; to conceive it three minutes; to collect the data in it all my life.”
F. Scott Fitzgerald

One of the most important tasks of a Proactive DBA is undoubtedly to gather accurate and reliable information. It is relatively easy to manage a few databases and instances, but when these get towards triple figures, you’ll need centralized management. Measurements, checks and alerts are vital for good centralized management of databases. To get effective checks for abnormalities that could mean a potential problem, you require information about what is normal. Every system has its own unique characteristics, , and so, to have a real idea of ââwhat’s going on with its health, we need a baseline of its behavior as a starting point.

There are many sources on the internet where you will find figures specifying the thresholds beyond which some activity, such as waits, is abnormal, but they are unlikely to reflect the actual circumstances of your system.

Remember your system is unique. For example , you’ll often hear it said that, if your system is running with constant CPU above 80% , then you’re wrestling with some kind of bottleneck . Well, this could be true, but would this necessarily mean you should become alarmed and try to work out what’s changed to give this figure? No. We get alerted to stress in the system before it becomes a problem by detecting changes in a range of metrics from the normal, not by seeing whether they exceed thresholds. It could be that, even after you’ve tuned your system to perfection, your normal baseline for CPU activity could be between 80% and 90%. This will always indicate that you need more hardware, but it could mean that you’re making very efficient use of the available hardware, and you haven’t the resources to upgrade it. There is no way of avoiding the need for baselines measured on your server-environment. I guarantee you will need to know the actual state of health of your servers, and the threshold figures that you find on the internet may not reflect the real world you inhabit and so will mislead you about what is going on with the servers in your care. Do not guess, collect.

These articles from my good friend and SQL Server MVP Ted Krueger is a very good start point. You should read it

• Baseline, Performance Reporting and being a proactive DBA
• Creating a baseline for SQL Server

Also the SQL Server MVP Erin Estellato has a good course in PluralSight – SQL Server: Benchmarking and Baselining

There are several tools for getting baselines, but Performance Monitor (PerfMon) is most commonly used, because it is free and comes with Windows. Performance Monitor uses Windows Performance counters which are a standard, so you can gather performance metrics from any server process. Even applications can be instrumented to produce them. There are several tools used by Ops teams to report and alert on the information gained from performance counter data. By gathering information from performance counters, you can, potentially, collect data from the entire technology stack in one place. You can then use PerfMon to overlay the data collected from the various sources to see how processes and servers interact under different conditions.

Today we will see how to properly use Windows PowerShell and the native cmdlet Get-Counter to collect Performance counters.

Get-Counter

Windows PowerShell has a native cmdlet called Get-Counter and by definition :

This means that I don’t need to worry about the low-level process of getting counters. Instead, I just need to get to learn how to properly use the Get-counter cmdlet.

Selecting the Counters

You’ll probably find that the set of counters you need is already there. To find them in the Get-Counter cmdlet we use the parameter -listset.

We can improve the view to show only the counter sets in alphabetical order, sorting and selecting only the CounterSetName Property ...

… or you can filter for only the SQL Server counter sets …

… filtering ‘SQL*’ in the -listset parameter:

In that view format, we are only showing the counter sets, but we can expand any specific set that we want in order to check what counters are included in it. Let´s use the SQLServer:Buffer Manager :

If we just select that listset…

…the output will be :

Or if we get the members :

You will have noticed the’ Counter‘ property that contains all the counters within the SQLServer:Buffer Manager list set. By expanding this property …

… we then have all the counters :

We also can export those counter s that we want to a text file :

Now, in order to add other counters, we just need to repeat the same process : Lets do it with SQLServer:Memory Manager

This way we can select all the counters, whether they are relevant to SQL Server or not, that we need in order to start our gathering.

Performance counters are combined under categories , as we’ve just seen by using the Get-Counter cmdlet with the-listset parameter. These categories combine the performance counters into a logic unit, but performance counters can also be partitioned into instances, such as ‘process thread' or ‘logical disk'. Let’s take a look into the ‘Logical Disk' list set. The property that says if it will have instances or not is CounterSetType :

It is a MultiInstance type. In case of only one instance, it will show SingleInstance. So if we expand the Counter Property:

As we can see, all of the counters have (*) in the name. It means that each counter has more than one instance, or logical disk in this case. The ‘% Free Space’ counter will be present in each logical disk of your system. If you want to get the data from a specific logical disk, you need to specify it, otherwise the cmdlet will get the data from all logical disks. Let’s expand the instances:

So if I just want the ‘Free Megabytes' from C: I will use ‘\LogicalDisk(C:)\Free Megabytes'. One of the beauties of PowerShell is that if I want all the instances, then I can just use the * and ‘\LogicalDisk(*)\Free Megabytes'. It will automatically collect the data from all instances of that counter.

Another important point is that we can use a formatted file (as a .csv) to store the counters. I prefer that approach, since in a formatted file I can use the columns of the file as properties in PowerShell. This way I can create a file with more information but only use what I need in the Get-Counter cmdlet.

If you are using the PowerShell 4.0 version of the cmdlet, you have the benefits of using the new common parameter PipelineVariable. This parameter stores the current object into a variable, allowing you to use it in further levels of the pipeline process. Keith Hill explains it better in PowerShell V4 – PipelineVariable Common Parameter

And our csv will be looking like this:

Back to the text file, let’s use the same counters and we can do it in the same command line :

Or using the PowerShell 4.0 Pipeline Variable :

As I mentioned already, if you want an instance of some counter in Logical Disk or any that is multi instance, it is just a matter of selecting what you want and then exporting it to the CSV file, using the -append parameter.

In this case , if you are using PowerShell 2.0, it does not have the -append parameter in the Export-Csv cmdlet. The trick is to use the cmdlet Convertto-Csv and then output the results to CSV file using Out-File:

Or In PowerShell 4.0 using PipelineVariable:

Now it is just choosing the counters you want and storing them in the CSV file.

Gathering the Data

Using the idea of the CSV file, we already have all the counters that we want to collect, lets start to do it :

It is just a matter of importing the CSV file, choosing the counter property and then using the Get-Counter cmdlet:

Well, it is not the best view. We need to expand the CounterSamples property and select the properties :

Now we get a better visualization of the counter information.

As I am not specifying the -computername parameter because it is collecting in the local computer. I could do the same process in any computers I want, just by informing the cmdlet via the -computername parameter …

…or even loading the computer names from a text file :

So if we are running in several computers, why not separate the data to server and counter/Instance? Well, sure. First lets take a look in the Path property. It contains all the information that I need :

If we split the path property in the ‘\\’ char it will create an array and we can see the items of that array with its index :

As we know that arrays in PowerShell are zero-based, the server name will be in the 2 index and the counter name 3 and 4 index. Now it is just a simple matter of splitting the values :

We also can use the Format-Table and -autosize parameter :

We can see that theInstanceName property is unnecessary, since it is shown also in the Counter property. In the further examples we will omit it.

SampleInterval, Continuous and MaxSamples

These parameters are used control de latency of the gathering. The SampleInterval parameter allows you to specify the time, in seconds, between the samples gathered. The Continuous, as the name itself suggests, is to collect the samples continuously. By Default, if it is not specified, Get-Counter only gets one sample. Lets say that I want to get the data in intervals of 10 seconds , continuously :

With the MaxSamples parameters, you can specify the number of sample to gather. The default is one. Assuming that you only want to gather 20 samples in an interval of 10 seconds each one :

Summary

In this article, we covered how to use the Get-Counter cmdlet for gathering the performance counters we need. In the next one, we will see how to store this data in a SQL Server table, and I’ll show some different ways to do it. Also we will learn how to do all this process using Windows PowerShell Background Jobs and scheduling them using Windows PowerShell Scheduled Jobs.