Merge Agent and ActiveX Parameters for Replication

Hi All,
I currently use the SQL Merge ActiveX control to perform synchronization
from my application. In SQL Enterprise Manager, you can configure the Merge
Agent Parameters (-KeepMessageAliveInterval, -QueryTimeout, etc.), but you
can also do this from the ActiveX control. My question is, which one takes
precedent? If I configure values in the Merge Agent's Parameters, then
configure different values in the ActiveX control, which are used? Does the
server take precedence? I would think the ActiveX control's parameters
would be used, but I just want to make sure.
Regards,
Brad
you can specify which profile you want to use. If you try to right click on
your agent, when it is created by an activeX control you will find that you
are unable to set these properties.
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
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:OEZ57eopFHA.2504@.tk2msftngp13.phx.gbl...
> Hi All,
> I currently use the SQL Merge ActiveX control to perform synchronization
> from my application. In SQL Enterprise Manager, you can configure the
Merge
> Agent Parameters (-KeepMessageAliveInterval, -QueryTimeout, etc.), but you
> can also do this from the ActiveX control. My question is, which one
takes
> precedent? If I configure values in the Merge Agent's Parameters, then
> configure different values in the ActiveX control, which are used? Does
the
> server take precedence? I would think the ActiveX control's parameters
> would be used, but I just want to make sure.
> Regards,
> Brad
>

Merge Agent

SQL Server Enterprise manager showing Distribution database as SUSPECT and
in result replication monitor do not show any information about replication
agent(Merge agent etc).
I detach database and try to re attach but system showing that database cant
be attached as "log scan" to those file is failed(reflecting files
corrupted).
I attach backup of distribution mdf and ldf file, this time it attached
sucessfully.
BUT the problem remains same, i.e REPLICATION MONITOR IS NOT SHOWING ANY
INFORMATION ABOUT MERGE AGENT.
NOTE. now distribution databse have merge agents record in "msmerge_agents"
table.
Thanks in advance
Best thing to do is restore from back up. Other than that you might try a
sp_attach_single_file_db
Then follow these instructions
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
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
"Anjaana Rahi" <anjaanarahi@.gmail.com> wrote in message
news:OgjgwZhBGHA.2704@.TK2MSFTNGP15.phx.gbl...
> SQL Server Enterprise manager showing Distribution database as SUSPECT
> and
> in result replication monitor do not show any information about
> replication
> agent(Merge agent etc).
> I detach database and try to re attach but system showing that database
> cant
> be attached as "log scan" to those file is failed(reflecting files
> corrupted).
> I attach backup of distribution mdf and ldf file, this time it attached
> sucessfully.
> BUT the problem remains same, i.e REPLICATION MONITOR IS NOT SHOWING ANY
> INFORMATION ABOUT MERGE AGENT.
> NOTE. now distribution databse have merge agents record in
> "msmerge_agents"
> table.
> Thanks in advance
>
>
|||Oops, sorry I missed the part about how your backup worked (but the merge
agents are now missing).
Try to script out the publications, then drop them and do a no-sync.
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
"Anjaana Rahi" <anjaanarahi@.gmail.com> wrote in message
news:OgjgwZhBGHA.2704@.TK2MSFTNGP15.phx.gbl...
> SQL Server Enterprise manager showing Distribution database as SUSPECT
> and
> in result replication monitor do not show any information about
> replication
> agent(Merge agent etc).
> I detach database and try to re attach but system showing that database
> cant
> be attached as "log scan" to those file is failed(reflecting files
> corrupted).
> I attach backup of distribution mdf and ldf file, this time it attached
> sucessfully.
> BUT the problem remains same, i.e REPLICATION MONITOR IS NOT SHOWING ANY
> INFORMATION ABOUT MERGE AGENT.
> NOTE. now distribution databse have merge agents record in
> "msmerge_agents"
> table.
> Thanks in advance
>
>

merge agent

sql server enterprise manager could not start merge agent
error:22022 can any one tell the solution for this would be appreciated.
Message posted via http://www.droptable.com
I think we need more info...what is the whole error message?
However, here are some tips on getting more details.
-Check the agent history (expand steps) for more detail about errors.
-Put an output file on the merge agent (use -Output c:\merge.txt
-OutputVerboseLevel 3)
-Try starting the agent from the command line (with an output file! :-))
-Check SQL Errorlog, SQL Agent Errorlog, and Application and System event
logs for more errors.
Tell us what you find so we can be more help. Or let me know if you need
exact steps on setting up an output file.
Donna
"pardhi a via droptable.com" wrote:

