Question: What is WorkTable in SQL Server?
Answer: If there is one question, I have been waiting to receive was this one. I had often thought that I will just make this one and post it out, but I have always kept the policy that I will only post questions which I see in interviews or in my SQL Server Performance Tuning Practical Workshop consultancy.
Before I answer this question, let us see where do we see worktables in SQL Server.
Let us run following script in the SQL Server Management Studio on the sample database AdventureWorks.
USE AdventureWorks2014 GO SET STATISTICS IO ON GO SELECT * FROM Production.Product p CROSS JOIN Production.Product p1 GO
If you run above query, there is good chance that it will give you following details in the message windows.
Table ‘Product’. Scan count 2, logical reads 30, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable‘. Scan count 1, logical reads 8110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You will notice in the message there is a table called worktable. However, if you see the original table there is no worktable. This is where the question has originated.
The question in another word is – Why does statistics show the worktable where it is not there in the original query?
Well, the answer is very simple – Quite often SQL Server has to perform any logical operations for any specific queries and to perform these logical operations SQL Server has to build a worktable. Worktables are built in tempdb and are dropped automatically when they are no longer needed.
So in other words, if you see worktable in SQL statistics, it means it is using TempDB.
I will be happy to read your experience and further observations. Please leave a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Well yes, but is that enough of an answer? When does it do this? Is it good or bad? Does a worktable have statistics? Indexes? If you see a worktable with millions of reads or IOs, what do you do about it?
is these are same to magic table ?
No they are very different.
Thanks Pinal for the valuable information.
My pleasure. I am glad you liked it.
What’s the difference between a worktable and a workfile please?
Good evening!
Bus the worktable is good or bad? it
Does it hinder my performance?
If so, can you optimize it?
Pinal,
I have a question on it. I have a query, If i use a perfect index on that query, Logical reads in worktable goes up to 554 but when i drop that index, comes down to 0. How is this table related to index usage? Thanks for your continuous support to community.
Hi Sir,
We are facing problem frequently in Sql server that temp tables (work tables) in a huge and applications are getting stucked. This is happening frequently.
Even we restart server with in 30 mints more that 8000 tables get created.
Please suggest me what to do.
Regards,
Balakrishna
Hey Balakrishna..Did you find any solution to this.
A query returns 0 logical reads for ‘Worktable’ in DEV environment. The same query returns over 4 million logical reads for ‘Worktable’ in PROD environment. The DEV and PROD table being queried are identical in both environments. Why so many logical reads in PROD??? How can I knock them down?