Total Pageviews

Thursday, March 23, 2017

The fastest and agentless performance data collector with Dashboard for Windows and SQL Server

PerfCollector:
PerfCollector provides thorough insight into Windows and SQL Server performance data in real-time combined with extreme reliability and accuracy.  Data collection process is light-weight, blazing fast and efficient. It is designed to collect more than 200 crucial performance metrics from multiple servers remotely. You will have the remarkable ability to monitor any server in real-time and to analyze the health status of any Windows and SQL Server instance at any given time.

The performance metrics collector process (PerfCollector.exe) is a command line executable and it is a native machine code; it is smaller, faster and has no dependencies. And of course, it has nearly no impact on the monitored server while collecting metrics data.

Some potential uses of this tool:

1.      Real-time performance monitoring and research.
2.      Historical performance data review and trend analysis.
3.      Load testing of Windows and SQL Server.

Download Link:
Version 1.2 - April 2017

The zipped files contain the following:
1.      How-To guide,
2.      Three dashboards for Grafana,
3.       PerfCollector.exe along with the license file.

Sample Dashboard:



What is PerfCollector?
PerfCollector is a WMI and T-SQL based metrics data collection process written in Free Pascal. It has no external dependencies and can be executed on any Windows OS without configuring and deploying to a remote server.

What does PerfCollector do?
It collects a predefined set of metrics data in a regular user defined interval (for example every 5 seconds) from one or more servers running on either Windows 2003 and above or SQL Server 2005 and above. Data collection can be performed remotely and the collection method is completely agentless and configuration free.

The collected data will be written sequentially to an influxDB (www.influxdata.com) database, which is one of the fastest Open Source Time Series Database.

How to use PerfCollector?
On the command prompt, simply execute the “PerfCollector.exe”, “PerfCollector.exe ?”, “PerfCollector.exe /?” or “PerfCollector.exe Help”. It will show the syntax and examples on how to use the performance collector.

Command line Syntax:
PerfCollector has a number of parameters, some are mandatory and some are optional. The following is the syntax and parameters of PerfCollector.:

Syntax:
PerfCollector.exe [WinServer\SQLServer] [Milliseconds] [Influxdb Server:Port] [InfluxDB]\[Print] [WIN][SQL] ["Environment Tag\Application Tag"].
Where each argument represents the following:

PerfCollector.exe is the name of the executable.

[WinServer\SQLServer] is the target Windows Server along with the SQL Server named instance. For a default instance of SQL Server providing the parameter value of “SQLServer”, the instance name is not required.

[Milliseconds] is the data collection interval. The recommended interval is 5000 milliseconds however it can be as low as 100 milliseconds.

[Influxdb Server:Port] represents the InfluxDB Server name along with the port number.

[InfluxDB]\[Print] is the database name of influxDB. [Print] is optional for displaying collected data on the console window. Please note that the database name is case sensitive.

[WIN][SQL] is the type of metrics data PerfCollector will collect from a target Server. If “WIN” is specified, then the PerfCollector collects only Windows Performance metrics. If “SQL” is specified, then the PerfCollector collects Windows and SQL Server Performance metrics. If nothing is specified, the PerfCollector will collect Windows metrics by default.

["Environment Tag\Application Tag"] is the tag to identify a target server. The ‘Environment Tag’ will be used for the environment that the target server belongs to and the ‘Application Tag’ is the name of the application that the target server is serving. One or both can be specified.

Example of using PerfCollector command line tool.
The InfluxDB has been installed on “SrvInFlux201” and the port of the influxDB is 8086. We would like to collect data from two Windows Servers (WinFin001, HLT092) and three SQL Server instances where one SQL Server is a named instance (FinSrv1092, HR093, SrvGE981\SQL2014). Streaming data will be then inserted into an influx database: “metricsdb”. The following are various ways we can collect data from each server:

Data Collection - Windows Only:
(a)    Collect Windows metrics every 2 seconds and insert collected data into metricsdb.

