Overview
My work requires skills balanced somewhat evenly between a DBA and a software engineer. Through this position, I've learned some common gaps of misunderstanding between the two parties. NHibernate is great for the software engineer because it abstracts away most details of the data layer. However, much like any ORM framework, the caveat it carries is that you must pay attention to the way it interacts with your database.
This is the first part of two posts to help enable your NHibernate-based application meet your performance and scalability needs. This is a focus of effectively gathering data and understanding about your bottleneck. The second part can be found here.
First and foremost, it's important to correctly identify your performance issue before considering solutions because you need to be able to quantify the difference in performance your changes have made. Many books and applications already exist to help you load test applications. The following section suggests a distilled methodology using basic tools to identify data-layer bottlenecks.
Tools You Will Need
My work requires skills balanced somewhat evenly between a DBA and a software engineer. Through this position, I've learned some common gaps of misunderstanding between the two parties. NHibernate is great for the software engineer because it abstracts away most details of the data layer. However, much like any ORM framework, the caveat it carries is that you must pay attention to the way it interacts with your database.
This is the first part of two posts to help enable your NHibernate-based application meet your performance and scalability needs. This is a focus of effectively gathering data and understanding about your bottleneck. The second part can be found here.
First and foremost, it's important to correctly identify your performance issue before considering solutions because you need to be able to quantify the difference in performance your changes have made. Many books and applications already exist to help you load test applications. The following section suggests a distilled methodology using basic tools to identify data-layer bottlenecks.
Tools You Will Need
- SQL Server (Developer Edition Preferred): This is the same distribution of your (enterprise/standard) production server except it's built for an isolated development environment. The specific tools we will be using include:
- SQL Profiler- The one and only! We use this extensively to view every database call NHibernate is sending to the database as well as the time and CPU cost each call creates. For those new to SQL profiler, make sure to learn the basics.
- Query analyzer- We use SQL profiler to gather the collection of database calls in our troublesome areas. We use query analyzer to dissect and evaluate the performance of atomic database calls by viewing their compiled execution plans. Optimizing specific queries is an art in itself, reaching beyond the scope of this article. To learn the basics of how to properly read and interpret an execution plan through query analyzer, consider starting here for an introduction.
- .NET profiler: On a higher level, we need some program capable of reporting performance from the perspective of the .NET side of our application, preferably an execution trace showing us method calls and execution times in the form of an execution tree path. From my experience, I can recommend programs such as JetBrains DotTrace, AQTime, and ACT.
- Stress Generator: Feel free to select your favorite third party utility, but the desired functionality you will need is the ability to hammer your target use case or method a variable number of times with a variable number of concurrent requests. To maximize control and focus, consider writing some custom load-testing module if you don't already have one. I've attached an example project in Visual Studio 2005 that should help guide you in the right direction. This is a skeleton console application, complete with a command line, threadpool, and mock setup that we have used in the past. Hopefully, you'll spend less time building load testing code, and more time experimenting!
- Perfmon: An invaluable performance metrics tool already built into your Windows system. All kinds of great counters can yield information, but too much information can muddy the waters. Use the following performance counters when running your scalability tests:
- SQLServer:Locks::Average wait time : The telltale sign of poor scalability, this shows how long a given thread is waiting for a lock to be released before it can complete its job.
- Memory::Page Faults/sec: How often does the database have to read from the disk versus memory? This can give hints into improving your indexing.