Merge Join's poor performance

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?

Liran R wrote:

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?

If I were you I would use the OLE DB Source component to do the join - there is absolutely nothing wrong with doing that. If you have a super-performant relational databsae engine at your disposal - why not use it?

Donald Farmer talks around this a little in his OVAL webcast. If you only watch one SSIS webcast in your life then it should be this one.

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||Thanks, I'll take a look. does the merge join takes place in the cache?|||

Can you elaborate as to what you mean by "the cache"?

-Jamie

|||

Sure. When I'm using a Lookup component to make the join, The lookup table is been cached by default. When I want to join to a large table (with several millions) I understood It's best practice to use the Merge Join Component, but as I see also this component caches the records in the machines memory, so I don't understand the benefit...

|||

There's a different sort of memory usage going on here.

Yes, the LOOKUP has an area of memory that we call the cache and is used for storing a lookup set.

MERGE JOIN stores data in memory (as do all asynchronous components) but its a different kind of memory and we don't refer to it as a cache. it is just the component's working area - more commonly termed a buffer. Also, this working area will change as MERGE JOIN does its work whereas the LOOKUP cache is static.

It is also worth saying that the MERGE JOIN can spool data to disk if it s running out of memory. LOOKUP cannot do that with its cache.

-Jamie

|||

Hi SSIS friend,

I remember asking a similar question a couple of months back. I think I had 15+ Merge Join components in a test package and the performance was awful. Jamie's advice then and now is pretty sound.

During my short period experimenting with SSIS, I came to realise that in order to create efficient packages, I had to utilise the power of both the SQL Server and SSIS engines. Each one is good at performing certain tasks better than the other. It takes time and I'm still learning, but the more you play around with it, the easier it gets to choose which engine should be used.

|||

Thanks guys.

sql

Merge Join's poor performance

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?

Liran R wrote:

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?

If I were you I would use the OLE DB Source component to do the join - there is absolutely nothing wrong with doing that. If you have a super-performant relational databsae engine at your disposal - why not use it?

Donald Farmer talks around this a little in his OVAL webcast. If you only watch one SSIS webcast in your life then it should be this one.

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||Thanks, I'll take a look. does the merge join takes place in the cache?|||

Can you elaborate as to what you mean by "the cache"?

-Jamie

|||

Sure. When I'm using a Lookup component to make the join, The lookup table is been cached by default. When I want to join to a large table (with several millions) I understood It's best practice to use the Merge Join Component, but as I see also this component caches the records in the machines memory, so I don't understand the benefit...

|||

There's a different sort of memory usage going on here.

Yes, the LOOKUP has an area of memory that we call the cache and is used for storing a lookup set.

MERGE JOIN stores data in memory (as do all asynchronous components) but its a different kind of memory and we don't refer to it as a cache. it is just the component's working area - more commonly termed a buffer. Also, this working area will change as MERGE JOIN does its work whereas the LOOKUP cache is static.

It is also worth saying that the MERGE JOIN can spool data to disk if it s running out of memory. LOOKUP cannot do that with its cache.

-Jamie

|||

Hi SSIS friend,

I remember asking a similar question a couple of months back. I think I had 15+ Merge Join components in a test package and the performance was awful. Jamie's advice then and now is pretty sound.

During my short period experimenting with SSIS, I came to realise that in order to create efficient packages, I had to utilise the power of both the SQL Server and SSIS engines. Each one is good at performing certain tasks better than the other. It takes time and I'm still learning, but the more you play around with it, the easier it gets to choose which engine should be used.

|||

Thanks guys.

Merge Join (Full outer join) never finishes.

I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?

Try this - replace your destination with a RowCount component - it does nothing except count rows into a variable. if the package succeeds, then you have narrowed the problem down to an issue with your destination.

If there is an issue with your destination, you should start to look into disk and memory usage.

hth - I'll look forward to hearing more.

Donald

|||The way I worked around the problem was to separate it into 2 dataflows, the first one just taking the left branch of the input to the merge join and landing it to a raw destination. Then in the second data flow (the merge join) I simply used the raw file as the left input and it worked fine. Does that eliminate the possibility of it being an issue with the destination? If not then I will recreate the original scenario and replace the destination with a RowCount component as you suggested. Thanks.|||

That's a tricky one. I wonder if the combined memory usage of your script component and the merge join was stressing the box.

Donald

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!
/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 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 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 of sqlservr.exe

Hi,

Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.

The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.

regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.

To set a fixed amount of memory

Expand a server group.

Right-click a server, and then click Properties.

Click the Memory tab.

Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!

Thaks for your valuable input.

Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.

Please advise.

Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!

I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.

Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.

Best regards!

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 ***

Memory Usage Grows over time

I am very new to TSQL and the program that follows loops many times over several hours. In task manager I have noticed that the PF usage has grown from 1.08 to 2.05 over several hours and results in a virtual memory shortage error being displayed. I am inclined to believe I am doing something wrong and accumulating some stack space. Can anyone tell me what I am doing wrong? PF does drop back to normal when I drop out of SQL.

thanks in advance

-Soup-

use sm

declare @.test_date as smalldatetime

declare @.ticker char(6)

declare @.buy_date smalldatetime

declare @.buy_price smallmoney

declare @.sell_date_5d smalldatetime

declare @.sell_price_5d smallmoney

declare @.sell_date_10d smalldatetime

declare @.sell_price_10d smallmoney

declare @.sell_date_20d smalldatetime

declare @.sell_price_20d smallmoney

if object_id('sm.dbo.Open_High_v2')is not null

