Case study:
How many times have we heard that tempdb should be on a fast cache drive? Thus to start with we had put tempdb files along with mdf files on fast drive. While the ldf's for the databases were on separate drive. This worked very well till SE's started writing queries to process data for internal use growing the tempdb mdf. Or when we had to load months of data in a database to resolve some issue growing its mdf out of limits and that choked that drive.
Thus we decided to shift tempdb to its own fast cache read write drive T:. On an average our tempdbs required only about 5 GB space. But once in a while shot unto 100 GB during some manual processing. What our Operations team decided was to let tempdbs of various servers each be on T: drive on the servers and share the same pool of space common to all. That pool being about 100GBs. The logic behind this was, when data was processed for one database, not necessarily other databases would also process that amount of data as well. So at a time even if one tempdb grew a lot it could use up 60-80GB and shrink back when the processing is over.
Thus that saved a lot of space given to each tempdb on each server if they were kept separate and had to be given 100GB each. It also helped since tempdb being on separate drive did not cause a bottle neck on the mdf drives, thus not slowing down other production queries. This resulted in faster data retrievals for client applications.
No comments:
Post a Comment