Latency In Transactional Replication

During last couple of weeks some new Tables & Fields were added. SO both the
time i have to re-intialize the Replication. my question is about LATENCY
where i have seen for both the subscriber(SUB) for SUB-1 is 3.5 Hours &
SUB-2 is 21 hrs. Size of the PUBLISHER DB is 18GB. AS Latency are in
Millisecs i have mentioned in HOURS for better understanding. i want to know
how i can resolve this issue as every time when new table or fields are
added it takes 21-22 hours to complete Replication Re-intialization process.
Any help will be highly appreciated. Thanking you in advance.
Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
changing a PK.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||TWO new Tables were added with unique key for couple of fields & were
not going to be use in Transactional Replication due to limitation that
each table needs to have at least one PK. and this 2 tables as no PK.
So using sp_repladdcolumn will not help, i maybe wrong.
please correct me if i am wrong as i am not SQL expert. again any help
& to correct me will be appreciated.
Paul Ibison wrote:
> Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
> changing a PK.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear Mr. Paul:
I have replied back with details & did see your
feedback. i will appreciate if you please kindly reply me at your
earliest & doing so will be highly appreciated.
thanks
Paul Ibison wrote:
> Now I'm confused - if you're adding tables to a publication this is
> supported and adding columns to an already published article is supported,
> but this seems to be not relevant to your case somehow. Is the problem that
> you want to add tables which don't have a PK? This is definitely
> unsupported, but you already know that, so please can you add a few more
> details about what it is that you want to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

LastNonEmpty and AverageOfChildren

Hello,

we are using SQL 2005 with SP1 and we have quite small cube (<1GB).

We have a couple of measures with aggregate function AverageOfChildren or LastNonEmpty.

End users have very poor performances while browsing cube using these measures.

When we replace agg. function with SUM, for example, performances become normal.

I saw simillar discusion on OLAP forum, but I did not understand what is the best recommendation for this situation?

Is this known issue, and how it can be avoided?

Best regards

Borko

Borko,

What's your aggregation strategy for this measure group? Queries to semi-additive measures are resolved by initially going down to granularity for the measure group's time dimension and subsequently computing the aggregate. Because of this, it's important to insure that you have an aggregation that includes the granularity attribute. If not already there, you can generally force the attribute to be included by setting the AggregationUsage property to "Full" in the cube editor. However, since this setting applies across all measure groups, you may want to set it, design aggregations for this measure group, and then rest the value.

I'm not positive that this will solve your issue, but this is where I would start.

-rob

Large Views need index

I have couple of large views to data for reporting. That will be wonderful i
f
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.[url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/url
]
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view..
.
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful
if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.
http://www.novicksoftware.com/Articl...SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>
|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...
|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>
|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü�meyer" wrote:
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
> >I have couple of large views to data for reporting. That will be wonderful
> >if
> > there is a way I can put indexs on a view. I could'n find a way to do so.
> > Any ideas?
> >
> > Thanks a lot.
> >
> >
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online:)
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be wonderful
>>if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldn´t suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online:)
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
>> (But remember that Index Views are only available in SQL2kEE)
>> HTH, Jens Suessmeyer.
>>
>> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be
>>wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>>
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>> I have couple of large views to data for reporting. That will be
>> wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David