do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

do I need Indexed Views for Replication

sql2k sp3
Howdy all. Ive been trying to avoid using Indexed Views
for Replication in my scenario because Im not totally
faliliar with the whole schemabinding deal. Plus I like
the convenience of sp_repladdcolumn. I was going to use
Custom Sync Objects instead but I think they may not work
for me. As you've all read in my previous posts, my
Subscriber has a different schema than my publisher. But I
just realized its different in ways I didnt see and now I
think Indexed Views are my only way to go.
I currently have a reporting server that at night has dts
packages that truncate the tables and repopulate from
production. But what I just realized is that some of the
destination tables are actually made up from two or more
source tables.
Source tables:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c2 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Example insert from nightly DTS package:
insert into bla(c1, c2, c3)
select t1.c1,t1.c2,t2.c2
from table1 t1
inner join table2 t2 on t1.c1 = t2.c1
So as you see, the destination is made up from two source
tables. I wrote a view for the initial population and the
data bcp'd successfully. But when I tried to insert a row
I had problems because of column c3. It makes sense that I
would need an Indexed View for this scenario because using
Custom Sync Objects the data is still coming from the
table, not a view. And since the Publisher doesnt have
column c3, it would seem a view would be needed. However,
before I flip flop back to and Indexed View, I would like
to see what you pros think.
TIA, ChrisR
is it possible to convert the DTS package to a publication and replicate
from the DTS source server to what is currenly your publisher and also to
your subscriber?
What are your requirements? It looks like data changes at discrete intervals
and then is static until the next package is run. Replication is the
replication of transactions or deltas (merge). If you have no real time
requirements DTS might be the best solution end to end. Either that or
perhaps snapshot replication.
Secondly I take it you are making a mistake in the schema.
I hope you mean this
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Instead of this

> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
As you can see there is no c3. Again we are going to need complete schemas
to address this, and tell us exactly what you are trying to do, and give the
exact requirements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
> sql2k sp3
> Howdy all. Ive been trying to avoid using Indexed Views
> for Replication in my scenario because Im not totally
> faliliar with the whole schemabinding deal. Plus I like
> the convenience of sp_repladdcolumn. I was going to use
> Custom Sync Objects instead but I think they may not work
> for me. As you've all read in my previous posts, my
> Subscriber has a different schema than my publisher. But I
> just realized its different in ways I didnt see and now I
> think Indexed Views are my only way to go.
> I currently have a reporting server that at night has dts
> packages that truncate the tables and repopulate from
> production. But what I just realized is that some of the
> destination tables are actually made up from two or more
> source tables.
> Source tables:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> Example insert from nightly DTS package:
> insert into bla(c1, c2, c3)
> select t1.c1,t1.c2,t2.c2
> from table1 t1
> inner join table2 t2 on t1.c1 = t2.c1
>
> So as you see, the destination is made up from two source
> tables. I wrote a view for the initial population and the
> data bcp'd successfully. But when I tried to insert a row
> I had problems because of column c3. It makes sense that I
> would need an Indexed View for this scenario because using
> Custom Sync Objects the data is still coming from the
> table, not a view. And since the Publisher doesnt have
> column c3, it would seem a view would be needed. However,
> before I flip flop back to and Indexed View, I would like
> to see what you pros think.
>
> TIA, ChrisR
>
>
|||> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run.
Those have been the requirements. Now I need it done continuosly.

