Distribution of temp db in multiple files

May be you could have just raised another question and I would have answered it. Anyway.

 

With indexes, you got to be careful. While 0 indexes is a bad idea, having 25 indexes is also a bad idea.

 

For the same reason that you have mentioned in your post. No indexes means row by row table scan of millions of rows which would be time consuming and and many indexes means updating all the indexes on any dml operation.

 

So bottom line is striking a right balance is the key.

HI Nitin,

Sorry for asking in old thread questions as there is no other option to contact you directly.

I have some query related to index in delete criteria. Below is my question.

HI Nitin,

I would like to give thanks to you for spending your time.

We have around 50 + databases (which all for different purpose) on our production environment , so it is very pain full to identify which query or stored procedure are making heavy use of tempdb( all though we checked with profiler to trace those query/stored procedure but not succeeded).

Yes, we do facing performance issue and that's the reason i am looking for a spliting temp db load to multiple files.

(as my senior has suggested to split temp db load to multiple files based on processor , so that I/O load will distributed accordingly )

Thanks,

Manish

Strictly personally, I am of the opinion that you should not rush to accommodate bloating temp db with more mdf and ndf files. As I said before, this is something which should do AFTER every other solution has failed.

The process is painful but worth it. Otherwise you will start seeing performance issues (I suspect you might already have them!) .

You will need to identify queries / stored procedures which are making heavy use of temp db and why.

Look at the following link: Given exclusively as an example, demonstrating how the real reason for huge temp db was entirely different.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47ab16d6-804c-4c67-badb-86070c931f54/sql-server-2012-tempdb-ballooning-to-more-than-75-gb?forum=sqldatabaseengine 

Any questions, please feel free to get in touch.

HI Nitin,

Our temp db size is approx. 31 gb. Our main goal is to distribute tempdb load from mdf file to other files i.e ndf.

Thanks,

Manish

Just out of curiosity what is size of tempdb size in production environment.

To my knowledge, as name suggests, the tempdb should be used only temporarily and if that's the case size should typically never increase.

Have you (or your client / organization) has reached to this solution after investigating all other things.