Getting started with snorql

• What to expect from this post?
• Know your tools
  - Dropwizard
  - Kotlin
  - Maven
• Setting up your local machine
• Setting up your application
  - Creating a new project
  - Adding dependencies
  - Build & Compile
  - Project Structure
  - Code
• Build & Run the Application
• Next Steps
• FAQs

In the world of engineering, where we all love to solve problems, there is one problem that has been bugging us for ages. You guessed it right, it’s database issues! We’ve all been there, right? Unintended peaks, increased latency, and those sleepless nights trying to debug the root cause of these issues. Oh wait, let’s not forget about the endless cycle of throwing hardware at the problem, only to find out it didn’t solve a thing.

Well, the situation wasn’t so different at Udaan either & that’s when some of us decided to take matters into our own hands and create a tool that would help to preemptively tackle these problems.

Enter …

What is snorql?

snorql is an open-source project developed at Udaan aimed at monitoring & diagnosing database-related problems using SQL metrics.

It provides visibility into how every database is performing, so you can stop playing the guessing game and start optimising your databases with ease. And let me tell you, this added visibility has been a game-changer for our organisation. It has boosted the accountability of every team.

The results speak for themselves. Thanks to snorql,

• We’ve seen increased performance across 50+ databases.
• Reduced cloud costs by more than 60%.
• Increased developer productivity
• Reduced debugging time

So, if you’re tired of playing the guessing game with your databases, it’s time to join the Snorql revolution and start optimising with ease!

If my preamble has ignited your excitement and you’re eager to get started, you’ve come to the right place. But in case you need further convincing, be sure to check out the fantastic article titled snorql — diagnosing databases like a boss.

What to expect from this post?

Snorql exposes multiple metrics like Active Queries, Blocked Queries, Index Stats, Database Growth, etc… complete list here. For this post, we will be picking up Active Queries. Active Queries is a valuable tool for SQL Server users who need to monitor and optimise the performance of their queries in real-time.

In this post, I am going to show you how to integrate snorql into a simple Kotlin Dropwizard application(You can replicate the same in any other Java Framework as well). At the end of this tutorial, your application will expose a RESTful API using which you will be able to find out the live query statistics for any given database.

Know your tools

For those already familiar with Kotlin, Dropwizard, and Maven feel free to skip this section.

Dropwizard

Dropwizard is a light Java framework for building RESTful web services. Dropwizard collects together stable libraries, such as Jersey, Jackson, and JDBI, into a lightweight package. For more details visit https://www.dropwizard.io/en/latest/.

Fun fact: Dropwizard was inspired by Ruby on Rails, a popular web application framework in the Ruby programming language. The creators of Dropwizard wanted to bring the same level of productivity and ease of use to the Java ecosystem.

Kotlin

Kotlin is a programming language that supports functional and object-oriented programming created by JetBrains, a software development company based in Russia. The syntax and concepts of Kotlin are similar to C#, Scala, and Java. For more details visit https://kotlinlang.org/

One fun fact about Kotlin is that its name is actually derived from an island near St. Petersburg, Russia, called Kotlin Island. The creators of Kotlin chose this name because many of them live in or around St. Petersburg and the island is a well-known landmark in the area.

Maven

Maven is a build automation and management tool for Java. With Maven, it is easy to define the building of the software and the dependencies.

Fun fact: Maven was created by Jason van Zyl in 2002 while he was waiting for a flight at Chicago’s O’Hare Airport.

Setting up your local machine

You will find a lot of articles & YouTube videos for the same, however, I’ve attached some quick references

• Installing & Setting up maven — https://www.freecodecamp.org/news/how-to-get-started-with-maven-36851d8cfd96/
• Installing Kotlin — https://www.oreilly.com/library/view/kotlin-cookbook/9781492046660/ch01.html
• Installing IntelliJ — https://www.jetbrains.com/help/idea/installation-guide.html

Setting up your application

