merge conflicts resolution en-masse

Recently added table to merge, another process is moving the records between
servers and beating merge causing conflicts. App so big - dev will have to
spend many hours finding cause & resolving. Until then I have to deal w/
conflicts...
How do I resolve conflicts when I have thousands of them? I want to keep the
winning change for all. Is it safe to simply delete all records from the
conflict table:
delete from conflict_MergePubName_MyTableName
Thanks
CB
Chris,
if you're happy with the default conflict resolution that has already
occurred, you should be able to cursor through your records and call
sp_deletemergeconflictrow
eg: exec sp_deletemergeconflictrow 'conflict_NorthwindRegionsMerge_Region',
'[dbo].[Region]', '{9E93B574-55A3-4D72-A108-EED0C8E6B899}',
'UK-3XSW02J.pubs'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge Agents Don't "Retry" After SQL Server Error 8645?

During a memory- and CPU-intensive nightly database update process, the
merge agents (running on the server) for two subscribers failed with
SQL Server Error 8645 (A time out occurred while waiting for memory
resources to execute the query). It's not surprising to see this error,
but the fact that the agents did not retry as configured is surprising.
I discovered that this message is listed as a "severity" of 17 - is
there some threshold that prevents a retry by the agent? A restart of
the agent was successful.
Justin H.
How many merge agents do you have? Are you limiting the number of concurrent
merge agents?
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
"Justin H." <jhenry@.gmail.com> wrote in message
news:1134585801.416828.82300@.g44g2000cwa.googlegro ups.com...
> During a memory- and CPU-intensive nightly database update process, the
> merge agents (running on the server) for two subscribers failed with
> SQL Server Error 8645 (A time out occurred while waiting for memory
> resources to execute the query). It's not surprising to see this error,
> but the fact that the agents did not retry as configured is surprising.
>
> I discovered that this message is listed as a "severity" of 17 - is
> there some threshold that prevents a retry by the agent? A restart of
> the agent was successful.
> Justin H.
>
|||There are only two subscribers, so two merge agents with no concurrency
limit configured. I'm not so concerned about the error as long as a
retry happens as configured.

Merge agent fails due to deadlock

Hi:
I met a problem this morning. the merge replication agen stopped, the
error message shows:
The process could not enumerate changes at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
-----
Transaction (Process ID 79) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
(Source: XXXXXX (Data source); Error number: 1205)
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
I never met this issue before, so please help
1) What may cause this issue?
2) How I could identify which process cause the deadlock issue?
3) Is there any way to log more information to identify what resource
is deadlock, and by who.
Thanks
Yong
Yong,
have a look at enabling traceflags 1204, 1205, 3605 to get more info. Don't
forget to turn off afterwards. Alternatively if you have sql server 2005,
the profiler shows the deadlock tree graphically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge agent error

We have a client on SQL Server 2000 sp2 whose merge replication had the
following error:
The merge process could not update the last received generation.
Does anyone have an idea as to the possible causes?
thanks
It turned out o be query timout invlolving sp_msdelgenzero
"Peter Feakins" wrote:

> We have a client on SQL Server 2000 sp2 whose merge replication had the
> following error:
> The merge process could not update the last received generation.
> Does anyone have an idea as to the possible causes?
> thanks
>

merg replication

Hi

when want to creat a merg replication , irecieved below error message!!

------------------

