Merge Dynamic filter problem

Hi!

I'm a merge newbie and have a couple of questions. I'm about to setup a merge replication with Sql Server 2005 and Sql Server CE as a subscriber. Situation is like this, we have 10 service technicians using pda.

I want to each pda user have their own data. What I understand I need to use dynamic filter and SUSER_NAME()? Do I need to create a "translation" table to map my system's UserId against SUSER_NAME? How have you solved this problem?

/Magnus

Hello magnus,

One easy approach is to have a column in the table for filtering purpose.

Please take a look on sp_addmergearticle (Transact-SQL) , from http://msdn2.microsoft.com/en-us/library/ms174329.aspx.

[ @.subset_filterclause = ] 'subset_filterclause'
Is a WHERE clause specifying the horizontal filtering of a table article without the word WHERE included. subset_filterclause is of nvarchar(1000), with a default of an empty string.

Important:
For performance reasons, we recommended that you not apply functions to column names in parameterized row filter clauses, such as LEFT([MyColumn]) = SUSER_SNAME(). If you use HOST_NAME in a filter clause and override the HOST_NAME value, you might have to convert data types by using CONVERT. For more information about best practices for this case, see the section "Overriding the HOST_NAME() Value" in Parameterized Row Filters.

Parameterized Row Filters - http://msdn2.microsoft.com/en-us/library/ms152478.aspx.

and

[ @.partition_options = ] partition_options
Defines the way in which data in the article is partitioned, which enables performance optimizations when all rows belong in only one partition or in only one subscription. partition_options is tinyint, and can be one of the following values.

Value Description
0 (default)
The filtering for the article either is static or does not yield a unique subset of data for each partition, that is, an "overlapping" partition.
1
The partitions are overlapping, and data manipulation language (DML) updates made at the Subscriber cannot change the partition to which a row belongs.
2
The filtering for the article yields non-overlapping partitions, but multiple Subscribers can receive the same partition.
3
The filtering for the article yields non-overlapping partitions that are unique for each subscription.

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
Thierry
First off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>
|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Even
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>
|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.
|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Andrew J. Kelly wrote:
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
>> Thanks,
>> But I use SpotLigth to monitor my server and if I limit the sql
>> server memory to 500 MB, It use 500 MB, afterv that I set no limit
>> and the memory grow up to 1.24 GB never upper.
>> Why ?
>> Thanks
>> "Andrew J. Kelly" wrote:
>> First off what are you using to determine the memory? DOn't use
>> task manager use Perfmon and the proper SQL Server memory counters.
>> SQL Server will only use memory if it needs to. It simply may not
>> need all 2GB. Even so with 2GB you likely to only see about
>> 1.75GB's used for SQL Server anyway.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
>> message news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>> HI,
>> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2
>> cpu, 2 GB of ram
>> The memory is configuring as dynamic, no max, no min
>> So, my question is :
>> Why the sql server use only 1.24 GB of ram et no 2 GB ?
>> Thanks a lot
>> Regards
>> Thierry
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:
> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> > Thanks,
> >
> > But I use SpotLigth to monitor my server and if I limit the sql server
> > memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> > grow up to 1.24 GB never upper.
> >
> > Why ?
> >
> > Thanks
> >
> > "Andrew J. Kelly" wrote:
> >
> > > First off what are you using to determine the memory? DOn't use task
> > > manager use Perfmon and the proper SQL Server memory counters. SQL
> Server
> > > will only use memory if it needs to. It simply may not need all 2GB.
> Even
> > > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > > anyway.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message
> > > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > > HI,
> > > >
> > > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
> 2 GB
> > > > of ram
> > > > The memory is configuring as dynamic, no max, no min
> > > >
> > > > So, my question is :
> > > >
> > > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > > >
> > > > Thanks a lot
> > > >
> > > > Regards
> > > >
> > > > Thierry
> > > >
> > >
> > >
> > >
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Eve
n
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sq
l
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>