This section assumes you have already set up Maven, Kotlin & IntelliJ (or any other IDE with support for maven projects).

Creating a new project

1. Click on File –> New –> Project –> Maven. You will see the following popup

2. Click on Next. You will be presented with the below screen. Give the project location & the project name. Click on Finish.

3. Your IDE will open the pom.xml file with the project having the following structure.

Adding dependencies

Before getting started with the implementation we need to add specific dependencies required for the project in the pom.xml file. Let’s do it together.

We start by defining the versions of Kotlin & Dropwizard in the properties tag.

<properties>
	  <maven.compiler.source>8</maven.compiler.source>
	  <maven.compiler.target>8</maven.compiler.target>
	  <kotlin.version>1.4.10</kotlin.version>
	  <kotlin.compiler.languageVersion>1.4</kotlin.compiler.languageVersion>
	  <kotlin.compiler.jvmTarget>1.8</kotlin.compiler.jvmTarget>
	  <dropwizard.version>2.0.13</dropwizard.version>
</properties>

Then proceed by adding the kotlin-stdlib-jdk8 & dropwizard-core under the dependencies section as shown below:

<dependencies>
    <dependency>
        <groupId>io.dropwizard</groupId>
        <artifactId>dropwizard-core</artifactId>
        <version>${dropwizard.version}</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.kotlin</groupId>
        <artifactId>kotlin-stdlib-jdk8</artifactId>
        <version>${kotlin.version}</version>
    </dependency>
</dependencies>

We will be adding some more dependencies in a similar fashion. Don’t worry, I will also explain the need for the same.

jdbi3

We will need this dependency to make connections to the database. I am using the jdbi3 here. This will work with any version of JDBC/JDBI.

Properties:

<jdbi.version>3.37.1</jdbi.version>

Dependencies:

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-core</artifactId>
    <version>${jdbi.version}</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>${jdbi.version}</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-kotlin-sqlobject</artifactId>
    <version>${jdbi.version}</version>
</dependency>

mssql driver

We will need to add this for the application to be able to successfully connect to the SQL server database. Omitting this will give you the infamous java.sql.SQLException: No suitable driver found exception. Here, I will be using the snorql-extensions module which gives us pre-defined metrics corresponding to SQL server, hence we are going with this driver.

There exists another module, snorql-framework which is the basic framework of the underlying tool. I will be exploring that in an upcoming article where we will know how to leverage this framework for different databases like Postgres, MySQL, etc…

Properties:

<mssql.version>9.2.1.jre8</mssql.version>

Dependencies:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>${mssql.version}</version>
</dependency>

snorql

Well, can’t omit this!

Properties:

<snorql.version>0.5.6</snorql.version>

Dependencies:

<dependency>
    <groupId>com.udaan.snorql</groupId>
    <artifactId>snorql-extensions</artifactId>
    <version>${snorql.version}</version>
</dependency>

We have successfully added all the dependencies.

Build & Compile

The next step is to specify the source directories in <sourceDirectory> and in <testSourceDirectory> in order to compile the source code. We will also configure the Kotlin plugin required to compile our source code.

Add the code to pom.xml as shown after the dependencies section

<dependencies>
	.....
</dependencies>
<build>
    <sourceDirectory>${project.basedir}/src/main/kotlin</sourceDirectory>
    <testSourceDirectory>${project.basedir}/src/test/kotlin</testSourceDirectory>
    <plugins>
        <plugin>
            <artifactId>kotlin-maven-plugin</artifactId>
            <groupId>org.jetbrains.kotlin</groupId>
            <version>${kotlin.version}</version>
            <executions>
                <execution>
                    <id>compile</id>
                    <goals>
                        <goal>compile</goal>
                    </goals>
                </execution>
                <execution>
                    <id>test-compile</id>
                    <goals>
                        <goal>test-compile</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

Our complete pom.xml looks like this.

As soon as you do this you will see kotlin highlighted in red. This is because the path does not exist as of now, which brings us to the next section → project structure

Project Structure

