Cache Money

That's how I makes it rain

That’s how I makes it rain

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.

johnnycash

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

#wholiveslikethis

#wholiveslikethis

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. For now you will have to settle for CrystalDisk results

R10 15k SaS P420 8 Spindles

R10 15k SaS P420 8 Spindles

RamDisk...Nuff Said

RamDisk…Nuff Said

Leave a Reply

Your email address will not be published. Required fields are marked *