> sql server enterprise manager could not start merge agent
> error:22022 can any one tell the solution for this would be appreciated.
> --
> Message posted via http://www.droptable.com
>
|||Hi Donna
Thanks it is working.Thanks for immediate response.
as you said i copied into the file then saw an error showing (cannot
generate SSPI context) then i stop and restart the agent from then onwards
starts working fine.you have any idea why (cannnot generate sspi context)
cause of the error.
Message posted via http://www.droptable.com

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

You can look at some easy PerfMon counters, such as Buffer Cache Hit Ratio, Page Life Expectancy, and Memory Grants Pending to get a better idea about whether you are seeing memory pressure. Memory pressure will also cause increased IO pressure.

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

You can look at some easy PerfMon counters, such as Buffer Cache Hit Ratio, Page Life Expectancy, and Memory Grants Pending to get a better idea about whether you are seeing memory pressure. Memory pressure will also cause increased IO pressure.

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong?
Tnx in advance!
/Magnus
Hi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong?
> Tnx in advance!
> /Magnus
>
>
|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong?
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/defaul...b;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage i
n
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doin
g
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;

I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;

select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC

There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong??

Tnx in advance!

/MagnusMagnus sterberg (magnus.osterberg@.abo.fi) writes:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
> in Task manager. It is ca 20 mb, everything is OK. Then I run this
> query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable
> contains ca 916.000 rows. I thought this query would still execute in
> only a few seconds, but it takes minutes. And the worst thing is that
> sqlserver.exe's memory usage grows to about 300-400 mb when the query
> runs. What am I doing wrong??

So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.

SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.

SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.

Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.

The query itself would benefit enormously by a non-clustered index on
postoffice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C183A0F9A0DYazorman@.127.0.0.1...
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> I am experiencing the following problem;
>>
>> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
>> in Task manager. It is ca 20 mb, everything is OK. Then I run this
>> query;
>>
>> select postoffice, count(*) as counter from tblTable
>> where postoffice is not null
>> group by postoffice
>> order by counter DESC
>>
>> There's no index or constraint on field "postoffice" and tblTable
>> contains ca 916.000 rows. I thought this query would still execute in
>> only a few seconds, but it takes minutes. And the worst thing is that
>> sqlserver.exe's memory usage grows to about 300-400 mb when the query
>> runs. What am I doing wrong??
> So what is the average row size of this table? Say that is 300 bytes,
> then that is 300 MB of data to read. That is not very likely to be done
> instantly.
> SQL Server's memory consumption will increase, as it will read the entire
> table into cache, and the table will stay in the cache as long as no
> other data competes about the space. This means that if you resubmit the
> query, the response time will be significantly shorter.
> SQL Server is designed to grab as much memory it can, as the more data
> in can have in cache, the better the response times. If there are other
> applications competing for memory on the machine, SQL Server will yield,
> but in this case it may be better to configure how much memory you want
> SQL Server to use.
> Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
> to see what shape the table is in. To defragment it, you would have to
> create a clustered index on the table, and then drop that index.
> The query itself would benefit enormously by a non-clustered index on
> postoffice.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks for a well-written answer!

I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds.
One index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.

Isn't there any other ways of speeding up selects? Well, I guess not...

/Magnus|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I guess my expectations on selecting based on a non-indexed column were
> somewhat high. When I added an index, my query executes in seconds. One
> index solved this problem, but my table contains 30-40 similar columns,
> and I don't think indexing every column is a good idea.
> Isn't there any other ways of speeding up selects? Well, I guess not...

Well, once data is in cache it will be faster. Or at least less slow.

But if you need to do this on every column, it sounds to me like one
of those things Analysis Services is good for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Magnus sterberg wrote:
> I guess my expectations on selecting based on a non-indexed column
> were somewhat high. When I added an index, my query executes in
> seconds.
> One index solved this problem, but my table contains 30-40 similar
> columns, and I don't think indexing every column is a good idea.

Two things come to mind:

1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.

