REG:SQL metrices

1

Hi…
I want to display a panel with Buffer cache hit ratio.
I know the query for that
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base’
AND OBJECT_NAME = ‘SQLServer:Buffer Manager’) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio’
AND a.OBJECT_NAME = ‘SQLServer:Buffer Manager’

But how can i pull this off in grafana.
When i try to query it shows a lot of measurements with different names from sql tables.
I am new to grafana ,so excuse me if it sounds stupid.?

Additional info…I am using a telegrf agent and using sql server plugin.

2

Are you using the MySQL data source? What panel are you using? What is the error ?

3

I am using SQL Server 2008 and telegraf agent to capture the SQL performance metrics.
i added new panel and if i try to query if is showing different table(measurements) names.

For example:
If i want to query Buffer cache hit ration in SQL server,i do that as below:
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base’
AND OBJECT_NAME = ‘SQLServer:Buffer Manager’) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio’
AND a.OBJECT_NAME = ‘SQLServer:Buffer Manager’

If i want to do the same in grafana how do i query Influxdb ,coz i dont know that what is the measure name for that.
I am using the telegraf sql server plug which garafana provide.

4

InfluxDB does not support joins, please look at InfluxDB docs for details on the InfluxDB query language

5

okay i have checked Influxdb querying ,but it saws how to query to the measurements collected by telegraf.
I want to know how i can create a measurement which corresponds to the above query.
Considering i am new to GO language how can i achieve the above query in my question.

6

InfluxDB is not a SQL database so it’s impossible.

What measurements do you have in InfluxDB?

7

I have;

Performance counters : 1000+ metrics from sys.dm_os_performance_counters
Performance metrics : special performance and ratio metrics
Wait stats : wait tasks categorized from sys.dm_os_wait_stats
Memory clerk : memory breakdown from sys.dm_os_memory_clerks
Database size : databases size trend from sys.dm_io_virtual_file_stats
Database IO : databases I/O from sys.dm_io_virtual_file_stats
Database latency : databases latency from sys.dm_io_virtual_file_stats
Database properties : databases properties, state and recovery model, from sys.databases
OS Volume : available, used and total space from sys.dm_os_volume_stats
CPU : cpu usage from sys.dm_os_ring_buffers

so we can only use whatever the telegraf collects.?
There is no way to manipulate or visualize a better insight?
coz what i was thinking was to add a table in the grafana dashboard to visualize sp_whoisactive query result with all the columns of the sp_whoisactive as Tags.

8

You can only query InfluxDB fields and measurements that telegraf collects, and the InfluxDB query language is very limited

9

How do we know then this measurement name gets this particular data ?