7 Replies Latest reply: Sep 2, 2014 1:52 AM by Stephen D'Amore RSS

    Temp DB - To Cache or Not to Cache

    Mark Harrison Adventurer

      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
          Jonathan Graves Newbie

          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
            Todd Moore Adventurer

            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
              Jeff Taylor Newbie

              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
                Stephen D'Amore Wayfarer

                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