Earlier today, I received an email from a client who has hired me earlier last week for Comprehensive Database Performance Health Check. They realized their counter Counter Temp Tables For Destruction Not Working and wanted me to help them. Let us learn about it today.
Brief History
Recently I helped an organization who had an issue with their SQL Server performance. After a brief investigation, we figured it out that it was issued with the TempDB. I helped them fixed the issue during our health check consultation. Now they were trying to implement their own monitoring on the top of the TempDB and during that they had configured Perfmon.
Here are the two counters they had configured to monitor their TempDB table creations.
- Temp Tables Creation Rate
- Temp Tables For Destruction
Now the issue was they were able to see the Temp Table Creation Rate perfectly fine but they were always seeing the flat line (no activity) for the second counter of Temp Tables For Destruction. This was indeed confusing to them.
Counter Temp Tables For Destruction
Their confusion was if TempTables are created they must be dropped otherwise it would be a big issue once too many tempTables are created.
Here is what they were seeing in their counter screen with both the counters but you will see only one counter of creation rate in the red whereas the destruction is flat on the bottom with the green line.
For additional clarity here is another screenshot where I have only kept one counter visible. You can see the all the numbers are zero.
The client was very much worried why this issue was happening. Honestly, the reason is we have understood both the counter differently. Let us read the definition of both them carefully.
Temp Tables Creation Rate – Number of temporary tables/table variables created per second.
Temp Tables For Destruction – Number of temporary tables/table variables waiting to be destroyed by the cleanup system thread.
The first counter is indeed the rate of creation of the temp table but the second counter is actually the temp table waiting to be dropped. Due to the speed of the system and how the temp tables are dropped, there is no temp table are waiting to drop.
I hope this blog post helps you clear any confusion you have for both the counters.
On a very separate note, I do not like counters based performance tuning methods. It is very old and it does not point you exactly where the problem is. However, we will discuss this in future blog posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