2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.

> Isn't there any other ways of speeding up selects? Well, I guess
> not...

Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, ... It all
depends. :-)

Kind regards

robert|||Thank you Robert, these all are things that I'll keep in mind.

One more thing though; why is the sqlservr.exe process consuming 395 mb RAM
when the entire server is idle?
My "cron job" is inserting a few 1000 rows of data at night, but now it is
late morning here in Finland, and the entire machine is more or less idle.
On my other servers, the RAM consumption on idle is about 20-30 mb only.
Anyone got any ideas?

/Magnus

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3ngkc3F1dnm7U1@.individual.net...
> Magnus sterberg wrote:
>> I guess my expectations on selecting based on a non-indexed column
>> were somewhat high. When I added an index, my query executes in
>> seconds.
>> One index solved this problem, but my table contains 30-40 similar
>> columns, and I don't think indexing every column is a good idea.
> Two things come to mind:
> 1. rethink your table design. If these columns are so similar you might
> be better off with a second table which is joined. But that of course
> depends on your data - just an idea.
> 2. Create indexes (possibly composite indexes) for most used queries. You
> might even get away with a single (or few) composite index if your queries
> only use a leading portion of this index's fields.
>> Isn't there any other ways of speeding up selects? Well, I guess
>> not...
> Well, there are tons of other options, like having data files on several
> disks, putting tx log on a separate disk, adding memory, ... It all
> depends. :-)
> Kind regards
> robert|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> One more thing though; why is the sqlservr.exe process consuming 395 mb
> RAM when the entire server is idle?

I believe that was in my first reply.

SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered without
reading from disk.

Thus, this is perfectly normal behaviour.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> One more thing though; why is the sqlservr.exe process consuming 395
>> mb RAM when the entire server is idle?
> I believe that was in my first reply.
> SQL Server is designed to get as much memory as it can, and only yield
> if an another application needs it. This is because it keeps data in
> cache so that future requests for the same data can be answered
> without reading from disk.
> Thus, this is perfectly normal behaviour.

Adding to that max memory consumption is easily configurable so if 400MB
is too much for you then simply turn that down.

Kind regards

robert

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus Ã?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus Österberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory Usage on SQL2k

We have a SQL Server Enterprise edition cluster.
The server has 2.5GB of RAM and SQL Server has Max Server Memory set to 2GB.
However, the Task Manager indicates that SQL Server is only using about 1750MB
There's no question in my mind that this server's load is such that it should
use every ounce of RAM.
Do I need to set the /3GB switch?
I think this /3 switch will be ignored since your OS is not running with the Windows extended memory switch (AWE) with 3+ gig physical memory. ie... the sql mem manager is dependent on OS mem allocation on startup ?
This apparent margin of RAM that appears to not be utilized by sql could still be possibly or potentially utilized under certain load conditions that you have yet to observe from the perspective of task manager. SS memory management certainly does have a
"mind" of it's own...
|||The behavior you show is as expected. Your using TASK Manager to view
memory and that is not always accurate with sql server. Use the sql memory
counters in perfmon instead. By default any application (SQL Server is an
application) can use up to 2GB of memory and the OS can use up to 2GB. You
have 2.5GB of which SQL Server will use up to 2GB. There are different
parts of sql server that use different parts of the available memory. When
SQL Server starts up it will reserve by default 256MB of memory for what is
called Memory to Leave. This is where the memory for among several other
things the worker threads are spawned. This comes right off the top of the
2GB and the rest is left for the memory pool which will dynamically use the
rest up to 2GB. So 2GB - 256MB is approx. 1.75GB and what you are seeing.
This is normal. The OS can use the other .5GB for it's use and any other
apps that you may need to run on the server. You could use the /3GB switch
to allow sql server to use up to 3GB (if you had that much) but in your case
you would starve the OS and paging would occur. Unless you add more ram I
wouldn't touch what you have.
Andrew J. Kelly SQL MVP
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy. com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?
|||The other answers are great. I wouldn't to stress what Andrew pointed out...
regardless of whether you can or not.. use the switch in this case...
you would ALWAYS want to allow the OS to have a reasonable amount of space
for memory .5G on a 2.5 G big is really a min IMHO. Many people starve the
OS without ever realizing it... For example... on a 4G box.. you would
really want to give the OS ~1G.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy. com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?

