This is a common requirement in all companies… servers must be baselined otherwise how do you know if changes are detrimental or beneficial? How do you know where to look if something suddenly goes wrong?
Well this is the easiest way I know in order to quickly start obtaining data which can help you monitor and diagnose problems.
We all know that Performance Monitor (perfmon) is ideal when looking for trends and information through its counters… so what we’re going to do is log some of these straight into SQL Server to provide data upon which to track trends and variations.
This method can be used on any server in the network in order to pass perfmon data to a centralised, local (or any other) SQL Server.
I use this method to monitor both SQL Servers and IIS servers and provide real time alerting… code a simple front end to provide graphs or a proc to send emails and you’ll never be caught out again.
First things first we need to go to our central SQL Server and create a database into which we’ll place this collected data. I’ve create one simply called “serverMonitor”.
create database serverMonitor
Next we need to create a link to connect out monitored server to the SQL Server. We do this via an ODBC driver so let’s create that.
Login to the server you wish to monitor… at this point I have to state that to set up this monitoring you ideally need to have a Windows login that you can use across all servers and has access to all machines and SQL Servers involved in this process.
Therefore if you have a specific windows login that you use for the SQL Server service, then you should ideally use this to login with at this stage.
Next, press Windows+R, then type “odbcad32” and press Enter:
In the resulting window, select “System DSN”, and click “Add”:
Select “SQL Server”, name your driver, provide a description if you wish, and then enter the server which you want to collect your data. I’m using a test server for me example:
Now, on the next screen I have to stress there seems to be a flaw in the way in which Windows deals with these drivers… you CAN enter a SQL Server login but chances are this will then fail when you try to use the driver, hence why I suggested you log in with a wide ranging Windows login earlier.
Therefore leave this window showing “With Windows NT authentication” and click next:
You can see that I’ve ensured that the driver points to the database that I created earlier:
Then you press Next and Finish. You can leave all defaults as they are.
You can then text the connection if you wish, or simply press OK:
Now you just need to set up the Performance Monitor, so to open this press Windows+R, they type “perfmon” and hit Enter:
Create a New User Defined Data Collector:
Select Create Manually for the first one, but after you’ve set one up then you can export the definition and then use that to create further monitoring more easily.
Select Performance Counter:
Then simply choose the counters that you wish to monitor. I always work on the principle that if you capture more than you need then you’re covered if something new goes awry… but if you find, over time, that you never use certain counters, then you can remove them at a later date.
Better be safe than sorry.
I also usually select 5 seconds for my collection rate. Now, I need to add a caveat here… if you’ve the space then great, but 5 seconds across a lot of counters over many machines means that you’ll be sifting through vast amounts of data. Therefore I tend to keep 5 second counters for 24 hours, then average them down to 15 seconds for the next 48 hours, averaged per minute for the next 4 days (therefore covering a full week) and after that I average yet further until the dataset is the size I require whilst covering the duration needed.
You then select where you would want the data saved. As this goes to SQL Server this doesn’t really matter, so I simply put it in C:\temp
At the “Run As” screen, you need to place the Windows Login that you’re either using now, or that you want this to run as:
Next, and finally, you need to right click the DataCollector itself and select Properties:
Change the Log Format to SQL and select your Driver as the Data Source Name and you’re done.
You can now right click and start your monitoring:
This places the data into a normalised few tables in the database you requested.
Here’s a quick sample so you can see the counters working… but I’ll leave the actual analysis for you to do on your own:
select d.machineName serverName, d.ObjectName, d.CounterName,
d.InstanceName, c.CounterValue, c.CounterDateTime
from counterDetails d
join counterData c
on d.CounterID = c.CounterID
Now you’ll never be caught out again in a crisis.