When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?I have been told recently, by a reliable source (Itzik Ben-Gan), that in
SQL 2000 the insert & deleted virtual tables accessible in triggers are
implemented in memory from the transaction log of the DB (ie. it builds
a table in RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is
being accessed because there's no better way to say, in an execution
plan, that it's actually the transaction log that's being accessed. I
guess you could interpret the execution plan step as "scan the change
history for the MY_TABLE object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using
the new row versioning technology that has been added to SQL 2005.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
BGL wrote:
>How are 'inserted' and 'deleted' implemented by SQL Server?
>When I look at the execution plan of a trigger that selects from 'inserted'
,
>I can see that the user table is queried instead and aliased as 'inserted':
>
>Execution Tree
>--
>Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
>BOL referers to inserted as "temporary, memory-resident tables to test the
>effects of certain data modifications".
>Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted
'
>is instead a view on top of MY_TABLE that accesses the new version of a
>record, and deleted accesses the old version?
>
>
>
>|||That's close, but there actually is not really any building going on. The ro
ws are already in memory because the inserts or deletes have been logged. Yo
u can think of inserted and deleted as views of the transaction log containi
ng the changed rows for the particular transaction.
Since the log records refer to the base table they are based on, the query p
lan uses that for the table name.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||Hi Mike,
I actually like the term views rather than virtual tables. What I said was t
hat inserted and deleted are views on top of the section in the log that con
tains the change that fired the trigger.
And since these log records are also buffered, chances are that if the secti
on in the log is not really big, it's in cache.
The reason it's important to realize this is that referring to inserted and
deleted means reading from the log, postponing its other activities.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||Thanks for the clarification (I was close - it was a long day for my
brain on Wednesday ;-) ).
<OffTopic>
Wow - posts from both Kalen & Itzik in the same thread. I've just given
myself a big headache by listening to a Podcast on .NET Rocks (that went
for about 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim
Tripp talking about snapshot & read committed snapshot isolations levels
(among other topics) in SQL 2005. It took me about 3 hours because I
kept having to go back and replay bits over and over again to understand
what she was talking about. And that was immediately after listening to
a 1 hour Podcast of Kalen talking about MSSQL history. (Kalen when are
you going to do a course or seminar in Sydney (much better city than
Melbourne)?)
I downloaded another of Kim's Podcasts (about the same size) for the
train trip home but I'm scared to start it for fear of exasperating my
headache. :-\
</OffTopic>
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Itzik Ben-Gan wrote:
> Hi Mike,
> I actually like the term views rather than virtual tables. What I said
> was that inserted and deleted are views on top of the section in the
> log that contains the change that fired the trigger.
> And since these log records are also buffered, chances are that if the
> section in the log is not really big, it's in cache.
> The reason it's important to realize this is that referring to
> inserted and deleted means reading from the log, postponing its other
> activities.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:eghs3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
> I have been told recently, by a reliable source (Itzik Ben-Gan),
> that in SQL 2000 the insert & deleted virtual tables accessible in
> triggers are implemented in memory from the transaction log of the
> DB (ie. it builds a table in RAM from reading the tlog of the
> database).
> At a guess I'd say that the execution plan tells you that MY_TABLE
> is being accessed because there's no better way to say, in an
> execution plan, that it's actually the transaction log that's
> being accessed. I guess you could interpret the execution plan
> step as "scan the change history for the MY_TABLE object", or
> something along those lines.
> In SQL 2005 the inserted & deleted tables are created in tempdb
> using the new row versioning technology that has been added to SQL
> 2005.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> BGL wrote:
>|||Did you see Kim's whitepaper on Snapshot Isolation? That might have much of
the same info, and if you had read that first, you might not have to keep re
winding the podcast!
We were planning on doing my course in Sydney, but not enough people signed
up. So we're doing the second w
in Canberra. Hope you can make it!--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
mp%23IsAaFHA.720@.TK2MSFTNGP15.phx.gbl...
Thanks for the clarification (I was close - it was a long day for my brain o
n Wednesday ;-) ).
<OffTopic>
Wow - posts from both Kalen & Itzik in the same thread. I've just given mys
elf a big headache by listening to a Podcast on .NET Rocks (that went for ab
out 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim Tripp talking
about snapshot & read committed snapshot isolations levels (among other topi
cs) in SQL 2005. It took me about 3 hours because I kept having to go back
and replay bits over and over again to understand what she was talking about
. And that was immediately after listening to a 1 hour Podcast of Kalen tal
king about MSSQL history. (Kalen when are you going to do a course or semin
ar in Sydney (much better city than Melbourne)?)
I downloaded another of Kim's Podcasts (about the same size) for the train t
rip home but I'm scared to start it for fear of exasperating my headache. :
-\
</OffTopic>
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Itzik Ben-Gan wrote:
Hi Mike,
I actually like the term views rather than virtual tables. What I said was t
hat inserted and deleted are views on top of the section in the log that con
tains the change that fired the trigger.
And since these log records are also buffered, chances are that if the secti
on in the log is not really big, it's in cache.
The reason it's important to realize this is that referring to inserted and
deleted means reading from the log, postponing its other activities.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||re: > In SQL 2005 the inserted & deleted tables are created in tempdb usin
g
the new row versioning technology that has been added to SQL 2005
The overhead of the writes to #temp should slow down trigger performance vs
the current implementation of viewing the log records in memory?
Thanks for the posts, all, I've enjoyed the feedback. -- BGL
"Itzik Ben-Gan" wrote:
> Hi Mike,
> I actually like the term views rather than virtual tables. What I said was
that inserted and deleted are views on top of the section in the log that c
ontains the change that fired the trigger.
> And since these log records are also buffered, chances are that if the sec
tion in the log is not really big, it's in cache.
> The reason it's important to realize this is that referring to inserted an
d deleted means reading from the log, postponing its other activities.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news
:eghs3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
> I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 200
0 the insert & deleted virtual tables accessible in triggers are implemented in memo
ry from the transaction log of the DB (ie. it builds a table in RAM from reading the
tl
og of the database).
> At a guess I'd say that the execution plan tells you that MY_TABLE is being acce
ssed because there's no better way to say, in an execution plan, that it's actually
the transaction log that's being accessed. I guess you could interpret the executio
n p
lan step as "scan the change history for the MY_TABLE object", or something along those lin
es.
> In SQL 2005 the inserted & deleted tables are created in tempdb using th
e new row versioning technology that has been added to SQL 2005.
> HTH
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> BGL wrote:
> How are 'inserted' and 'deleted' implemented by SQL Server?
> When I look at the execution plan of a trigger that selects from 'inserted
',
> I can see that the user table is queried instead and aliased as 'inserted'
:
> Execution Tree
> --
> Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
> BOL referers to inserted as "temporary, memory-resident tables to test the
> effects of certain data modifications".
> Why does the plan reveal that MY_TABLE is queried? It looks like 'inserte
d'
> is instead a view on top of MY_TABLE that accesses the new version of a
> record, and deleted accesses the old version?
>
>
>|||I actually understood it all because Itzik covered it with a good demo
in the T-SQL course in Sydney last Wednesday. I think half the problem
was that I was trying to do some logic puzzles (at http://laser.narr.as)
at the same time as trying to concentrate on what Kim was saying (and
the fact that it was late Friday arvo). I'll be sure to check out her
whitepaper though. Thanks for the tip.
I'd love to come to your Canberra session but the missus & I are
expecting our 3rd baby at the beginning of July so I'm afraid I'll be a
tad busy but I hope your time down under will be great (I'm sure your
SQL sessions will be fantastic).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Kalen Delaney wrote:
> Did you see Kim's whitepaper on Snapshot Isolation? That might have
> much of the same info, and if you had read that first, you might not
> have to keep rewinding the podcast!
> We were planning on doing my course in Sydney, but not enough people
> signed up. So we're doing the second w
in Canberra. Hope you can> make it!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23mp%23IsAaFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks for the clarification (I was close - it was a long day for
> my brain on Wednesday ;-) ).
> <OffTopic>
> Wow - posts from both Kalen & Itzik in the same thread. I've just
> given myself a big headache by listening to a Podcast on .NET
> Rocks (that went for about 100min - wmplayer sucked up 980MB of
> virtual RAM!) of Kim Tripp talking about snapshot & read committed
> snapshot isolations levels (among other topics) in SQL 2005. It
> took me about 3 hours because I kept having to go back and replay
> bits over and over again to understand what she was talking
> about. And that was immediately after listening to a 1 hour
> Podcast of Kalen talking about MSSQL history. (Kalen when are you
> going to do a course or seminar in Sydney (much better city than
> Melbourne)?)
> I downloaded another of Kim's Podcasts (about the same size) for
> the train trip home but I'm scared to start it for fear of
> exasperating my headache. :-\
> </OffTopic>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Itzik Ben-Gan wrote:
>
No comments:
Post a Comment