Merge Join

Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
Leila
It depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...

Merge Join

Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimize
r
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...

Merge Join

Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I executed the following query in Northwind:
> >
> > select c.* from customers c
> > join orders o on c.customerid=o.customerid
> >
> > There are original indexes available on these two tables:
> >
> > 1) Clustered (Customers.CustomerID)
> > 2) Clustered (Orders.OrderID)
> > 3) nonClustered (Orders.CustomerID)
> >
> > The execution plan shows that a nested loop is used to return the result.
> > But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> > Now both tables are sorted based on the join predicate. But still nested
> > loop happens. I think Merge Join can be more efficient. Why nested loop
> > still is preferred by query optimizer?
> > Thanks in advance,
> > Leila
> >
> >sql

Merge doesn't re-propagate changes arrived from Transaccional repl

I have a the following scenario:
- A transactional publicaction "TRN2000" (in SrvSQLServer2000 server)
- A push subscription to "TRN2000" publication(in SrvSQLServer2005 server)
- A merge publication "Merge2005" defined in SrvSQLServer2005 over the same
Database/tables
that transactional subscription replicate
- Subscriptions to "Merge2005" publication
Then, this is the behavior:
- Data changes in SrvSQLServer2000 are replicated to SrvSQLServer2005 through
the TRN2000 subscription (OK)
- When I directly modify data(update, insert,) in the SrvSQLServer2005,
merge susbscriptions
receives data changes (OK)
But the problem is that data changes received in the SrvSQLServer2005
database
through the "Trn2000" replication..are not propagated to the "Merge2005"
subscriptions..
I suspect that the problem is that Transactional Push subscription use Bulk
Operations over
SrvSqlServer2005 and these are being ignored by the "Merge2005" replication
triggers.
some idea? workaround?
thanks in advance,
Alexander.
If the problem occurs during initialization, have a look at the
@.fire_triggers_on_snapshot parameter in sp_addarticle.
HTH,
Paul Ibison
|||Thanks Paul for the answer.
Unfortunately, the problem happens not only at initialization. It happens
when propagating any change on the data..
some idea?
"Paul Ibison" wrote:

> If the problem occurs during initialization, have a look at the
> @.fire_triggers_on_snapshot parameter in sp_addarticle.
> HTH,
> Paul Ibison
|||In that case I'm confused. The commands replicated are sent down as calls to
insert/update/delete stored procedures on the subscriber. This isn't like a
bulk insert or a fast load which themselves don't fire triggers. Is your
transactional setup one that involves updating subscribers ie has triggers of
its own? Are there any other triggers on the table? Are any/all triggers
marked as NOT FOR REPLICATION?
Rgds,
Paul Ibison
|||Table “T” on which the Transaction replication records the data (same table
“T” that also is published through the Merge replication) doesn’t have other
triggers.
OK, the use of “Bulk Insert” by the Transactional replication was only a
supposition.
I thought that insert could be doing “bulk insert” because if I execute:
sp_addtabletocontents @.table_name='T'
Merge replication replicate pending changes (changes that became on table
“T” through the Transaccional replication) to its subscribers.
(I could send you by mail my replication scripts if you think that they can
clarify something)
"Paul Ibison" wrote:

> In that case I'm confused. The commands replicated are sent down as calls to
> insert/update/delete stored procedures on the subscriber. This isn't like a
> bulk insert or a fast load which themselves don't fire triggers. Is your
> transactional setup one that involves updating subscribers ie has triggers of
> its own? Are there any other triggers on the table? Are any/all triggers
> marked as NOT FOR REPLICATION?
> Rgds,
> Paul Ibison
>
|||I'll have a go at reproducing and then post back...Meanwhile can you confirm
that the changes are definitely coming from transactional replication and not
being bulk loaded into the republished table directly.
Paul Ibison
|||Surely,
I have verified that making data modifications (through sql standard
sentences insert, update, delete)
on the database that has the transaccional publication, the changes arrive
correctly at table T and these
same changes are not re-propagated to merge subscriptions.
I thank for your dedication in this subject
Alexander Wolff
|||Hello Alexander & Paul,
I'm having exactly the same problem,
I wonder if you've found a solution (or explanation) for this issue.
Thanks,
Vladimir Kofman

