• Forums
    • Public Forums
      • Community Connect
      • Dynatrace
        • Dynatrace Open Q&A
      • Application Monitoring & UEM
        • AppMon & UEM Open Q&A
      • Network Application Monitoring
        • NAM Open Q&A
  • Home /
  • Public Forums /
  • Application Monitoring & UEM /
  • AppMon & UEM Open Q&A /
avatar image
Question by Christopher L. · Aug 30, 2012 at 08:50 PM · plugins

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!

Comment

People who like this

0 Show 0
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

8 Replies

  • Sort: 
  • Most voted
  • Newest
  • Oldest
avatar image

Answer by Derek A. · Mar 03, 2015 at 12:58 AM

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.

Comment

People who like this

0 Show 0 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image

Answer by PTC S. · Mar 02, 2015 at 06:35 PM

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.

Comment

People who like this

0 Show 0 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image

Answer by Andreas G. · Feb 28, 2015 at 02:43 AM

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

Comment

People who like this

0 Show 3 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image Vanesa I. · May 05, 2015 at 08:32 PM 0
Share

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

avatar image Andreas G. ♦ Vanesa I. · May 06, 2015 at 06:03 PM 0
Share

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?

avatar image Jian C. Vanesa I. · Jun 16, 2015 at 03:05 AM 0
Share

hi vanesa

i got the same error as you, did you find a way to resolve it, thanks 

avatar image

Answer by Vanesa I. · Feb 28, 2015 at 12:40 AM

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

Comment

People who like this

0 Show 0 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image

Answer by Dayesh K. · Dec 24, 2013 at 01:33 AM

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

Comment

People who like this

0 Show 5 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image Andreas G. ♦ · Dec 24, 2013 at 09:45 AM 0
Share

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

avatar image Dayesh K. Andreas G. ♦ · Dec 24, 2013 at 07:09 PM 0
Share

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

avatar image Andreas G. ♦ Dayesh K. · Dec 25, 2013 at 05:18 PM 0
Share

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

avatar image Keerti S. Andreas G. ♦ · Jun 29, 2015 at 10:02 PM 0
Share

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?

avatar image Graeme W. Keerti S. · Jun 29, 2015 at 11:20 PM 0
Share

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

avatar image

Answer by Rajesh J. · Oct 24, 2012 at 12:33 AM

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

Comment

People who like this

0 Show 2 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image Andreas G. ♦ · Oct 24, 2012 at 12:59 AM 0
Share

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

avatar image Rob V. · Oct 24, 2012 at 01:00 AM 0
Share

Our Windows Performance Monitor plugin has to run on a Windows machine.

Rob

avatar image

Answer by David R. · Oct 11, 2012 at 08:24 PM

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 
 

 

Comment

People who like this

0 Show 3 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

avatar image Andreas G. ♦ · Oct 11, 2012 at 09:34 PM 0
Share

Hi David

THANKS for sharing this. I will cross link this answer to the plugin download page.

Andi

avatar image Lopes D. · Oct 28, 2013 at 08:42 PM 0
Share


This was very useful just now, thanks David

avatar image Kristof R. · Oct 28, 2013 at 08:50 PM 0
Share

That is a great tip! Thanks for that!

avatar image

Answer by Andreas G. · Aug 30, 2012 at 09:00 PM

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

Comment

People who like this

0 Show 0 · Share
10 |2000000 characters needed characters left characters exceeded
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 10 attachments (including images) can be used with a maximum of 50.0 MiB each and 250.0 MiB total.

How to get started

First steps in the forum
Read Community User Guide
Best practices of using forum

NAM 2019 SP5 is available


Check the RHEL support added in the latest NAM service pack.

Learn more

LIVE WEBINAR

"Performance Clinic - Monitoring as a Self Service with Dynatrace"


JANUARY 15, 3:00 PM GMT / 10:00 AM ET

Register here

Follow this Question

Answers Answers and Comments

11 People are following this question.

avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image

Related Questions

Silkperformer 15.5 plugin for dynatrace 6.5

Any plugin/way to see vmware hardware parameters like battery, fan, processors?

License Count Plugin support for AppMon 7.0

Top Process Monitoring Plugin - unable to execute monitor

Data power plugin not working! No data in dashboard.

Forum Tags

dotnet mobile monitoring load iis 6.5 kubernetes mainframe rest api dashboard framework 7.0 appmon 7 health monitoring adk log monitoring services auto-detection uem webserver test automation license web performance monitoring ios nam probe collector migration mq web services knowledge sharing reports window java hybris javascript appmon sensors good to know extensions search 6.3+ server documentation easytravel web dashboard kibana system profile purelytics docker splunk 6.1 process groups account 7.2 rest dynatrace saas spa guardian appmon administration production user actions postgresql upgrade oneagent measures security Dynatrace Managed transactionflow technologies diagnostics user session monitoring unique users continuous delivery sharing configuration alerting NGINX splitting business transaction client 6.3 installation database scheduler apache mobileapp RUM php dashlet azure purepath agent 7.1 appmonsaas messagebroker nodejs 6.2 android sensor performance warehouse
  • Forums
  • Public Forums
    • Community Connect
    • Dynatrace
      • Dynatrace Open Q&A
    • Application Monitoring & UEM
      • AppMon & UEM Open Q&A
    • Network Application Monitoring
      • NAM Open Q&A