Cache Money

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.