PerfCollectorexe WinFin001 2000 SrvinFlux201:8086 metricsdb

(b)   Collect Windows metrics every 5 seconds and insert collected data into metricsdb. Add Server identification with tag.
PerfCollectorexe WinFin001 5000 SrvinFlux201:8086 metricsdb Win “Production\Finance”

(c)    Collect Windows metrics every second and insert collected data into metricsdb.
PerfCollector.exe HLTSrv62 1000 SrvinFlux201:8086 metricsdb Win “Production\Health watch System”

Data Collection – Windows and SQL Server:

(a)    Collect Windows and SQL Server metrics from FinSrv1092 where SQL Server has been installed as a default instance. Insert data into influx database without tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb SQL

(b)   Collect Windows and SQL Server metrics from FinSrv1092 where SQL Server has been installed as a default instance. Insert data into influx database with tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb SQL “HR\Employee CRM”.

(c)    Collect only Windows metrics from FinSrv1092 without SQL Server metrics, though, SQL Server has been installed as a default instance. Insert data into influx database with tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb Win “HR\CRM Management”.

(d)   Collect Windows and SQL Server metrics from SrvGE981, where SQL Server has been installed as a named instance (SQL2014). Insert data into influx database with tag.
PerfCollector.exe SrvGE981\SQL2014 5000 SrvinFlux201:8086 metricsdb       SQL“GEO\Geographic Survey App.

Permission to Run PerfCollector:
PerfCollector only supports AD Accounts to collect metrics data. The AD Account which is used to execute PerfCollector.exe needs appropriate permission on the target (or remote) Windows and SQL Servers.

(a)   To collect Windows Server Metrics: WMI Read permission on the target (remote) Windows Server.
(b)   To collect SQL Server Metrics: VIEW SERVER STATE and SELECT permission on master and msdb database on the target (remote) SQL Server.

To collect SQL Server metrics data, both WMI and SQL Server permission is required.

Connectivity testing for data collection process from a Target Server:
The data collection process can be tested without having influxDB installed. To test the connectivity and to display the collected metrics on the screen for a remote server, for example “WinSrv2016” or “DellSRV016”, execute any of the following commands:

Connectivity Test - Windows:
C:\Metrics\PerfCollector.exe WinSrv2016 1000 NoServer:NoPort NoDB\Print WIN
Connectivity Test - SQL Server:
C:\Metrics\PerfCollector.exe DellSRV016 1000 NoServer:NoPort NoDB\Print SQL

Collecting real-time metrics data Using Windows Task Scheduler:
To collect metrics data from multiple remote servers, Windows Scheduler Task can be used seamlessly. A Multiple Windows Scheduler task needs to be created to collect data from multiple servers. The AD Account which will be executing each task must have WMI and SQL Server permission on the target server:

1.      Security option: Make sure that the option “When running the task, use the following account” for the Windows Account which executes the PerfCollector has WMI and SQL Server access permission on the target/remote server.
2.      Create a Windows Scheduler Task with the following options:
(a)      “Run whether user is logged on or not”;
(b)     “Run with highest privileges”;
(c)      “Hidden” and
(d)     “Configure for:” choose the preferable server option.
3.      In the “Edit Action”, input the following information:
(a)      Insert the location along with the PerfCollector.exe. Example:

C:\PerfCollector\PerfCollector.exe

(b)     “Add arguments (optional)”: Insert all the parameters without the executable name. Example: SrvGE981\SQL2014 5000 SrvinFlux201:8086 metricsdb SQL “GEO\Geographic Survey App”.

(c)      “Start in (optional)”, insert the location of the executable.

4.      Schedule the task to run every XXX minutes, and make sure “Do not run a second instance” from the Settings of the task has been selected.
5.      You may consider choosing “disable all task history” for smaller task histories.

False positive Alert of Anti-Virus:
You may receive a False Positive Warning from select Anti-Virus software. In that case, you may consider excluding the executable file (PerfCollector.exe) from being scanned.