This is my project structure. You can go with the same to start with & later customise according to your requirement.

Code

Step 1: We start by creating a config.yml file inside src/main/resources . We can define parameters in this configuration file which will be deserialised to an instance of our configuration class. We need to add at least one parameter, otherwise the Dropwizard framework will throw an error during compilation. Let’s go ahead & add this:

environment: local

Step 2: Next, we define our Configuration class, which extends the Dropwizard Configuration class. The property that we added in the config.yml file will be deserialized here. Create a file name AppConfig.kt inside the path src/main/kotlin/com/hawkeye/configuration & add the following code:

package com.hawkeye.configuration

import com.fasterxml.jackson.annotation.JsonProperty
import io.dropwizard.Configuration


class AppConfig(@JsonProperty("environment") val environment: String): Configuration()

Step 3: We create JdbiFactory class where we will be connecting to a given database

Step 4: Next, we create BootstrapSnorqlApplication.kt file inside the path src/main/kotlin/com/hawkeye & add the following code:

package com.hawkeye.core

import org.jdbi.v3.core.Jdbi
import org.jdbi.v3.core.kotlin.KotlinPlugin
import org.jdbi.v3.sqlobject.SqlObjectPlugin
import org.jdbi.v3.sqlobject.kotlin.KotlinSqlObjectPlugin

class JdbiFactory {

    /**
     * Return instance of JDBI with all plugins installed
     * @param dbName Name of the DB to connect
     */
    fun getInstance(dbName: String): Jdbi {
        return Jdbi.create(getConnectionStringByDatabaseName(dbName))
            .installPlugin(SqlObjectPlugin())
            .installPlugin(KotlinPlugin())
            .installPlugin(KotlinSqlObjectPlugin())
    }

    private fun getConnectionStringByDatabaseName(dbName: String): String {
        return when(dbName) {
            "db-prod" -> "jdbc:sqlserver://<server-name>:<port>;database=$dbName;user=<username>;password=<pwd>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
            else -> ""
        }
    }
}

Here we have used a private function getConnectionStringByDatabaseName() to get the connection string. Ideally, this should come from a vault or secret.

Step 5: Now we need to implement the Connection interface that snorql-framework exposes. Let’s call it SqlServerConnection & it will hold the methods required for snorql to interact with user's database. For this post implementing the run() method alone would be suffice. We will explore the others in a separate detailed blog post.

Create a SqlServerConnection.kt file in the path src/main/kotlin/com/hawkeye/core & add the below code.

package com.hawkeye.core

import com.udaan.snorql.framework.metric.Connection
import com.udaan.snorql.framework.models.HistoricalDatabaseResult
import javax.inject.Inject

class SqlServerConnection @Inject constructor(private val jdbi3Factory: JdbiFactory) : Connection {
    override fun <T> run(databaseName:String, query: String, mapClass: Class<T>,params: Map<String, *>): List<T> {
        return if(params.isNotEmpty()){
            jdbi3Factory.getInstance(databaseName).withHandle<List<T>, Exception> { handle ->
                handle.createQuery(query).bindMap(params).mapTo(mapClass).toList()
            }
        } else{
            jdbi3Factory.getInstance(databaseName).withHandle<List<T>, Exception> { handle ->
                handle.createQuery(query).mapTo(mapClass).toList() as List<T>
            }
        }
    }

    override fun storeData(storageBucketId: String, columns: List<String>, rows: List<List<Any>>) {
        TODO("Not yet implemented")
    }

    override fun getHistoricalData(
        storageBucketId: String,
        metricId: String,
        databaseName: String,
        columns: List<String>,
        paginationParams: Map<String, *>,
        params: Map<String, *>
    ): HistoricalDatabaseResult {
        TODO("Not yet implemented")
    }
}

Step 6: We are done with all the utility classes, now we can focus on our API endpoint.

snorql exposes a lot of useful SQL metrics that can be integrated & used with your application. For this post, we will be picking the metric that will be most relevant to anyone who has ever used a database — Active Queries.