Memory Usage on SQL2k

We have a SQL Server Enterprise edition cluster.
The server has 2.5GB of RAM and SQL Server has Max Server Memory set to 2GB.
However, the Task Manager indicates that SQL Server is only using about 1750
MB
There's no question in my mind that this server's load is such that it shoul
d
use every ounce of RAM.
Do I need to set the /3GB switch?I think this /3 switch will be ignored since your OS is not running with the
Windows extended memory switch (AWE) with 3+ gig physical memory. ie... the
sql mem manager is dependent on OS mem allocation on startup ?
This apparent margin of RAM that appears to not be utilized by sql could sti
ll be possibly or potentially utilized under certain load conditions that yo
u have yet to observe from the perspective of task manager. SS memory manage
ment certainly does have a
"mind" of it's own...|||The behavior you show is as expected. Your using TASK Manager to view
memory and that is not always accurate with sql server. Use the sql memory
counters in perfmon instead. By default any application (SQL Server is an
application) can use up to 2GB of memory and the OS can use up to 2GB. You
have 2.5GB of which SQL Server will use up to 2GB. There are different
parts of sql server that use different parts of the available memory. When
SQL Server starts up it will reserve by default 256MB of memory for what is
called Memory to Leave. This is where the memory for among several other
things the worker threads are spawned. This comes right off the top of the
2GB and the rest is left for the memory pool which will dynamically use the
rest up to 2GB. So 2GB - 256MB is approx. 1.75GB and what you are seeing.
This is normal. The OS can use the other .5GB for it's use and any other
apps that you may need to run on the server. You could use the /3GB switch
to allow sql server to use up to 3GB (if you had that much) but in your case
you would starve the OS and paging would occur. Unless you add more ram I
wouldn't touch what you have.
Andrew J. Kelly SQL MVP
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?|||The other answers are great. I wouldn't to stress what Andrew pointed out...
regardless of whether you can or not.. use the switch in this case...
you would ALWAYS want to allow the OS to have a reasonable amount of space
for memory .5G on a 2.5 G big is really a min IMHO. Many people starve the
OS without ever realizing it... For example... on a 4G box.. you would
really want to give the OS ~1G.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?

Memory Usage On SQL 2K

I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it is
showing well over 7GB in use but the processes do not add up to near that
amount. How can I determine what is grabbing all the memory on this server?
Thanks, Sean
Duplicate Post
"Sean" wrote:

> I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it is
> showing well over 7GB in use but the processes do not add up to near that
> amount. How can I determine what is grabbing all the memory on this server?
> Thanks, Sean

Memory Usage On SQL 2K

I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it is
showing well over 7GB in use but the processes do not add up to near that
amount. How can I determine what is grabbing all the memory on this server?
Thanks, SeanDuplicate Post
"Sean" wrote:

> I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it i
s
> showing well over 7GB in use but the processes do not add up to near that
> amount. How can I determine what is grabbing all the memory on this serve
r?
> Thanks, Sean

Memory Usage On SQL 2K

I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it is
showing well over 7GB in use but the processes do not add up to near that
amount. How can I determine what is grabbing all the memory on this server?
Thanks, SeanDuplicate Post
"Sean" wrote:
> I have SQL 2000 running with 8GG of RAM and 8 procs. In Task Manager it is
> showing well over 7GB in use but the processes do not add up to near that
> amount. How can I determine what is grabbing all the memory on this server?
> Thanks, Sean

Memory Usage on SQL 2000

Good Morning All,
I have a SQL server with 8GB of memory and 8 processors. The memory use in
Task Manager is showing over 7GB in use but the processes in Task Manager do
not add up to anywhere near that amount of RAM being used.
My question is how do I determine what is using all of the RAM on this server?
Thanks, SeanHi
Once you use PAE or AWE, task manager does not show the memory correctly.
You need to look at the Performance Counters presented in the OS perfmon
utility.
The see what is configured for SQL run the following in QA:
sp_configure 'show advanced options', 1
sp_configure 'awe enabled'
sp_configure 'min server memory'
sp_configure 'max server memory'
Regards
Mike
"Sean" wrote:
> Good Morning All,
> I have a SQL server with 8GB of memory and 8 processors. The memory use in
> Task Manager is showing over 7GB in use but the processes in Task Manager do
> not add up to anywhere near that amount of RAM being used.
> My question is how do I determine what is using all of the RAM on this server?
> Thanks, Sean