The process could not make a generation at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200994)
----------------------------------
Cannot insert the value NULL into column 'nicknames', table 'MSmerge_genhistory'; column does not allow nulls. INSERT fails.
(Source:'Subscriber'. (Data source); Error number: 515
--------------------Check @.@.servername on all servers - I did have a situation like this.

Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>
|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.

> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?

> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.

> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb[vbcol=seagreen]
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return
>

Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.

> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?

> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.

> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficu
lt to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?[/vb
col]
The key word here is "nearly". There are several ways a query plan can be re
-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (
worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest yo
u investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andrew,
> Thanks for the response. Your information agrees with what I have run acr
oss searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curio
us about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secre
t handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familia
r with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sen
t to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
would be difficult to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached pl
ans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any incre
ase today. So far,
> performance of the overall application hasn't shown any degradation. This
is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our
target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return[vbcol=seagreen]
>

Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process
>> in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of
>> VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration
>> settings
>> are really just "suggestions"!
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Daniel,
>> The min and max settings are for the memory pool only and there is another section of memory
>> called the MemToLeave area that is not included in those limits. This includes such things as
>> the SQL Server exe, dll', extended procs, net libs etc. Even the worker threads can use up to
>> 128MB by default. But more than likely you are seeing the results of the procedure cache (which
>> is included in the memory pool). Especiallyif you are stress testing with adhoc queries. SQL
>> Server will use as much memory as is available up until the limits if any are set. Even if your
>> data only amounts to 15MB you can have many thousands of cached plans in the procedure cache and
>> they will stay there until the OS asks for some of SQL Servers memory. By default the OS and SS
>> will dynamically share the available memory but if the OS never asks for it SS will keep it. So
>> if you never set a limit SS expects to use all that it can and that is by design and usually
>> works best.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in message
>> news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration settings
>> are really just "suggestions"!
>>
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Daniel,
> >
> > The min and max settings are for the memory pool only and there is
another
> > section of memory called the MemToLeave area that is not included in
those
> > limits. This includes such things as the SQL Server exe, dll', extended
> > procs, net libs etc. Even the worker threads can use up to 128MB by
> > default. But more than likely you are seeing the results of the
procedure
> > cache (which is included in the memory pool). Especiallyif you are
stress
> > testing with adhoc queries. SQL Server will use as much memory as is
> > available up until the limits if any are set. Even if your data only
> > amounts to 15MB you can have many thousands of cached plans in the
> > procedure cache and they will stay there until the OS asks for some of
SQL
> > Servers memory. By default the OS and SS will dynamically share the
> > available memory but if the OS never asks for it SS will keep it. So if
> > you never set a limit SS expects to use all that it can and that is by
> > design and usually works best.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> > message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
> >>I have a database that starts out as 9Mb. Never grows to more than
15Mb.
> >> However, after running an endurance test for 5 days, the sqlservr
process
> >> in
> >> TaskMan has grown to over 550Mb VM size.
> >>
> >> Can anyone help me understand how a 15 Mb database can consume 550Mb of
> >> VM
> >> space and never give it back? And/or how to encourage sqlservr to
return
> >> memory that it shouldn't be using?
> >>
> >> Thanks in advance,
> >>
> >> Dan
> >>
> >> P.S. BTW I already know that min & max server memory configuration
> >> settings
> >> are really just "suggestions"!
> >>
> >
> >
>

Memory Usage of SQL Server Ce

Hi,

I'm wondering about the memory usage of SQL Server Ce and the Compact Framework.

I've heard I can use only 32 MB per process. SQL Server Ce runs in process and my database file is about 60 MB big.

1. Does that means, I can only use for my application:
32 MB - CLR - SQL-Server Runtime - parts of my database file hold in memory

2. Is SQL Server Ce swapping parts of my database file into my process memory?
If so, is there a chance to group some tables, that they are swapped together to increase
performance?

3. Means big databases big memory usage in my process memory?

Thanks,
Stefan
You only need SQL CE memory for your connection object and the data objects that you have currently loaded. The remaining database resides on disk.|||Thanks,
Stefan

Memory usage in RS(ASP.NET worker process)

Hi all,

We have been experiencing a problem with a couple of our reports that a
number of people seem to have hit previously. Basically the issue is if
the report is over a certain size the ASP.NET worker process hits its
threshold (60%) and the process gets shut down. We have followed the
advice listed in some of the posts in increasing this threshold which
manages to get the report through but we still have a few concerns.

1. Why won't the process use virtual memory. It seems to be limted to
physical memory available and when we increased the threshold, if that
physical memory runs out we get an out of memory exception.

2. The memory doesn't appear to be being released, I would have
throught the garbage collection would kick in pretty soon after the
report was finished but watching the process the memory stays in use
for a large amount of time after the report has finised rendering, with
no new activity on the server.

3. How does this scale at all? I have seen the argument that reports of
this size are unfeasable, and agree to an extent... unfortunately our
clients don't and they need a system capable of delivering them all the
data, regardless of the size of the report. I also read a suggestion to
use DTS to deliver a csv file to the client, but this sounds like a one
off workaround, more than an ongoing process that say an end user could
intiate once a month (for a thousand or so different companies).

This leads me to my final concern.... We have observed that the memory
will pile up, ie if the user kicks off one report it uses x amount...
if another user kicks off another report that will use an additional
amount of memory.... so even if the report isn't too big, it would only
take ten users running medium size reports to run the server out of
memory.... does anyone have any suggestions as to how we should cater
for this?

Thanks in advance
Greg

We are still having issues in this regard, does anyone have any insights?

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.codecomments.com ***
My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.codecomments.com ***

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.codecomments.com ***My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.codecomments.com ***

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.developersdex.com ***My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
--
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.developersdex.com ***

Memory Usage / Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Hi Dan,
By default, sql server (of any ilk) will grow to use whatever memory is
available in the system. The main assumption is that it's the key thing
that's happening on that server. You can limit the usage if you want. In
Enterprise Manager, if you right-click the server, you can set a maximum
memory value under the "Memory" tab. Alternately, you can set it via T-SQL.
We find we can get it to "play" happily with other apps that way. Exchange
is the other main culprit if it happens to be on the same box and we often
have to limit the size of store.exe when it's there as well.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:OMDX9vp8EHA.2012@.TK2MSFTNGP15.phx.gbl...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Hi Greg,
Since this is MSDE, we don't have Enterprise Manager. How do we limit it
with just the stuff provided with MSDE?

> The main assumption is that it's the key thing that's happening on that
server.
Obviously that's not a good assumption when MSDE is included as part of
another application. :-)
Thanks,
Ray
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:O0Ba53r8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi Dan,
> By default, sql server (of any ilk) will grow to use whatever memory is
> available in the system. The main assumption is that it's the key thing
> that's happening on that server. You can limit the usage if you want. In
> Enterprise Manager, if you right-click the server, you can set a maximum
> memory value under the "Memory" tab. Alternately, you can set it via
T-SQL.[vbcol=seagreen]
> We find we can get it to "play" happily with other apps that way. Exchange
> is the other main culprit if it happens to be on the same box and we often
> have to limit the size of store.exe when it's there as well.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
> news:OMDX9vp8EHA.2012@.TK2MSFTNGP15.phx.gbl...
process[vbcol=seagreen]
VM[vbcol=seagreen]
return
>
|||hi Ray,
"abc" <please@.newsgroup.only> ha scritto nel messaggio
news:OQ15beC9EHA.3820@.TK2MSFTNGP11.phx.gbl
> Hi Greg,
> Since this is MSDE, we don't have Enterprise Manager. How do we limit
> it with just the stuff provided with MSDE?
>
> Obviously that's not a good assumption when MSDE is included as part
> of another application. :-)
if you don't like Transact-SQL, you can have a look at a free prj of mine,
which requires SQL-DMO to be installed, that provide a user interface
similar to Enterprise Manager, where your memory needs can be set in a
"friendly" UI
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Memory Problem

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

