- Community Home
- >
- Storage
- >
- HPE Nimble Storage
- >
- Array Setup and Networking
- >
- Re: Temp DB - To Cache or Not to Cache
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2014 05:42 AM
08-26-2014 05:42 AM
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.
Solved! Go to Solution.
- Tags:
- cache
- DB
- sql temp db
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2014 08:27 AM
08-26-2014 08:27 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2014 09:11 AM
08-26-2014 09:11 AM
SolutionIf 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2014 01:52 PM
08-26-2014 01:52 PM
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.
twitter: @nick_dyer_
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-27-2014 06:12 PM
08-27-2014 06:12 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-28-2014 11:57 AM
08-28-2014 11:57 AM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-28-2014 12:29 PM
08-28-2014 12:29 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-02-2014 01:52 AM
09-02-2014 01:52 AM
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