temp DB advice - Using Temporary tables

Question

I am working a report, where in the result is combination of multiple #temp tables. Structure is as below

1. Stored procedure 1 which has a temp table which gives a 0.5 million rows

2. Stored procedure 2 which has a temp table which give 0.1 million rows

Finally i need to combine the result set of above 2 SP , again use a temp table and make one final result set for report. Now i am worried about the performance, later if data increases, will it effect temp db. We usually stage the data monthly , in a month it Database may contain about 1 million rows. How much is the maximum capacity temp db accommodates. Will it effect with above approach.

Answer 1

Size of the tempdb is limited only by the size of disk on which it is stored. (Or can be limited in the properties of the database.)

As for 1 million rows. Nowadays it is not much, even "a little". Especially, if we talk about data for a report.

But, I'd checked if you really need that temp tables. Getting rid of them (if they are unnecessary) you can speed up the query and decrease the tempdb usage.

Answer 2

First, it is not about number of rows it is about size of row. So, if you have 7KB per row then for .6 million rows it would be roughly around 4 GB. Now, this is not the end, SQL Server use TempDb for storing internal objects, version objects and user objects which also include intermediate result. You can expect the size to be grown more than 4 GB in your case. There are two possible ways to overcome this:

1. Tune your queries, minimize use of Temp table, Table variable, CTE, large objects like VARCHAR(MAX) or cursors.
2. Increase your Tempdb file size. Calculate the max size either based on observation [re-building indexes is a best bet]

In real world scenerio, there is always a chance for improvement in query itself. Check if you can avoid using tempdb by joining table correctly or by using views.