> Secondly I take it you are making a mistake in the schema.
Yes. You are correct.
I'll have to post back everything later. Based on what you're seeing, have
you ever had a setup like this without Indexed Views? I dont think it can be
done, but know that you have lots more experience.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
> is it possible to convert the DTS package to a publication and replicate
> from the DTS source server to what is currenly your publisher and also to
> your subscriber?
> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run. Replication is the
> replication of transactions or deltas (merge). If you have no real time
> requirements DTS might be the best solution end to end. Either that or
> perhaps snapshot replication.
> Secondly I take it you are making a mistake in the schema.
> I hope you mean this
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
> Instead of this
>
> As you can see there is no c3. Again we are going to need complete schemas
> to address this, and tell us exactly what you are trying to do, and give
the
> exact requirements.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
>
|||I'm still kind of confused.
It sounds like there are 3 servers, server 1, server 2, and server 3.
Currently a DTS package reads table 1 and table 2 on server 1, and sends
data to server 2 to a table called table 3. Then server 2 publishes table 3
to server 3 to a table called table 4.
All of these tables are different.
Does this describe your situation.
I'd replicate from table 1 & 2 to server 2 table 3, and have a second
publication on server 1 replicating to table 4 on server 3. Replicating two
tables to 1 is a little more complex as you need to figure out which table
will generate a complete row.
It if table 1 contributes column a, b, c, and table 2 contributes column d,
you need the log reader to wirte a command to the distribution database when
there will be a complete set of columns for the row, and there has to be
some relationship between table 1 and table 2. Normally there is an
intersection table or join table and key off this table as a row in the join
table means that there is a row in table 1 and table 2. Then you also have
to replicate table 1 and table 2 as well, so that you have all the columns
for table 3 on the subscriber. The store procedure has to read tables 1 and
tables 2 to build table 3.
I normally use custom sync objects for this sort of stuff. Indexed views are
an option, but there is a space and performance penalty to pay (slight) for
using them, and they aren't resilient to schema changes.
As you have seen custom sync objects aren't problem free either, but they
seem to be an option in your case.
"ChrisR" <chris@.noemail.com> wrote in message
news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
> intervals
> Those have been the requirements. Now I need it done continuosly.
>
> Yes. You are correct.
> I'll have to post back everything later. Based on what you're seeing, have
> you ever had a setup like this without Indexed Views? I dont think it can
be[vbcol=seagreen]
> done, but know that you have lots more experience.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
> intervals
schemas
> the
>
|||No. Two servers. Currently, a DTS Package runs nightly to populate one from
the other. I need to change that and start using replications because it
needs to be done continuosly. As you mentioned earlier:
Source table:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
These tables cannot be changed. I was able to replicate this scenario using
Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
BCP the snapshot with a view that matched the destination table, but the
insert and updates were causing problems because the source is two tables
and the destination is only one.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||Is there a one to one mapping of table 1 to table 2? Which is the parent and
which is the child?
"ChrisR" <chris@.noemail.com> wrote in message
news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> No. Two servers. Currently, a DTS Package runs nightly to populate one
from
> the other. I need to change that and start using replications because it
> needs to be done continuosly. As you mentioned earlier:
> Source table:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> These tables cannot be changed. I was able to replicate this scenario
using[vbcol=seagreen]
> Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
> BCP the snapshot with a view that matched the destination table, but the
> insert and updates were causing problems because the source is two tables
> and the destination is only one.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||For this scenario yes, there is a one to one. Table1 being the parent.
However this is only one of my scenarios. I will also be dealing with some
one to many, and many to many. How does this affect the outcome out of
curiosity. FYI I wont be replicating the DRI. Also. I was just rereading
you're previous reply:

>It if table 1 contributes column a, b, c, and table 2 contributes column d,
>you need the log reader to wirte a command to the distribution database
when
>there will be a complete set of columns for the row, and there has to be
>some relationship between table 1 and table 2. Normally there is an
>intersection table or join table and key off this table as a row in the
join
>table means that there is a row in table 1 and table 2. Then you also have
>to replicate table 1 and table 2 as well, so that you have all the columns
>for table 3 on the subscriber. The store procedure has to read tables 1
and
>tables 2 to build table 3.
If Im reading this correctly, it looks like alot of work in place of Indexed
Views. I mean how hard is it to have the Log Reader write a command to the
Dist db when there's a complete set of columns for a row? Are there schema
change problems with Indexed Views other than not being able to use
sp_repladdcolumn?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:epYGQ6FmEHA.596@.TK2MSFTNGP11.phx.gbl...
> Is there a one to one mapping of table 1 to table 2? Which is the parent
and[vbcol=seagreen]
> which is the child?
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> from
> using
to[vbcol=seagreen]
tables[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
ws[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>
|||I just re-read this once more:
Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber.
If Im reading this correctly, it's one of the things I was afraid of. Are
you saying I need to have table1 and table2 on the Subscriber so I can
create table3? Im pretty sure you are, but wanted to make sure?
As always, thanks so much for all you're help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||yes, you will need table 1 and table 2 to be replicated to the subscriber as
well.
Consider this, the log reader will extract all the columns in a table which
is being replicated and construct an insert/update/or delete statement only
consisting of columns from that table.
So if you replicate table 2 the commands written to the distribution agent
will be the pk and c3. Where will the distribution agent get the value for
c2 from?
It will have to join on table 1 in the subscriber to get this value. If its
not there it will never be able to plug it in.
You need to have the log reader key off table 2 when a complete record for
t3 exists which means that c1, c2 & c3 must be either in the proc or some of
these columns must be already on this table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I just re-read this once more:
> Then you also have
columns[vbcol=seagreen]
> If Im reading this correctly, it's one of the things I was afraid of. Are
> you saying I need to have table1 and table2 on the Subscriber so I can
> create table3? Im pretty sure you are, but wanted to make sure?
> As always, thanks so much for all you're help.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||This is as I expected. Since this is a denormalized reporting server, I
think I will revert back to Indexed Views. To have both Table1 and Table2 on
the Subscriber so I can make up Table3 just doesnt make sense to me. It
almost seems to me as though its adding another layer of complexity. Can you
tell me if there are any schema change problems to watch out for with
Indexed Views besides the fact that I cant use sp_repladdcolumn? Any other
type of big gotchas that you can think of would be appreciated as well.
Also, whens the book coming out?
Thank you once more,
ChrisR
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekB3ztOmEHA.1376@.TK2MSFTNGP12.phx.gbl...
> yes, you will need table 1 and table 2 to be replicated to the subscriber
as
> well.
> Consider this, the log reader will extract all the columns in a table
which
> is being replicated and construct an insert/update/or delete statement
only
> consisting of columns from that table.
> So if you replicate table 2 the commands written to the distribution agent
> will be the pk and c3. Where will the distribution agent get the value for
> c2 from?
> It will have to join on table 1 in the subscriber to get this value. If
its
> not there it will never be able to plug it in.
> You need to have the log reader key off table 2 when a complete record for
> t3 exists which means that c1, c2 & c3 must be either in the proc or some
of[vbcol=seagreen]
> these columns must be already on this table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...
> columns
Are[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
views[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>

DMV data lifetime

I'm interested to know how long data 'resides' in a DMV - what causes that data to change etc. since they are dynamic views, and not tables, and I'm sure I've queried that, onyl to find that, later, the data is no longer in there. Is it on server restart, or is there a process or something else that defines how?

I've reviewd the BOL (http://msdn2.microsoft.com/en-us/library/ms188754.aspx) but that didn't really make me any wiser...

Cheers

Dynamic Management Views doesn't hold any data as they return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. The results are produced when they are called and results are dependant on underlying information existence.

|||

Thanks Satya. I was aware that these are not 'tables' that hold the data... my base question is: how long is the "underlying information" going to "exist".

For example: sys.dm_exec_query_stats ... how long will that information be in there? Since I am considering setting up some 'snapshotting jobs' that will capture some of the information available in some of the sys.dm_ views, I need to know what data is available, and how long it is available for...

|||

Geth

As per the explanation I believe it will not be stored anywhere and the results will be in cache to provide the information, even your explanation gives me another thought and would like to here from Dev. team in this regard.

|||

I'd also love to see a dev post on this topic as well, Satya. while I was hoping someone could point me to a definitive answer somewhere in BOL, it is nice to know that it isn't just a case of my not seeing/finding the answer in BOL/google/forums etc.

The closest I've come is that this data might be available until SQL Service stops - but that (a) seems very long, (b) not necessarily accurate - I am 80% sure I had some info from the query_stats DMV that was available in the morning, yet gone by the afernoon, when I wanted to show someone something... (hence the reason I am interested in capturing and persisting the interesting bits to a DB).

|||

Hello,

The answer is "it depends on the DMV".

In the case of sys.dm_exec_query_stats, the BOL says

Returns aggregate performance statistics for cached query plans. The view contains one row per query plan and the lifetime of the row is tied to the plan itself. When a plan is removed from the cache, the corresponding row is eliminated from this view.

You and other customers have expressed "I am interested in capturing and persisting the interesting bits to a DB" and we have heard you.

Thanks!

division in a view

In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff

Distributed Transactions fail

We are trying to create SPs that are using linked servers. I have been able
to create the linked server and see the views and tables. The issue is that
after creating a SP the uses the 4 part naming convention all atempt to
actually save the querry have failed. In some cases I can run the SQL code
but I just cant save it as a SP
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Now before you say visit the MS knowledge base I have and this did not fix
the problem:
http://support.microsoft.com/defaul...kb;en-us;839279
DTC is turned on and the logon is set as a Network Service.What else could
be the issue. Is it user permission probs. Deprately need help. I cant even
save via QA or .NET designer
JP
.NET Software DevelperJP,
Is the DTC service running on both servers? Might look at the 'data access'
, 'rpc' and 'rpc out' settings...not sure if this has anything to do with
what you're experiencing but LSs can sometimes return some error messages
that may not directly point to the cause of an issue.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:8D2DF49D-0F5F-4470-8F26-C6D34209E6B3@.microsoft.com...
> We are trying to create SPs that are using linked servers. I have been
> able
> to create the linked server and see the views and tables. The issue is
> that
> after creating a SP the uses the 4 part naming convention all atempt to
> actually save the querry have failed. In some cases I can run the SQL code
> but I just cant save it as a SP
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction. [OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Now before you say visit the MS knowledge base I have and this did not fix
> the problem:
> http://support.microsoft.com/defaul...kb;en-us;839279
> DTC is turned on and the logon is set as a Network Service.What else could
> be the issue. Is it user permission probs. Deprately need help. I cant
> even
> save via QA or .NET designer
>
>
> --
> JP
> .NET Software Develper

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.
Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:

> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save the
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.
|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
[vbcol=seagreen]
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest using
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an inventor
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The o
peration
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provi
der returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trac
e [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:

> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both
S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now,
we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save t
he
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable t
o
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB pro
vider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
[vbcol=seagreen]
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest usin
g
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an invent
or
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:
>

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:
> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save the
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest using
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an inventor
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:
> > We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> > and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> > can run views in S2 and S3 however we cannot modify the views in S2 or add
> > new views linked to S11.
> > Actually, we can modify the views and run them but when we tried to save the
> > changes, this error message will show up:
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> > could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> > begin a distributed transaction.
> > [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> > message: New transaction cannot enlist in the specific transaction
> > coordinator.]
> > [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> > Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> >
> > What should we do to fix this?
> >
> > All servers run Windows 2000 and SQL 2000.
> >
> > Thanks.