> Surely,
> I have verified that making data modifications (through sql standard
> sentences insert, update, delete)
> on the database that has the transaccional publication, the changes
> arrive
> correctly at table T and these
> same changes are not re-propagated to merge subscriptions.
> I thank for your dedication in this subject
> Alexander Wolff
>

Merge Agent is reporting deadlocks

Can anyone tell me what could be causing the following deadlock issues? I
have done a SQL Profiler trace which isn't picking up the Deadlock. The
subscription type is pull and there are more than 12 merge agents on the
Publisher
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
The process could not enumerate changes at the 'Publisher'.
Repl Agent Status: 5
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Publisher'.
Repl Agent Status: 3
Percent Complete: 0
Category:SQLSERVER
Source: SQLBDG01PA
Number: 1205
Message: Transaction (Process ID 94) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Repl Agent Status: 3
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Distributor 'SQLBDG01PA'
Hello,
You may want to enable 1204, 3605 and 1205 flag to see more information
about this deadlock:
The following article is for your reference:
832524 SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/?id=832524
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Merge Agent is reporting deadlocks
>thread-index: AcYE+kfsejv9d52cQuKGAjAGyxI8Wg==
>X-WBNR-Posting-Host: 159.99.4.20
>From: =?Utf-8?B?U3lkbmV5?= <hsc@.newsgroup.nospam>
>Subject: Merge Agent is reporting deadlocks
>Date: Mon, 19 Dec 2005 16:14:02 -0800
>Lines: 36
>Message-ID: <1CF81079-5E0F-46A6-8C0C-2FFE03906D20@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67673
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Can anyone tell me what could be causing the following deadlock issues? I
>have done a SQL Profiler trace which isn't picking up the Deadlock. The
>subscription type is pull and there are more than 12 merge agents on the
>Publisher
>The process could not enumerate changes at the 'Publisher'.
>Percent Complete: 0
>The process could not enumerate changes at the 'Publisher'.
>Repl Agent Status: 5
>Percent Complete: 0
>Category:NULL
>Source: Merge Replication Provider
>Number: -2147200999
>Message: The process could not enumerate changes at the 'Publisher'.
>Repl Agent Status: 3
>Percent Complete: 0
>Category:SQLSERVER
>Source: SQLBDG01PA
>Number: 1205
>Message: Transaction (Process ID 94) was deadlocked on lock resources with
>another process and has been chosen as the deadlock victim. Rerun the
>transaction.
>Repl Agent Status: 3
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Distributor 'SQLBDG01PA'
>
|||Thank you for that information Peter,
It helped me isolate where the deadlock is. Below is the output from the log
which shows that the deadlock is occurring in some SQL stored procedures. An
update is being done on one of our tables nad this is where it occurrs. This
is a major problem for us as the site we are supporting cannot synchronise
their servers at all. There are 12 servers to synchronise but only 8 can be
done and then they all start to fail one at a time. If anyone has any other
information about how to get around this problem it would be greatly
appreciated.
Node:1
KEY: 9:1977058079:7 (ee0128400385) CleanCnt:1 Mode: U Flags: 0x0
Grant List 0::
Owner:0x42bdf5c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:147 ECID:0
SPID: 147 ECID: 0 Statement Type: SELECT Line #: 23
Input Buf: RPC Event: sp_MSenumchanges;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:101 ECID:0 Ec0x5D4DB530)
Value:0x51ad0560 Cost0/1E8)
Node:2
KEY: 9:1977058079:1 (95002952f11f) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x5a33bdc0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:101 ECID:0
SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 935
Input Buf: RPC Event: sp_upd_BE8D5D46BE0642C56A093B6C6808492A;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
Value:0x42bdc420 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
Value:0x42bdc420 Cost0/0)
End deadlock search 485 ... a deadlock was found.
"Peter Yang [MSFT]" wrote:

> Hello,
> You may want to enable 1204, 3605 and 1205 flag to see more information
> about this deadlock:
> The following article is for your reference:
> 832524 SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/?id=832524
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
>
|||Hello,
Based on my research, the new SQL Server build 818 changed how the Merge
Agent locks records on the Publisher while it is synchronizing changes to
the subscriber. This new design change might cause the Merge Agent to
Deadlock with the Update from the application. We discovered we can "tune"
the Merge agent to lock a smaller number of records and hopefully avoid the
deadlocking.
The SQL Server help topic below describe how to create a new Merge Agent
Profile. In the new profile change the DownloadReadChangesPerBatch setting
from the default
setting of 100 to 25. This will lock a smaller number of records per batch
while synchronizing. We believe 25 is a good compromise but it may need to
be adjusted.
See SQL Server Help Topics:
- Merge Agent Profile
- How to create a replication agent profile (Enterprise Manager)
Another the work around is to run the Merge Agent every minute instead of
continuously so when it fails with a deadlock, the Agent will automatically
restart.
Please rest assured this issue has been routed to the proper channel. If
there is any update on this, we will let you know. However, it may take
some time and we appreciate your patience.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Merge Agent is reporting deadlocks
>thread-index: AcYGeQ48jpTy5VEoQhSZsrR+3H87vw==
>X-WBNR-Posting-Host: 159.99.4.20
>From: =?Utf-8?B?U3lkbmV5?= <hsc@.newsgroup.nospam>
>References: <1CF81079-5E0F-46A6-8C0C-2FFE03906D20@.microsoft.com>
<VdGdTKTBGHA.1236@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Merge Agent is reporting deadlocks
>Date: Wed, 21 Dec 2005 13:54:02 -0800
>Lines: 127
>Message-ID: <EED67217-642D-4E1C-808D-E4E51CD66D2C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67722
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Thank you for that information Peter,
>It helped me isolate where the deadlock is. Below is the output from the
log
>which shows that the deadlock is occurring in some SQL stored procedures.
An
>update is being done on one of our tables nad this is where it occurrs.
This
>is a major problem for us as the site we are supporting cannot
synchronise
>their servers at all. There are 12 servers to synchronise but only 8 can
be
>done and then they all start to fail one at a time. If anyone has any
other
>information about how to get around this problem it would be greatly
>appreciated.
>Node:1
>KEY: 9:1977058079:7 (ee0128400385) CleanCnt:1 Mode: U Flags: 0x0
> Grant List 0::
> Owner:0x42bdf5c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:147
ECID:0
> SPID: 147 ECID: 0 Statement Type: SELECT Line #: 23
> Input Buf: RPC Event: sp_MSenumchanges;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:101 ECID:0 Ec0x5D4DB530)
>Value:0x51ad0560 Cost0/1E8)
>Node:2
>KEY: 9:1977058079:1 (95002952f11f) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x5a33bdc0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:101
ECID:0[vbcol=seagreen]
> SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 935
> Input Buf: RPC Event: sp_upd_BE8D5D46BE0642C56A093B6C6808492A;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
>Value:0x42bdc420 Cost0/0)
>Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
>Value:0x42bdc420 Cost0/0)
>End deadlock search 485 ... a deadlock was found.
>
>"Peter Yang [MSFT]" wrote:
rights.[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
with
>

Menu Hierarchy Display Order (Recursion)

Hi -
I'm using the following Table and Stored Procedure below, to store and
access a menu hierarchy. The recursive stored procedure is working
perfectly and returns a text string outlining my current menu hierarchy.
Currently the stored procedure returns the hiearchy in the order that the
records were created (MenuItemID)
However, I've recently added a "DisplayOrder" column to my table. For
example, let's say that I want Biking to appear before Skiing. Changing the
item's MenuItemID is not an option. So, I'm using a DisplayOrder column. I
need to integrate the DisplayOrder column into my stored procedure so that
it returns the hierarchy in the correct order. I'm not sure what I'm doing
wrong, but I just can't seem to get this part working.
Any help would be appreciated.
Thanks,
Brian
CREATE TABLE MenuItem
(
MenuItemID INT,
ItemName VARCHAR(30),
ParentID INT,
DisplayOrder INT
)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(1, 'Activities', NULL, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(2, 'Fishing', 1, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(3, 'Skiing', 1, 1)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(4, 'Hiking', 1, 2)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(5, 'Biking', 1, 3)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(6, 'Swimming', 1, 4)
CREATE PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT
SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID)
WHILE @.MenuItemID IS NOT NULL
BEGIN
EXEC spCreateMenu @.MenuItemID
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND MenuItemID > @.MenuItemID)
END
END
EXEC spCreateMenu 1Hi Brian,
Try this.. Hope this helps.
ALTER PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT,
@.rowcount int
SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
WHILE (@.rowcount <> 0)
BEGIN
EXEC spCreateMenu @.MenuItemID
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
END
END|||Excellent - Thanks very much for your help!!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B9AE2855-8675-4E07-BF3D-52A2BF892335@.microsoft.com...
> Hi Brian,
> Try this.. Hope this helps.
> ALTER PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT,
> @.rowcount int
> SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> WHILE (@.rowcount <> 0)
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> END
> END|||Look up CTEs (common table expressions). They handle recursive logic
without actually having a recursive stored procedure. One SQL statement
will do it for you.
"Brian Patrick" <bpatrick100@.hotmail.com> wrote in message
news:OUFoLJKaGHA.3652@.TK2MSFTNGP03.phx.gbl...
> Hi -
> I'm using the following Table and Stored Procedure below, to store and
> access a menu hierarchy. The recursive stored procedure is working
> perfectly and returns a text string outlining my current menu hierarchy.
> Currently the stored procedure returns the hiearchy in the order that the
> records were created (MenuItemID)
> However, I've recently added a "DisplayOrder" column to my table. For
> example, let's say that I want Biking to appear before Skiing. Changing
the
> item's MenuItemID is not an option. So, I'm using a DisplayOrder column.
I
> need to integrate the DisplayOrder column into my stored procedure so that
> it returns the hierarchy in the correct order. I'm not sure what I'm
doing
> wrong, but I just can't seem to get this part working.
> Any help would be appreciated.
> Thanks,
> Brian
>
> CREATE TABLE MenuItem
> (
> MenuItemID INT,
> ItemName VARCHAR(30),
> ParentID INT,
> DisplayOrder INT
> )
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (1, 'Activities', NULL, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (2, 'Fishing', 1, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (3, 'Skiing', 1, 1)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (4, 'Hiking', 1, 2)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (5, 'Biking', 1, 3)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (6, 'Swimming', 1, 4)
>
> CREATE PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT
> SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID
FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID)
> WHILE @.MenuItemID IS NOT NULL
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND MenuItemID > @.MenuItemID)
> END
> END
>
> EXEC spCreateMenu 1
>

Memory Warning

I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size= 65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
HillaireThere are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818095
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire

Memory Warning

I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
There are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire
|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/default...b;en-us;818095
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire

Memory Warning

I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
HillaireThere are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/defaul...kb;en-us;818095
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire

Memory usage\Performance problem

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

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

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

Memory usage\Performance problem

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

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

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

Memory usage\Performance problem

I am experiencing the following problem;

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

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

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

Tnx in advance!

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

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

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

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

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

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

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

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

Thanks for a well-written answer!

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

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

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

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

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

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

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

Two things come to mind:

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

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

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

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

Kind regards

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

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

/Magnus

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

I believe that was in my first reply.

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

Thus, this is perfectly normal behaviour.

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

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

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

Kind regards

robert

Memory usage\Performance problem

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

Memory Usage Following ISA Installation

Hi There
I have just installed ISA 2004 to a Win 2003 server. This had also installed
an instance of SQL MSDE which is eating up the memory. Is there an easy
command prompt statement or registry hack I can run to restrict the maximum
memory usage possible as we are regularly getting "Low Virtual Memory" errors
as it does not seem to be releasing the memory quickly enough. I'm not very
familiar with SQL, so any help out be greatly appriciated. There is no SQL or
MSDE option under the programs list in the Start Menu, so I can't open
Enterprise manager like you would on a normal SQL box. However, there is a
SQL folder within the Program Files directory although this looks a bit slim
compared to a normal install. Failing being able to restrict memory usage, is
it feasble to remove the MSDE installation? I also have Backup Exec running
which is dependant upon the MSSQL service.
Many thanks
Bob
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" wrote:

> Hi There
> I have just installed ISA 2004 to a Win 2003 server. This had also installed
> an instance of SQL MSDE which is eating up the memory. Is there an easy
> command prompt statement or registry hack I can run to restrict the maximum
> memory usage possible as we are regularly getting "Low Virtual Memory" errors
> as it does not seem to be releasing the memory quickly enough. I'm not very
> familiar with SQL, so any help out be greatly appriciated. There is no SQL or
> MSDE option under the programs list in the Start Menu, so I can't open
> Enterprise manager like you would on a normal SQL box. However, there is a
> SQL folder within the Program Files directory although this looks a bit slim
> compared to a normal install. Failing being able to restrict memory usage, is
> it feasble to remove the MSDE installation? I also have Backup Exec running
> which is dependant upon the MSSQL service.
> Many thanks
> Bob
|||Thanks for the reply, however, maybe its me, but I didn't think that really
answered my problem, maybe I'm not looking in the right places, but where is
the Min and Max memory settings? Is it something I have to do from a command
prompt?
Bob
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> INF: SQL Server Memory Usage:
> http://support.microsoft.com/default...;en-us;q321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Bob Sampson" wrote:
|||Hi
Yes. MSDE does not have a UI, you need to run T-SQL code through OSQL.
To set a minimum of 64Mb and a Maximum of 256Mb, run from the command
prompt:
osql -Q"sp_configure 'min server memory', 64 RECONFIGURE WITH OVERRIDE" -E
osql -Q"sp_configure 'sp_configure 'max server memory', 256 RECONFIGURE WITH
OVERRIDE" -E
--
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/
"Bob Sampson" <BobSampson@.discussions.microsoft.com> wrote in message
news:44FB02FD-8B86-4061-B8AC-D2D4F9F38165@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, however, maybe its me, but I didn't think that
> really
> answered my problem, maybe I'm not looking in the right places, but where
> is
> the Min and Max memory settings? Is it something I have to do from a
> command
> prompt?
> Bob
> "Mike Epprecht (SQL MVP)" wrote:

Memory usage

Hello,

I need to know which of the following two methods do need less RAM.

There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.

The Structure of a dimension Table looks like

CarID (tinyint), Description (varchar(20))
1 BMW
2 Porsche

I want to join the 2 Big Tables in a materialized view. Later i will run queries like
select * into #temp from dbo.vw_materialized_view where Car = 'BMW'

So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?

Hope you got that ;-)

