Navigate back to the homepage

Activate Hibernate Query Statistics to investigate your performance issues

Mark Brown
January 31st, 2021 · 1 min read

👋 Introduction

Often, you’re may find yourself wondering why application performance has degraded – and almost always that issue originates from your persistence layer. You can interrogate your query statistics in SQL Server using some of the built in (Transact SQL) views – which have been invaluable before to me investigating issues in customer environments.

Sometimes, however, you don’t have access to the database, or the database technology in use does not provide its statistics (or you don’t know how to access them) – and the good news is, you can enable Hibernate config to log your query statistics!

⚙️ Configuration

By default, the statistic mechanism is disabled. To enable it, you must set the ‘statistics’ property; spring.jpa.properties.hibernate.generate_statistics, e.g;

1spring.jpa.properties.hibernate.generate_statistics=true

To see the statistics printed in the log, you need to enable the logging level. I recently found this logging particularly useful when investigating a test slow down – I enabled this on the test suite via test properties like below (working example can be found on my GitHub);

1@TestPropertySource(properties = [
2 "spring.jpa.properties.hibernate.generate_statistics=true",
3 "logging.level.org.hibernate.stat=debug"
4])

🪵 Log output

With the configuration correctly enabled, the following output can be seen when running a simple count query;

12021-01-31 19:32:48.547 DEBUG 13790 --- [ main] o.h.stat.internal.StatisticsImpl : HHH000117: HQL: select count(*) from CarGarage x WHERE x.id = :id, time: 4ms, rows: 1
22021-01-31 19:32:48.549 INFO 13790 --- [ main] i.StatisticalLoggingSessionEventListener : Session Metrics {
3 16902 nanoseconds spent acquiring 1 JDBC connections;
4 0 nanoseconds spent releasing 0 JDBC connections;
5 528078 nanoseconds spent preparing 1 JDBC statements;
6 2453571 nanoseconds spent executing 1 JDBC statements;
7 0 nanoseconds spent executing 0 JDBC batches;
8 0 nanoseconds spent performing 0 L2C puts;
9 0 nanoseconds spent performing 0 L2C hits;
10 0 nanoseconds spent performing 0 L2C misses;
11 0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
12 12963 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)

As you can see specific query statistics can now be seen as well as the overall session statistics. My query returned 1 row (the count) in 4ms – obviously there is no performance issue in this example, but this is where they would start to show.

The session statistics also give detailed metrics of some low level operations – acquiring JDBC connections, preparing statements, cache hits, etc. If you find yourself in the unfortunate position that your need some of these metrics, they are invaluable!

🪡 Conclusion

When facing performance issues – the more tricks you have up your sleeve the better, and as you can see it is straightforward to enable Hibernate Query Statistics which could provide you with the tools to narrow down that needle in your haystack!

More articles from Mark Brown

Improve query caching with IN clause padding

In my experience, the number one cause of application performance problems is not your application code – it’s your persistence layer. Problems in this area can be caused by many different ‘sins’; Improper entity relationships (think LAZY vs EAGER fetching), Inefficient queries or indeed the cardinal sin – N+1 queries! A lot of the time the application can end up in this way as a result of a lack of awareness of what you are asking the persistence layer to do. Often, you’ll find that simply enabling logging of sql statements will open your eyes to the problem. In fact, when using a data access framework that generates statements on your behalf – it should be mandatory that you inspect the generated statements to ensure both their effectiveness, and their performance.

October 31st, 2020 · 3 min read

Using Testcontainers for Integration Testing

As a follow on from my last post about running sql server in docker, I thought i’d write about something that I have just introduced for the first time into one of my projects – in the hope that some of you feel as passionate as I do about testing your production code (and not just the equivalent in H2-specific syntax 😱). I hope that through this post I can show you how easy it is to include this dependency into your project, and be on your way to having your integration tests running against your production database!

October 20th, 2020 · 2 min read
© 2020–2021 Mark Brown
Link to $https://twitter.com/marktjbrownLink to $https://github.com/mtjbLink to $https://instagram.com/marktjbrownLink to $https://www.linkedin.com/in/mark-brown-9952b4a8/