Windows Performance Monitor is an invaluable tool when you don’t have external enterprise monitoring tools and you need to face performance problems, whether you have a web/application server, a mail server or a database server.
But what I don’t personally like of it is what you get in terms of graphing. If you schedule and collect a big amount of performance metrics you will likely get lost in adding/removing such metrics from the graphical interface.
What I’ve done long time ago (and I’ve done again recently after my old laptop has been stolen 🙁 ) is to prepare a PHP script that parse the resulting CSV file and generate automatically one graph for each metric that could be found.
Unfortunately, most of Windows Sysadmin between you will disagree that I’ve done this using a Linux Box. But I guess you can use my script if you install php inside cygwin. The other tool you need, is rrdtool, again I use it massively to resolve my graphing needs.
How to collect your data
Basically you need to create any Data Collector within the Performance Monitor that generates a log file. You can specify directly a CSV file (Log format: Comma separated) or generate a BLG file and convert it later (Log format: Binary). System dumps are not used, so if you use the standard Performace template, you can delete it from your collection.
Remember that the more counters you take, the more the graph generation will take. The script does not run in parallel, so it will use only one core. Generally:
Where (Speed factor) is depending on both the CPU speed and the disk speed because of the huge number of syncs required to update several thousands of files. I’ve tried to reduce the number of rrdupdates by queuing several update values in a single command line and I’ve noticed an important increase of performances, but I know it’s not enough.
rrdtool graph/root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.png--start"1366721762"--end"1366724017"--width453DEF:ds0=/root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.rrd:value:LAST:step=5LINE1:ds0#0000FF:"IPv4\Datagrams Received Unknown Protocol" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:" " COMMENT:" Maximum " GPRINT:ds0max:"%6.2lf" COMMENT:" Average " GPRINT:ds0avg:"%6.2lf" COMMENT:" Minimum " GPRINT:ds0min:"%6.2lf"
Now it’s done!
The script generate a folder with the name of the server (LUDO in my example) and a subfolder for each class of counters (as you see in Performance Monitor).
Inside each folder you will have a PNG (and an rrd) for each metric.
Important: The RRD are generated with a single round-robin archive with a size equal to the number of samples. If you want to have the rrd to store your historical data you’ll need to modify the script. Also, the size of the graph will be the same as the number of samples (for best reading), but limited to 1000 to avoid huge images.
Would be nice to have a prepared set of graphs for standard graphs with multiple metrics (e.g. CPU user, system and idle together) and additional lines like regressions…
Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command prompt.
However, things are changing fast after Microsoft released the Powershell some years ago. It’s surprising, now Windows powershell support new best of breed features like aliases and pipelines. 😀
Today Microsoft itself recommends Windows Core installations instead of the full ones, and also SQLServer 2012 comes with a lot of new Commandlets to manage your server.
So I’ve decided to move my first steps in the Powershell world and I’ve created a script for a customer that installs and configure a SQL2008 with a single Powershell script.
The commented command is to get the installed features after the installation. No really need to display it, it works really well.
Dynamically prepare a configuration file
The unattended installation needs some parameters prepared in a configuration file.
This is likely where you will change most of your stuff depending on your standards:
Components, paths, service accounts, you can change everything or modify the script to accept also this variables as parameters.
The full documentation about filling the configuration file is on the MSDN:
Off course you’ll need an installation media downloaded from the Microsoft customers site with the correct License Keys and mounted somewhere. (remember the $sourceDir parameter?) I’ve decided to change the path in the directory containing the media and then change it back.
The Service Pack installation has been a little more painful, normally would be simple but actually the powershell prompt is returned immediately after firing the command. So, to wait it, I’ve had to figure out the name of the process (is the executable name without the file extension .exe), get its process id and wait for that process:
By default SQLServer starts listening on a dynamic port. If you have a default and you want to configure it without opening the configuration manager, you can do it with this snipplet that I’ve copied from sirSql (thank you for sharing this).
# change the TCP port at the end of the installation
My recent move to Switzerland has kept me a little busy.I’ve applied last August for a consulting position at Trivadis, in their Lausanne location. I can’t hide I’m quite excited to be part of a great company, but now I need to “ride the wave” and come back with some new posts, especially now that MySQL and Oracle are out with new releases. (Well, Oracle Database 12c will be out SOON, I hope!).
Stay tuned, some interesting content coming soon.
Privacy & Cookies Policy
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.