How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? – Interview Question of the Week #204

Question: How to Create an Empty Table and Fool Optimizer to Believe It Contains Data?

Answer: Before I answer this question, I must acknowledge that I have never heard this question in my 20 years career before and I must ask you back, where do you come up with these kinds of questions.

How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? - Interview Question of the Week #204 foolstat0

In any case, I really loved your question and let us see the answer to your question. Honestly, I must accept that today, I am going to show you one of the tricks, which I use all the time during Comprehensive Database Performance Health Check.

Let us first create a table.

Solarwinds
CREATE TABLE #T1 (col1 INT)
GO

Next, retrieve data from the table but keeping the execution plan on. Please note that you can enable your execution plan by typing shortcut key CTRL + M.

SELECT *
FROM #T1
GO

How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? - Interview Question of the Week #204 foolstat1

In the execution plan, pay attention to the parameters estimated number of the rows. You will see that the estimated number of the rows are just 1. Following this, update the statistics of the table with keyword ROWCOUNT.

UPDATE STATISTICS #T1
WITH ROWCOUNT = 5000
GO

Now once again, run the select statement with the following command:

SELECT *
FROM #T1
GO

How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? - Interview Question of the Week #204 foolstat2

Again, check the execution plan. This time you will see that the estimated number of rows are 5000 whereas we have not populated any further data in the table.

This is quite a neat trick to use when you are working with SQL Server Performance Tuning projects where you do not want to populate more data in your system but just want to create an execution plan to check the scenario pretending that your tables have a lot of data.

After the testing is completed you can reset your database statistics by running update statistics with a full scan.

UPDATE STATISTICS #T1 
WITH FULLSCAN
GO

After you have run the demonstration you can drop the test table to clean up the database.

DROP TABLE #T1
GO

Did you know this cool trick? If yes, I would love to know when and where you use it. Please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203
Next Post
How to Track Autogrowth of Any Database? – Interview Question of the Week #205

Related Posts

1 Comment. Leave new

  • After reset statistics by running update statistics with a full scan.

    UPDATE STATISTICS #T1
    WITH FULLSCAN

    it didn’t update Estimated no of rows.

    Reply

Leave a Reply

Menu