License and Execution Restriction:
There are three different license modes of the PerfCollector. Regardless of the licensing mode, PerfCollector will always collect real-time metrics data:

1.      Self-Generated License: When the PerfCollector runs for the first time and if no license file has been found in the folder, it will generate a self-license which can be used to collect metrics data from only one local or remote machine for 30 days. After 30 days, PerfCollector will stop collecting all metrics data. You may delete the existing license file (PerfCollector.lic), then re-run the PerfCollector.exe which will cause a new license to generate for another 30 days.

2.      Courtesy License: The license already provided with the PerfCollector will be able to collect metrics data from 5 remote servers for 30 days. After 30 days, PerfCollector will stop collecting all metrics data.

3.      HOST based License: This type of license is specific for a Host and can collect metrics data from any number of servers for an unlimited time. However, this license can’t be used on a different Host.

4.      Enterprise License: This type of license can be used on any host for any number of target remote servers without any time limitations. With this license, PerCollector.exe can be used on any server that resides in the domain.

InfluxDB and Grafana Installation and importing dashboard:



9 comments:

  1. Hello,

    I like and follow your work on this. I install InfluxDB, Grafana and PerfCollector on my laptop. I am not able to get it running correctly :-(

    I run PerCollector only Windows and seems running correctly but when I query InlfuxDB with show measurements, I get nothing.

    When I try to run PerfCollector with Win and SQL i get an error.

    Hope you could give me some help.

    Thanks

    ----

    PerfCollector.exe localhost\l2012 5000 localhost:8086 metricsdb SQL

    Current license utilization: 1 out of 5
    Number of granted target servers whih this license = 5
    License was granted to CIRX153P


    Windows and SQL Server Performance Data Collector
    Sarjen Haque, sarjen@gmail.com
    http:\\sqltouch.blogspot.ca
    Version : 1.1.0
    Date : March 2017

    Microsoft\ Windows\ 10\ Entreprise;\ (10.0.14393);\ 64\ bits;\ LENOVO;\ 20BFS0F400;\ Processor:1\ Core:4;\ Memory:8\ 00\ GB
    Microsoft\ SQL\ Server\ 2012\ (SP3-CU6-GDR);Enterprise\ Edition\ (64-bit);\ SP3\ (11.0.6567.0);\ Standalone
    EAccessViolation:Access violation

    ReplyDelete
  2. Hi, please make sure that the port 8086 is open in the windows firewall.
    For the second error, please check WMI classes do exits for sql server.

    ReplyDelete
  3. Hello this post is great, but i am triying to install the metricdb before import the performance metric but i hace an error on the configuration of the metricsdb.
    the error say:
    Unknown error
    InfluxDB Error: undefined


    ReplyDelete
    Replies
    1. First install influxdb and create the database, then run the PerfCollector.

      Delete
  4. i create the database metricdb on inflixDB but after this the dashboard didnt give me nothing, do i have to configure another think on my machine?
    Can you please help me out.
    Thanks

    ReplyDelete
    Replies
    1. Please make sure that the Grafana and its data source has been configured correctly. Note that the InfluxDB data source is case sensitive. You can read www.Grafana.net documentation for influxDB and Grafana configuration.

      Delete
  5. HI Sarjen,
    Tnak you a lot for this loader. Il want to use it rather than telegraf which i use and get data,in order to check the dashbords with it.
    When Launching PerfCollector, no measument is created in metricdb and the perfCollector.log is empty
    Is there any verbose mode for debug?

    thank you
    Mathias

    ReplyDelete
    Replies
    1. Hi
      Tried the same, same problem. No log and no series created in metricsdb.So this does not seem to work. Any news on this problem?

      Delete
  6. Hi Sarjen,

    What the cost on the PerfCollector HOST based License or Enterprise License?

    Thanks,
    Denny-

    ReplyDelete