Example of how to measure database query response time?

[sloshburch]

Does anyone have examples of how to use Splunk to measure database query response time?

[sloshburch · Accepted solution]

The Splunk Product Best Practices team helped produce this response. Read more about example use cases in the Splunk Platform Use Cases manual.

For more information about this example see Database Module KPIs and thresholds in the Splunk IT Service Intelligence Modules manual.

Set up this example use case to find the average amount of time, in ms, it takes for query requests to return responses for each database host.

Load data

How to implement: This example use case depends on databases logs.

Install the appropriate add-ons for the platforms that exist in your environment.

• Splunk DB Connect
• Splunk Add-on for Microsoft SQL Server
• Splunk Add-on for Oracle Database

You can find installation and configuration instructions in the Details tab of each Splunkbase item. Additional configuration details are available in the Database Module configurations manual has additional installation and configuration details.

Best practice: For more granular results with scripted inputs, you can increase the frequency at which the input runs using the interval setting in inputs.conf. Running the input more frequently consumes more storage, and running it less frequently uses less, which can affect license consumption. The default interval is 60 seconds. See Scripted Input in the input.conf topic of the Splunk Enterprise Admin manual.

Best practice: For all of the data inputs, specify a desired target index to provide a more sustainable practice for data access controls and retention models. By default, Splunk collects the data in the default index named main.

Get insights

Run the following search.

index=* tag=database tag=performance tag=query

| timechart span=5min avg(response_time) BY host

Best practice: In searches, replace the asterisk in index=* with the name of the index that contains the data. By default, Splunk stores data in the main index. Therefore, index=* becomes index=main. Use the OR operator to specify one or multiple indexes to search. For example, index=main OR index=security. See About managing indexes and How indexing works in Splunk docs for details.

Help

The Database Module troubleshooting section in the Splunk IT Service Intelligence Modules manual lists troubleshooting resources you can apply to this example use case.

If no results appear, it may be because the add-ons were not deployed to the search heads, so the needed tags and fields are not defined. Deploy the add-ons to the search heads to access the needed tags and fields. See About installing Splunk add-ons in the Splunk Add-ons manual.

For troubleshooting tips that you can apply to all add-ons, see Troubleshoot add-ons in the Splunk Add-ons manual.

For more support, post a question to the Splunk Answers community.