My understanding is that TempDB does not deduplicate or compress well so it is best to leave those features turned off. Does anyone know if that advice applies to TempDB volumes on an AFF system? Any other suggestions/thoughts on best practices for SQL Server volumes?
Solved! See The Solution
3 REPLIES 3
TempDB is temporary workspace and very dynamic.
The lifetime of objects in this space is very short term.
Both dedupe and compression won't get you a win in space savings compared to the CPU and IOs they'd cost.
Best practice would be to leave them off, even in an AFF environ.
I hope this response has been helpful to you.
At your service,
Eugene E. Kashpureff, Sr.
Independent NetApp Consultant http://www.linkedin.com/in/eugenekashpureff
Senior NetApp Instructor, Fast Lane US http://www.fastlaneus.com/
(P.S. I appreciate 'kudos' on any helpful posts.)
Re: TempDB on AFF
2016-11-09 11:34 AM
I have a slightly different opinion for TempDB than does Eugene - although I will stress that he is quite right (as he is in essentially all things NetApp) in the general case.
The specific use case where I found AFF with inline dedupe and inline compression on TempDB to be useful was for a large scale data warehousing application. Total TempDB allocation was 5TB total spread across 6 database instances. This application supported significant multi-table joins and other actions that chewed up a lot of temporary space.
AFF did not address write performance, since writes are acknowledged from NVRAM of course. So SSD as the write target is not a big deal. CPU use was higher as expected to process the inline efficiency but not a concern as it was an AFF8080 with CPU cycles to burn. Disk usage typically held to 50% of actual size through efficiency. Of course all instances never managed to use their max temp space together either, but at scale I could easily rely on a 2.5TB committed storage size required for the TempDBs during allocation and sizing planning.
At higher scale, if you have the CPU, the cost of the flash (along with the storage tax) can be higher than not using the efficiencies - the difference between hardware for AFF8080 vs AFF8040 was much smaller than would be the cost of the SSD for TempDB as this grows further to storage it native size. Application is currently limited in that we can't make use of non-NSE disks, so 3.8TB SSDs are not an option and the smaller ones were way higher cost per GB.
In this same use case if I were limited to AFF8040 as the controllers I likely would not have used inline efficiencies on the TempDBs because CPU wasn't as plentiful. It's a "soft" cost that can be considered but hard to quantify during purchase cycles without a really good idea of exactly how your applications and databases behave.
Hope this helps.
Senior Systems Engineer | cStor
NCIE SAN Clustered, Data Protection
Kudos and accepted solutions are always appreciated.
Eugene and Bob,
Thank you both very much for your thoughts! We don't have super-large TempDB volumes in our environment so I will leave the efficiency features off, however Bob's analysis is good to know about in case that changes.