What is WorkTable in SQL Server? – Interview Question of the Week #146

Question: What is WorkTable in SQL Server?

What is WorkTable in SQL Server? - Interview Question of the Week #146 worktable

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.

 

What is WorkTable in SQL Server? - Interview Question of the Week #146 worktable1

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)

, , ,
Previous Post
What are Forwarded Records in SQL Server? – Interview Question of the Week #145
Next Post
How to Validate Email Address in SQL Server? – Interview Question of the Week #147

Related Posts

10 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?

    Reply
  • is these are same to magic table ?

    Reply
  • Yashveer Gurjar
    January 21, 2018 6:42 pm

    Thanks Pinal for the valuable information.

    Reply
  • What’s the difference between a worktable and a workfile please?

    Reply
  • Leonardo Marinho
    May 25, 2018 4:50 am

    Good evening!

    Bus the worktable is good or bad? it

    Does it hinder my performance?

    If so, can you optimize it?

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Hey Balakrishna..Did you find any solution to this.

    Reply

Leave a Reply

Menu