Prev: GHD IV Salon Styler
Next: Cast Numeric data type gain during Sum
From: simon on 28 Jul 2010 05:44
On 27 jul., 23:53, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> simon (zupan....(a)gmail.com) writes:
> > Table variable is useful only for very small data sets and for
> > transfering in functions or procedures (in sql2008). Any other benefit
> > of table variable?
>
> Yes, since table variables do not have statistics they do not cause
> recompilations, which can be good for performance. Yes, exactly the
> same reason why you in other occasions should not use them.
>
> There is one more advantage with table variables over temp tables:
>
> This procedure is created successfully, despite it refers to a non-
> existing column:
>
>    CREATE TABLE alf (a int NOT NULL,
>                      b int NOT NULL)
>    go
>    CREATE PROCEDURE alf_sp AS
>    CREATE TABLE #evil (x int NOT NULL)
>    UPDATE alf
>    SET    a = e.x
>    FROM   alf
>    JOIN   #evil ON e.x = a.c
>    go
>    DROP TABLE alf
>    DROP PROCEDURE alf_sp
>
> But if you make #evil a table variable, the error is detected at compile-
> time. Since a table variable is a declare entity, there is no deferred
> name resolution involved with it.
>
> > Table variable is also written on disk in temp db and not in ram as
> > many people thinks.
>
> Correct!
>
> > Using temp tables is in many cases better solution.
>
> Yup. As you say, table variables are good when the volume is small,
> but at bigger volumes temp tables are usually better. But I have a
> very large procedure, around 3000 lines log. 43 table variables, and
> no temp tables. They were temp tables originally, but at least in
> SQL 2000, recompilation was a complete killer.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland,

thank you for your explanation.
Just one question more, what do you mean with recompilation?
Isn't stored procedure compiled only frst time, when it is executed
otherwise the plan is used from the cache?

Best regards, Simon
From: Erland Sommarskog on 28 Jul 2010 18:03
simon (zupan.net(a)gmail.com) writes:
> thank you for your explanation.
> Just one question more, what do you mean with recompilation?
> Isn't stored procedure compiled only frst time, when it is executed
> otherwise the plan is used from the cache?

Yes, but there are several situations where recompilations can occur.
Updated statistics is one reason. Another is that a table that did not exist
when the procedure first was created, now exists. (Typical example: temp
table.) Yet other reason is that an index was created or dropped for a
table.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: simon on 29 Jul 2010 04:31
On 29 jul., 00:03, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> simon (zupan....(a)gmail.com) writes:
> > thank you for your explanation.
> > Just one question more, what do you mean with recompilation?
> > Isn't stored procedure compiled only frst time, when it is executed
> > otherwise the plan is used from the cache?
>
> Yes, but there are several situations where recompilations can occur.
> Updated statistics is one reason. Another is that a table that did not exist
> when the procedure first was created, now exists. (Typical example: temp
> table.) Yet other reason is that an index was created or dropped for a
> table.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Temp table is created inside a stored procedure and dropped at the
end.
That is normal scenario. Temp table life cycle is only when the stored
procedure is executed, that is 1 scond for example.
Would be the procedure recompilated every time because of that?
I don't understand exactly, when the statistic is created for temp
table?
I guess when the SP is executed the first time, at recompilation.
But at the end od procedure, the temp table is dropped - is statistic
also dropped?
I guess not, because when I execute the procedure the second time, it
executes faster than the first time.
So I guess it uses the statistic from the previous creation of temp
table, but I'm not sure. Maybe it's created every time.
Do you know where I can see, if the SP is recompiled when I execute it
the second time? And if statistic is recreated. Is there some system
view or something?
Do you have some url which explains this kind of scenarious?

Thank you very much for your help.

Best regards,
Simon
From: Erland Sommarskog on 29 Jul 2010 07:04
simon (zupan.net(a)gmail.com) writes:
> Temp table is created inside a stored procedure and dropped at the
> end.
> That is normal scenario. Temp table life cycle is only when the stored
> procedure is executed, that is 1 scond for example.
> Would be the procedure recompilated every time because of that?

Originally, when the procedure was created, there was a hole in the
execution plan where the table was referred. These holes were filled
when the procedure was executed the first time. Next time the procedure
is executed, there are no holes to fill, and there is no recompilation
because of this.

But consider now this situation:

CREATE PROCEDURE inner_sp AS
SELECT a FROM #temp
go
CREATE PROCEDURE outer_sp AS
CREATE TABLE #temp(a int NOT NULL)
EXEC inner_sp
go

Here the SELECT statement in inner_sp will be recompiled every time, because
it is a new temp table every time.

> I don't understand exactly, when the statistic is created for temp
> table?

For a normal table, when 500 rows have been modified, this will trigger
auto-statistics, from there auto-stats runs when 20% of the rows has been
modified. To that comes index creation and UPDATE STATISTICS.

For a temp table it is the same, except that there is an extra threshold
of 6 rows. For more information, see this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx.

> I guess when the SP is executed the first time, at recompilation.
> But at the end od procedure, the temp table is dropped - is statistic
> also dropped?

Yes. The temp table itself is actually not entirely dropped; the metadata
for it is cached (in most cases), but that is only metadata.

> I guess not, because when I execute the procedure the second time, it
> executes faster than the first time.

There may be multiple reasons for this. One is that when on the second time,
data from permanent tables are now in cache.

> Do you know where I can see, if the SP is recompiled when I execute it
> the second time? And if statistic is recreated. Is there some system
> view or something?

Yes, you can use Profiler to look for compilation and recompilation events.
There is both the event SP:Recompile and SQL:StmtRecompile. Don't forget to
add the column EventSubClass to the trace, as this column holds the reason
for the recompilation.

As always, be careful with running Profiler for any longer time against a
production system.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: simon on 29 Jul 2010 11:00
Hi Erland,

first thnak you for your answer.

There in document writes:
"If the statistics object is defined on a temporary table..."

Who defines statistic object, isn't it created automatically and
always, when you create temp table?

So, in this situation, the recompilation won't happened again every
time:

CREATE PROCEDURE outer_sp AS
CREATE TABLE #temp(a int NOT NULL)
INSERT INTO #temp........--the statistics are also created(always),
because I insert some data into table
SELECT a FROM #temp
DROP TABLE #temp -- it also clear all statistics

So, each time I execute this procedure, the statistics are created
(and index statistics also, if there is some index), but recompilation
would be done only first time.
On the other hand, you mentioned, if statistics are changed, the
recompilation is done. So, than it should be recompiled each
execution?

Obviously, creating statistics each time(and maybe also recompilation)
is still much faster(if there is a lot of rows) than have table
variable without statistics and recompilation, but also with not
optimal execution plan(because of lack of statistics).

What about the scenario, where I have the normal table instead of temp
table:

CREATE PROCEDURE outer_sp AS
SELECT a INTO temp_userID FROM .....--I must have unique name if
there are more users executing the same procedure, so I add userID
into the name of table -- there is also minimal logging because of
SELECT INTO
SELECT a FROM temp_user
DROP TABLE temp_user

Is this good solution, do you have any comment?

I have another question(sorry to bother you).

http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

Here are the steps when you use a temporary table:
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read
activity)
5) DROP TABLE (write activity)
4) Release the locks

So, when you create temp table, the whole temp db is locked? How is
that possible and why? If I have many users than it is a huge
performance kill?
Is it possible to create more than one temp db - maybe that would
solve that kind of problem.

Have a nice day,
Simon
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: GHD IV Salon Styler
Next: Cast Numeric data type gain during Sum