Memory Usage on SQL 2000

Good Morning All,
I have a SQL server with 8GB of memory and 8 processors. The memory use in
Task Manager is showing over 7GB in use but the processes in Task Manager do
not add up to anywhere near that amount of RAM being used.
My question is how do I determine what is using all of the RAM on this server?
Thanks, Sean
Hi
Once you use PAE or AWE, task manager does not show the memory correctly.
You need to look at the Performance Counters presented in the OS perfmon
utility.
The see what is configured for SQL run the following in QA:
sp_configure 'show advanced options', 1
sp_configure 'awe enabled'
sp_configure 'min server memory'
sp_configure 'max server memory'
Regards
Mike
"Sean" wrote:

> Good Morning All,
> I have a SQL server with 8GB of memory and 8 processors. The memory use in
> Task Manager is showing over 7GB in use but the processes in Task Manager do
> not add up to anywhere near that amount of RAM being used.
> My question is how do I determine what is using all of the RAM on this server?
> Thanks, Sean

Memory Usage on SQL 2000

Good Morning All,
I have a SQL server with 8GB of memory and 8 processors. The memory use in
Task Manager is showing over 7GB in use but the processes in Task Manager do
not add up to anywhere near that amount of RAM being used.
My question is how do I determine what is using all of the RAM on this serve
r?
Thanks, SeanHi
Once you use PAE or AWE, task manager does not show the memory correctly.
You need to look at the Performance Counters presented in the OS perfmon
utility.
The see what is configured for SQL run the following in QA:
sp_configure 'show advanced options', 1
sp_configure 'awe enabled'
sp_configure 'min server memory'
sp_configure 'max server memory'
Regards
Mike
"Sean" wrote:

> Good Morning All,
> I have a SQL server with 8GB of memory and 8 processors. The memory use i
n
> Task Manager is showing over 7GB in use but the processes in Task Manager
do
> not add up to anywhere near that amount of RAM being used.
> My question is how do I determine what is using all of the RAM on this ser
ver?
> Thanks, Sean

memory usage of aspnet_wp.exe

Hi Friends
when i open task manager i see that "aspnet_wp.exe" taking over 100,000 k (memory usage).
is it normal ? i dont have any issues but sometimes my machine runs sluggishly and wondering is it bcoz aspnet_wp.exe?
any ideas on this much appreciated.

am running sql server 2005 standard edition and visual studio standard edi on xp prof mechine.

It's not necessarily abnormal, depending on what kinds of reports you're working with. We rely on ASP.Net and CLR for memory management, and those layers are responsible for garbage collection and memory reuse. You can recycle IIS (iisreset.exe) to force an unload of the app domain, which will unload all the memory.

Memory usage in Task Manager

Hallo,

I am a newbie on SQL server and my problem is this:

My SQL server runs with many client queries and after a while I can observe that the meory usage of the SQL server shown in the Windows Task Manager is growing up (e.g. 260 MB !!!).

I checked the online books and found the settings "min/max server memory" which I set to

min = 4 MB
max = 20 MB

by Enterprise manager.

Then I restarted my SQL server, checked the memory settings again by Enterprise manager and started many client queries. The memory usage in Task Manager nevertheless exeeded the 20 MB.

What is my failure? How can I limit the memory usage of SQL server?

Thank you very much for any help...After the change, did you stop and restart SQL?

Memory usage in SQL Server

Hello all,

How can I tell how much memory SQL Server is using on a server. On
Windows 2000, whenever I go to Task manager/processes/memory usage SQL
Server seems to be showing 1,744,124K. On all of my servers with
various size, usage of databases, all of them show SQL Server to be
using about the same amount of memory. Can someone explain this to me?
Shouldn't it use more for larger databases, heavy hitting databases?

Also, I normally check Dynamically configure SQL Server memory and put
the maximum threshold to a little bit less than the max on the server.
The minimum query memory is set to 1024. Is that 1024 a subset of the
memory used by SQL Server, or is this additional that can be used?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> How can I tell how much memory SQL Server is using on a server. On
> Windows 2000, whenever I go to Task manager/processes/memory usage SQL
> Server seems to be showing 1,744,124K. On all of my servers with
> various size, usage of databases, all of them show SQL Server to be
> using about the same amount of memory. Can someone explain this to me?
> Shouldn't it use more for larger databases, heavy hitting databases?

