
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
We considering implementing multiple tempdb's to deal with a tempdb system table contention problem. I've read what I can from the documentation and white-papers (maybe I missed something), but there doesn't seem to be any recommendation on how to size them. BTW, we're currently running 12.5.0.3, but will be migrating to12.5.1 withing a few months. Our current tempdb is 2.0 Gb. It's that size because we were given some extra disk space with a recent hardware upgrade. We didn't do any careful calculation (is there one?) to determine the required tempdb size. We figured if our old 1.0 Gb tempdb was big enough, increasing it to 2.0 Gb couldn't hurt and will allow for future growth (without having to fight operations for more disk space <-- a sad story). We have one particular application that is experiencing slowdowns because of contention on syscolumns (among others). There are about 35 users of this application, but only about 20 of those 35 are doing the job function that experiences the contention. The other 15 use a different function in the application, which doesn't seem to be affected. We were thinking about creating three (no reason, it's just more) additional tempdb's, which we would bind that application to. The space for the additional tempdb's would be taken from the existing 2.0 Gb tempdb, so it will be shrinking in size. Here are the questions we have: How do you determine the correct number of additional tempdb's for this application? Do you just add two, bind the application, and see if the problem goes away. If not, add a third, and so on? How do we know what size to make the additional tempdb's. Since we're only binding one application to them, we're thinking they should all be the same size. The new tempdb's would have to be a minimum size of, the maximum amount of space any one user of the application would need. That would likely NOT be big enough for concurrent access. When we bind the application to the new tempdb's, do we unbind from the default tempdb? It seems like that should be the approach we take, but I don't remember reading that anywhere. How do you determine the maximum size that our current tempdb is utilized? I recall a query based on OAM pages that showed usage at a point-in-time, but that was long ago (4.9.2). Maybe there's something better now. Is it possible to determine the maximum amount of tempdb that any one user uses? That would be interesting to know for sizing the new tempdb's. Thanks. Richard
| <-- __Chronological__ --> | <-- __Thread__ --> |