I was reading the Voice of the DBA and it referred to a post by Brent Ozar about caching at the database level. After discussing it with my homey, he thought it sounded a lot like memoization (I like to say it with a 3 stooges voice, mem moy Zay Shun). According to wiki, Memoization is a specific case of optimization, which seems to be what we are doing when we cache the results of a stored procedure.
Since I’m doing some projects utilizing RamDisk, I took things a little further and thought, what If we used this same method and loaded the Cache database onto a RamDisk? This seems like another perfect use for a RamDisk. Quite often people are using them for tempDb, so why not for Cache databases as well?
Setting this up was extremely simple, Brent has laid out a skeleton sp that allows you to update the cache and read from it, however implementation is left up to you. I created the database and log file on the RamDisk and set a maximum size. I was able to modify Brent’s template and add the check for database existence, as well as table existence. If the database doesn’t exist, just return the data like normal. If the database exists, but the table doesn’t, then go ahead and create the table and cache the data. I promise I will start using AdventureWorks and not the LoadLab BeatMe naming structures after this 🙂
USE [LoadLab] GO /****** Object: StoredProcedure [dbo].[usp_GetSomeStuff_fromCacheMoneyHomey] Script Date: 2/27/2014 1:36:19 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_GetSomeStuff_fromCacheMoneyHomey] @StuffId INT AS BEGIN /* *Check if the caching database exists. If not, then just get the results without caching * In case of a problem with RamDisk, this will keep things working as normal */ IF NOT EXISTS ( SELECT NAME FROM MASTER.dbo.sysdatabases WHERE ( '[' + NAME + ']' = 'CacheMoneyHomey' OR NAME = 'CacheMoneyHomey' ) ) BEGIN SELECT @StuffId ,beatMeName ,beatMeEmail FROM dbo.BeatMe WHERE beatMeId = @StuffId END /* *Check if the table exists. If not, then use select into and create it. This could be expanded to create indexes */ ELSE IF NOT EXISTS ( SELECT * FROM CacheMoneyHomey.sys.sysobjects WHERE NAME = 'GetSomeStuff' ) BEGIN SELECT @StuffId AS beatMeId ,beatMeName ,beatMeEmail INTO CacheMoneyHomey.dbo.GetSomeStuff FROM dbo.BeatMe WHERE @StuffId = beatMeId END /* *Check if the data is cached. If so, then MAKE IT RAIN */ ELSE IF NOT EXISTS ( SELECT * FROM CacheMoneyHomey.dbo.GetSomeStuff WHERE beatMeId = @StuffId ) BEGIN INSERT INTO CacheMoneyHomey.dbo.GetSomeStuff ( beatMeId ,beatMeName ,beatMeEmail ) SELECT @StuffId ,beatMeName ,beatMeEmail FROM dbo.BeatMe WHERE beatMeId = @StuffId END SELECT * FROM CacheMoneyHomey.dbo.GetSomeStuff WHERE beatMeId = @StuffId END GO
There are so many cool ideas that can be spawned from this tactic. When using RamDisk it seems like you can exert much more control over what is kept in memory. You could cache heavily reported on data sets and have the business define expiration/invalidation rules. You can write smart routines that keep track of result sets and keep the most frequently used sets in cache. If you can’t tell I’m extremely excited to play with this (playing with RamDisk in general is so much fun…hopefully I don’t go blind). The OzarUnlimited team had presented caching as a recipe for awesome during the San Diego training and I was intrigued. Most of the caching techniques I have researched all required application level modifications, but Brent has presented an idea that can be transparent to the application. I’m currently trying to find a neat way to benchmark the differences between using the disk based Cache db and the RamDisk based Cache db.