topic Temp DB - To Cache or Not to Cache in Array Setup and Networking

Temp DB - To Cache or Not to Cache

marktheblue45 — Tue, 26 Aug 2014 12:42:15 GMT

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.

Re: Temp DB - To Cache or Not to Cache

jgraves129 — Tue, 26 Aug 2014 15:27:02 GMT

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.

Re: Temp DB - To Cache or Not to Cache

tmoore106 — Tue, 26 Aug 2014 16:11:10 GMT

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.

Re: Temp DB - To Cache or Not to Cache

Nick_Dyer — Tue, 26 Aug 2014 20:52:50 GMT

+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.

Re: Temp DB - To Cache or Not to Cache

marktheblue45 — Thu, 28 Aug 2014 01:12:46 GMT

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.

Re: Temp DB - To Cache or Not to Cache

aspnerd82 — Thu, 28 Aug 2014 18:57:39 GMT

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?

Re: Temp DB - To Cache or Not to Cache

marktheblue45 — Thu, 28 Aug 2014 19:29:57 GMT

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

Re: Temp DB - To Cache or Not to Cache

sdamore72 — Tue, 02 Sep 2014 08:52:02 GMT

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