About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, July 5, 2014

No Statistic Update for Table Variable


Read Previous Post: Statistic for @Table Variable

In previous post we were checking about the presence of Statistic for @Table Variable, here will try to dig more on execution plan to see, whether the statistics which is used by optimizer is up-to-the-minute or stale.     

Again we are using same setup code for finding our conclusion. Here we are creating our table variable and inserting 5000 records in it.

set nocount on  

declare @tab_var table
(id int primary key clustered) 

declare @i int = 0    

begin tran
 while @i < 5000
 begin
  insert into @tab_var
  values (@i)  set @i=@i+1
 end
commit tran  

let set the Statistic Profile ON to see whether any statistic created for the same.

set statistics profile on
select * from @tab_var --option(recompile)
set statistics profile off

Rows Executes StmtText                                   EstimateRows
---- -------- --------------------------------------------- ----------
5000 1        select * from @tab_var                        1        
5000 1        |--Clustered Index Scan(OBJECT:(@tab_var))    1        

From the output we can see it uses the statistic, but the estimated Rows count(1) and Actual rows counts(5000) is varying with high percentage. This means the statistic is not updated with the insertion of   5k records.

But if it is so, lets set the strict recompile Option true to the query and see the estimated Row count.

set statistics profile on
select * from @tab_var option(recompile)
set statistics profile off
Rows Executes StmtText                                   EstimateRows
---- -------- --------------------------------------------- ----------
5000 1        select * from @tab_var                        5000        
5000 1        |--Clustered Index Scan(OBJECT:(@tab_var))    5000        

So here we can see the estimated number of rows increases to 5000, that means now the plan is up-to-the-minute plan.

So what conclusion we are getting here is Statistic are available for Table Variable but statics are nit updated with DML operation.

So one need to explicitly call Option (Recompile) to make the statistic update for Table variable. And that is the difference between Table variable and Temp Table.

The joy of release of ur code is more pleasurable than keeping it with you



Post Reference: Vikram Aristocratic Elfin Share

No comments:

Post a Comment