Thank youmemory usage will be managed by sql server

If you create an index on a view, then that data will be stored just like a base table, so you incur more overhead and disk storage, or if it's small enough, in memory

But all of that is managed by sql server

And if you don't index the view and the joins afre simple enough, then it'll use the indexes on the table

What was the question again?

Memory Tab; SQL Server Properties (Accessing)

Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
BillSee sp_configure in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
Bill|||Check out sp_configure in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>|||Hi Narayana,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Narayana Vyas Kondreddi" wrote:
> See sp_configure in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>
>|||Hi Andrew,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Andrew J. Kelly" wrote:
> Check out sp_configure in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> > Hi,
> > - I cannot access the Memory Tab to change the memory settings.
> > - I receive the following error message: Error 8651: Could not perform the
> > requested operation because the minimum query memory is not available.
> > Decrease the configured value for the 'min memory per query' server
> > configuration option.
> > - The maximum memory was accidentily set to 0 in the SQL Server Properties
> > Memory Tab.
> >
> > Is there another way to access and change the maximum memory setting other
> > than through Enterprise Manager?
> >
> > Thank you.
> > Bill
> >
>
>

Memory Tab; SQL Server Properties (Accessing)

Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
Bill
See sp_configure in SQL Server Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
Bill
|||Check out sp_configure in BooksOnLine.
Andrew J. Kelly SQL MVP
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>
|||Hi Narayana,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Narayana Vyas Kondreddi" wrote:

> See sp_configure in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>
>
|||Hi Andrew,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Andrew J. Kelly" wrote:

> Check out sp_configure in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
>
>

Memory Tab; SQL Server Properties (Accessing)

Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
BillSee sp_configure in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
Hi,
- I cannot access the Memory Tab to change the memory settings.
- I receive the following error message: Error 8651: Could not perform the
requested operation because the minimum query memory is not available.
Decrease the configured value for the 'min memory per query' server
configuration option.
- The maximum memory was accidentily set to 0 in the SQL Server Properties
Memory Tab.
Is there another way to access and change the maximum memory setting other
than through Enterprise Manager?
Thank you.
Bill|||Check out sp_configure in BooksOnLine.
Andrew J. Kelly SQL MVP
"BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>|||Hi Narayana,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Narayana Vyas Kondreddi" wrote:

> See sp_configure in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
> Hi,
> - I cannot access the Memory Tab to change the memory settings.
> - I receive the following error message: Error 8651: Could not perform the
> requested operation because the minimum query memory is not available.
> Decrease the configured value for the 'min memory per query' server
> configuration option.
> - The maximum memory was accidentily set to 0 in the SQL Server Properties
> Memory Tab.
> Is there another way to access and change the maximum memory setting other
> than through Enterprise Manager?
> Thank you.
> Bill
>
>|||Hi Andrew,
Thank you for the suggestion.
I entered the following in the query analyzer, and was then able to access
the memory tab again to make the correction to max memory:
sp_configure 'max server memory', 2147483647
go
reconfigure
go
Thank you!
Bill
"Andrew J. Kelly" wrote:

> Check out sp_configure in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "BillC-CA" <BillC-CA@.discussions.microsoft.com> wrote in message
> news:E29C205C-BC23-4B3B-BCBC-A95FCC806551@.microsoft.com...
>
>

Memory Question

This question is aimed to the experienced DBAs in server
configuration:
We currently have the following configuration:
-. Windows 2000 Server SP4
-. SQL Server Standard SP3
-. 1GB RAM
We are going to upgrade the RAM size. According with Books
On Line, SQL Server supports up to 2GB.
My question is, if we put 4GB on the server, SQL Server
will use 2GB max and Windows would be able to use the
other 2 Gigs?
or
The server (Windows and SQL Server) will not see more than
2GB alltogether?
Thanks in advance.Hope you will get you answer here:
http://support.microsoft.com/?id=274750
"DBA" <anonymous@.discussions.microsoft.com> wrote in message
news:00e201c39800$7cb7fb40$a501280a@.phx.gbl...
> This question is aimed to the experienced DBAs in server
> configuration:
> We currently have the following configuration:
> -. Windows 2000 Server SP4
> -. SQL Server Standard SP3
> -. 1GB RAM
> We are going to upgrade the RAM size. According with Books
> On Line, SQL Server supports up to 2GB.
> My question is, if we put 4GB on the server, SQL Server
> will use 2GB max and Windows would be able to use the
> other 2 Gigs?
> or
> The server (Windows and SQL Server) will not see more than
> 2GB alltogether?
> Thanks in advance.

Memory Leak Reading Appointment from MS Exchange Mailbox

Hi,

When using the following code to read an appointment from a MS Exchange mailbox the application experiences a memory leak.

I can provide a test application that easily demonstrates the issue.

ADODB.Connection _Conn = new ADODB.Connection();

_Conn.Provider = "exoledb.datasource";

try

{

this.Cursor = Cursors.WaitCursor;

_Conn.Open("http://" + Environment.UserDomainName + "." + Environment.MachineName + "/Exchange/" + this.txtMailbox.Text + "/Calendar", "", "", 0);

CDO.Appointment cdoAppointment = new CDO.Appointment();

try

{

for (int i = 1; i < int.Parse(this.txtNoOfTestReads.Text); i++)

{

cdoAppointment.DataSource.Open("http://" + Environment.UserDomainName + "." + Environment.MachineName + "/Exchange/" + this.txtMailbox.Text + "/Calendar/" + this.txtApptFileName.Text + ".eml", _Conn, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adFailIfNotExists, ADODB.RecordOpenOptionsEnum.adOpenSource, "", "");

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

cdoAppointment = null;

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if (_Conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)

_Conn.Close();

_Conn = null;

this.Cursor = Cursors.Default;

}

Regards,

Craig

I would post this to the exchange dl ->

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.exchange.applications

This could just be a GC issue, try adding a line in the loop like below:

System.Gc.Collect();

System.Gc.WaitForPendingFinalizers();

Matt