Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>
|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>
Showing posts with label memory. Show all posts
Showing posts with label memory. Show all posts
Wednesday, March 28, 2012
Performance gain by switching from Win2000 to Win2003?
Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>
Performance gain by switching from Win2000 to Win2003?
Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>sql
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>sql
Friday, March 23, 2012
Performance counter missing
Sometimes the SQL Performance counters dissappear.
I have this problem when I change "Reserve physical memory" from Enterprise
Managers (I'm using dynamically memory allocation), its ask me about restart
the service, if I answer Yes the service is restarted but the sql counters
disappear. When I restart (shutdown) the server, the counter appear again...
The same occurs if I use sp_configure 'set working set size', after I
restart the service, the counters are missing; when I restart the server,
it's OK...
It isn't a big problema cause I can restart the server for a while, but I
think it's not a normal condition.
sio Nunes
It's a bug that usually happens if you restart SQL Server and have something
running that is polling the counters at the time you shut down the service.
The trick is to ensure nothing is accessing any of the counters (perfmon,
custom traces etc) before you restart.
These may help:
SQL Perf Counters Missing After MDAC Install on Cluster [Q246328]
"Event ID (1001) in Source (SQLCTR70) Could Not Be Found" [Q196657]
Missing Objects and Counters in Performance Monitor [Q127207]
SQL Performance Monitor Counters Missing [Q227662]
Andrew J. Kelly SQL MVP
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:%23EgzFlC5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Sometimes the SQL Performance counters dissappear.
> I have this problem when I change "Reserve physical memory" from
> Enterprise
> Managers (I'm using dynamically memory allocation), its ask me about
> restart
> the service, if I answer Yes the service is restarted but the sql counters
> disappear. When I restart (shutdown) the server, the counter appear
> again...
> The same occurs if I use sp_configure 'set working set size', after I
> restart the service, the counters are missing; when I restart the server,
> it's OK...
> It isn't a big problema cause I can restart the server for a while, but I
> think it's not a normal condition.
> sio Nunes
>
>
I have this problem when I change "Reserve physical memory" from Enterprise
Managers (I'm using dynamically memory allocation), its ask me about restart
the service, if I answer Yes the service is restarted but the sql counters
disappear. When I restart (shutdown) the server, the counter appear again...
The same occurs if I use sp_configure 'set working set size', after I
restart the service, the counters are missing; when I restart the server,
it's OK...
It isn't a big problema cause I can restart the server for a while, but I
think it's not a normal condition.
sio Nunes
It's a bug that usually happens if you restart SQL Server and have something
running that is polling the counters at the time you shut down the service.
The trick is to ensure nothing is accessing any of the counters (perfmon,
custom traces etc) before you restart.
These may help:
SQL Perf Counters Missing After MDAC Install on Cluster [Q246328]
"Event ID (1001) in Source (SQLCTR70) Could Not Be Found" [Q196657]
Missing Objects and Counters in Performance Monitor [Q127207]
SQL Performance Monitor Counters Missing [Q227662]
Andrew J. Kelly SQL MVP
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:%23EgzFlC5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Sometimes the SQL Performance counters dissappear.
> I have this problem when I change "Reserve physical memory" from
> Enterprise
> Managers (I'm using dynamically memory allocation), its ask me about
> restart
> the service, if I answer Yes the service is restarted but the sql counters
> disappear. When I restart (shutdown) the server, the counter appear
> again...
> The same occurs if I use sp_configure 'set working set size', after I
> restart the service, the counters are missing; when I restart the server,
> it's OK...
> It isn't a big problema cause I can restart the server for a while, but I
> think it's not a normal condition.
> sio Nunes
>
>
Performance counter missing
Sometimes the SQL Performance counters dissappear.
I have this problem when I change "Reserve physical memory" from Enterprise
Managers (I'm using dynamically memory allocation), its ask me about restart
the service, if I answer Yes the service is restarted but the sql counters
disappear. When I restart (shutdown) the server, the counter appear again...
The same occurs if I use sp_configure 'set working set size', after I
restart the service, the counters are missing; when I restart the server,
it's OK...
It isn't a big problema cause I can restart the server for a while, but I
think it's not a normal condition.
Ésio NunesIt's a bug that usually happens if you restart SQL Server and have something
running that is polling the counters at the time you shut down the service.
The trick is to ensure nothing is accessing any of the counters (perfmon,
custom traces etc) before you restart.
These may help:
SQL Perf Counters Missing After MDAC Install on Cluster [Q246328]
"Event ID (1001) in Source (SQLCTR70) Could Not Be Found" [Q196657]
Missing Objects and Counters in Performance Monitor [Q127207]
SQL Performance Monitor Counters Missing [Q227662]
--
Andrew J. Kelly SQL MVP
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:%23EgzFlC5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Sometimes the SQL Performance counters dissappear.
> I have this problem when I change "Reserve physical memory" from
> Enterprise
> Managers (I'm using dynamically memory allocation), its ask me about
> restart
> the service, if I answer Yes the service is restarted but the sql counters
> disappear. When I restart (shutdown) the server, the counter appear
> again...
> The same occurs if I use sp_configure 'set working set size', after I
> restart the service, the counters are missing; when I restart the server,
> it's OK...
> It isn't a big problema cause I can restart the server for a while, but I
> think it's not a normal condition.
> Ésio Nunes
>
>
I have this problem when I change "Reserve physical memory" from Enterprise
Managers (I'm using dynamically memory allocation), its ask me about restart
the service, if I answer Yes the service is restarted but the sql counters
disappear. When I restart (shutdown) the server, the counter appear again...
The same occurs if I use sp_configure 'set working set size', after I
restart the service, the counters are missing; when I restart the server,
it's OK...
It isn't a big problema cause I can restart the server for a while, but I
think it's not a normal condition.
Ésio NunesIt's a bug that usually happens if you restart SQL Server and have something
running that is polling the counters at the time you shut down the service.
The trick is to ensure nothing is accessing any of the counters (perfmon,
custom traces etc) before you restart.
These may help:
SQL Perf Counters Missing After MDAC Install on Cluster [Q246328]
"Event ID (1001) in Source (SQLCTR70) Could Not Be Found" [Q196657]
Missing Objects and Counters in Performance Monitor [Q127207]
SQL Performance Monitor Counters Missing [Q227662]
--
Andrew J. Kelly SQL MVP
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:%23EgzFlC5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Sometimes the SQL Performance counters dissappear.
> I have this problem when I change "Reserve physical memory" from
> Enterprise
> Managers (I'm using dynamically memory allocation), its ask me about
> restart
> the service, if I answer Yes the service is restarted but the sql counters
> disappear. When I restart (shutdown) the server, the counter appear
> again...
> The same occurs if I use sp_configure 'set working set size', after I
> restart the service, the counters are missing; when I restart the server,
> it's OK...
> It isn't a big problema cause I can restart the server for a while, but I
> think it's not a normal condition.
> Ésio Nunes
>
>
Wednesday, March 21, 2012
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David
|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David
|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan
.>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds
,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds
,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan
.>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
Tuesday, March 20, 2012
Performance and TempDB
I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||My guess if that you have one or several queries that now uses a much worse plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
down is to work the query plans. Ideally, you would compared to before this happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that is
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:
>My guess if that you have one or several queries that now uses a much worse plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
>simply.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||New statistics will force recompilation. Reindexing will produce new statistics (INDEXDEFRAG will
not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
>>My guess if that you have one or several queries that now uses a much worse plan that it used to
>>do.
>>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track
>>this
>>down is to work the query plans. Ideally, you would compared to before this happened to track down
>>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan,
>>quite
>>simply.
>>I am seeing high CPU.
>>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||My guess if that you have one or several queries that now uses a much worse plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
down is to work the query plans. Ideally, you would compared to before this happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that is
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:
>My guess if that you have one or several queries that now uses a much worse plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
>simply.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||New statistics will force recompilation. Reindexing will produce new statistics (INDEXDEFRAG will
not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
>>My guess if that you have one or several queries that now uses a much worse plan that it used to
>>do.
>>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track
>>this
>>down is to work the query plans. Ideally, you would compared to before this happened to track down
>>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan,
>>quite
>>simply.
>>I am seeing high CPU.
>>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>
Performance and TempDB
I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%
.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:[vbcol=seagreen]
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% fr
ee.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier.
Only way to track this
down is to work the query plans. Ideally, you would compared to before this
happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, l
ack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a differe
nt execution plan, quite
simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...[vbcol
=seagreen]
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90
%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at
a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that i
s
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:[vbcol=seagreen]
>My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier
. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this
happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics,
lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a differ
ent execution plan, quite
>simply.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||New statistics will force recompilation. Reindexing will produce new statist
ics (INDEXDEFRAG will
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...[vbcol
=seagreen]
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%
.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:[vbcol=seagreen]
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% fr
ee.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier.
Only way to track this
down is to work the query plans. Ideally, you would compared to before this
happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, l
ack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a differe
nt execution plan, quite
simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...[vbcol
=seagreen]
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90
%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at
a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that i
s
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:[vbcol=seagreen]
>My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier
. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this
happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics,
lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a differ
ent execution plan, quite
>simply.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||New statistics will force recompilation. Reindexing will produce new statist
ics (INDEXDEFRAG will
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...[vbcol
=seagreen]
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]
Monday, March 12, 2012
Performance and Detection
I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !
Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>
|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ
|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com
|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !
Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>
|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ
|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com
|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Performance and Detection
I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application whic
h
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the serve
r
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application whic
h
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the serve
r
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Performance and Detection
I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
--
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> >I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> > Server has a Memory of 512 K. There are different users using different
> > applications like access, Crystal reports and other general application
> > which
> > accesses these database in Production enviornment.
> >
> > Problem:
> > Sometime we experience slowdowns and then after about 5 MIN
> > everything starts moving again. Sometimes I have to disconnect users from
> > the SQL SERVER to make sure everything is working again. Reboot of the
> > server
> > also helps and will work as normal. But time and again we experience
> > slowdowns.
> > Number of users accessing this server is about 35. Not sure how do I fix
> > this problem. How do I know who is the culprit and whats locking it up.
> > Sp_WHO and SP_lock2 doesn't tell anything.
> >
> > Please advice.
> >
> > Jazzman !
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
--
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> >I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> > Server has a Memory of 512 K. There are different users using different
> > applications like access, Crystal reports and other general application
> > which
> > accesses these database in Production enviornment.
> >
> > Problem:
> > Sometime we experience slowdowns and then after about 5 MIN
> > everything starts moving again. Sometimes I have to disconnect users from
> > the SQL SERVER to make sure everything is working again. Reboot of the
> > server
> > also helps and will work as normal. But time and again we experience
> > slowdowns.
> > Number of users accessing this server is about 35. Not sure how do I fix
> > this problem. How do I know who is the culprit and whats locking it up.
> > Sp_WHO and SP_lock2 doesn't tell anything.
> >
> > Please advice.
> >
> > Jazzman !
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Performance / Paging
Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Serv
er 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Serv
er 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Performance / Paging
Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Performance / Paging
Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
Bill
Hi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB?
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
Bill
Hi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB?
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Wednesday, March 7, 2012
Performace montior
HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
.. "
also i check that i am administrator and i login as administrator
pls help
-TP
Hi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default...812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
.. "
also i check that i am administrator and i login as administrator
pls help
-TP
Hi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default...812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
Performace montior
HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/defaul...;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preforma
ce
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collecte
d.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/defaul...;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preforma
ce
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collecte
d.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
Performace montior
HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>
Subscribe to:
Posts (Atom)