So how much memory is available on these boxes? Which edition of SQL
Server are they running?

SQL Server is designed to grab as much memory there is available, so if
these servers have 2GB of memory, it is very likely that all servers
by time reach this level. (The rest is left to the Operating System.(

> Also, I normally check Dynamically configure SQL Server memory and put
> the maximum threshold to a little bit less than the max on the server.

Actually, there is no reason to set any limit at all, unless there
are other apps on the machine. If you do this setting, add more memory,
and forget the setting, the new memory will not be used.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||These boxes have about 4 gigs of memory total. They are running SQL
2000 SP3. But all servers regardless of activity and size or number of
databases are showing as SQL Server using the same amount of memory.

*** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> These boxes have about 4 gigs of memory total. They are running SQL
> 2000 SP3. But all servers regardless of activity and size or number of
> databases are showing as SQL Server using the same amount of memory.

And which edition? Standard Editoin cannot acceess more than 2GB of
memory, as I recall.

Enterrise Manager can access more, but you need to use the /AWE, /PAE
or /3GB switches in your Boot.ini. I don't this sort of memory configuration
myself, but search for this in Knowledge Base, and you should find some
useful information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C463036A89Yazorman@.127.0.0.1...
> Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> > These boxes have about 4 gigs of memory total. They are running SQL
> > 2000 SP3. But all servers regardless of activity and size or number of
> > databases are showing as SQL Server using the same amount of memory.
> And which edition? Standard Editoin cannot acceess more than 2GB of
> memory, as I recall.

Correct. And 1.7 is in the ballpark of a maxed out server from what we
have.

> Enterrise Manager can access more, but you need to use the /AWE, /PAE

Err, I think you mean the Enterprise Version. And I believe this is only
Advanced Server 2000 (or if you use certain versions of 2003)

> or /3GB switches in your Boot.ini. I don't this sort of memory
configuration
> myself, but search for this in Knowledge Base, and you should find some
> useful information.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||It is Enterprise Edition. So why are all my enterprise editions showing
SQL using 1.7gigs of memory when there is quite more available. And why
are all of them using the same amount? Some have more activity than
others, some have larger databases than others. But all of them are
using the same amount of memory? Por Que?

*** Sent via Developersdex http://www.developersdex.com ***|||It is Enterprise Edition. So why are all my enterprise editions showing
SQL using 1.7gigs of memory when there is quite more available. And why
are all of them using the same amount? Some have more activity than
others, some have larger databases than others. But all of them are
using the same amount of memory? Por Que?

*** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> It is Enterprise Edition. So why are all my enterprise editions showing
> SQL using 1.7gigs of memory when there is quite more available. And why
> are all of them using the same amount? Some have more activity than
> others, some have larger databases than others. But all of them are
> using the same amount of memory? Por Que?

I suggested last night that you search Knowledge Base, as I was just
off to bed. Apparently, you prefer to someone else do that work for you,
so I performed this search:
http://support.microsoft.com/search...&mode=a&x=0&y=0

This article looks like the best start:
http://support.microsoft.com/defaul...kb;en-us;274750.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:jcHRe.53$ON3.4168@.news.uswest.net...
> It is Enterprise Edition. So why are all my enterprise editions showing
> SQL using 1.7gigs of memory when there is quite more available. And why
> are all of them using the same amount? Some have more activity than
> others, some have larger databases than others. But all of them are
> using the same amount of memory? Por Que?
>
> *** Sent via Developersdex http://www.developersdex.com ***

As Erland has already explained, SQL Server doesn't release memory once it
has grabbed it. Thus it doesn't take it long to grab memory up to any limit
that might be set. My guess is that if you open enterprise manager, and go
to server properties and look on the memory tab, that sql server has been
configured to use up to about 1.7gb.

Brian.

--
www.cryer.co.uk/brian|||That is not the case. My SQL Servers have been configured to use more
than 1.7G of memory. But they are all using the same amount 1.7G.

Raziq.

*** Sent via Developersdex http://www.developersdex.com ***