Splunk DB Connect: Is there documentation on how to create a dashboard and visualizations for long running SQL queries?

[pprakash2]

Hi,

May I please know if there is any documentation to create dashboards, graphs etc, for desired SQL queries? I've connected the app to an MSSQL database. I want to create a dashboard for long running queries. Any suggestions?

[ryanoconnor]

You might benefit from using the following add-on:

https://splunkbase.splunk.com/app/2648/#/overview

This should give you plenty of information regarding performance of your MSSQL database.

[pprakash2]

Thanks ryanconnor. I have a sql query which gives me list of long running queries. Below is the query.

Can I represent this output in line graph, if yes, how can I do it? Please help me

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_seconds DESC;

[tmuth_splunk]

It might help if you can post a csv of the output of the above query for those of us who don't have an active SQL Server DB handy.