Active Queries, also known as live query statistics, is a feature in SQL Server that allows users to monitor the progress of their queries in real time. It provides information on the status of a query, such as the percentage of completion, elapsed time, and estimated time remaining, as well as the execution plan used by SQL Server to process the query.

For that, create a SnorqlMetricsResource.kt file inside src/main/kotlin/com/hawkeye/resources & add the below code.

package com.hawkeye.resources

import com.fasterxml.jackson.annotation.JsonProperty
import com.udaan.snorql.extensions.performance.PerformanceEnums
import com.udaan.snorql.extensions.performance.models.ActiveQueryInput
import com.udaan.snorql.extensions.performance.models.ActiveQueryResult
import com.udaan.snorql.framework.metric.SqlMetricManager
import com.udaan.snorql.framework.models.IMetricRecommendation
import com.udaan.snorql.framework.models.MetricPeriod
import javax.ws.rs.Consumes
import javax.ws.rs.POST
import javax.ws.rs.Path
import javax.ws.rs.Produces
import javax.ws.rs.container.AsyncResponse
import javax.ws.rs.container.Suspended
import javax.ws.rs.core.MediaType
import javax.ws.rs.core.Response

@Path("/snorql")
class SnorqlMetricsResource {
    /*
    * Fetches list of active-queries metric for a [activeQueryMetricInput]
    */
    @POST
    @Path("activeQueries")
    @Consumes(MediaType.APPLICATION_JSON)
    @Produces(MediaType.APPLICATION_JSON)
    fun getActiveQueryMetric(
        input: Input,
        @Suspended asyncResponse: AsyncResponse
    ) {

        val activeQueryInput = ActiveQueryInput(
            databaseName = input.databaseName,
            metricPeriod = input.metricPeriod,
            metricId = input.metricId
        )
        try {
            val response = SqlMetricManager.getMetric<ActiveQueryInput, ActiveQueryResult, IMetricRecommendation>(
                PerformanceEnums.ACTIVE_QUERIES.getId(),
                activeQueryInput
            )
            asyncResponse.resume(Response.ok(response).build())

        } catch (e: Exception) {
            println("Something went wrong. Error : ${e.message as String}")
            Response.status(500).entity(e.message as String).build()
        }
    }
}

data class Input (
    @JsonProperty("metricId")
    val metricId: String = PerformanceEnums.ACTIVE_QUERIES.getId(),
    @JsonProperty("metricPeriod")
    val metricPeriod: MetricPeriod,
    @JsonProperty("databaseName")
    val databaseName: String
)

Step 7: We are almost done, all that’s left is to implement our Application class to run an instance of our Dropwizard RESTful server. Create a HawkeyeApplication class inside & add the following code:

package com.hawkeye

import com.google.inject.AbstractModule
import com.google.inject.Guice
import com.hawkeye.configuration.AppConfig
import com.hawkeye.core.SqlServerConnection
import com.hawkeye.resources.SnorqlMetricsResource
import com.udaan.snorql.extensions.SQLCommonMetrics
import com.udaan.snorql.framework.metric.SqlMetricManager
import io.dropwizard.Application
import io.dropwizard.setup.Environment

class HawkeyeApplication: Application<AppConfig>() {
    companion object {
        @JvmStatic fun main(args: Array<String>) = HawkeyeApplication().run(*args)
    }

    override fun run(appConfig: AppConfig, environment: Environment) {
        environment.jersey().register(SnorqlMetricsResource::class.java)
        registerSQLMetrics()
    }

    private fun registerSQLMetrics() {
        val sqlServerConnectionInstance =
            Guice.createInjector(SqlClientModule()).getInstance(SqlServerConnection::class.java)
        SqlMetricManager.setConnection(sqlServerConnectionInstance)
        SQLCommonMetrics.initialize()
    }
}

class SqlClientModule : AbstractModule()

