SQL SERVER- High CPU and Transaction Type WorkFileGroup_fake_worktable and Workfile

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.

SQL SERVER- High CPU and Transaction Type WorkFileGroup_fake_worktable and Workfile worktable

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)

, , , , ,
Previous Post
Practical Real World Performance Tuning – Games , Interaction and Feedback
Next Post
SQL SERVER – Applying NOLOCK to Every Single Table in Select Statement – SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Related Posts

Leave a Reply

Menu