I’ve talked about how the fastest query is the one you never make, and why when you’re scaling up a database application, you probably want to start caching query results in the app tier. Caching means:
- Less licensing costs for SQL Server
- Lower CPU usage both for query execution and for compilation time
- Lower locking/blocking overhead (even NOLOCK requires a schema stability lock, for example)
- Less demands on query workspace memory
But how do you go about finding which queries to cache?
Find your most frequently executed queries, then ask 3 questions about them.
Run sp_BlitzCache @SortOrder = ‘executions’, or look on the Plans Executions tab of the Consultant Toolkit. Look at the executions per minute column – and on seriously high-volume servers, I’ll usually divide those numbers by 60 to get executions per second, which drives the point home even more. With those queries in mind, ask:
Question 1: How often does this data change? Often, we’re dealing with single-table queries that fetch a value from a global config table. The data just doesn’t change often, and could easily be fetched just once at application server startup. If we needed to change a config value, worst case, we could restart the app servers gradually and have them fetch the updated data.
When you’re not sure how often data changes, look at the table’s contents:
@DatabaseName = 'mydbname',
@SchemaName = 'dbo',
@TableName = 'myconfigtable';
In particular, you’re looking at the statistics on each index to show you how many writes the table has had recently. It can be tough to discern exactly when this number resets: it varies based on your version, patch level, failovers, restores, etc., but just the ratio of reads vs writes can be really enlightening. For example, at a recent client, we noted that we were fetching employee phone numbers millions of times per day, when only a handful of changes had been made over the last several months.
Question 2: Will out-of-date data really hurt? Some data absolutely, positively has to be up to the millisecond, but you’d be surprised how often I see data frequently queried out of the database when the freshness doesn’t really matter. For example, one client discovered they were fetching live inventory data on every product page load, but the inventory number didn’t even matter at that time. During sales, the only inventory count that mattered was when the customer completed checkout – inventory was checked then, and orders only allowed if products were still in inventory then.
Question 3: How much data are we talking about? Is it a config table with only a handful of rows, or is it a product list with millions of variations? The less data involved, the easier it is to cache, especially if we’re trying to cache in memory on the application servers. The more data involved, the more it’s going to hurt if we try to duplicate that data on every app server – and the more we need to start investigating a dedicated caching system like Redis, AWS ElastiCache, Azure Cache, or Google Cloud Memorystore.
Which leads me to the second of my caching truths:
- The fastest query is the one you never make, and
- The first query you cache is the hardest.
When you implement caching for the very first time in an application, you’re going to be torn about a million little decisions. What caching platform do you use? How do you set it up? How do you test that it’s working? How much redundancy do you need? How will you add the code to your application?
But when you see the first results – millisecond-level response times for frequently accessed data, and lower loads on your database server – you’ll be sold. You’ll cache first, and ask questions later. That’s when you’ll start running into problems with cache invalidation, but for now, find your most frequently executed queries and ask those 3 questions.