1

We have a vendor that has jobs set up to execute a stored procedure that builds a table in TempDB, creates indexes on it, inserts 100s of GB of data into it, does whatever it needs to with the data, then drops the table. It does this process repeatedly throughout the day.

The server is a VM with 16 processors and 120 GB of RAM. TempDB has 8 data files that are 100 GB each and 1 log file that's 50 GB. We determined these sizes after the drive ran out of space a few times in development. I've got the user db data files, user db log files, as well as TempDB files on their own drives which are all SSDs. This app is the only one using this server so sharing TempDB isn't a problem.

Is this a problem? This is the first time I've seen an app that did so much work in TempDB. What problems should I be watching for with this setup?

0
0

Important things :

  • Depending on your version of sql server, use TF 1117 and 1118 to equally grow all the files and alleviate tempdb contention. Those are safe trace flags to have.
  • Make sure your tempdb files are all equally sized and set to grow by a fix MB as opposed to Percent growth. I would suggest you preallocate so auto-growth is minimal to none.

Followups:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.