Profiler : Getting the memory usage of a query

Hi

Im using profiler to get information about query on the server here but i cant seem to be able to get the memory usage of a query. I can acess the cpu, I/O and duration but i cant seem to be able to get that last info i need.

Is there any way for me to obtain it trough profiler?

Not through profiler nosql

processors replacement

Your execution plans are going to need updating. They are
developed using a combination of cpu, memory and disk
speed, so if one changes its going to effect the rest.
Its not a big job, just look up update statistics, then
watch your server fly...
Peter
"The best minds are not in government. If any were,
business would steal them away."
Ronald Reagan

>--Original Message--
>Hi,
>SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>memory (3G used for sql server). We're going to replace
>these processors with the new, faster 4 processors. Is
>there any known problem we can expect regarding starting
>sql services and databases with new processors?
>TIA
>Armin
>.
>
They have to shut down the server to add the upgrades and all execution
plans are lost when you stop sql server anyway. And the plans are not
directly related to disk speed at all. Even CPU and memory are things that
are taken into account at run time and not necessarily at compile time. A
complex plan will always have a single and a parallel plan created the first
time it is run. At run time it is decided based on how busy the cpu's are,
memory available, etc that determine if the single threaded plan is used or
a parallel plan and how many threads generated.
Andrew J. Kelly SQL MVP
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:766e01c494e9$304f2300$a301280a@.phx.gbl...[vbcol=seagreen]
> Your execution plans are going to need updating. They are
> developed using a combination of cpu, memory and disk
> speed, so if one changes its going to effect the rest.
> Its not a big job, just look up update statistics, then
> watch your server fly...
> Peter
> "The best minds are not in government. If any were,
> business would steal them away."
> Ronald Reagan
>

processors replacement

Hi,
SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
memory (3G used for sql server). We're going to replace
these processors with the new, faster 4 processors. Is
there any known problem we can expect regarding starting
sql services and databases with new processors?
TIA
Arminarmin,
As long as Windows comes up, should be fine. ;-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
armin wrote:
> Hi,
> SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
> memory (3G used for sql server). We're going to replace
> these processors with the new, faster 4 processors. Is
> there any known problem we can expect regarding starting
> sql services and databases with new processors?
> TIA
> Armin|||Your execution plans are going to need updating. They are
developed using a combination of cpu, memory and disk
speed, so if one changes its going to effect the rest.
Its not a big job, just look up update statistics, then
watch your server fly...
Peter
"The best minds are not in government. If any were,
business would steal them away."
Ronald Reagan
>--Original Message--
>Hi,
>SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>memory (3G used for sql server). We're going to replace
>these processors with the new, faster 4 processors. Is
>there any known problem we can expect regarding starting
>sql services and databases with new processors?
>TIA
>Armin
>.
>|||They have to shut down the server to add the upgrades and all execution
plans are lost when you stop sql server anyway. And the plans are not
directly related to disk speed at all. Even CPU and memory are things that
are taken into account at run time and not necessarily at compile time. A
complex plan will always have a single and a parallel plan created the first
time it is run. At run time it is decided based on how busy the cpu's are,
memory available, etc that determine if the single threaded plan is used or
a parallel plan and how many threads generated.
--
Andrew J. Kelly SQL MVP
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:766e01c494e9$304f2300$a301280a@.phx.gbl...
> Your execution plans are going to need updating. They are
> developed using a combination of cpu, memory and disk
> speed, so if one changes its going to effect the rest.
> Its not a big job, just look up update statistics, then
> watch your server fly...
> Peter
> "The best minds are not in government. If any were,
> business would steal them away."
> Ronald Reagan
>
> >--Original Message--
> >Hi,
> >
> >SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
> >memory (3G used for sql server). We're going to replace
> >these processors with the new, faster 4 processors. Is
> >there any known problem we can expect regarding starting
> >sql services and databases with new processors?
> >
> >TIA
> >
> >Armin
> >.
> >|||Thank you very much, to all of you!
If I unterstood complete discussion correctly, as soon as
NT comes up, I should let it fly?
BR
Armin
>--Original Message--
>armin,
>As long as Windows comes up, should be fine. ;-)
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>armin wrote:
>> Hi,
>> SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>> memory (3G used for sql server). We're going to replace
>> these processors with the new, faster 4 processors. Is
>> there any known problem we can expect regarding
starting
>> sql services and databases with new processors?
>> TIA
>> Armin
>.
>sql

Processor + Memory