PLEASE help me

Charlie

Hello, Charlie

Here are a few suggestions that would reduce the memory pressure on the server:
- Increase the value of the MINIMUM_SUPPORT parameter. The default (0.03) value requires a group of items to appear in at least 3% of the transactions to be considered for the left hand side of a rule. By increasing it, the algorithm will analyze fewer items
- Decrease the MAXIMUM_ITEMSET_COUNT parameter.
- Decrease the value of MAXIMUM_ITEMSET_SIZE or increase the value of MINIMUM_ITEMSET_SIZE. If shorter rules correlating two or three items are OK, then decrease the MAX... parameter (the default value is 3, which generates rules with up to 3 items on the left hand side).

Hope this helps|||

Can you recommended a good source for reading up on tuning Analysis Services and in particular the mining model viewer? Fixing problems is one thing, but I'd also like to build up a good foundation of knowledge.

Thanks,

Nick

|||

Hi Bogdan, Thank you!

I reduce the parameters, but this model is important to a supermarket and they dont want reduce some parameters because yours business.

I was reading, Its posible that the problem is support in 32 bits?. The server have 4 procesor and 16 GB of RAM.

Please any suggestion

HELP ME!!

Charlie

|||

Carlos, can you post the exact error message that you receive? That may help us to narrow down the issue.

Thanks

|||

Are you running the 64 bit or 32 bit version of Analysis Services? With the 32 bit version, the server cannot take advantage of the 16 GB of RAM.

|||

Hi,

The Error is : Server Timeout. the procces has been canceled.

umm, There is limit of records in a platform of 32 bits?

The process run for 3 hours and begins to read the cases, when it read the last cases it is when fails.

Thank you for the help!

Charlie

|||

