SQL with Manoj

 
Recently one Stored Procedure was giving issues on our Production system. The SP had some temporary (#) tables that were getting populated in sequence, but the final result was not as expected. As Temp-Tables cannot be accessed across other sessions you cannot see what’s going on. Also as its a PROD system I could not dissect or change the SP and see what’s happening inside.

Debugging a SP on a DEV machine is simple. I can add some extra checks after temp-table population code to get the record count by using “SELECT @@rowcount”, and was able to see the issues.

But this cannot be done on PROD, as you cannot alter the SPs there. So, to see which temp table is being populated you can use below query. This will also show the records count if any temp table is in mid of population.

SELECT 
	T.NAME AS TABLE_NAME,
	S.ROW_COUNT
FROM TEMPDB.sys.dm_db_partition_stats AS S 
INNER JOIN TEMPDB.sys.tables AS T 
ON S.OBJECT_ID = T.OBJECT_ID 
WHERE S.INDEX_ID < 2
and T.NAME like '%#TempTable%'; -- Give #Table name here