We have registered our SnorqlMetricsResource class & instantiated the SqlServerConnection class so that the application can recognise it.

Build & Run the Application

We are ready to run our HawkeyeApplication

To do that add the configuration as shown below:

Notice that we have added server src/main/resources/config.yml in the program arguments. This tells the Dropwizard app to run as a server and against the location of the configuration file provided.

If everything goes well you should see something like this in the terminal. If not & you are shouted at with some errors, don’t worry I’ve got you covered for that too. Head over to the FAQ section & check the errors/resolutions that I faced.

Head over to Postman or any other similar tool & test the API. Click on Import -> Raw text -> Copy paste the curl call given below -> Click on Continue -> Click on Send

curl --location --request POST 'localhost:8080/snorql/activeQueries' \
--header 'Content-Type: application/json' \
--data-raw '{
    "databaseName": "db-prod",
    "metricId": "performance_activeQueries",
    "metricPeriod": "REAL_TIME",
    "recommendationRequired": true
}'

Postman Request & Response:

Here, we have queried to see all the active queries for the database db-prod. The response is divided into 3 parts:

metricInput

This returns the input provided by the user with default values in optional fields.

"metricInput": {
    "from": null,
    "to": null,
    "recommendationRequired": false,
    "metricId": "performance_activeQueries",
    "metricPeriod": "REAL_TIME",
    "databaseName": "db-prod"
  }

metricOutput

This gives you the list of queries running at this moment in your database along with all the relevant parameters associated with them. This could be useful in the following scenarios:

• When you’re trying to debug the increased latency on your database & you want to check if there are any long-running queries that have been hogging your CPU by looking at elapsedTime. These long-running queries can be caused by a variety of issues, such as inefficient query plans, suboptimal indexing, or poorly written queries. In addition to this, we can also use this to analyze the distribution of query execution times across the database and take proactive steps to prevent issues before they occur.
• When you are in a state where the database looks hung or a process is not proceeding, checking for database blocking makes a great deal of sense & you can figure out the exact sessionId blocking this query with the help of blockedBy.
• The openTransactionCount parameter is used to monitor the number of open transactions in a database at any given time. If this count is consistently high, it may indicate that there are transactions that are not being committed or rolled back in a timely manner. This can lead to issues such as resource contention, increased locking, and deadlocks, which can negatively impact database performance and availability. By using the openTransactionCount parameter in conjunction with other performance metrics, such as query execution time and query wait time, we can correctly identify the queries that need our attention.

The above scenarios are just some instances that one might encounter & how having a quick visibility of the active queries would help us identify & resolve issues quickly.

"metricOutput": {
    "result": {
	"queryList": [
	    {
		"sessionId": 627,
		"status": "running",
		"blockedBy": 0,
		"waitType": null,
		"waitResource": "",
		"percentComplete": 0,
		"waitTime": "00:00:00",
		"cpuTime": 2,
		"logicalReads": 19,
		"reads": 0,
		"writes": 0,
		"elapsedTime": "0d 00:00:00",
		"queryText": "<?query --  \\n\\nSELECT DISTINCT ffi.shipment_group_id, so.extra_data, so.order_type, cac.amount_in_paisa as consumed_amount_in_paisa FROM fulfillment_line_items ffi JOIN seller_order_line ol on ol.order_line_id = ffi.order_line_id JOIN seller_order so on so.order_id = ol.seller_order_id LEFT OUTER JOIN collected_advance_consumable cac on cac.consumed_for = ffi.shipment_group_id WHERE ffi.shipment_group_id in (@P0)\\n\\n--?>",
		"storedProc": "",
		"command": "SELECT",
		"loginName": "udaan",
		"hostName": "order-processing-daemons-6fdfb74677-86xtx",
		"programName": "order-processing-daemons-6fdfb74677-86xtx/order-mgmt",
		"hostProcessId": 0,
		"lastRequestEndTime": "2023-02-24 10:36:18.283",
		"loginTime": "2023-02-24 10:32:52.12",
		"openTransactionCount": 0
	     }
	 ]
    },
    "recommendation": null
}

