As a part of my SQL Server Performance Tuning and Optimization consultancy, I face interesting situations and every time I am able to resolve them. This was one of the more interesting ones where I was not getting any hint from the internet. Since it was a new thing to me, I am sharing it via this blog. In this blog, we would learn about the cause of WorkFileGroup_fake_worktable and Workfile.
THE PROBLEM
My client contacted me for a performance issue with the application and at the same time, they observed high CPU caused by the SQL Server process. We did more data analysis during the issue time. We ran below query along with many other queries which I used to find the issue.
SELECT name, COUNT(1) FROM sys.dm_tran_active_transactions GROUP BY name, transaction_type
We found that when the issue happens, there are a lot of transaction of type Workfile and WorkFileGroup_fake_worktable coming as more prominent values.
WORKAROUND/SOLUTION
After more data gathering we were able to conclude that this was due to a lot of spills to tempdb database. Basically, when we run a query it tries to use memory for temporary work like Hash join, Sort Join etc. But if the memory is not sufficient then SQL Server smartly uses TempDB database. This is seen as “Hash Warning” and “Sort Warning” in the query plan. You can read more about them on my earlier blog.
SQL SERVER – Introduction of Showplan Warning
Later, we did more analysis and nailed down the statements which were causing this. Once we optimized those queries, the issue was completely resolved.
If you want my help in tuning your SQL Server Performance, feel free to contact me.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
