SQL Server Monitor Plugin
Your SQL Server Monitor Plugin looks pretty cool. We are looking at it. Couple quick questions if you have a minute:
1. Does it install anything on the SQL Server machine?
2. Does it need a DB ID and password?
3. Does it need ksh ability or other remote access to the SQL Server machine
We have issues of course with all three in production!
This plugin works without the need to connect to the SQL server directly, but just queries the various perfmon counters. Whatever account the dynaTrace Collector is running as will need to have access to perfmon on the SQL server. I believe at minimum, the Performance Monitor Users group.
Hi, I have a service account which has the access to start and stop dynatrace collector and server on the PSM server(it is a different machine). The database server is on a different machine and SQL server is connected using SQL server authentication and not windows authentication. I am not able to configure my plugin in this case. Can someone help me in doing it.
Hi
The SQL Server Monitor Plugin defines a set of Windows Performance Counters that "most" SQL Servers provide. It seems that in your case your SQL Server instance doesnt provide some of these metrics. MAybe they are called differently or they have been removed. AS the SQL Server Monitor Plugin really just defines the template for these counters you can simply edit these Measures in your Windows Performance Monitor definition and either delete these measures (if your SQL Server doesnt provide them) or change the credentials (if they are called differently).
How can you tell whether these measures are available under a different name or not available at all? I always just open the Windows Performance Monitor tool that comes with your windows installation. I do this on the Collector where you actually execute the Monitor. Then I have a look at the list of Measures that SQL Server provides. In your case you want to look at the SQL Server::General STatistics measures. have a look on whether these metrics that you are missing, e.g: User Connections exist. Maybe they are in a different counter group. Once you found them validate if these metrics actually deliver values. I also do this using the built-in windows performance monitor tool. If it works I simply take the credentials such as "Counter", "Instance", "Object" and copy these values over to my dynatrace measure definition. You can find these measure definitoins on the Measure Tab of your Windows Performance Monitor in your System Profile
I hope this helps
Andi
Thanks Andreas for your answer, but unfortunately it is not helping.
Windows measurements work but SQL Monitor Measures don't work, all measurements return "null":
Access: Full Scans/sec: null
Access: Page Splits/sec: null
Buffer: Cache hit ration: null
Buffer: Checkpoint pages/sec: null
Buffer: Lazy writes/sec: null
Buffer: Page life expectancy: null
Buffer: Page reads/sec: null
Buffer: Page writes/sec: null
Database: Log Cache Hit Ration: null
Database: Log Flushes/sec: null
Errors: Errors/sec: null
Locks: Average Wait Time (ms): null
Locks: Number of Deadlocks/sec: null
Locks: Requests/sec: null
Stats: Batch Requests/sec: null
Stats: SQL Compilations/sec: null
Stats: Transactions: null
Stats: User Connections: null
But measurements and names are correct on collector and on application machine, I have checked that as you suggested (see attached PerfMon.rar).
Any further idea to fix this?
Thanks!
Vanessa
The monitoring plugin will create a log file on the collector where it is executed. can you check the log file through the System Information Dashlet and see if there is anything in there?
Hello!
I need help.
SQL Monitor fails for the defined measures with an error, but however it provides some measures that are not listed in the SQL Monitor. How can I fix this?
Thanks!
Vanessa
Schedule Details
Name: xxxxxxxx
Status: scheduled
Description:
Type: Windows Performance Monitor
Last Run: 06:21:53 (EST)
Last Run Result: incomplete
Next Run: 06:31:53 (EST) Duration (last run): < 1s
Schedule: Every 10 Minutes
Execution Target: dynaTrace Collector@#####
Plugin Active: active
System: PTC Integrity Server
Result Status: success
Result: Last polling execution terminated successfully
Message: Executing some performance queries caused errors
Detailed message: SQLServer:General Statistics
User Connections
all instances:Unable to get Value. Please check if the host is reachable and the requested Performance Counter is available. Detailed error message: The returned data is valid.
(System error code: 0x0)
Transactions
all instances:Unable to get Value. Please check if the host is reachable and the requested Performance Counter is available. Detailed error message: The returned data is valid.
(System error code: 0x0)
SQLServer:Buffer Manager
Lazy writes/sec
all instances:Unable to get Value. Please check if the host is reachable and the requested Performance Counter is available. Detailed error message: The returned data is valid.
(System error code: 0x0)
...
Retrieved measurements: Access: Full Scans/sec: null
Access: Page Splits/sec: null
Buffer: Cache hit ration: null
Buffer: Checkpoint pages/sec: null
Buffer: Lazy writes/sec: null
Buffer: Page life expectancy: null
Buffer: Page reads/sec: null
Buffer: Page writes/sec: null
Committed Bytes In Use: 24.0
Current Disk Queue Length: 0.0
Database: Log Cache Hit Ration: null
Database: Log Flushes/sec: null
Disk Reads/sec: 13.0
Disk Writes/sec: 11.0
Errors: Errors/sec: null
Free Diskspace: 104407.0
Free Memory: 1.3158852E7
Idle Time: 96.06400000000001
Locks: Average Wait Time (ms): null
Locks: Number of Deadlocks/sec: null
Locks: Requests/sec: null
LogicalDisk Free Percentage: 21.944
Pages Input: 156.0
Pages Output: 0.0
Pages/Sec: 156.0
PhysicalDisk Read: 633473.0
PhysicalDisk Write: 405951.0
Process Count: 63.0
Process Memory Usage: 6.380183552E9
Processor Interrupts/sec: 26537.419
Processor Time: 3.919
Run Queue, current: 0.0
Stats: Batch Requests/sec: null
Stats: SQL Compilations/sec: null
Stats: Transactions: null
Stats: User Connections: null
User Time: 2.102
Gives the error "failed to initialize perfmon library. case:Unable to initialize PerformanceCounter Object" The database to monitor is SQL Server 2012
Any help appreciated
are you able to get the perf counters using the windows performance monitor tool? The error message indicates that the perf counters that dynaTrace wants to query are not available on your machine. Maybe these perfcounter names have changed for SQL 2012 - or - it is also possible that the perfcounters are not correctly registered/installed. First step is to double check with the tool that comes with Windows
The perfcounters are available and are registered
The message also says about to check if the host is reachable
All the server are within the same LAN and are accessible to each other
We are using the latest dynaTrace client and server
Here is one thing to consider: the monitor is executed on the selected Collector and will try to query the data from the Host that you have listed in the list of Hosts. So - you need to make sure of two things
a) the Collector runs on a windows machine and the windows account the Collector Services runs with needs to have remote registry access in order to query the remote registry keys of your SQL Server
b) verify that the hosts you have configured in your list of hosts are reachable from your Collector
Andi
How to check if the Windows collector has remote registry access? Also, in case remote registry is not setup; can you provide the steps to set it up?
Keerti,
There's fairly complete documentation on what to do and what to check at: How To Retrieve Performance Metrics with Windows Performance Monitor
Let is know if this works for you!
-- Graeme
Is it possible to use a monitoring collector running on on Linux with the same domain user which has access to the perfmon on the Windows SQL Server machine.
Thanks,.
Rajesh
This is not possible. The Windows Performance Monitor needs to run on a Windows Collector as it uses a windows library to query this data
Hello,
some additional remarks, as it took us quite some time to get the SQL Server plugin working. And as most folks don't use the Windows perf. counters on a daily basis perhaps these tips might be also useful to others.
When executing the monitor we got on most systems this error:
Detailed message: SQLServer:Buffer Manager
Lazy writes/sec
all instances:Invalid data from PerformanceCounters. Please check if the host is reachable and the requested Performance Counter is available. Detailed error message: The returned data is valid.
(System error code: 0x0)
and so on for "Page reads/sec", "Pages writes/sec" etc.
The quickest solution is to execute:
on the target Windows SQL server: typeperf -q > counter.txt
from a remote server: typeperf -q -s <target-server> > counter.txt
This will list all available perf. counters and especially their object name.
Standart SQL installation e.g. returns:
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Lazy writes/sec
But if you have a SQL named instance or upgraded SQL Express to full SQL you might get:
MSSQL$SQLEXPRESS:Buffer Manager\Lazy writes/sec
MSSQL$2008:Buffer Manager\Lazy writes/sec
Here the monitor fails, because the object name is incorrect.
Depending on the amount of standard or non standard SQL servers, you might want to use one SQL monitor for those standard instances or another for your named instance.
For the non standard SQL instance update the object name “SQLServer” with the correct one e.g. MSSQL$2008 or MSSQL$SQLEXPRESS in this example.
By changing the object name like this we got the SQL perf monitor working on all customer systems.
Some hopefully useful commands to query the target Windows/SQL server:
- Open DOS/CMD window as the monitoring user:
runas /user:
\
CMD
- List available performance counters:
typeperf -q -s > counter.txt
- List all counters for an object (here for the "Buffer Manager"):
typeperf -q "SQLServer:Buffer Manager" -s
-Query the counter "Buffer cache hit ratio" from the object "Buffer Manager":
typeperf -sc 2 "SQLServer:Buffer Manager\Buffer cache hit ratio" -s
Hi David
THANKS for sharing this. I will cross link this answer to the plugin download page.
Andi
Hi Chris
The plugin (SQLServer Monitoring Plugin) extends our Windows Performance Monitor with additional pre-configured Windows Performance Counters. That means
a) nothing gets installed on the SQL Server machine -> we just query the SQL Server Perf Counters that SQL Server exposes
b) No Username/pwd required. All you need is a Collector that runs under a Windows User Account that can access the Windows Performance Counters on your SQL Server Machine
c) It only needs remote registry access from the Collector to your SQL Server Machine in order to access Windows Performance Counters
For more information on our Windows Performance Monitor check out Windows Performance Monitor
Andi
