My advice is to do functionality or, preferably, load testing in a dev\test
environment. As well as create a detailed project plan. I have run into a
lot of issues with upgrades but the clients that did throrough testing made
the fixes a part of the project plan instead of a Monday morning fire fight.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"sacgar" <sacgar@.discussions.microsoft.com> wrote in message
news:8D9F1A4F-B591-4E8D-8F63-0ACF2D0ED91C@.microsoft.com...
> Hi ,
> We are planning for ‘in place’ SQL Upgrade from SQL 2000(Ent. Edition with
> SP4) to SQL 2005 Ent.Edition with SP2 for
> 1)Replication Distribution Server that serves to PULL subscriptions.
> 2)One for the Subscriber Cluster (Active Passive)Server (its distribution
> server already upgraded to SQL 2005)
> We have gone through the requirements from Microsoft Technical Resource
> Guide but looking if somebody had any issues or any advice other than
> Upgrade
> Advisor reported before we move for Upgrade for both of above cases as
> some
> people indicated they had issue with Password Policy , Appreciate your
> reply…
>
Please go through the below article as I have faced this issue , check if
applicable in your scenario...
Thanks
Ajay
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:26E92972-53B2-4063-8D68-24B72C69FB94@.microsoft.com...
> My advice is to do functionality or, preferably, load testing in a
> dev\test environment. As well as create a detailed project plan. I have
> run into a lot of issues with upgrades but the clients that did throrough
> testing made the fixes a part of the project plan instead of a Monday
> morning fire fight.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "sacgar" <sacgar@.discussions.microsoft.com> wrote in message
> news:8D9F1A4F-B591-4E8D-8F63-0ACF2D0ED91C@.microsoft.com...
>
|||Sorry Missed this link earlier
Please go through this link for DB Move
http://support.microsoft.com/kb/314546/
Thanks
Ajay
"Ajay" <ajudba@.gmail.com> wrote in message
news:OsXeYk0ZIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Please go through the below article as I have faced this issue , check if
> applicable in your scenario...
> Thanks
> Ajay
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:26E92972-53B2-4063-8D68-24B72C69FB94@.microsoft.com...
>
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Wednesday, March 7, 2012
'AS IS' Upgrade from SQL 2K to SQL 2K5
Saturday, February 25, 2012
article on the optimiser generating a query plan
Is there a nice article on where i can find out how the query optimiser
chooses a query plan and what kind a plan it might choose with some examples
?
Whether it may use nested loop joins or merge or hash , whether it will do a
table scan, Clustered Index scan or seek.,etc...Take a look at:
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
and
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> Is there a nice article on where i can find out how the query optimiser
> chooses a query plan and what kind a plan it might choose with some
examples
> ?
> Whether it may use nested loop joins or merge or hash , whether it will do
a
> table scan, Clustered Index scan or seek.,etc...
>|||The 2 URLs are the same. Is that a generic place u sent me to about SQL
Server or is there a specific article out there
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:uIj1A3FbDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Take a look at:
>
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
> and
>
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > Is there a nice article on where i can find out how the query optimiser
> > chooses a query plan and what kind a plan it might choose with some
> examples
> > ?
> > Whether it may use nested loop joins or merge or hash , whether it will
do
> a
> > table scan, Clustered Index scan or seek.,etc...
> >
> >
>|||Superb... I havent read it though but it looks like what i was looking for.
When is part 8 due ? Do you have all that clubbed together somehow for an
easy print ?
Good Luck to u
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0e5d01c36cb4$6583dec0$a601280a@.phx.gbl...
> http://www.sql-server-
> performance.com/jc_sql_server_quantative_analysis1.asp
>
> >--Original Message--
> >Is there a nice article on where i can find out how the
> query optimiser
> >chooses a query plan and what kind a plan it might choose
> with some examples
> >?
> >Whether it may use nested loop joins or merge or hash ,
> whether it will do a
> >table scan, Clustered Index scan or seek.,etc...
> >
> >
> >.
> >|||Hassan,
MS SQL Server Performance Tuning and Optimization for Developers, Part 2:
The Query Optimizer
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> Is there a nice article on where i can find out how the query optimiser
> chooses a query plan and what kind a plan it might choose with some
examples
> ?
> Whether it may use nested loop joins or merge or hash , whether it will do
a
> table scan, Clustered Index scan or seek.,etc...
>|||Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> Hassan,
> MS SQL Server Performance Tuning and Optimization for Developers, Part 2:
> The Query Optimizer
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > Is there a nice article on where i can find out how the query optimiser
> > chooses a query plan and what kind a plan it might choose with some
> examples
> > ?
> > Whether it may use nested loop joins or merge or hash , whether it will
do
> a
> > table scan, Clustered Index scan or seek.,etc...
> >
> >
>|||Hassan,
Except some basic stuff, I dont believe it holds good for SQL2000
considering the presentation date and the author not explictly stating the
SQL Server version.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKkSoMObDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > MS SQL Server Performance Tuning and Optimization for Developers, Part
2:
> > The Query Optimizer
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > > Is there a nice article on where i can find out how the query
optimiser
> > > chooses a query plan and what kind a plan it might choose with some
> > examples
> > > ?
> > > Whether it may use nested loop joins or merge or hash , whether it
will
> do
> > a
> > > table scan, Clustered Index scan or seek.,etc...
> > >
> > >
> >
> >
>|||This is a SQL Server 6.5 presentation and doesn't apply to SQL Server 7.0 or
2000.
The articles at
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/sql7qp.asp
and
http://msdn.microsoft.com/library/en-us/dnsql7/html/sqlquerproc.asp
were written for SQL Server 7.0 but are 90% accurate for SQL Server 2000 as
well.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKkSoMObDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > MS SQL Server Performance Tuning and Optimization for Developers, Part
2:
> > The Query Optimizer
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > > Is there a nice article on where i can find out how the query
optimiser
> > > chooses a query plan and what kind a plan it might choose with some
> > examples
> > > ?
> > > Whether it may use nested loop joins or merge or hash , whether it
will
> do
> > a
> > > table scan, Clustered Index scan or seek.,etc...
> > >
> > >
> >
> >
>
chooses a query plan and what kind a plan it might choose with some examples
?
Whether it may use nested loop joins or merge or hash , whether it will do a
table scan, Clustered Index scan or seek.,etc...Take a look at:
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
and
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> Is there a nice article on where i can find out how the query optimiser
> chooses a query plan and what kind a plan it might choose with some
examples
> ?
> Whether it may use nested loop joins or merge or hash , whether it will do
a
> table scan, Clustered Index scan or seek.,etc...
>|||The 2 URLs are the same. Is that a generic place u sent me to about SQL
Server or is there a specific article out there
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:uIj1A3FbDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Take a look at:
>
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
> and
>
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > Is there a nice article on where i can find out how the query optimiser
> > chooses a query plan and what kind a plan it might choose with some
> examples
> > ?
> > Whether it may use nested loop joins or merge or hash , whether it will
do
> a
> > table scan, Clustered Index scan or seek.,etc...
> >
> >
>|||Superb... I havent read it though but it looks like what i was looking for.
When is part 8 due ? Do you have all that clubbed together somehow for an
easy print ?
Good Luck to u
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0e5d01c36cb4$6583dec0$a601280a@.phx.gbl...
> http://www.sql-server-
> performance.com/jc_sql_server_quantative_analysis1.asp
>
> >--Original Message--
> >Is there a nice article on where i can find out how the
> query optimiser
> >chooses a query plan and what kind a plan it might choose
> with some examples
> >?
> >Whether it may use nested loop joins or merge or hash ,
> whether it will do a
> >table scan, Clustered Index scan or seek.,etc...
> >
> >
> >.
> >|||Hassan,
MS SQL Server Performance Tuning and Optimization for Developers, Part 2:
The Query Optimizer
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> Is there a nice article on where i can find out how the query optimiser
> chooses a query plan and what kind a plan it might choose with some
examples
> ?
> Whether it may use nested loop joins or merge or hash , whether it will do
a
> table scan, Clustered Index scan or seek.,etc...
>|||Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> Hassan,
> MS SQL Server Performance Tuning and Optimization for Developers, Part 2:
> The Query Optimizer
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > Is there a nice article on where i can find out how the query optimiser
> > chooses a query plan and what kind a plan it might choose with some
> examples
> > ?
> > Whether it may use nested loop joins or merge or hash , whether it will
do
> a
> > table scan, Clustered Index scan or seek.,etc...
> >
> >
>|||Hassan,
Except some basic stuff, I dont believe it holds good for SQL2000
considering the presentation date and the author not explictly stating the
SQL Server version.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKkSoMObDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > MS SQL Server Performance Tuning and Optimization for Developers, Part
2:
> > The Query Optimizer
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > > Is there a nice article on where i can find out how the query
optimiser
> > > chooses a query plan and what kind a plan it might choose with some
> > examples
> > > ?
> > > Whether it may use nested loop joins or merge or hash , whether it
will
> do
> > a
> > > table scan, Clustered Index scan or seek.,etc...
> > >
> > >
> >
> >
>|||This is a SQL Server 6.5 presentation and doesn't apply to SQL Server 7.0 or
2000.
The articles at
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/sql7qp.asp
and
http://msdn.microsoft.com/library/en-us/dnsql7/html/sqlquerproc.asp
were written for SQL Server 7.0 but are 90% accurate for SQL Server 2000 as
well.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKkSoMObDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Is that good enough for SQL 2000 ? It says Tech-Ed 97.. Can you confirm ?
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23gjaEuNbDHA.2820@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > MS SQL Server Performance Tuning and Optimization for Developers, Part
2:
> > The Query Optimizer
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/dat411.asp?frame=true
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:uX8$UyFbDHA.1940@.TK2MSFTNGP10.phx.gbl...
> > > Is there a nice article on where i can find out how the query
optimiser
> > > chooses a query plan and what kind a plan it might choose with some
> > examples
> > > ?
> > > Whether it may use nested loop joins or merge or hash , whether it
will
> do
> > a
> > > table scan, Clustered Index scan or seek.,etc...
> > >
> > >
> >
> >
>
Thursday, February 9, 2012
Are 'inserted' and 'deleted' tables or views?
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?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:
>
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
--
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
> 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:
>
Subscribe to:
Posts (Atom)