Microsoft SQL server extension (2.30) pulls information only for ext:tech.MSSQL.AvailabilityPercent

[Yosi_Neuman]

Hi guys,

customer installed MS SQL extension (2.30) connect it to one of their DBs with all the checkboxs marked with V but only get information in ext:tech.MSSQL.AvailabilityPercent metric.

In log we see select with Rows fetched: -1

Any suggestion what we are missing here?

Thanks in advance for your input here 

Yos 

Here is a sample from the log  

2022-08-03 04:42:00.556 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Connection created
2022-08-03 04:42:00.556 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Cursor obtained from connection
2022-08-03 04:42:00.556 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'availability_replica': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Availability Replica' AND instance_name = '_Total'
2022-08-03 04:42:00.560 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.560 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'buffer_manager': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Buffer Manager'
2022-08-03 04:42:00.564 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.566 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'databases': SELECT counter_name, cntr_value, cntr_type, instance_name FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Databases' AND instance_name = 'RA_P2P'
2022-08-03 04:42:00.570 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.573 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'general_statistics': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics'
2022-08-03 04:42:00.578 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.584 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'latches': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Latches'
2022-08-03 04:42:00.587 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.590 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'locks': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND instance_name = 'Database'
2022-08-03 04:42:00.593 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.596 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'memory_manager': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Memory Manager'
2022-08-03 04:42:00.600 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.602 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Executing query 'sql_statistics': SELECT counter_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:SQL Statistics'
2022-08-03 04:42:00.614 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [_execute_queries] Rows fetched: -1
2022-08-03 04:42:00.621 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [calculate] Calculating metrics
2022-08-03 04:42:00.621 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [report] Reporting metrics
2022-08-03 04:42:00.624 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [report] Reported 0 metrics in this call
2022-08-03 04:42:00.626 UTC INFO    [Python][1683775089632440610][test][140129062606592][ThreadPoolExecutor-0_13] - [query] Executed query() method 4214 times so far

[vagiz_duseev]

Hello!

Please try our new release of the extension which is now installable from the Hub.
https://www.dynatrace.com/support/help/technology-support/dynatrace-extensions/dynatrace-extension-r...

I suspect it won't fix the problem but it's easier to update and install. And the version you are using will become deprecated approximately at the end of this year.

From the logs it looks like the extension is able to connect but fails for query proper dynamic management views and no rows are returned for our monitoring queries (-1 rows indicates that). I would double check the credentials to make sure the user you connect with is allowed to query these views.

[Yosi_Neuman]

@vagiz_duseev 

Thanks for your the prompt answer!

Can you please elaborate a bit for guy with no knowledge regarding MSSQL rights like me, which credentials we need to ask from MSSQL admins?

We showed them (MSSQL admins) the Database user permissions requested and the answer was it granted so we need some more guidance how to approach them in order to get the right credentials 🤔 

Thanks again 

Yos 

[vagiz_duseev · Accepted solution]

Sure. If MS SQL admins say that the proper permissions are granted you could test these credentials via an IDE such as DBeaver (which is something I personally use). Try to connect to the instance and run a query like

select * from sys.dm_os_performance_counters;

If that works, then the issue might be related to the fact the your installation is using named instances. The new extension takes care of that and is able to automatically query the proper instance.