Distribution of temp db in multiple files

Question

HI All I recently got one task for distribution of tempdb load to multiple ndf files. I found some tips like based on number of processor we can make ndf files. My question is --Does it require any down time for a higly used OLTP database just by simply writing alter command for adding a file group. --How can i make sure traffics are distributed equally to all files (.mdf and .ndf) --Any new suggestions. Thanks Manish

Nitin Sontakke · Answer

Please refer to the attached which will give you an idea of stored procedures using physical temp table. You will get idea of volume of work. When did you restart your server last? I am not 100% sure but you can issue following query for that: SELECT sqlserver_start_time FROM sys . dm_os_sys_info ; Is there a regular interval for your server restart? If not can you please try restarting the server if possible. The tempdb should go back to normal. I know it will increase again! Believe me optimizing your sps is the real solution to this problem!

Nitin Sontakke · Answer

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.

Nitin Sontakke · Answer

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.

Manish Kumar · Answer

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. Does where condition in delete statement looks for an index for example: delete from mytable where insertedon>='2015/02/01' if there is no index on mytable does it go for table scan (among millions of records) as per my understanding (please correct me if i am wrong)  if there is no index on a table then DML operation will work fast as there is no index maintenance. Thanks Manish

Manish Kumar · Answer

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

Nitin Sontakke · Answer

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.

Manish Kumar · Answer

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