drop table sm.dbo.Open_High

create table sm.dbo.Open_High

( ticker char(6),

detect_date smalldatetime,

buy_date smalldatetime,

buy_price smallmoney,

sell_date_5d smalldatetime,

sell_price_5d smallmoney,

sell_date_10d smalldatetime,

sell_price_10d smallmoney,

sell_date_20d smalldatetime,

sell_price_20d smallmoney,

)

if object_id('sm.dbo.Temp_Price')is null

create table sm.dbo.Temp_Price

(

price char (6),

Trade_Date smalldatetime,

[Open] smallmoney,

High smallmoney,

Low smallmoney,

[Close] smallmoney,

Volume int

)

if object_id('date_list') is not null

begin

close date_list

deallocate date_list

end

declare date_list cursor for

select distinct ticker, trade_date

from price

order by Ticker asc, Trade_Date asc

open date_list

fetch next from date_list into @.ticker,@.test_date

while (@.@.fetch_status=0)

begin

set @.buy_date=NULL

set @.buy_price=NULL

set @.sell_date_5d=NULL

set @.sell_price_5d=NULL

set @.sell_date_10d=NULL

set @.sell_price_10d=NULL

set @.sell_date_20d=NULL

set @.sell_price_20d=NULL

insert into sm.dbo.temp_price

select Top 5 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.buy_date=(select min(trade_date)from sm.dbo.temp_price)

set @.buy_price=(select [open] from sm.dbo.temp_price where trade_date=@.buy_date)

set @.sell_price_5d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_5d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

insert into sm.dbo.temp_price

select Top 10 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.sell_price_10d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_10d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

insert into sm.dbo.temp_price

select Top 20 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.sell_price_20d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_20d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

BEGIN TRANSACTION;

insert into sm.dbo.open_high_v2

values

(

@.ticker,

@.test_date,

@.buy_date,

@.buy_price,

@.sell_date_5d,

@.sell_price_5d,

@.sell_date_10d,

@.sell_price_10d,

@.sell_date_20d,

@.sell_price_20d

)

COMMIT TRANSACTION;

fetch next from date_list into @.ticker,@.test_date

end

close date_list

deallocate date_list

select * from sm.dbo.Open_High

One cause may be you're using transactions without error checking which reserves memory. If you're just inserting without error checking, you don't need to use transactions.

Adamus

|||

transactions was a desparate attempt to stop pf growth. pf growth was present before transaction start and commit were added.

Thanks for the idea.

-Soup-

|||

After looking at the code further, it appears the overhead you're concerned with is a necessary evil in order to accomplish your task.

Although performance should always be a concern, sometimes the elephant in the living room serves a productive and inevitable purpose.

Adamus

|||

Without further informaion on what I am doing incorrectly in TSQL, I guess it is time to turn in a bug report to the SQL developement team. Repeated loops of a program appear to discover a problem with TSQL or SQL server. Is this an acceptable trouble report process, or do I need to go somewhere else to enter this as a formal trouble report?

-Soup-

|||

I didn't understand your trouble..

There are someother way there to achive your desired output.. why not, you can try to rewrite your query in PROPER & best way.. as other says..

|||I have to withdraw previous post about memory growing over time. Something other than this program must have been the problem. Regarding Proper & best way, as I said I am new to TSQL and have know idea how this program differs from Proper & best way. I do know that this program may have been a learning experience for me, but I allowed it to execute for 11 Days, yes Days and it did not complete. After only 8 hours a C++ program did the job. I suspect my skill at TSQL is at fault.|||

For improving performance of above SQL queries, You can use "table" data type instead of physical temp tables temp_price and Open_High_v2. Another problem is cursor. First you can take all data from "price" table into another temp table. This temp table also derived by "table" datatype. And apply cursor on this table. Next you can remove "Begin Transaction" and "commit transaction" statements...

Next check performance of TSQL...

Jefy

|||Lay off the crack pipe Jefy

Memory usage

Why the memory usage in task manager shows significant difference before and
after the restart of SQL Server service ? Is that due to the memory leak ?
I am running SQL Server 2000 sp3a
Thanks very much,
natasa
Natasa,
If SQL Server's memory is set to be dynamic (default), it will grow to the
min boundary then up-to the max boundary (if needed) then fluctuate in
between.
HTH
Jerry
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa
|||also if you have /PAE enabled (> 4GB RAM) , Task Manager will say a silly
amount for SQLServr.exe - around 100mb. In this scenario Perfmon/SQL Server
Memory Manager/Total Server Memory is the correct thing to look at
cheers,
Andy
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa
|||to regulate SQL memory usage
use WSRM windows system resource manager
for win2k3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"natasa" wrote:

> Why the memory usage in task manager shows significant difference before and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa

Memory usage

Why the memory usage in task manager shows significant difference before and
after the restart of SQL Server service ? Is that due to the memory leak ?
I am running SQL Server 2000 sp3a
Thanks very much,
natasaNatasa,
If SQL Server's memory is set to be dynamic (default), it will grow to the
min boundary then up-to the max boundary (if needed) then fluctuate in
between.
HTH
Jerry
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||also if you have /PAE enabled (> 4GB RAM) , Task Manager will say a silly
amount for SQLServr.exe - around 100mb. In this scenario Perfmon/SQL Server
Memory Manager/Total Server Memory is the correct thing to look at
cheers,
Andy
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||to regulate SQL memory usage
use WSRM windows system resource manager
for win2k3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"natasa" wrote:

> Why the memory usage in task manager shows significant difference before a
nd
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa