Filter clause

We are moving to SQL 2005 Standard Edition are use numerouse filters
for replication. In SQL 2000, we had a lot of the filters setup using
an OR statement in the filterclause (ie. a.company = b.company or
a.subcompany = b.company), and had no problems with adding the filters.
In SQL 2005, creating these filters takes forever, if created at all.
Has anyone out there seen a problem like this?
Any help would be appreciated.
Thanks,
Amy Marshall
Can you define "takes forever" as well as "if created at all"? Are they not
being created? Are you receiving errors? What is happening?
As far as creating these en mass. if you are using the GUI and are stuck in
that world, then plan on spending a few days clicking through and setting
this stuff up. Instead, you can very easily setup the base replication
configuration, generate a script, and then add all of the filters into the
script, in a fraction of the time it takes to use the GUI.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||How many tables? Did you select the option to automatically generate
filters? This is very lengthy for large related tables in both SQL 2000 and
SQL 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||I have about 10 tables that use the 'OR' filter that links to one
table. What I did was script the package from SQL 2000 and ran it in
Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
filter will be created in less than a second. With the 'OR' statement,
I usually end up cancelling it after 5-10 minutes for each table with
that filter. (We were testing with the CTP Sept. version, and did not
have this issue...Could it be Standard vs. Enterprise?)
Thanks,
Amy
|||No, the edition doesn't matter. I can't reproduce this on the RTM bits.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1136292153.538075.219420@.g14g2000cwa.googlegr oups.com...
>I have about 10 tables that use the 'OR' filter that links to one
> table. What I did was script the package from SQL 2000 and ran it in
> Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
> filter will be created in less than a second. With the 'OR' statement,
> I usually end up cancelling it after 5-10 minutes for each table with
> that filter. (We were testing with the CTP Sept. version, and did not
> have this issue...Could it be Standard vs. Enterprise?)
> Thanks,
> Amy
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?
The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the device
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
--
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:
> What exactly are these "I/O" devices? Are these in addition to the device
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> >
> > We're using SQL2005 standard edition.
> > We have a system with two data I/O devices, each having one data file we
> > where wondering what the best aproach is in assigning tables/data to these
> > groups:
> >
> > - Make one filegroup with two files (one per I/O device) each equally
> > sized
> > and all tables in the db are assigned to this group
> > According to BOL SQL Server will fill both files in the group
> > in a round robin way -extend files equally-
> >
> > - Make two filegroups each with one file and assign individual tables to
> > the
> > files in
> > the group manually, and try to figure out the best 'balance' ourselves.
> > Downside: takes a lot of time.
> >
> > any hints on approach/practices?
> >
>
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the devic
e
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>