I have the following setup -
Database Server having (Report Server Database + Application Database)
Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75 gb
disk space)
Software - Windows 2K Advance Server SP4, SQL 2000 sp3
IIS Server ( Report Server, Application Website)
Hardware config - 2CPU (1130 mhz), 1 gb RAM
Software - Windows 2K Server SP4, Reporting Services with SP1
Flow of application - User visits the application web site, queries (data
size 2.3 million records for a month) the and internally application passes
this query to Report Server through URL and data comes back to the User.
This thing works fine as long user queries for 2-3 days of data, but it
starts throwing "Server not available..." if it goes beyond that. (I saw
that on the web server aspnet_wp.exe starts crashing)
Any suggestion or solutions are welcome. I'm thinking of putting Report
Server web site on the Database Server, but I want to avoid that as I don't
want to run IIS with SQL 2000 (may be I'm wrong).
Thanks in advance,
SuhaibDo you have the /3GB flag set in c:\boot.ini? Take a look at for some common
things
http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx
Another thing to do would be to monitor the Process | Privates bytes
performance counter for the aspnet_wp.exe process.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Suhaib Khan" <skhan@.faicorp.com> wrote in message
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||how many rows are returned to the report?
have you some aggregated data? does it SQL Server standard or enterprise
edition?
Have you try to use OLAP Cubes?
"Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||What is the size of the result set? How many pages? Also, what are you
rending it as (html, pdf, etc?). How long does it take from query analyzer?
Are you sending lots of data to RS and letting it filter or letting the
query itself filter out the data before it arrives at RS?
Bruce L-C
"Suhaib Khan" <skhan@.faicorp.com> wrote in message
news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have the following setup -
> Database Server having (Report Server Database + Application Database)
> Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
gb
> disk space)
> Software - Windows 2K Advance Server SP4, SQL 2000 sp3
>
> IIS Server ( Report Server, Application Website)
> Hardware config - 2CPU (1130 mhz), 1 gb RAM
> Software - Windows 2K Server SP4, Reporting Services with SP1
> Flow of application - User visits the application web site, queries (data
> size 2.3 million records for a month) the and internally application
passes
> this query to Report Server through URL and data comes back to the User.
> This thing works fine as long user queries for 2-3 days of data, but it
> starts throwing "Server not available..." if it goes beyond that. (I saw
> that on the web server aspnet_wp.exe starts crashing)
> Any suggestion or solutions are welcome. I'm thinking of putting Report
> Server web site on the Database Server, but I want to avoid that as I
don't
> want to run IIS with SQL 2000 (may be I'm wrong).
>
> Thanks in advance,
> Suhaib
>|||how many rows are returned to the report?
for 5 days - 430,000.
have you some aggregated data?
Yes, but we are not using any filter option on Report Server side
does it SQL Server standard or enterprise edition?
enterprise
Have you try to use OLAP Cubes?
Not now.
We are upgrading our current Reports from pure html using ASP to ASP.NET
with Reporting Services. We used to allow user's to query data for 3
months, again these months were not prefined, user's were allowed to select
Jan to March or Feb to April, with Reporting Services we are not able cross
more than 3 days forget about going across 3 months.
Database is partitioned based on Month, Vendor, and Year ID (Currently we
just have one vendor and one month of data, so I can't balme my partiton)
Suhaib
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> how many rows are returned to the report?
> have you some aggregated data? does it SQL Server standard or enterprise
> edition?
> Have you try to use OLAP Cubes?
> "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > I have the following setup -
> >
> > Database Server having (Report Server Database + Application Database)
> > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive (75
> gb
> > disk space)
> > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> >
> >
> > IIS Server ( Report Server, Application Website)
> > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > Software - Windows 2K Server SP4, Reporting Services with SP1
> >
> > Flow of application - User visits the application web site, queries
(data
> > size 2.3 million records for a month) the and internally application
> passes
> > this query to Report Server through URL and data comes back to the User.
> >
> > This thing works fine as long user queries for 2-3 days of data, but it
> > starts throwing "Server not available..." if it goes beyond that. (I saw
> > that on the web server aspnet_wp.exe starts crashing)
> >
> > Any suggestion or solutions are welcome. I'm thinking of putting Report
> > Server web site on the Database Server, but I want to avoid that as I
> don't
> > want to run IIS with SQL 2000 (may be I'm wrong).
> >
> >
> > Thanks in advance,
> >
> > Suhaib
> >
> >
>|||430 000 rows'
wow!!!!
its very very big!!!
why do you return so many details in your report?
If you use aggregated function in RS, then the time to process the report
will be huge!!! in this case, I recommend to create multiple datasets (1 for
the detail, 1 for aggregated data with an SQL statement which do the
calculations (sums, group by...))
Maybe you can schedule the report and save it as a snapshot report. (or save
it as PDF in a network folder)
"Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
news:%23hIXORiXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> how many rows are returned to the report?
> for 5 days - 430,000.
> have you some aggregated data?
> Yes, but we are not using any filter option on Report Server side
> does it SQL Server standard or enterprise edition?
> enterprise
> Have you try to use OLAP Cubes?
> Not now.
> We are upgrading our current Reports from pure html using ASP to ASP.NET
> with Reporting Services. We used to allow user's to query data for 3
> months, again these months were not prefined, user's were allowed to
select
> Jan to March or Feb to April, with Reporting Services we are not able
cross
> more than 3 days forget about going across 3 months.
>
> Database is partitioned based on Month, Vendor, and Year ID (Currently we
> just have one vendor and one month of data, so I can't balme my partiton)
>
> Suhaib
>
> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> > how many rows are returned to the report?
> > have you some aggregated data? does it SQL Server standard or enterprise
> > edition?
> > Have you try to use OLAP Cubes?
> >
> > "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> > news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > I have the following setup -
> > >
> > > Database Server having (Report Server Database + Application Database)
> > > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive
(75
> > gb
> > > disk space)
> > > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> > >
> > >
> > > IIS Server ( Report Server, Application Website)
> > > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > > Software - Windows 2K Server SP4, Reporting Services with SP1
> > >
> > > Flow of application - User visits the application web site, queries
> (data
> > > size 2.3 million records for a month) the and internally application
> > passes
> > > this query to Report Server through URL and data comes back to the
User.
> > >
> > > This thing works fine as long user queries for 2-3 days of data, but
it
> > > starts throwing "Server not available..." if it goes beyond that. (I
saw
> > > that on the web server aspnet_wp.exe starts crashing)
> > >
> > > Any suggestion or solutions are welcome. I'm thinking of putting
Report
> > > Server web site on the Database Server, but I want to avoid that as I
> > don't
> > > want to run IIS with SQL 2000 (may be I'm wrong).
> > >
> > >
> > > Thanks in advance,
> > >
> > > Suhaib
> > >
> > >
> >
> >
>|||Do a report in Query Analyzer and return 430,000 rows. Now wait and wait
somemore and wait somemore. If your customer needs a data extract (for
instance to load their own datamart) then you should use DTS. Otherwise, you
should have reports that provide drill through that allows the customer to
get to the data they are interested in. No human is going to browse 430,000
rows. I suggest you look at how to do drill through. RS supports drill
through reports very cleanly and it is the way to go.
As far as scheduling a PDF. This would be a 5,000 page PDF report. This is
not reasonable to expect a system to do. I strongly suggest re-analyzing
your requirements and solutions. Nobody will have a solution for a 5,000
page report that is speedy. Again, if you are extracting data for someone's
datamart then use DTS.
Bruce L-C
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:umOPgHrXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> 430 000 rows'
> wow!!!!
> its very very big!!!
> why do you return so many details in your report?
> If you use aggregated function in RS, then the time to process the report
> will be huge!!! in this case, I recommend to create multiple datasets (1
for
> the detail, 1 for aggregated data with an SQL statement which do the
> calculations (sums, group by...))
> Maybe you can schedule the report and save it as a snapshot report. (or
save
> it as PDF in a network folder)
> "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> news:%23hIXORiXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > how many rows are returned to the report?
> > for 5 days - 430,000.
> >
> > have you some aggregated data?
> > Yes, but we are not using any filter option on Report Server side
> >
> > does it SQL Server standard or enterprise edition?
> > enterprise
> >
> > Have you try to use OLAP Cubes?
> > Not now.
> >
> > We are upgrading our current Reports from pure html using ASP to ASP.NET
> > with Reporting Services. We used to allow user's to query data for 3
> > months, again these months were not prefined, user's were allowed to
> select
> > Jan to March or Feb to April, with Reporting Services we are not able
> cross
> > more than 3 days forget about going across 3 months.
> >
> >
> > Database is partitioned based on Month, Vendor, and Year ID (Currently
we
> > just have one vendor and one month of data, so I can't balme my
partiton)
> >
> >
> > Suhaib
> >
> >
> > "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> > news:%23MZgb%23gXEHA.1048@.tk2msftngp13.phx.gbl...
> > > how many rows are returned to the report?
> > > have you some aggregated data? does it SQL Server standard or
enterprise
> > > edition?
> > > Have you try to use OLAP Cubes?
> > >
> > > "Suhaib Khan" <skhan@.faicorp.com> a écrit dans le message de
> > > news:ebyDrUgXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > > I have the following setup -
> > > >
> > > > Database Server having (Report Server Database + Application
Database)
> > > > Hardware Config - 4 CPU 1.6 ghz, 8 GB RAM, About 15 GB RAID x5 drive
> (75
> > > gb
> > > > disk space)
> > > > Software - Windows 2K Advance Server SP4, SQL 2000 sp3
> > > >
> > > >
> > > > IIS Server ( Report Server, Application Website)
> > > > Hardware config - 2CPU (1130 mhz), 1 gb RAM
> > > > Software - Windows 2K Server SP4, Reporting Services with SP1
> > > >
> > > > Flow of application - User visits the application web site, queries
> > (data
> > > > size 2.3 million records for a month) the and internally application
> > > passes
> > > > this query to Report Server through URL and data comes back to the
> User.
> > > >
> > > > This thing works fine as long user queries for 2-3 days of data, but
> it
> > > > starts throwing "Server not available..." if it goes beyond that. (I
> saw
> > > > that on the web server aspnet_wp.exe starts crashing)
> > > >
> > > > Any suggestion or solutions are welcome. I'm thinking of putting
> Report
> > > > Server web site on the Database Server, but I want to avoid that as
I
> > > don't
> > > > want to run IIS with SQL 2000 (may be I'm wrong).
> > > >
> > > >
> > > > Thanks in advance,
> > > >
> > > > Suhaib
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label cpu. Show all posts
Showing posts with label cpu. Show all posts
Friday, March 30, 2012
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.
>
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
Monday, March 26, 2012
Performance Degraded and RESOURCE_SEMAPHORE started appearing
Hello,
We are running a 4 CPU SQL Server 2000 with RAID arrays of 5 disks each on
two separate channels of a controller.
It all worked great... However, a few days ago the performance went way
down.
I am noticing a lot of RESOURCE_SEMAPHORE waittypes in the sysprocesses
table.
Also the Avg Disk Queue Length counter started showing high numbers.
Is there something specific I need to look for? When running the SQL
Profiler I do not see anything unusual - just that the queries are taking
much longer to complete.
Should I reboot the server? It was not rebooted since March, 2003.
Please let me know!
Thank you,
ArsenHi Andrew,
All disks look okay. I do not see scans of tables. Indexes are used as
before.
Although, I just noticed that the Cache Hit is around 52%, where as on my
other SQL Server boxes it is around 98%. Could this be something?
How to fix this? Reboot?
Arsen|||while this sounds like a hardware problem occured at the
point a few days ago, can you just quickly check to see if
there is a particular query causing this
look in profiler for anything >> 5sec cpu or duration
>--Original Message--
>Hello,
>We are running a 4 CPU SQL Server 2000 with RAID arrays
of 5 disks each on
>two separate channels of a controller.
>It all worked great... However, a few days ago the
performance went way
>down.
>I am noticing a lot of RESOURCE_SEMAPHORE waittypes in
the sysprocesses
>table.
>Also the Avg Disk Queue Length counter started showing
high numbers.
>Is there something specific I need to look for? When
running the SQL
>Profiler I do not see anything unusual - just that the
queries are taking
>much longer to complete.
>Should I reboot the server? It was not rebooted since
March, 2003.
>Please let me know!
>Thank you,
>Arsen
>
>.
>
We are running a 4 CPU SQL Server 2000 with RAID arrays of 5 disks each on
two separate channels of a controller.
It all worked great... However, a few days ago the performance went way
down.
I am noticing a lot of RESOURCE_SEMAPHORE waittypes in the sysprocesses
table.
Also the Avg Disk Queue Length counter started showing high numbers.
Is there something specific I need to look for? When running the SQL
Profiler I do not see anything unusual - just that the queries are taking
much longer to complete.
Should I reboot the server? It was not rebooted since March, 2003.
Please let me know!
Thank you,
ArsenHi Andrew,
All disks look okay. I do not see scans of tables. Indexes are used as
before.
Although, I just noticed that the Cache Hit is around 52%, where as on my
other SQL Server boxes it is around 98%. Could this be something?
How to fix this? Reboot?
Arsen|||while this sounds like a hardware problem occured at the
point a few days ago, can you just quickly check to see if
there is a particular query causing this
look in profiler for anything >> 5sec cpu or duration
>--Original Message--
>Hello,
>We are running a 4 CPU SQL Server 2000 with RAID arrays
of 5 disks each on
>two separate channels of a controller.
>It all worked great... However, a few days ago the
performance went way
>down.
>I am noticing a lot of RESOURCE_SEMAPHORE waittypes in
the sysprocesses
>table.
>Also the Avg Disk Queue Length counter started showing
high numbers.
>Is there something specific I need to look for? When
running the SQL
>Profiler I do not see anything unusual - just that the
queries are taking
>much longer to complete.
>Should I reboot the server? It was not rebooted since
March, 2003.
>Please let me know!
>Thank you,
>Arsen
>
>.
>
performance degradation
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
You have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server_...nce_tuning.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
You have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server_...nce_tuning.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you
performance degradation
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank youYou have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server...ance_tuning.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank youYou have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server...ance_tuning.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you
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]
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Friday, March 9, 2012
Performance
I have an application that allows for searches, but it appears when I stress
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?
It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?
|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>
|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?
|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>
|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> Let's assume that it is optimized. Where to next?
|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...
|||Have a look here:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> you
> Let's assume that it is optimized. Where to next?
>
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?
It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?
|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>
|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?
|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>
|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> Let's assume that it is optimized. Where to next?
|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...
|||Have a look here:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> you
> Let's assume that it is optimized. Where to next?
>
Wednesday, March 7, 2012
Performance
I have an application that allows for searches, but it appears when I stress
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stre
ss
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure i
f
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to dea
l
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if
I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if
I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> you
> Let's assume that it is optimized. Where to next?
>
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stre
ss
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure i
f
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to dea
l
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if
I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> Raid
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if
I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> you
> Let's assume that it is optimized. Where to next?
>
Performance
I have an application that allows for searches, but it appears when I stress
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
--
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
> > - Did you enable /3GB switch, if possible.
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly
>> what you are doing. But more than likely your queries and / or
>> indexes are not optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly what
> you
>> are doing. But more than likely your queries and / or indexes are not
>> optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?
>
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
--
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
> > - Did you enable /3GB switch, if possible.
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly
>> what you are doing. But more than likely your queries and / or
>> indexes are not optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly what
> you
>> are doing. But more than likely your queries and / or indexes are not
>> optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?
>
Performance
I have a SQL server 7.0 which is installed on a dual
processors machine (NT4.0), when I try to do a query its
fairly slow, I have checked CPU utilization and memory
they are ok, is there anyway to find out what is wrong
here?
Also is there any tool I can check SQL server performance?
thanks in advance-RobCheck the query plan, also use profiler to see
the statistics of the query, such as reads, writes, etc.
>--Original Message--
>I have a SQL server 7.0 which is installed on a dual
>processors machine (NT4.0), when I try to do a query its
>fairly slow, I have checked CPU utilization and memory
>they are ok, is there anyway to find out what is wrong
>here?
>Also is there any tool I can check SQL server performance?
>thanks in advance-Rob
>.
>|||I am new this area, what is the query plan and how can I
use the profiler.
Thanks-rob
>--Original Message--
>Check the query plan, also use profiler to see
>the statistics of the query, such as reads, writes, etc.
>>--Original Message--
>>I have a SQL server 7.0 which is installed on a dual
>>processors machine (NT4.0), when I try to do a query its
>>fairly slow, I have checked CPU utilization and memory
>>they are ok, is there anyway to find out what is wrong
>>here?
>>Also is there any tool I can check SQL server
performance?
>>thanks in advance-Rob
>>.
>.
>|||Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
newsgroup post.
The query plan will show you problems like Table Scans that can be solved
with more selective where clauses and/or applying indexes to table in
question.
There is obviously much more to it than that, but there is a start for you.
Also, visit www.sql-server-performance.com
Cheers,
Greg Jackson
Portland, OR|||Hi Rob,
You may also want to refer to these articles that may help you troubleshoot performance issues.
224587 HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/?id=224587
This step-by-step article describes how to troubleshoot SQL Server performance issues. Troubleshooting
performance issues involves the use of a series of steps to isolate and determine the cause of an application
slowdown.
~~~
243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
This article describes how to handle a performance issue that applications may experience in conjunction with
Microsoft SQL Server: slow performance of a specific query or group of queries.
~~~
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
This article describes how to troubleshoot the slow performance of many concurrent ad-hoc queries.
~~~
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze
that information to successfully resolve blocking issues.
~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jaxon" <GregoryAJackson@.hotmail.com>
> References: <045401c3a960$5eccf1f0$a301280a@.phx.gbl> <037501c3a964$e45d7660$a401280a@.phx.gbl>
<05b301c3a966$30440bb0$a101280a@.phx.gbl>
> Subject: Re: Performance
> Date: Wed, 12 Nov 2003 16:31:22 -0800
> Lines: 19
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> Message-ID: <OKLL41XqDHA.2964@.tk2msftngp13.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: 66.203.99.51
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316205
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
> newsgroup post.
> The query plan will show you problems like Table Scans that can be solved
> with more selective where clauses and/or applying indexes to table in
> question.
> There is obviously much more to it than that, but there is a start for you.
> Also, visit www.sql-server-performance.com
>
> Cheers,
>
> Greg Jackson
> Portland, OR
>
>
processors machine (NT4.0), when I try to do a query its
fairly slow, I have checked CPU utilization and memory
they are ok, is there anyway to find out what is wrong
here?
Also is there any tool I can check SQL server performance?
thanks in advance-RobCheck the query plan, also use profiler to see
the statistics of the query, such as reads, writes, etc.
>--Original Message--
>I have a SQL server 7.0 which is installed on a dual
>processors machine (NT4.0), when I try to do a query its
>fairly slow, I have checked CPU utilization and memory
>they are ok, is there anyway to find out what is wrong
>here?
>Also is there any tool I can check SQL server performance?
>thanks in advance-Rob
>.
>|||I am new this area, what is the query plan and how can I
use the profiler.
Thanks-rob
>--Original Message--
>Check the query plan, also use profiler to see
>the statistics of the query, such as reads, writes, etc.
>>--Original Message--
>>I have a SQL server 7.0 which is installed on a dual
>>processors machine (NT4.0), when I try to do a query its
>>fairly slow, I have checked CPU utilization and memory
>>they are ok, is there anyway to find out what is wrong
>>here?
>>Also is there any tool I can check SQL server
performance?
>>thanks in advance-Rob
>>.
>.
>|||Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
newsgroup post.
The query plan will show you problems like Table Scans that can be solved
with more selective where clauses and/or applying indexes to table in
question.
There is obviously much more to it than that, but there is a start for you.
Also, visit www.sql-server-performance.com
Cheers,
Greg Jackson
Portland, OR|||Hi Rob,
You may also want to refer to these articles that may help you troubleshoot performance issues.
224587 HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/?id=224587
This step-by-step article describes how to troubleshoot SQL Server performance issues. Troubleshooting
performance issues involves the use of a series of steps to isolate and determine the cause of an application
slowdown.
~~~
243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
This article describes how to handle a performance issue that applications may experience in conjunction with
Microsoft SQL Server: slow performance of a specific query or group of queries.
~~~
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
This article describes how to troubleshoot the slow performance of many concurrent ad-hoc queries.
~~~
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze
that information to successfully resolve blocking issues.
~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jaxon" <GregoryAJackson@.hotmail.com>
> References: <045401c3a960$5eccf1f0$a301280a@.phx.gbl> <037501c3a964$e45d7660$a401280a@.phx.gbl>
<05b301c3a966$30440bb0$a101280a@.phx.gbl>
> Subject: Re: Performance
> Date: Wed, 12 Nov 2003 16:31:22 -0800
> Lines: 19
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> Message-ID: <OKLL41XqDHA.2964@.tk2msftngp13.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: 66.203.99.51
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316205
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
> newsgroup post.
> The query plan will show you problems like Table Scans that can be solved
> with more selective where clauses and/or applying indexes to table in
> question.
> There is obviously much more to it than that, but there is a start for you.
> Also, visit www.sql-server-performance.com
>
> Cheers,
>
> Greg Jackson
> Portland, OR
>
>
performance
I am running sp_who2
I discover some task are occpied the CPU
one is Log Writer CPUTIme 36906
One is checkpoint sleep CPUTIme 26422
how can i stop it?Those are SQL Server system processes - you don't want to
stop them from doing what they need to do. CPU time is
cumulative for the processes.
-Sue
On Wed, 30 Mar 2005 16:11:31 +0800, "Test" <TEst@.test.com>
wrote:
>I am running sp_who2
>I discover some task are occpied the CPU
>one is Log Writer CPUTIme 36906
>One is checkpoint sleep CPUTIme 26422
>how can i stop it?
>
I discover some task are occpied the CPU
one is Log Writer CPUTIme 36906
One is checkpoint sleep CPUTIme 26422
how can i stop it?Those are SQL Server system processes - you don't want to
stop them from doing what they need to do. CPU time is
cumulative for the processes.
-Sue
On Wed, 30 Mar 2005 16:11:31 +0800, "Test" <TEst@.test.com>
wrote:
>I am running sp_who2
>I discover some task are occpied the CPU
>one is Log Writer CPUTIme 36906
>One is checkpoint sleep CPUTIme 26422
>how can i stop it?
>
Saturday, February 25, 2012
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
Subscribe to:
Posts (Atom)