DialogError message type

Hi There

In my testing i have seen the following i just would like confirmation that i am correct.

When something happens to cause a Dialog Error say for example message validation fails.

I have noticed that the actual DialogError message always goes to the initiator queue, is this correct ?

If so then logic to handle the ErrorDialog message type only has to be in the initiator activated SP that logic is not required in the target queue activated SP since the ErrorDialog message always goes to the initiator queue, is that correct ?

Thanx

Both sides need to be prepared to deal with the error message. The same message validation can fail for a message sent on the other direction, or the initiator code can issue an explicit END CONVERSATION ... WITH ERROR (or even an ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS), or the initiator can drop the contract used on the conversation, or the service. All these result in an error message being sent to the target, and the list is not complete.

HTH,
~ Remus

Diagnosing Memory Use

I've noticed high memory consumption on some of our workstations that eventually cause unacceptable performance problems. Through Task Manager, I see that SQL Server is consuming the bulk of this memory.

A coworker has implemented a system to periodically restart SQL Server (using netstop/netstart). This does hold down memory usage but I'd like to avoid doing this.

I'd like to diagnose SQL Server and find exactly what it is doing that consumes so much memory.

I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler

Which is the best tool to do this and how would I approach this scenario?The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.|||Originally posted by joejcheng
The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.

Task Manager shows both Mem Usage (actively paged in) and VM Size (reserved but not necessarily paged in) for the sqlservr.exe process. Those are 651MB/670MB respectively. Using Performance Monitor I see that the disk use is high, and the "Pages/sec" is high.

Shortly after doing a netstop/netstart restart of SQL Server Mem Usage/VM Size = 36MB/44MB respectively and performance is great. Our application has already reconnected to SQL Server and is operating at full speed.

This really has to be SQL Server related.|||Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.|||Originally posted by MCrowley
Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.

Thanks for the feedback!

Actually, there are no users just an internal app that runs 24/7.

We could cut SQL Server's memory allotment but as you said, that will increase disk use which is the main problem. What I want to know is why it is using the memory that it is. Once I identify that, I can redesign to use less resources. I suspect a design problem rather than simply a lack of resources.|||Ahh. Then your problem seems to be that your database has grown to more than probably 500 MB. More likely to more than 1GB. SQL Server (and Oracle, Informix, DB2, etc.) will store information in memory for faster access. You can cut down on the amount of I/O you need to perform by reducing redundancy in the data, or applying indexes on the tables that are queried most often.

The first method may be beyond help, as reconfiguring the database is near impossible without a total re-write of the application. As for the second, open Profiler in the SQL program group, and connect to your server. Under Events, remove Security Audit and Sessions. On the filters page, add a limit under Reads of greater than or equal say 100 reads. 1 read is only a logical I/O rather than a physical one, but you should be able to figure out quickly which queries are your problem children. Good luck.|||Hi,

I had similar doubts and i would like you to see the following link, which is a discussion on the same forum
http://www.dbforums.com/showthread.php?threadid=966886&referrerid=47114

regards,
henry

Diagnosing Memory Problems?

I've noticed high memory consumption on some of our workstations that
eventually cause unacceptable performance problems. Through Task
Manager, I see that SQL Server (sqlsrvr.exe) is consuming the bulk of
this memory.
We've found that restarting SQL Server (using netstop/netstart)
dramatically reduces memory usage but it gradually creeps back to an
unacceptable level and becomes a problem (high pages/sec thrashing). We
are setting up a system that will periodically restart SQL Server. This
is a stopgap solution.
I'd like to identify what in SQL Server is using this memory and why.
Ideally, I would redesign my applications so that this wouldn't happen.
I suspect some kind of design flaw or usage error and I want to identify
it. Adding physical RAM isn't an option since this is running on a 70
workstation farm.
I don't want to simply limit SQL Server's memory allotment since this
leads to increased disk use which is the main problem.
I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler
Which is the best tool to do this and how would I approach this scenario?
Thank You!SQL Server and most other high-end database products are
essentially disk caching engines (that provide
transactions and management etc)
so under most circumstances, its suppose to use all
available memory for most effective disk caching
it will also release memory to other apps if the available
system memory falls below 4-6MB (i do wish MS would
increase this setting to 16-32MB)
however, if you are not using it as a dedicated DB server,
use Ent Manager to restrict the max memory to some desired
amount, 64-128M is good for a development system.
don't trying to figure out which queries are using memory,
unless you want to reduce the amount of data.
>--Original Message--
>I've noticed high memory consumption on some of our
workstations that
>eventually cause unacceptable performance problems.
Through Task
>Manager, I see that SQL Server (sqlsrvr.exe) is consuming
the bulk of
>this memory.
>We've found that restarting SQL Server (using
netstop/netstart)
>dramatically reduces memory usage but it gradually creeps
back to an
>unacceptable level and becomes a problem (high pages/sec
thrashing). We
>are setting up a system that will periodically restart
SQL Server. This
>is a stopgap solution.
>I'd like to identify what in SQL Server is using this
memory and why.
>Ideally, I would redesign my applications so that this
wouldn't happen.
>I suspect some kind of design flaw or usage error and I
want to identify
>it. Adding physical RAM isn't an option since this is
running on a 70
>workstation farm.
>I don't want to simply limit SQL Server's memory
allotment since this
>leads to increased disk use which is the main problem.
>I am aware of three tools to diagnose this:
>- Performance Monitor
>- Enterprise Manager "Current Activity"
>- SQL Server Profiler
>Which is the best tool to do this and how would I
approach this scenario?
>Thank You!
>.
>|||Excellent insight. Thank you!
joe chang wrote:
> SQL Server and most other high-end database products are
> essentially disk caching engines (that provide
> transactions and management etc)
> so under most circumstances, its suppose to use all
> available memory for most effective disk caching
> it will also release memory to other apps if the available
> system memory falls below 4-6MB (i do wish MS would
> increase this setting to 16-32MB)
> however, if you are not using it as a dedicated DB server,
> use Ent Manager to restrict the max memory to some desired
> amount, 64-128M is good for a development system.
> don't trying to figure out which queries are using memory,
> unless you want to reduce the amount of data.|||I am consistantly getting Physical Memory Usage High errors. My Buffer Cache is running at 99% and the Server Memory is at 87%. Any thoughts on what's causing this?

Determining the cause of tempdb growth?

I'm trying to determine the cause of tempdb to grow from ~ 30MB to well over
40GB. I have a SQL Profiler trace that was run at the time of this growth
but I don't see any particular SQL statements that consumed a lot of CPU or
performed a lot of reads (maybe the maximum was 50000 reads).
Any ideas as to what I could do to identify this problem?
Thanks in advance.Creation of temp tables and ORDER BY statements can cause tempdb growth. = Tempdb may also be used by other (SQL Server internal) query =optimization processes.
With that said, 40GB does sound rather large. How large are your databases?
What type of operations were being performed during the growth?
-- Keith
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message =news:OPLVdaYmDHA.1800@.TK2MSFTNGP10.phx.gbl...
> I'm trying to determine the cause of tempdb to grow from ~ 30MB to =well over
> 40GB. I have a SQL Profiler trace that was run at the time of this =growth
> but I don't see any particular SQL statements that consumed a lot of =CPU or
> performed a lot of reads (maybe the maximum was 50000 reads).
> > Any ideas as to what I could do to identify this problem?
> > Thanks in advance.
> >