cbdl10 wrote:

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

PLEASE help me

Charlie

hi,
i've got the same problem as yours ..
I can't find a solution yet .. did u?

is it correct considering a MAXIMUM_ITEMSET_COUNT equal to count(*) of my fact table?
[if i reduces my fact table, which means that if i have a fact table working with a 180,000 rows i'll consider only 9,000rows randomly and even in this situation, the algorithm lasts 25min to be accomplished]
|||

cbdl10 wrote:

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

hi there,

i've got the same problem as you ... I'm working with 180,000 records fact table and i've memory problem too (32bit, 2gb ram).

I tried to sample my fact table, by using only 9,000 records randomly chosen but it lasts more or less 25minutes. So, i decided to try to tune algorithm parameters:

MAXIMUM_ITEMSET_COUNT: 9,000;

MAXIMUM_ITEMSET_SIZE: 2;

MINIMU_SUPPORT = 0.1;

It executes very fast, but it doesn't show any result .. does a tuning howto exist? did u solve your problem?

Memory Problem

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

PLEASE help me

Charlie

Hello, Charlie

Here are a few suggestions that would reduce the memory pressure on the server:
- Increase the value of the MINIMUM_SUPPORT parameter. The default (0.03) value requires a group of items to appear in at least 3% of the transactions to be considered for the left hand side of a rule. By increasing it, the algorithm will analyze fewer items
- Decrease the MAXIMUM_ITEMSET_COUNT parameter.
- Decrease the value of MAXIMUM_ITEMSET_SIZE or increase the value of MINIMUM_ITEMSET_SIZE. If shorter rules correlating two or three items are OK, then decrease the MAX... parameter (the default value is 3, which generates rules with up to 3 items on the left hand side).

Hope this helps|||

Can you recommended a good source for reading up on tuning Analysis Services and in particular the mining model viewer? Fixing problems is one thing, but I'd also like to build up a good foundation of knowledge.

Thanks,

Nick

|||

Hi Bogdan, Thank you!

I reduce the parameters, but this model is important to a supermarket and they dont want reduce some parameters because yours business.

I was reading, Its posible that the problem is support in 32 bits?. The server have 4 procesor and 16 GB of RAM.

Please any suggestion

HELP ME!!

Charlie

|||

Carlos, can you post the exact error message that you receive? That may help us to narrow down the issue.

Thanks

|||

Are you running the 64 bit or 32 bit version of Analysis Services? With the 32 bit version, the server cannot take advantage of the 16 GB of RAM.

|||

Hi,

The Error is : Server Timeout. the procces has been canceled.

umm, There is limit of records in a platform of 32 bits?

The process run for 3 hours and begins to read the cases, when it read the last cases it is when fails.

Thank you for the help!

Charlie

|||

cbdl10 wrote:

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

PLEASE help me

Charlie

hi,
i've got the same problem as yours ..
I can't find a solution yet .. did u?

is it correct considering a MAXIMUM_ITEMSET_COUNT equal to count(*) of my fact table?
[if i reduces my fact table, which means that if i have a fact table working with a 180,000 rows i'll consider only 9,000rows randomly and even in this situation, the algorithm lasts 25min to be accomplished]
|||

cbdl10 wrote:

Hi,

I need help, Im workin with 30,000,000 rows in Asociation Rules algorithm, but, when process the model, it fail for memory problem.

hi there,

i've got the same problem as you ... I'm working with 180,000 records fact table and i've memory problem too (32bit, 2gb ram).

I tried to sample my fact table, by using only 9,000 records randomly chosen but it lasts more or less 25minutes. So, i decided to try to tune algorithm parameters:

MAXIMUM_ITEMSET_COUNT: 9,000;

MAXIMUM_ITEMSET_SIZE: 2;

MINIMU_SUPPORT = 0.1;

It executes very fast, but it doesn't show any result .. does a tuning howto exist? did u solve your problem?

Memory pb

Hie,
When i restrict the memory of the db in property dialog box, the process sqlserv.exe increase his memry above this limit why ?Have you enabled AWE? That pretty much circumvents the normal Windows process management, mostly because that is exactly what it is supposed to do!

Even if you haven't enabled AWE, the server responds to its environment. If large blocks of RAM are not being used, then the OS can elect to dynamically ease the restrictions on an application that is memory starved (which happens fast with a busy SQL Server).

-PatP

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAM
What is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>