metadata

This section gives us details about the queries used by snorql to generate the above output. This also comes with a brief description & quick reference to the official Microsoft documentation.

The supportsHistorical & minimumRepeatInterval are part of a feature where snorql helps us to record & store the result set of a query output at a given interval allowing us to revisit past data. This will help in postmortems, triaging issues & finding anomalies. This feature calls for a separate detailed post of its own.

"metadata": {
    "underlyingQueries": [
        "SELECT sessionId=s.session_id\\n ,status=r.STATUS\\n ,blockedBy=r.blocking_session_id\\n ,waitType=r.wait_type\\n, waitResource=r.wait_resource\\n, waitTime=CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8)\\n, cpuTime=r.cpu_time\\n, logicalReads=r.logical_reads\\n, r.reads\\n ,r.writes,\\n r.percent_complete as percentComplete\\n,elapsedTime=CONVERT(varchar, (r.total_elapsed_time/1000 / 86400))+ 'd ' +\\n CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8)\\n ,queryText=CAST((\\n         '<?query --  ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (\\n  (\\n CASE r.statement_end_offset\\n WHEN - 1\\n THEN Datalength(st.TEXT)\\n  ELSE r.statement_end_offset\\n END - r.statement_start_offset\\n) / 2\\n) + 1) + CHAR(13) + CHAR(13) + '--?>'\\n ) AS XML)\\n, storedProc=COALESCE(\\n QUOTENAME(DB_NAME(st.dbid)) + N'.' + \\n QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' +\\n QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ''\\n)\\n  --,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan\\n, r.command\\n ,loginName=s.login_name\\n ,hostName=s.host_name\\n ,programName=s.program_name\\n ,hostProcessId=s.host_process_id\\n ,lastRequestEndTime=s.last_request_end_time\\n ,loginTime=s.login_time\\n ,openTransactionCount=r.open_transaction_count\\n FROM sys.dm_exec_sessions AS s\\n INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id\\n CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st\\n --OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if you want to see plan \\n ORDER BY r.blocking_session_id"
    ],
    "referenceDocumentation": [],
    "description": "",
    "supportsHistorical": true,
    "minimumRepeatInterval": "10"
}

Congratulations!! You have successfully integrated snorql into your project.

If you want to check out the entire code, visit here at https://github.com/lakshmi-udaan/hawkeye

Next Steps

What we did?

Having obtained the requisite data, we set out to create a powerful visualisation tool. To this end, we augmented the APIs with a user-friendly UI layer and produced the SQL Dashboard, which provides both a comprehensive birds-eye view of all databases and detailed, granular views of individual ones. Our organisation’s developers rely on it heavily for SQL-related tasks. Regrettably, it is not currently open-source, but if you’re interested, I’m happy to discuss further. Here is a sneak peak of the same.

SQL Diagnostics & Monitoring Dashboard at udaan built using snorql

What you can do?

Now that you know how to use snorql, it’s only a matter of time before you unlock its full potential & start boosting your database performance exponentially.

You can find the list of all available metrics here. You can get these metrics by simply altering the parameters being passed inside SqlMetricManager.getMetric() as we did while creating the API endpoint for Active Queries here.

In my next post, we will deep dive into how we can use some of these metrics to enhance your database productivity to 10x.

Thanks for reading!

FAQs

• Could not find artifact com.udaan.snorql:snorql-framework:pom:0.5.6 in xyz
  Solution: Do a mvn clean install. This will clean whatever created from the previous build, then build your project and add the jars to your local maven repository (.m2\repository)
• If you are prompted with this error java.sql.SQLException: No suitable driver found
  Solution: You have to load jdbc driver. You have missed out on adding mssql-jdbc

References:

https://udaan-com.github.io/snorql/

https://medium.com/engineering-udaan/snorql-diagnosing-databases-like-a-boss-54f2e8fbf57a