Array Setup and Networking
1748104 Members
4959 Online
108758 Solutions
New Discussion

Re: Temp DB - To Cache or Not to Cache

 
SOLVED
Go to solution
marktheblue45
Valued Contributor

Temp DB - To Cache or Not to Cache

I'm a little unsure as to whether a volume or datastore created for SQL Temp DB's is Cache worthy. Since it's a temp DB I figure that it will receive read request briefly but not long term. Any suggestions folks?

Regards,

               Mark.

7 REPLIES 7
jgraves129
New Member

Re: Temp DB - To Cache or Not to Cache

Hi Mark,

As you correctly point out, the data in tempdb is transient.  Since it is short lived, this data often does not receive any significant benefit from being place in SSD.  As a matter of fact, depending upon how tempdb is used it can consume a significant amount of the cache available on the array.  In these scenarios, the cache churn generated by tempdb can adverse impact the cache hit rate of other volumes and received no significant benefit itself. Generally speaking, it is best to disable caching on tempdb volumes (both data and log files).

If disabling caching on the tempdb volume does adversely impact your application performance, you can quickly revert to a cached policy. 

tmoore106
Trusted Contributor
Solution

Re: Temp DB - To Cache or Not to Cache

If I were a betting man, I would not cache it. That said, one nice thing about Nimble is the ability to test it both ways in a non-disruptive manner. Configure a custom performance policy, with caching enabled and run some testing (longer better than shorter). Then edit the policy to disable cache and repeat your testing. If you don't mind sharing your results, please do.

Nick_Dyer
Honored Contributor

Re: Temp DB - To Cache or Not to Cache

+1 on what Todd said.

In a POC that was ran down under in ANZ it was found that their TempDB served quite high cache hits (>80%) even though caching was disabled; reason being any read to the TempDB typically caused the array to hairpin and read the IO from NVRAM or DRAM, as that was where the data was at the time.

I think it was detailed somewhere on NimbleConnect, i'll see if i can find it.

Nick Dyer
twitter: @nick_dyer_
marktheblue45
Valued Contributor

Re: Temp DB - To Cache or Not to Cache

yep, DRAM is used. You get cache hits on Log volumes. There was a good post on here about the mechanics of this.

Thanks Again,

Mark.

aspnerd82
New Member

Re: Temp DB - To Cache or Not to Cache

What is your ms latency for tempdb when using or not using cache? How much did you fill up your tempdb during each test?

Is there a way to only use cache for say tempdb lun?

marktheblue45
Valued Contributor

Re: Temp DB - To Cache or Not to Cache

Latency broadly the same read 1.5 ms. But cache utilisation has dropped of from 90+ percent on the whole array to 60 ish percent. Tempdb was poisoning the cache. To early to make any major conclusions. Got a lot of vm migrations this weekend to distort findings

sdamore72
New Member

Re: Temp DB - To Cache or Not to Cache

Hello All,

Sorry for getting to the party so late, but I have something hopefully useful to say regarding tempdb.


I/O to tempdb SHOULD be mostly sequential for things like sorts, (order by, group by, etc.), as well as for what is called version store. Version store involves copying the before image of a data page involved in an update transaction to another page in temdb in order to maintain transaction read consistency. Think of it as like a COW snapshot, only  at the db level and not the storage level. Because version store would tend to string these before image pages together contiguously, then that should result in a sequential workload for tempdb. Then we have temp tables, which are also created in tempdb. Depending on how the application creates and then accesses these temp tables, access to these could be random in nature or sequential. Since most code I have seen tends to iterate through, and probably even sort through the entire results set for the temp table, then both the creation of and selection from a temp table should be mostly sequential.


The short story is that most of what is detailed above should result in sequential I/O, however I have experimented with this a bit and seen varying results when specifying a log versus a data perf policy. As has been said earlier, if a SQL Server data policy is chosen, but access to the volume is sequential, then the array will handle that well due to the way the caching algorithms work. However, for direct attached iSCSI volumes I would lean towards choosing a log perf policy for a log and tempdb vol, and a data per policy for data and index files regardless of the db platform as a rule of thumb.


One more thing worth mentioning is that lots of customers use VMDK's or CSV's for both data and log files, and rarely do we hear of performance issues, so the array just handles it.


Hopefully this was helpful in some way.



Best Regards,
Steve