Hi,
Can anybody direct me to an article or an white paper, which explains SQL Server 2000 Processor and Memory architecture and how they work together
Lets take a scenario
SQL Server 2000 Enterprise edition, 8GB of physical memory and 4 processors (same server) are available.
So some of the questions that I have in my mind are
(a) How can we distribute 8GB of memory across these 4 processor. How do we configure it
(b) Can we control, the size of memory distributed across these processor. That is, can I configure 5 GB of memory to one processor and the rest equally distributed to other processors? If yes, how do we go about it
(c) How does SQL Server parallel processing works
(d) I understand that AWE is used to increase the memory capacity for sql server app, if the memory of the server is more than 4 GB, but how exactly is this done?
Would really appreciate, if someone can provide me the link to the right article or if someone can take sometime to explain me this
Thanks very muc
GYKGYK wrote:
> Hi,
> Can anybody direct me to an article or an white paper, which explains SQL Server 2000 Processor and Memory architecture and how they work together?
No link, but the book Inside SQL-Server 2000 by Kalen Delaney explains
in great detail the internals of SQL-Server.
> Lets take a scenario;
> SQL Server 2000 Enterprise edition, 8GB of physical memory and 4 processors (same server) are available.
> So some of the questions that I have in my mind are;
> (a) How can we distribute 8GB of memory across these 4 processor. How do we configure it?
IFAIK you can't. This is done automatically and dynamically by
SQL-Server. These sound like features you might find in an Operating
System, not in a RDBMS. There is only one setting you can make: you can
determine the amount of memory that is available to SQL-Server (in
total).
> (b) Can we control, the size of memory distributed across these processor. That is, can I configure 5 GB of memory to one processor and the rest equally distributed to other processors? If yes, how do we go about it?
Not with SQL-Server. This would be an OS requirement. The memory
SQL-Server uses is exclusively reserved for SQL-Server, and not shared
with other applications. Since SQL-Server manages its memory, it doesn't
make sense to assign individual blocks of memory to individual CPU's.
> (c) How does SQL Server parallel processing works?
In a nutshell: by having multiple threads working on a query, where each
thread is handled by a different CPU. It will be used if SQL-Server
estimates it will decrease the total elapsed time of the query.
> (d) I understand that AWE is used to increase the memory capacity for sql server app, if the memory of the server is more than 4 GB, but how exactly is this done?
> Would really appreciate, if someone can provide me the link to the right article or if someone can take sometime to explain me this.
> Thanks very much
> GYK
Hope this helps,
Gert-Jan|||In addition to Kalen's excellent "Inside SQL Server 2000" there's also a
fair bit on memory architecture in BOL plus there's also Ken Hendersons new
book "The Guru's Guide to SQL Server Architecture and Internals" that really
gets into the nitty gritty of UMS and memory architecture including AWE
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:439E3FF7-340A-4A35-8BFE-93C26F570BAB@.microsoft.com...
> Hi,
> Can anybody direct me to an article or an white paper, which explains SQL
Server 2000 Processor and Memory architecture and how they work together?
> Lets take a scenario;
> SQL Server 2000 Enterprise edition, 8GB of physical memory and 4
processors (same server) are available.
> So some of the questions that I have in my mind are;
> (a) How can we distribute 8GB of memory across these 4 processor. How do
we configure it?
> (b) Can we control, the size of memory distributed across these processor.
That is, can I configure 5 GB of memory to one processor and the rest
equally distributed to other processors? If yes, how do we go about it?
> (c) How does SQL Server parallel processing works?
> (d) I understand that AWE is used to increase the memory capacity for sql
server app, if the memory of the server is more than 4 GB, but how exactly
is this done?
> Would really appreciate, if someone can provide me the link to the right
article or if someone can take sometime to explain me this.
> Thanks very much
> GYK|||Absolutely! Ken's book goes into the next level down from mine, discussing
the details of the OS architecture that allow SQL Server to do what it does.
I'm only in the middle of the third chapter, and I've learned a lot!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ONkNv8kxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> In addition to Kalen's excellent "Inside SQL Server 2000" there's also a
> fair bit on memory architecture in BOL plus there's also Ken Hendersons
new
> book "The Guru's Guide to SQL Server Architecture and Internals" that
really
> gets into the nitty gritty of UMS and memory architecture including AWE
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:439E3FF7-340A-4A35-8BFE-93C26F570BAB@.microsoft.com...
> > Hi,
> >
> > Can anybody direct me to an article or an white paper, which explains
SQL
> Server 2000 Processor and Memory architecture and how they work together?
> >
> > Lets take a scenario;
> > SQL Server 2000 Enterprise edition, 8GB of physical memory and 4
> processors (same server) are available.
> >
> > So some of the questions that I have in my mind are;
> > (a) How can we distribute 8GB of memory across these 4 processor. How do
> we configure it?
> > (b) Can we control, the size of memory distributed across these
processor.
> That is, can I configure 5 GB of memory to one processor and the rest
> equally distributed to other processors? If yes, how do we go about it?
> > (c) How does SQL Server parallel processing works?
> > (d) I understand that AWE is used to increase the memory capacity for
sql
> server app, if the memory of the server is more than 4 GB, but how exactly
> is this done?
> >
> > Would really appreciate, if someone can provide me the link to the right
> article or if someone can take sometime to explain me this.
> >
> > Thanks very much
> > GYK
>

ProcessId remains in memory?

Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.
The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:

>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.
|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com... [vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:

ProcessId remains in memory?

Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com...
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on
>>Windows
>>Server 2003 and notice that applications that have been running during the
>>day, have more and more ProcessID's for the same application. The
>>applications sometimes is closed by the user, sometimes crash, but usually
>>they will (after a refresh of EM) not show. I see the Host name in the
>>list
>>of items in Process Info, and now see that each machine has multiple
>>entries
>>for the same application. It seems that the process Info remains fixed in
>>server memory.
>>Anyone know why this should have just started to happed. As far as I can
>>tell, only the clients would have any updates (MS Updates and fixes) the
>>client application has not changed apart from cosmetically. The connection
>>is through ADODB MDAC 2.8
>>Thanks for the time. Hope this is clear.

ProcessId remains in memory?

Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:

>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entrie
s
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.
4ax.com...[vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>