Performance Help Help....

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
> > > >
> > > >
> > >
> > >
> >
> >
>

Performance difference between Std & Enterprise Editions

Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one instance
of SQLServer that was standard edition. We upgraded the SQLServer to
Enterprise edition and set the max memory to 3584MB. Performance is worse
now. Is this coincidental or should we expect it? Is there some other
setting that we need to do?
BTW, we implemented log shipping and found that the msdb DB did not contain
the tables and stored procedures we needed. Apparently the upgrade from Std
to Enterprise edition did not upgrade the msdb DB. We backed up an msdb DB
on another Enterprise SQLServer system and restored it on our main server.
The resored msdb DB has the tables and stored procedures we needed and we
were able to fire up the log shipping. Is this situation normal?
Picine Karppehd
Look at the topics "UPDATE STATISTICS" and "Reading Pages" in Books Online.
The former will show you how to update the statistics on your database,
which may not be optimal after the upgrade. The latter describes "advanced
scan," a type of read-ahead I/O which Ent uses but Std doesn't, and may
account for some of the differences.
Might also see http://support.microsoft.com/?kbid=243589
http://www.aspfaq.com/
(Reverse address to reply.)
"Picine Karppehd" <PicineKarppehd@.hotmail.com.(donotspam)> wrote in message
news:E1126808-7BF5-4802-A4FD-DACE5E8965A9@.microsoft.com...
> Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one
instance
> of SQLServer that was standard edition. We upgraded the SQLServer to
> Enterprise edition and set the max memory to 3584MB. Performance is worse
> now. Is this coincidental or should we expect it? Is there some other
> setting that we need to do?
> BTW, we implemented log shipping and found that the msdb DB did not
contain
> the tables and stored procedures we needed. Apparently the upgrade from
Std
> to Enterprise edition did not upgrade the msdb DB. We backed up an msdb
DB
> on another Enterprise SQLServer system and restored it on our main server.
> The resored msdb DB has the tables and stored procedures we needed and we
> were able to fire up the log shipping. Is this situation normal?
> --
> Picine Karppehd
>

Performance difference between Std & Enterprise Editions

Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one instance
of SQLServer that was standard edition. We upgraded the SQLServer to
Enterprise edition and set the max memory to 3584MB. Performance is worse
now. Is this coincidental or should we expect it? Is there some other
setting that we need to do?
BTW, we implemented log shipping and found that the msdb DB did not contain
the tables and stored procedures we needed. Apparently the upgrade from Std
to Enterprise edition did not upgrade the msdb DB. We backed up an msdb DB
on another Enterprise SQLServer system and restored it on our main server.
The resored msdb DB has the tables and stored procedures we needed and we
were able to fire up the log shipping. Is this situation normal?
Picine KarppehdLook at the topics "UPDATE STATISTICS" and "Reading Pages" in Books Online.
The former will show you how to update the statistics on your database,
which may not be optimal after the upgrade. The latter describes "advanced
scan," a type of read-ahead I/O which Ent uses but Std doesn't, and may
account for some of the differences.
Might also see http://support.microsoft.com/?kbid=243589
http://www.aspfaq.com/
(Reverse address to reply.)
"Picine Karppehd" <PicineKarppehd@.hotmail.com.(donotspam)> wrote in message
news:E1126808-7BF5-4802-A4FD-DACE5E8965A9@.microsoft.com...
> Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one
instance
> of SQLServer that was standard edition. We upgraded the SQLServer to
> Enterprise edition and set the max memory to 3584MB. Performance is worse
> now. Is this coincidental or should we expect it? Is there some other
> setting that we need to do?
> BTW, we implemented log shipping and found that the msdb DB did not
contain
> the tables and stored procedures we needed. Apparently the upgrade from
Std
> to Enterprise edition did not upgrade the msdb DB. We backed up an msdb
DB
> on another Enterprise SQLServer system and restored it on our main server.
> The resored msdb DB has the tables and stored procedures we needed and we
> were able to fire up the log shipping. Is this situation normal?
> --
> Picine Karppehd
>

Performance difference between Std & Enterprise Editions

Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one instance
of SQLServer that was standard edition. We upgraded the SQLServer to
Enterprise edition and set the max memory to 3584MB. Performance is worse
now. Is this coincidental or should we expect it? Is there some other
setting that we need to do?
BTW, we implemented log shipping and found that the msdb DB did not contain
the tables and stored procedures we needed. Apparently the upgrade from Std
to Enterprise edition did not upgrade the msdb DB. We backed up an msdb DB
on another Enterprise SQLServer system and restored it on our main server.
The resored msdb DB has the tables and stored procedures we needed and we
were able to fire up the log shipping. Is this situation normal?
--
Picine KarppehdLook at the topics "UPDATE STATISTICS" and "Reading Pages" in Books Online.
The former will show you how to update the statistics on your database,
which may not be optimal after the upgrade. The latter describes "advanced
scan," a type of read-ahead I/O which Ent uses but Std doesn't, and may
account for some of the differences.
Might also see http://support.microsoft.com/?kbid=243589
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Picine Karppehd" <PicineKarppehd@.hotmail.com.(donotspam)> wrote in message
news:E1126808-7BF5-4802-A4FD-DACE5E8965A9@.microsoft.com...
> Our main server is a Compaq Quad 700Mhz server with 4GB RAM and one
instance
> of SQLServer that was standard edition. We upgraded the SQLServer to
> Enterprise edition and set the max memory to 3584MB. Performance is worse
> now. Is this coincidental or should we expect it? Is there some other
> setting that we need to do?
> BTW, we implemented log shipping and found that the msdb DB did not
contain
> the tables and stored procedures we needed. Apparently the upgrade from
Std
> to Enterprise edition did not upgrade the msdb DB. We backed up an msdb
DB
> on another Enterprise SQLServer system and restored it on our main server.
> The resored msdb DB has the tables and stored procedures we needed and we
> were able to fire up the log shipping. Is this situation normal?
> --
> Picine Karppehd
>

Performance decrease on new server

This one is really bugging me and I'd really appreciate some pointers.
Had a db that worked fine on a dual PII-400 server with 2 gb RAM running NT
4 Server SP6 with SQL Server 7.0.
Bought a dual xeon 2.4 Ghz, with 2 gb RAM and installed Windows 2000 Server
SP4. Then installed SQL Server 7.0 and then applied SP4.
There's plenty of disk space, ie, over 40 Gb free and the database's mdf
file is 2 gb and its ldf file 271 Mb.
I moved the db using detach / attach and ran sp_updatestats to the db
afterwards.
Performance is generally OK (though the db is hardly being pushed) except
for when I try to execute a complicated query. On the old box, this query
would take up 85 - 95% of cpu for around 30 seconds. On the new box, the
same query takes up 100% cpu and goes on for minutes. I haven't actually
timed it, but it takes an unacceptable time.
I could rewrite the query - split it into two or something - but surely this
should be unnecessary on a more powerful box.
I'm fairly newby to sql server admin so may have missed something obvious.
The query is usually executed via odbc from an Access 2000 application, but
I've run it locally on the server to remove Access from the equation.
I've run the Profiler but to be honest it tells me little. Just shows the
sql transact statement starting but not finishing (I end up cancelling the
query before it completes because the system is live).This is a multi-part message in MIME format.
--=_NextPart_000_0D08_01C393E8.DA49A0E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:#WTPEhAlDHA.644@.TK2MSFTNGP11.phx.gbl...
This one is really bugging me and I'd really appreciate some pointers.
Had a db that worked fine on a dual PII-400 server with 2 gb RAM running =NT
4 Server SP6 with SQL Server 7.0.
Bought a dual xeon 2.4 Ghz, with 2 gb RAM and installed Windows 2000 =Server
SP4. Then installed SQL Server 7.0 and then applied SP4.
There's plenty of disk space, ie, over 40 Gb free and the database's mdf
file is 2 gb and its ldf file 271 Mb.
I moved the db using detach / attach and ran sp_updatestats to the db
afterwards.
Performance is generally OK (though the db is hardly being pushed) =except
for when I try to execute a complicated query. On the old box, this =query
would take up 85 - 95% of cpu for around 30 seconds. On the new box, =the
same query takes up 100% cpu and goes on for minutes. I haven't =actually
timed it, but it takes an unacceptable time.
I could rewrite the query - split it into two or something - but surely =this
should be unnecessary on a more powerful box.
I'm fairly newby to sql server admin so may have missed something =obvious.
The query is usually executed via odbc from an Access 2000 application, =but
I've run it locally on the server to remove Access from the equation.
I've run the Profiler but to be honest it tells me little. Just shows =the
sql transact statement starting but not finishing (I end up cancelling =the
query before it completes because the system is live).
--=_NextPart_000_0D08_01C393E8.DA49A0E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:
sp_MSforeachtable 'update =statistics ? with fullscan'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh" wrote in message news:#WTPEhAlDHA.644@.T=K2MSFTNGP11.phx.gbl...This one is really bugging me and I'd really appreciate some =pointers.Had a db that worked fine on a dual PII-400 server with 2 gb RAM running =NT4 Server SP6 with SQL Server 7.0.Bought a dual xeon 2.4 Ghz, with =2 gb RAM and installed Windows 2000 ServerSP4. Then installed SQL =Server 7.0 and then applied SP4.There's plenty of disk space, ie, over 40 =Gb free and the database's mdffile is 2 gb and its ldf file 271 Mb.I =moved the db using detach / attach and ran sp_updatestats to the dbafterwards.Performance is generally OK (though the db is =hardly being pushed) exceptfor when I try to execute a complicated =query. On the old box, this querywould take up 85 - 95% of cpu for around 30 seconds. On the new box, thesame query takes up 100% cpu and =goes on for minutes. I haven't actuallytimed it, but it takes an =unacceptable time.I could rewrite the query - split it into two or something =- but surely thisshould be unnecessary on a more powerful box.I'm =fairly newby to sql server admin so may have missed something =obvious.The query is usually executed via odbc from an Access 2000 application, =butI've run it locally on the server to remove Access from the equation.I've =run the Profiler but to be honest it tells me little. Just shows =thesql transact statement starting but not finishing (I end up cancelling =thequery before it completes because the system is =live).

---

--=_NextPart_000_0D08_01C393E8.DA49A0E0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0035_01C39495.993D8690
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for that, Tom. Just tried it. No joy.
I ran the offending query overnight and it took 50 minutes. Previously =it took 30 seconds.
Clearly, there's something fundamental that I've not done.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elXzkpAlDHA.1764@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
--=_NextPart_000_0035_01C39495.993D8690
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for that, Tom. Just tried =it. No joy.
I ran the offending query overnight and =it took 50 minutes. Previously it took 30 seconds.
Clearly, there's something fundamental =that I've not done.
"Tom Moreau" = wrote in message news:elXzkpAlDHA.1764=@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:

sp_MSforeachtable 'update =statistics ? with fullscan'

---

--=_NextPart_000_0035_01C39495.993D8690--|||This is a multi-part message in MIME format.
--=_NextPart_000_00C0_01C3948D.9B62D130
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
OK, re-reading it, I see that the OS and hardware have changed but not =SQL Server. I'd be interested in the query plan it generates, as well =as the statistics IO. Also, there may be a disk issue here. Have you =looked at avg disk queue length? What is your disk configuration?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:Owo##xIlDHA.392@.TK2MSFTNGP11.phx.gbl...
Thanks for that, Tom. Just tried it. No joy.
I ran the offending query overnight and it took 50 minutes. Previously =it took 30 seconds.
Clearly, there's something fundamental that I've not done.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elXzkpAlDHA.1764@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
--=_NextPart_000_00C0_01C3948D.9B62D130
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK, re-reading it, I see that the OS =and hardware have changed but not SQL Server. I'd be interested in the query =plan it generates, as well as the statistics IO. Also, there may be a disk =issue here. Have you looked at avg disk queue length? What is your =disk configuration?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh" wrote in message news:Owo##xIlDHA.392@.T=K2MSFTNGP11.phx.gbl...
Thanks for that, Tom. Just tried =it. No joy.
I ran the offending query overnight and =it took 50 minutes. Previously it took 30 seconds.
Clearly, there's something fundamental =that I've not done.
"Tom Moreau" = wrote in message news:elXzkpAlDHA.1764=@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:

sp_MSforeachtable 'update =statistics ? with fullscan'

---

--=_NextPart_000_00C0_01C3948D.9B62D130--

Performance decrease after OS upgrade

We were running a 4 processor server with 8 GB RAM and 800GB hard disk
space on Windows 2000 server and SQL Server 2000.
Recently we have upgraded the server to 8 processor and added another
800GB. The OS was upgradded to Windows 2003 enterprise edition.
All the processor are 2.5GHz Xeon preocessors.

After upgradation the performance of the server has gone down from what
it was giving before the upgrade.

It seems that multiprocessing is not ocurring.

The model of the server is HP DL740

The OS is installed in a built in array(5i controller) of the server
and the SQL server is installed in a external array(6400 controller).

I will really aprecite if anyone can give any clue to improve the
performance.

Thanks in advance.

Taw.Really need more details... One thought, when you added the 800 GB of
disk space -- I assumed you added a new array? How was it configured?
i.e. Was the existing array named drive E and then the new array named
drive F? Or did you span it into one giant 1.6T array?|||Before the upgrade the OS(Windows200) and the SQLServer was both in the
external array. OS was in one logical drive (C:) and Sqlserver in
another logical drive (D:). The built in array was not used.

For upgrading the system we have added 2X36.4 GB harddisk in the built
in array and clean installed the OS(Windows 2003). Now there is one
1.6TB (14X146 GB) external array,RAID 5. The external array is the
logical drive D: and the OS(RAID1) is the logical drive C:|||Taw,

From the limited information provided, your biggest performance hit here is
the use of RAID 5. RAID1 is much faster, especially for the transaction
log. If we assume that the configuration is generally the same just more
processors and more spindles, I would look into pulling back your max degree
of parallelism.

Also is SQL completely installed on the external array? The default install
is to the C: drive and this may be where tempdb is located.

<tawfiq.choudhury@.grameenphone.com> wrote in message
news:1108442363.222496.139900@.g14g2000cwa.googlegr oups.com...
> Before the upgrade the OS(Windows200) and the SQLServer was both in the
> external array. OS was in one logical drive (C:) and Sqlserver in
> another logical drive (D:). The built in array was not used.
> For upgrading the system we have added 2X36.4 GB harddisk in the built
> in array and clean installed the OS(Windows 2003). Now there is one
> 1.6TB (14X146 GB) external array,RAID 5. The external array is the
> logical drive D: and the OS(RAID1) is the logical drive C:|||My only guess is that you have a "HP Modular Smart Array 30". I
remember buried somewhere in it's documentation, the optimal number of
disks per logical drive is 8. But the array is top-of-the-line and
having 14 disks in one logical drive shouldn't impact it that much.
You can try playing with the Parallellism Query Plan Threshold setting.
Otherwise, sorry, I have no other ideas.|||Yes SQL is installed completely in the external array.

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi
"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegr oups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:

>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:

>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

Performance between Server and PC

Hi All,
Recently we have an application running for 9 hours+ on an HP DL760 G2 with
8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
tested with the same database and application on a normal pc which only have
2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
took about 5 hours to complete. Both server and PC is using Windows 2003
Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
According to the client, their normal operation is working fine. The issue
only happen on that application. But it is weird that the same application
running on normal PC is faster than the server. We just reformat that server,
but the result still took 9hours + to complete.
does anyone know what happen? I have checked all the counter, everything
seem works fine. But I found that the process is pretty fast for 1st 4 hours
then the performance will start degrade.
I also enabled AWE on that server with setting the max memory to 9.6GB RAM
(80% of 12GB). did I leave out something?
I have also tested on HP ML530 G2, Dual Processors and 2GB RAM with RAID 5
(3 HDD 10K RPM). The application still take about 9+ hours to complete.
Any advice is much appreciated. Thanks.
--
Regards,
VenedictVenedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 with
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only have
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.

Performance between Server and PC

Venedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 wit
h
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only ha
ve
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it onl
y
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.Hi All,
Recently we have an application running for 9 hours+ on an HP DL760 G2 with
8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
tested with the same database and application on a normal pc which only have
2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
took about 5 hours to complete. Both server and PC is using Windows 2003
Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
According to the client, their normal operation is working fine. The issue
only happen on that application. But it is weird that the same application
running on normal PC is faster than the server. We just reformat that server
,
but the result still took 9hours + to complete.
does anyone know what happen? I have checked all the counter, everything
seem works fine. But I found that the process is pretty fast for 1st 4 hours
then the performance will start degrade.
I also enabled AWE on that server with setting the max memory to 9.6GB RAM
(80% of 12GB). did I leave out something?
I have also tested on HP ML530 G2, Dual Processors and 2GB RAM with RAID 5
(3 HDD 10K RPM). The application still take about 9+ hours to complete.
Any advice is much appreciated. Thanks.
--
Regards,
Venedict|||Venedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 wit
h
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only ha
ve
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it onl
y
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.

Performance and SQL 2005 - need some assistance

Hi,
I'm trying to decide if our client's SQL 2005 server specs are
adequate.
SERVER SPECS:
---
It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
2gig).
Duel xeons (3 gig cpus i think from memory)
6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
The database itself is around 20gig.
The performance numbers below were taken over a 20 minute period where
about 20 people were using our system. The SQL server is dedicated to
our system.
---
The main question the client are asking is if it needs more memory to
improve performance.
I'm pretty new to sql server 2005 performance analysis, so any feedback
would be great.
Here is some performance numbers related to Memory:
Available MBytes free = 900 Mb (average)
Page Faults/sec = 1800 (average)
Pages/sec = 11 (average) / min 0, max 1255 (there was
3 main spikes here)
PageFile usage = 9%
Avg Private Bytes = 1.65 gig (sqlserver)
Working set sqlserver = 1.65 gig
Buffer cache hit ratio = 99%
Checkpoint pages/sec = 0
Lazy write/sec = 16 (max 108)
Page life expectancy = 251 (min 11, max 1200)
Target server memory = 1.61 gig
Total server memory = 1.53 gig
So I am a bit confused here... the available megabytes seems ok (900Mb)
but it seems to be using the page file quite a bit (and thus
contributing to I/O)? Why would this happen?
There are severe spikes (users could be running big queries) but
overall what do you guys think?
I'll also mention the disk I/O. This seems to be an issue from what I
can see.
The database is on d:, which had:
% Disk Time = 744 (min 0, max 13954)
Av. Disk Queue Length = 7 (min 0, max 140)
Avg. Disk sec/Read = 0
Avg Disk sec/Write = 0
The log files are on e:
I am thinking about splitting the page file so it is on c:\ and e:\
(currently only c.
The tempdb database is also on c:
We seem to be having quite a bit of page splits/sec (which from what I
have read can contribute to I/O):
Page Splits/sec = 355 (min 0, max 1890)
Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
try a fill factor of say 80?
Any suggestions on the above numbers?
(I can also provide other performance stats if required (such as locks
etc), providing I have them)
Thanks for your time!
Daviddavconts@.gmail.com wrote:
> Hi,
> I'm trying to decide if our client's SQL 2005 server specs are
> adequate.
> SERVER SPECS:
> ---
> It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
> 2gig).
> Duel xeons (3 gig cpus i think from memory)
> 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
> The database itself is around 20gig.
> The performance numbers below were taken over a 20 minute period where
> about 20 people were using our system. The SQL server is dedicated to
> our system.
> ---
> The main question the client are asking is if it needs more memory to
> improve performance.
> I'm pretty new to sql server 2005 performance analysis, so any feedback
> would be great.
> Here is some performance numbers related to Memory:
> Available MBytes free = 900 Mb (average)
> Page Faults/sec = 1800 (average)
> Pages/sec = 11 (average) / min 0, max 1255 (there was
> 3 main spikes here)
> PageFile usage = 9%
> Avg Private Bytes = 1.65 gig (sqlserver)
> Working set sqlserver = 1.65 gig
> Buffer cache hit ratio = 99%
> Checkpoint pages/sec = 0
> Lazy write/sec = 16 (max 108)
> Page life expectancy = 251 (min 11, max 1200)
> Target server memory = 1.61 gig
> Total server memory = 1.53 gig
> So I am a bit confused here... the available megabytes seems ok (900Mb)
> but it seems to be using the page file quite a bit (and thus
> contributing to I/O)? Why would this happen?
> There are severe spikes (users could be running big queries) but
> overall what do you guys think?
> I'll also mention the disk I/O. This seems to be an issue from what I
> can see.
> The database is on d:, which had:
> % Disk Time = 744 (min 0, max 13954)
> Av. Disk Queue Length = 7 (min 0, max 140)
> Avg. Disk sec/Read = 0
> Avg Disk sec/Write = 0
> The log files are on e:
> I am thinking about splitting the page file so it is on c:\ and e:\
> (currently only c.
> The tempdb database is also on c:
> We seem to be having quite a bit of page splits/sec (which from what I
> have read can contribute to I/O):
> Page Splits/sec = 355 (min 0, max 1890)
> Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
> try a fill factor of say 80?
> Any suggestions on the above numbers?
> (I can also provide other performance stats if required (such as locks
> etc), providing I have them)
> Thanks for your time!
> David
>
What does the Full Scans/sec (under SQL Server:Access Methods) counter
show? As far as fill factor, that depends on how the table is used. A
table that is fairly static should have a high fill factor, while a
table that is frequently inserted into should have a lower fill factor.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Unfortunately I didn't run that counter when doing the benchmarking
(after analysing the results I wish I did though!!)
I can run the counter now, but the db is not being used much at the
moment (some testing by a few people)
Our application hasn't gone "live" yet, but we are expecting quite a
bit of usage, so we want to try and anticipate/sort out any performance
issues before then.|||David,
You haven't told us anything about what you were doing during these time
period being monitored. How many transactions and of what type occurred
during this time? How busy were the CPU's? And most importantly what king of
performance issues are you having? As for disks you can move the files
around all you want onto the different logical drives but if they are all on
the same physical drive array it won't matter one bit. If you are doing a
lot of writes or tempdb activity you may want to think about splitting up
that single raid into several and separating the log files (including
tempdb) from the rest of the files. These might be worth having a look at:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/downl...gWithTempDB.doc
Physical Database Storage:
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152490147.388959.285070@.m73g2000cwd.googlegroups.com...
> Hi,
> I'm trying to decide if our client's SQL 2005 server specs are
> adequate.
> SERVER SPECS:
> ---
> It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
> 2gig).
> Duel xeons (3 gig cpus i think from memory)
> 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
> The database itself is around 20gig.
> The performance numbers below were taken over a 20 minute period where
> about 20 people were using our system. The SQL server is dedicated to
> our system.
> ---
> The main question the client are asking is if it needs more memory to
> improve performance.
> I'm pretty new to sql server 2005 performance analysis, so any feedback
> would be great.
> Here is some performance numbers related to Memory:
> Available MBytes free = 900 Mb (average)
> Page Faults/sec = 1800 (average)
> Pages/sec = 11 (average) / min 0, max 1255 (there was
> 3 main spikes here)
> PageFile usage = 9%
> Avg Private Bytes = 1.65 gig (sqlserver)
> Working set sqlserver = 1.65 gig
> Buffer cache hit ratio = 99%
> Checkpoint pages/sec = 0
> Lazy write/sec = 16 (max 108)
> Page life expectancy = 251 (min 11, max 1200)
> Target server memory = 1.61 gig
> Total server memory = 1.53 gig
> So I am a bit confused here... the available megabytes seems ok (900Mb)
> but it seems to be using the page file quite a bit (and thus
> contributing to I/O)? Why would this happen?
> There are severe spikes (users could be running big queries) but
> overall what do you guys think?
> I'll also mention the disk I/O. This seems to be an issue from what I
> can see.
> The database is on d:, which had:
> % Disk Time = 744 (min 0, max 13954)
> Av. Disk Queue Length = 7 (min 0, max 140)
> Avg. Disk sec/Read = 0
> Avg Disk sec/Write = 0
> The log files are on e:
> I am thinking about splitting the page file so it is on c:\ and e:\
> (currently only c.
> The tempdb database is also on c:
> We seem to be having quite a bit of page splits/sec (which from what I
> have read can contribute to I/O):
> Page Splits/sec = 355 (min 0, max 1890)
> Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
> try a fill factor of say 80?
> Any suggestions on the above numbers?
> (I can also provide other performance stats if required (such as locks
> etc), providing I have them)
> Thanks for your time!
> David
>|||Thanks Andrew, I'll try and clarify.
Here is a bit more info.
Our application is a medical software application - think of it as a
radiology management system where a patients reports, bookings,
accounting etc are stored in our system.
So the queries do all sorts of lookups for patients, reports etc.
In the benchmark, we had about 20 people hit the app (and thus SQL 2005
indirectly) running all sorts of scenarios.
Our main concern was general slowness in the application. There were
points were it performs ok, whilst other times it seemed to hang/become
very slow (there are a few out of control queries submitted by users, I
have captured all the "long" queries for more analysis). We have the
same application running at another hospital, which have 30 users on at
one time and they don't experience as much slowness. Their db is
smaller though (10gig vs 20gig). Hence we are investigating how to get
their sql server working as best it can.
Here is some of the workload stats:
% Processor Time (TOTAL) = 59 (average) / (min 1, max 100)
Batch Requests/sec = 224 (average) / (min 0, max 1363)
SQL Compilations/sec = 5 (average) / (min 0, max 44)
SQL Re-Compilations/sec = 0
The CPU usage seemed ok, although there was occasional spikes to 90+
Locks info:
Average Latch Wait Time (ms) = 5 (average) / (min 0, max 57)
Latch Waits/sec = 110 (average) / (min 0, max 533)
Total Latch Wait Time (ms) = 227 (average) / (min 0, max 2348)
Average Wait Time (ms) = 19 (average) / (min 0, max 1813)
Lock Requests/sec = 102986 (average) / (min 0,
max 609802)
Lock Timeouts/sec = 0
Lock Wait Time (ms) = 4 (average) / (min 0, max
372)
Lock Waits/sec = 0
Tempdb info (currently 1 file, with initial size of 10gb):
Log Bytes Flushed/sec = 41 (min 0, max 12287)
Log File(S) size (KB) = 2047992 (min 2047992, max
2047992)
Log File(s) used size (KB) = 1385 (min 1358, max 1414)
Log Flush Waits/sec = 0
Free space in tempdb (KB) = 9600781
Version Cleanup rate (KB/s) = 0
Version Generation rate (KB/s) = 0
Here are some other counters:
Workfiles Created/sec = 6 (min 0, max 50)
Worktables Created/sec = 4 (min 0, max 28)
Worktables from Cache ratio = 28 (min 8, max 41)
Temp Tables Creation Rate = 0
Temp Tables For Destruction = 0
I've also been reading about the /3GB boot.ini switch. Could enabling
this result in SQL Server using more physical memory rather than
paging?
I'm still confused as to why there seems to be a lot of paging when
there is 900mb available? Or should I get the site to bump the memory
up to 4gb and then use the /3GB switch.
One last thing. Andrew, you mention a good point:
"As for disks you can move the files around all you want onto the
different logical drives but if they are all on the same physical drive
array it won't matter one bit."
Is there a way to control this (I personally don't have experience
managing raid disks so sorry if this is a stupid question)? I'll check
the links you mentioned below.
Thanks all for your input so far!
David
Andrew J. Kelly wrote:[vbcol=seagreen]
> David,
> You haven't told us anything about what you were doing during these time
> period being monitored. How many transactions and of what type occurred
> during this time? How busy were the CPU's? And most importantly what king
of
> performance issues are you having? As for disks you can move the files
> around all you want onto the different logical drives but if they are all
on
> the same physical drive array it won't matter one bit. If you are doing a
> lot of writes or tempdb activity you may want to think about splitting up
> that single raid into several and separating the log files (including
> tempdb) from the rest of the files. These might be worth having a look at:
>
> http://www.sql-server-performance.c...nce_audit10.asp
> Performance Audit
> http://www.microsoft.com/technet/pr...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=224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
> http://sqldev.net/misc/WaitTypes.htm Wait Types
> Tempdb in 2005:
> http://download.microsoft.com/downl...gWithTempDB.doc
> Physical Database Storage:
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message
> news:1152490147.388959.285070@.m73g2000cwd.googlegroups.com...|||David,
Thanks for the extra info. If it is a single physical array then any
activity on one logical drive will potentially affect the other logical
drives as well. This is because it is a single unit in terms of disks and
the number of heads. The only way to avoid that is to use separate physical
drives. Sometimes adding more drives to a raid 10 will help since it spreads
the load more. But logging and tempdb activity and data access are always
counter productive to each other. How much depends on the volume and drive
configurations. My guess is that you have a lot of untuned queries or tables
that are lacking proper indexes. When you run profiler don't just look for
the queries that take the longest time but pay attention to the number of
reads as well. If you have a lot of queries with a lot of reads you will
most likely have to go to disk to satisfy some of them. That will lead to
increased blocking etc. For only 30 users your batch requests seem sort of
high for an app such as this. It sounds like it may be a bit chatty and
could use some optimization as well. The Pages per second is the one you
really want to pay attention to more than the pagefaults since pagefaults
include a lot of soft faults as well. In your case it does seem to be a
little higher than normal if the conditions are as you say but not that
excessive that I would put it top of the priority list. I would find and
tune the poorly performing queries first. And as to adding more ram. What
edition of OS and SQL Server are you running? If it is Standard Edition of
SQL you can only use 2GB anyway so adding an additional GB will not help.
One last thing. Are you sure there are no other apps or services running on
that machine?
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152498395.129036.45440@.35g2000cwc.googlegroups.com...
> Thanks Andrew, I'll try and clarify.
> Here is a bit more info.
> Our application is a medical software application - think of it as a
> radiology management system where a patients reports, bookings,
> accounting etc are stored in our system.
> So the queries do all sorts of lookups for patients, reports etc.
> In the benchmark, we had about 20 people hit the app (and thus SQL 2005
> indirectly) running all sorts of scenarios.
> Our main concern was general slowness in the application. There were
> points were it performs ok, whilst other times it seemed to hang/become
> very slow (there are a few out of control queries submitted by users, I
> have captured all the "long" queries for more analysis). We have the
> same application running at another hospital, which have 30 users on at
> one time and they don't experience as much slowness. Their db is
> smaller though (10gig vs 20gig). Hence we are investigating how to get
> their sql server working as best it can.
> Here is some of the workload stats:
> % Processor Time (TOTAL) = 59 (average) / (min 1, max 100)
> Batch Requests/sec = 224 (average) / (min 0, max 1363)
> SQL Compilations/sec = 5 (average) / (min 0, max 44)
> SQL Re-Compilations/sec = 0
> The CPU usage seemed ok, although there was occasional spikes to 90+
> Locks info:
> Average Latch Wait Time (ms) = 5 (average) / (min 0, max 57)
> Latch Waits/sec = 110 (average) / (min 0, max 533)
> Total Latch Wait Time (ms) = 227 (average) / (min 0, max 2348)
> Average Wait Time (ms) = 19 (average) / (min 0, max 1813)
> Lock Requests/sec = 102986 (average) / (min 0,
> max 609802)
> Lock Timeouts/sec = 0
> Lock Wait Time (ms) = 4 (average) / (min 0, max
> 372)
> Lock Waits/sec = 0
> Tempdb info (currently 1 file, with initial size of 10gb):
> Log Bytes Flushed/sec = 41 (min 0, max 12287)
> Log File(S) size (KB) = 2047992 (min 2047992, max
> 2047992)
> Log File(s) used size (KB) = 1385 (min 1358, max 1414)
> Log Flush Waits/sec = 0
> Free space in tempdb (KB) = 9600781
> Version Cleanup rate (KB/s) = 0
> Version Generation rate (KB/s) = 0
> Here are some other counters:
> Workfiles Created/sec = 6 (min 0, max 50)
> Worktables Created/sec = 4 (min 0, max 28)
> Worktables from Cache ratio = 28 (min 8, max 41)
> Temp Tables Creation Rate = 0
> Temp Tables For Destruction = 0
> --
> I've also been reading about the /3GB boot.ini switch. Could enabling
> this result in SQL Server using more physical memory rather than
> paging?
> I'm still confused as to why there seems to be a lot of paging when
> there is 900mb available? Or should I get the site to bump the memory
> up to 4gb and then use the /3GB switch.
> One last thing. Andrew, you mention a good point:
> "As for disks you can move the files around all you want onto the
> different logical drives but if they are all on the same physical drive
> array it won't matter one bit."
> Is there a way to control this (I personally don't have experience
> managing raid disks so sorry if this is a stupid question)? I'll check
> the links you mentioned below.
> Thanks all for your input so far!
> David
>
> Andrew J. Kelly wrote:
>|||davconts@.gmail.com wrote:
> Unfortunately I didn't run that counter when doing the benchmarking
> (after analysing the results I wish I did though!!)
> I can run the counter now, but the db is not being used much at the
> moment (some testing by a few people)
> Our application hasn't gone "live" yet, but we are expecting quite a
> bit of usage, so we want to try and anticipate/sort out any performance
> issues before then.
>
I'm not sure memory is necessarily your problem. True, more would be
better, but generally poor performance is due to inefficient coding,
lack of proper indexes, basic things like that. SQL does a pretty good
job of managing its resources, but really falls down when given bad code.
If I were in your shoes, I would capture some trace logs while your
system is under load, and identify those processes/queries that generate
the most work. Focus on disk reads, as high disk reads are a good
indicator of table or index scans, a prime opportunity for improvement.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks all for your responses so far... greatly appreciated.
Yes I do think we have some work to do in optimising queries and
indexes.. I captured some traces of queries that took more than 2
seconds (some took 3 mins !!!) for the programmers to start looking
into. Maybe with the extra size of this customers database, it has
exposed that the queries need tuning (whereas our other site with a
much smaller database doesn't have as much of a problem). I/O appears
to be a problem.
One more question though. Andrew mentioned above that the standard
version of SQL server can only use 2gb ram.
I read this somewhere else, but wanted to verify it for SQL 2005.
The following link says for standard edition of SQL Server 2005, that
the RAM is "Operating system maximum" (which is also for Enterprise
version):
http://www.microsoft.com/sql/prodin...e-features.mspx
So it appears that standard version of SQL 2005 does not have a set mem
usage restriction (such as SQL 2005 express)?
Thanks!|||I am sorry. I was thinking SQL 2000. Yes SQL 2005 Standard edition can use
up to the OS max. That was one of the benefits of upgrading if you had Std
edition.
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152571874.284923.92510@.35g2000cwc.googlegroups.com...
> Thanks all for your responses so far... greatly appreciated.
> Yes I do think we have some work to do in optimising queries and
> indexes.. I captured some traces of queries that took more than 2
> seconds (some took 3 mins !!!) for the programmers to start looking
> into. Maybe with the extra size of this customers database, it has
> exposed that the queries need tuning (whereas our other site with a
> much smaller database doesn't have as much of a problem). I/O appears
> to be a problem.
> One more question though. Andrew mentioned above that the standard
> version of SQL server can only use 2gb ram.
> I read this somewhere else, but wanted to verify it for SQL 2005.
> The following link says for standard edition of SQL Server 2005, that
> the RAM is "Operating system maximum" (which is also for Enterprise
> version):
> http://www.microsoft.com/sql/prodin...e-features.mspx
> So it appears that standard version of SQL 2005 does not have a set mem
> usage restriction (such as SQL 2005 express)?
> Thanks!
>|||No worries
Glad they decided to lift that memory restriction for 2005 standard!
My boss was asking me about the page splits/sec value.
(Page Splits/sec = 355 (average) / (min 0, max 1890)
He believes that the vast majority of the sql work done by application
is reading (rather than inserting). Examples of inserts our app
performs might be user audit trails, and adding reports etc.
Now when you are reading (ie running a select query), you would not be
adding to the index yeah? And so you wouldn't be splitting pages..
right? So why would the page splits/sec be elevated?
Or have I got my understanding of page splits incorrect?
Andrew J. Kelly wrote:
> I am sorry. I was thinking SQL 2000. Yes SQL 2005 Standard edition can use
> up to the OS max. That was one of the benefits of upgrading if you had Std
> edition.
> --
> Andrew J. Kelly SQL MVP

Performance and SQL 2005 - need some assistance

Hi,
I'm trying to decide if our client's SQL 2005 server specs are
adequate.
SERVER SPECS:
---
It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
2gig).
Duel xeons (3 gig cpus i think from memory)
6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
The database itself is around 20gig.
The performance numbers below were taken over a 20 minute period where
about 20 people were using our system. The SQL server is dedicated to
our system.
---
The main question the client are asking is if it needs more memory to
improve performance.
I'm pretty new to sql server 2005 performance analysis, so any feedback
would be great.
Here is some performance numbers related to Memory:
Available MBytes free = 900 Mb (average)
Page Faults/sec = 1800 (average)
Pages/sec = 11 (average) / min 0, max 1255 (there was
3 main spikes here)
PageFile usage = 9%
Avg Private Bytes = 1.65 gig (sqlserver)
Working set sqlserver = 1.65 gig
Buffer cache hit ratio = 99%
Checkpoint pages/sec = 0
Lazy write/sec = 16 (max 108)
Page life expectancy = 251 (min 11, max 1200)
Target server memory = 1.61 gig
Total server memory = 1.53 gig
So I am a bit confused here... the available megabytes seems ok (900Mb)
but it seems to be using the page file quite a bit (and thus
contributing to I/O)? Why would this happen?
There are severe spikes (users could be running big queries) but
overall what do you guys think?
I'll also mention the disk I/O. This seems to be an issue from what I
can see.
The database is on d:, which had:
% Disk Time = 744 (min 0, max 13954)
Av. Disk Queue Length = 7 (min 0, max 140)
Avg. Disk sec/Read = 0
Avg Disk sec/Write = 0
The log files are on e:
I am thinking about splitting the page file so it is on c:\ and e:\
(currently only c:).
The tempdb database is also on c:
We seem to be having quite a bit of page splits/sec (which from what I
have read can contribute to I/O):
Page Splits/sec = 355 (min 0, max 1890)
Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
try a fill factor of say 80?
Any suggestions on the above numbers?
(I can also provide other performance stats if required (such as locks
etc), providing I have them)
Thanks for your time!
Daviddavconts@.gmail.com wrote:
> Hi,
> I'm trying to decide if our client's SQL 2005 server specs are
> adequate.
> SERVER SPECS:
> ---
> It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
> 2gig).
> Duel xeons (3 gig cpus i think from memory)
> 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
> The database itself is around 20gig.
> The performance numbers below were taken over a 20 minute period where
> about 20 people were using our system. The SQL server is dedicated to
> our system.
> ---
> The main question the client are asking is if it needs more memory to
> improve performance.
> I'm pretty new to sql server 2005 performance analysis, so any feedback
> would be great.
> Here is some performance numbers related to Memory:
> Available MBytes free = 900 Mb (average)
> Page Faults/sec = 1800 (average)
> Pages/sec = 11 (average) / min 0, max 1255 (there was
> 3 main spikes here)
> PageFile usage = 9%
> Avg Private Bytes = 1.65 gig (sqlserver)
> Working set sqlserver = 1.65 gig
> Buffer cache hit ratio = 99%
> Checkpoint pages/sec = 0
> Lazy write/sec = 16 (max 108)
> Page life expectancy = 251 (min 11, max 1200)
> Target server memory = 1.61 gig
> Total server memory = 1.53 gig
> So I am a bit confused here... the available megabytes seems ok (900Mb)
> but it seems to be using the page file quite a bit (and thus
> contributing to I/O)? Why would this happen?
> There are severe spikes (users could be running big queries) but
> overall what do you guys think?
> I'll also mention the disk I/O. This seems to be an issue from what I
> can see.
> The database is on d:, which had:
> % Disk Time = 744 (min 0, max 13954)
> Av. Disk Queue Length = 7 (min 0, max 140)
> Avg. Disk sec/Read = 0
> Avg Disk sec/Write = 0
> The log files are on e:
> I am thinking about splitting the page file so it is on c:\ and e:\
> (currently only c:).
> The tempdb database is also on c:
> We seem to be having quite a bit of page splits/sec (which from what I
> have read can contribute to I/O):
> Page Splits/sec = 355 (min 0, max 1890)
> Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
> try a fill factor of say 80?
> Any suggestions on the above numbers?
> (I can also provide other performance stats if required (such as locks
> etc), providing I have them)
> Thanks for your time!
> David
>
What does the Full Scans/sec (under SQL Server:Access Methods) counter
show? As far as fill factor, that depends on how the table is used. A
table that is fairly static should have a high fill factor, while a
table that is frequently inserted into should have a lower fill factor.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Unfortunately I didn't run that counter when doing the benchmarking
(after analysing the results I wish I did though!!)
I can run the counter now, but the db is not being used much at the
moment (some testing by a few people)
Our application hasn't gone "live" yet, but we are expecting quite a
bit of usage, so we want to try and anticipate/sort out any performance
issues before then.|||David,
You haven't told us anything about what you were doing during these time
period being monitored. How many transactions and of what type occurred
during this time? How busy were the CPU's? And most importantly what king of
performance issues are you having? As for disks you can move the files
around all you want onto the different logical drives but if they are all on
the same physical drive array it won't matter one bit. If you are doing a
lot of writes or tempdb activity you may want to think about splitting up
that single raid into several and separating the log files (including
tempdb) from the rest of the files. These might be worth having a look at:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
Physical Database Storage:
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152490147.388959.285070@.m73g2000cwd.googlegroups.com...
> Hi,
> I'm trying to decide if our client's SQL 2005 server specs are
> adequate.
> SERVER SPECS:
> ---
> It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
> 2gig).
> Duel xeons (3 gig cpus i think from memory)
> 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
> The database itself is around 20gig.
> The performance numbers below were taken over a 20 minute period where
> about 20 people were using our system. The SQL server is dedicated to
> our system.
> ---
> The main question the client are asking is if it needs more memory to
> improve performance.
> I'm pretty new to sql server 2005 performance analysis, so any feedback
> would be great.
> Here is some performance numbers related to Memory:
> Available MBytes free = 900 Mb (average)
> Page Faults/sec = 1800 (average)
> Pages/sec = 11 (average) / min 0, max 1255 (there was
> 3 main spikes here)
> PageFile usage = 9%
> Avg Private Bytes = 1.65 gig (sqlserver)
> Working set sqlserver = 1.65 gig
> Buffer cache hit ratio = 99%
> Checkpoint pages/sec = 0
> Lazy write/sec = 16 (max 108)
> Page life expectancy = 251 (min 11, max 1200)
> Target server memory = 1.61 gig
> Total server memory = 1.53 gig
> So I am a bit confused here... the available megabytes seems ok (900Mb)
> but it seems to be using the page file quite a bit (and thus
> contributing to I/O)? Why would this happen?
> There are severe spikes (users could be running big queries) but
> overall what do you guys think?
> I'll also mention the disk I/O. This seems to be an issue from what I
> can see.
> The database is on d:, which had:
> % Disk Time = 744 (min 0, max 13954)
> Av. Disk Queue Length = 7 (min 0, max 140)
> Avg. Disk sec/Read = 0
> Avg Disk sec/Write = 0
> The log files are on e:
> I am thinking about splitting the page file so it is on c:\ and e:\
> (currently only c:).
> The tempdb database is also on c:
> We seem to be having quite a bit of page splits/sec (which from what I
> have read can contribute to I/O):
> Page Splits/sec = 355 (min 0, max 1890)
> Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
> try a fill factor of say 80?
> Any suggestions on the above numbers?
> (I can also provide other performance stats if required (such as locks
> etc), providing I have them)
> Thanks for your time!
> David
>|||Thanks Andrew, I'll try and clarify.
Here is a bit more info.
Our application is a medical software application - think of it as a
radiology management system where a patients reports, bookings,
accounting etc are stored in our system.
So the queries do all sorts of lookups for patients, reports etc.
In the benchmark, we had about 20 people hit the app (and thus SQL 2005
indirectly) running all sorts of scenarios.
Our main concern was general slowness in the application. There were
points were it performs ok, whilst other times it seemed to hang/become
very slow (there are a few out of control queries submitted by users, I
have captured all the "long" queries for more analysis). We have the
same application running at another hospital, which have 30 users on at
one time and they don't experience as much slowness. Their db is
smaller though (10gig vs 20gig). Hence we are investigating how to get
their sql server working as best it can.
Here is some of the workload stats:
% Processor Time (TOTAL) = 59 (average) / (min 1, max 100)
Batch Requests/sec = 224 (average) / (min 0, max 1363)
SQL Compilations/sec = 5 (average) / (min 0, max 44)
SQL Re-Compilations/sec = 0
The CPU usage seemed ok, although there was occasional spikes to 90+
Locks info:
Average Latch Wait Time (ms) = 5 (average) / (min 0, max 57)
Latch Waits/sec = 110 (average) / (min 0, max 533)
Total Latch Wait Time (ms) = 227 (average) / (min 0, max 2348)
Average Wait Time (ms) = 19 (average) / (min 0, max 1813)
Lock Requests/sec = 102986 (average) / (min 0,
max 609802)
Lock Timeouts/sec = 0
Lock Wait Time (ms) = 4 (average) / (min 0, max
372)
Lock Waits/sec = 0
Tempdb info (currently 1 file, with initial size of 10gb):
Log Bytes Flushed/sec = 41 (min 0, max 12287)
Log File(S) size (KB) = 2047992 (min 2047992, max
2047992)
Log File(s) used size (KB) = 1385 (min 1358, max 1414)
Log Flush Waits/sec = 0
Free space in tempdb (KB) = 9600781
Version Cleanup rate (KB/s) = 0
Version Generation rate (KB/s) = 0
Here are some other counters:
Workfiles Created/sec = 6 (min 0, max 50)
Worktables Created/sec = 4 (min 0, max 28)
Worktables from Cache ratio = 28 (min 8, max 41)
Temp Tables Creation Rate = 0
Temp Tables For Destruction = 0
--
I've also been reading about the /3GB boot.ini switch. Could enabling
this result in SQL Server using more physical memory rather than
paging?
I'm still confused as to why there seems to be a lot of paging when
there is 900mb available? Or should I get the site to bump the memory
up to 4gb and then use the /3GB switch.
One last thing. Andrew, you mention a good point:
"As for disks you can move the files around all you want onto the
different logical drives but if they are all on the same physical drive
array it won't matter one bit."
Is there a way to control this (I personally don't have experience
managing raid disks so sorry if this is a stupid question)? I'll check
the links you mentioned below.
Thanks all for your input so far!
David
Andrew J. Kelly wrote:
> David,
> You haven't told us anything about what you were doing during these time
> period being monitored. How many transactions and of what type occurred
> during this time? How busy were the CPU's? And most importantly what king of
> performance issues are you having? As for disks you can move the files
> around all you want onto the different logical drives but if they are all on
> the same physical drive array it won't matter one bit. If you are doing a
> lot of writes or tempdb activity you may want to think about splitting up
> that single raid into several and separating the log files (including
> tempdb) from the rest of the files. These might be worth having a look at:
>
> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> Performance Audit
> http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
> 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=224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
> Disk Monitoring
> http://sqldev.net/misc/WaitTypes.htm Wait Types
> Tempdb in 2005:
> http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
> Physical Database Storage:
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message
> news:1152490147.388959.285070@.m73g2000cwd.googlegroups.com...
> > Hi,
> >
> > I'm trying to decide if our client's SQL 2005 server specs are
> > adequate.
> >
> > SERVER SPECS:
> > ---
> > It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
> > 2gig).
> > Duel xeons (3 gig cpus i think from memory)
> > 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
> >
> > The database itself is around 20gig.
> >
> > The performance numbers below were taken over a 20 minute period where
> > about 20 people were using our system. The SQL server is dedicated to
> > our system.
> > ---
> >
> > The main question the client are asking is if it needs more memory to
> > improve performance.
> > I'm pretty new to sql server 2005 performance analysis, so any feedback
> > would be great.
> >
> > Here is some performance numbers related to Memory:
> >
> > Available MBytes free = 900 Mb (average)
> > Page Faults/sec = 1800 (average)
> > Pages/sec = 11 (average) / min 0, max 1255 (there was
> > 3 main spikes here)
> > PageFile usage = 9%
> > Avg Private Bytes = 1.65 gig (sqlserver)
> > Working set sqlserver = 1.65 gig
> > Buffer cache hit ratio = 99%
> > Checkpoint pages/sec = 0
> > Lazy write/sec = 16 (max 108)
> > Page life expectancy = 251 (min 11, max 1200)
> > Target server memory = 1.61 gig
> > Total server memory = 1.53 gig
> >
> > So I am a bit confused here... the available megabytes seems ok (900Mb)
> > but it seems to be using the page file quite a bit (and thus
> > contributing to I/O)? Why would this happen?
> > There are severe spikes (users could be running big queries) but
> > overall what do you guys think?
> >
> > I'll also mention the disk I/O. This seems to be an issue from what I
> > can see.
> > The database is on d:, which had:
> >
> > % Disk Time = 744 (min 0, max 13954)
> > Av. Disk Queue Length = 7 (min 0, max 140)
> > Avg. Disk sec/Read = 0
> > Avg Disk sec/Write = 0
> >
> > The log files are on e:
> >
> > I am thinking about splitting the page file so it is on c:\ and e:\
> > (currently only c:).
> > The tempdb database is also on c:
> >
> > We seem to be having quite a bit of page splits/sec (which from what I
> > have read can contribute to I/O):
> >
> > Page Splits/sec = 355 (min 0, max 1890)
> >
> > Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
> > try a fill factor of say 80?
> >
> > Any suggestions on the above numbers?
> >
> > (I can also provide other performance stats if required (such as locks
> > etc), providing I have them)
> >
> > Thanks for your time!
> >
> > David
> >|||David,
Thanks for the extra info. If it is a single physical array then any
activity on one logical drive will potentially affect the other logical
drives as well. This is because it is a single unit in terms of disks and
the number of heads. The only way to avoid that is to use separate physical
drives. Sometimes adding more drives to a raid 10 will help since it spreads
the load more. But logging and tempdb activity and data access are always
counter productive to each other. How much depends on the volume and drive
configurations. My guess is that you have a lot of untuned queries or tables
that are lacking proper indexes. When you run profiler don't just look for
the queries that take the longest time but pay attention to the number of
reads as well. If you have a lot of queries with a lot of reads you will
most likely have to go to disk to satisfy some of them. That will lead to
increased blocking etc. For only 30 users your batch requests seem sort of
high for an app such as this. It sounds like it may be a bit chatty and
could use some optimization as well. The Pages per second is the one you
really want to pay attention to more than the pagefaults since pagefaults
include a lot of soft faults as well. In your case it does seem to be a
little higher than normal if the conditions are as you say but not that
excessive that I would put it top of the priority list. I would find and
tune the poorly performing queries first. And as to adding more ram. What
edition of OS and SQL Server are you running? If it is Standard Edition of
SQL you can only use 2GB anyway so adding an additional GB will not help.
One last thing. Are you sure there are no other apps or services running on
that machine?
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152498395.129036.45440@.35g2000cwc.googlegroups.com...
> Thanks Andrew, I'll try and clarify.
> Here is a bit more info.
> Our application is a medical software application - think of it as a
> radiology management system where a patients reports, bookings,
> accounting etc are stored in our system.
> So the queries do all sorts of lookups for patients, reports etc.
> In the benchmark, we had about 20 people hit the app (and thus SQL 2005
> indirectly) running all sorts of scenarios.
> Our main concern was general slowness in the application. There were
> points were it performs ok, whilst other times it seemed to hang/become
> very slow (there are a few out of control queries submitted by users, I
> have captured all the "long" queries for more analysis). We have the
> same application running at another hospital, which have 30 users on at
> one time and they don't experience as much slowness. Their db is
> smaller though (10gig vs 20gig). Hence we are investigating how to get
> their sql server working as best it can.
> Here is some of the workload stats:
> % Processor Time (TOTAL) = 59 (average) / (min 1, max 100)
> Batch Requests/sec = 224 (average) / (min 0, max 1363)
> SQL Compilations/sec = 5 (average) / (min 0, max 44)
> SQL Re-Compilations/sec = 0
> The CPU usage seemed ok, although there was occasional spikes to 90+
> Locks info:
> Average Latch Wait Time (ms) = 5 (average) / (min 0, max 57)
> Latch Waits/sec = 110 (average) / (min 0, max 533)
> Total Latch Wait Time (ms) = 227 (average) / (min 0, max 2348)
> Average Wait Time (ms) = 19 (average) / (min 0, max 1813)
> Lock Requests/sec = 102986 (average) / (min 0,
> max 609802)
> Lock Timeouts/sec = 0
> Lock Wait Time (ms) = 4 (average) / (min 0, max
> 372)
> Lock Waits/sec = 0
> Tempdb info (currently 1 file, with initial size of 10gb):
> Log Bytes Flushed/sec = 41 (min 0, max 12287)
> Log File(S) size (KB) = 2047992 (min 2047992, max
> 2047992)
> Log File(s) used size (KB) = 1385 (min 1358, max 1414)
> Log Flush Waits/sec = 0
> Free space in tempdb (KB) = 9600781
> Version Cleanup rate (KB/s) = 0
> Version Generation rate (KB/s) = 0
> Here are some other counters:
> Workfiles Created/sec = 6 (min 0, max 50)
> Worktables Created/sec = 4 (min 0, max 28)
> Worktables from Cache ratio = 28 (min 8, max 41)
> Temp Tables Creation Rate = 0
> Temp Tables For Destruction = 0
> --
> I've also been reading about the /3GB boot.ini switch. Could enabling
> this result in SQL Server using more physical memory rather than
> paging?
> I'm still confused as to why there seems to be a lot of paging when
> there is 900mb available? Or should I get the site to bump the memory
> up to 4gb and then use the /3GB switch.
> One last thing. Andrew, you mention a good point:
> "As for disks you can move the files around all you want onto the
> different logical drives but if they are all on the same physical drive
> array it won't matter one bit."
> Is there a way to control this (I personally don't have experience
> managing raid disks so sorry if this is a stupid question)? I'll check
> the links you mentioned below.
> Thanks all for your input so far!
> David
>
> Andrew J. Kelly wrote:
>> David,
>> You haven't told us anything about what you were doing during these time
>> period being monitored. How many transactions and of what type occurred
>> during this time? How busy were the CPU's? And most importantly what king
>> of
>> performance issues are you having? As for disks you can move the files
>> around all you want onto the different logical drives but if they are all
>> on
>> the same physical drive array it won't matter one bit. If you are doing a
>> lot of writes or tempdb activity you may want to think about splitting up
>> that single raid into several and separating the log files (including
>> tempdb) from the rest of the files. These might be worth having a look
>> at:
>>
>> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
>> Performance Audit
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
>> 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=224587 Troubleshooting App
>> Performance
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
>> Disk Monitoring
>> http://sqldev.net/misc/WaitTypes.htm Wait Types
>> Tempdb in 2005:
>> http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
>> Physical Database Storage:
>> --
>> Andrew J. Kelly SQL MVP
>> <davconts@.gmail.com> wrote in message
>> news:1152490147.388959.285070@.m73g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I'm trying to decide if our client's SQL 2005 server specs are
>> > adequate.
>> >
>> > SERVER SPECS:
>> > ---
>> > It currently has 3 gig of ram, (SQL 2005 setup for min 1.5gig / max
>> > 2gig).
>> > Duel xeons (3 gig cpus i think from memory)
>> > 6 drives in RAID 1 + 0 config (pairs are mirrorred then stripped)
>> >
>> > The database itself is around 20gig.
>> >
>> > The performance numbers below were taken over a 20 minute period where
>> > about 20 people were using our system. The SQL server is dedicated to
>> > our system.
>> > ---
>> >
>> > The main question the client are asking is if it needs more memory to
>> > improve performance.
>> > I'm pretty new to sql server 2005 performance analysis, so any feedback
>> > would be great.
>> >
>> > Here is some performance numbers related to Memory:
>> >
>> > Available MBytes free = 900 Mb (average)
>> > Page Faults/sec = 1800 (average)
>> > Pages/sec = 11 (average) / min 0, max 1255 (there was
>> > 3 main spikes here)
>> > PageFile usage = 9%
>> > Avg Private Bytes = 1.65 gig (sqlserver)
>> > Working set sqlserver = 1.65 gig
>> > Buffer cache hit ratio = 99%
>> > Checkpoint pages/sec = 0
>> > Lazy write/sec = 16 (max 108)
>> > Page life expectancy = 251 (min 11, max 1200)
>> > Target server memory = 1.61 gig
>> > Total server memory = 1.53 gig
>> >
>> > So I am a bit confused here... the available megabytes seems ok (900Mb)
>> > but it seems to be using the page file quite a bit (and thus
>> > contributing to I/O)? Why would this happen?
>> > There are severe spikes (users could be running big queries) but
>> > overall what do you guys think?
>> >
>> > I'll also mention the disk I/O. This seems to be an issue from what I
>> > can see.
>> > The database is on d:, which had:
>> >
>> > % Disk Time = 744 (min 0, max 13954)
>> > Av. Disk Queue Length = 7 (min 0, max 140)
>> > Avg. Disk sec/Read = 0
>> > Avg Disk sec/Write = 0
>> >
>> > The log files are on e:
>> >
>> > I am thinking about splitting the page file so it is on c:\ and e:\
>> > (currently only c:).
>> > The tempdb database is also on c:
>> >
>> > We seem to be having quite a bit of page splits/sec (which from what I
>> > have read can contribute to I/O):
>> >
>> > Page Splits/sec = 355 (min 0, max 1890)
>> >
>> > Our indexes have a fill factor of 95 and a rebuilt nightly. Should I
>> > try a fill factor of say 80?
>> >
>> > Any suggestions on the above numbers?
>> >
>> > (I can also provide other performance stats if required (such as locks
>> > etc), providing I have them)
>> >
>> > Thanks for your time!
>> >
>> > David
>> >
>|||davconts@.gmail.com wrote:
> Unfortunately I didn't run that counter when doing the benchmarking
> (after analysing the results I wish I did though!!)
> I can run the counter now, but the db is not being used much at the
> moment (some testing by a few people)
> Our application hasn't gone "live" yet, but we are expecting quite a
> bit of usage, so we want to try and anticipate/sort out any performance
> issues before then.
>
I'm not sure memory is necessarily your problem. True, more would be
better, but generally poor performance is due to inefficient coding,
lack of proper indexes, basic things like that. SQL does a pretty good
job of managing its resources, but really falls down when given bad code.
If I were in your shoes, I would capture some trace logs while your
system is under load, and identify those processes/queries that generate
the most work. Focus on disk reads, as high disk reads are a good
indicator of table or index scans, a prime opportunity for improvement.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks all for your responses so far... greatly appreciated.
Yes I do think we have some work to do in optimising queries and
indexes.. I captured some traces of queries that took more than 2
seconds (some took 3 mins !!!) for the programmers to start looking
into. Maybe with the extra size of this customers database, it has
exposed that the queries need tuning (whereas our other site with a
much smaller database doesn't have as much of a problem). I/O appears
to be a problem.
One more question though. Andrew mentioned above that the standard
version of SQL server can only use 2gb ram.
I read this somewhere else, but wanted to verify it for SQL 2005.
The following link says for standard edition of SQL Server 2005, that
the RAM is "Operating system maximum" (which is also for Enterprise
version):
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
So it appears that standard version of SQL 2005 does not have a set mem
usage restriction (such as SQL 2005 express)?
Thanks!|||I am sorry. I was thinking SQL 2000. Yes SQL 2005 Standard edition can use
up to the OS max. That was one of the benefits of upgrading if you had Std
edition.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152571874.284923.92510@.35g2000cwc.googlegroups.com...
> Thanks all for your responses so far... greatly appreciated.
> Yes I do think we have some work to do in optimising queries and
> indexes.. I captured some traces of queries that took more than 2
> seconds (some took 3 mins !!!) for the programmers to start looking
> into. Maybe with the extra size of this customers database, it has
> exposed that the queries need tuning (whereas our other site with a
> much smaller database doesn't have as much of a problem). I/O appears
> to be a problem.
> One more question though. Andrew mentioned above that the standard
> version of SQL server can only use 2gb ram.
> I read this somewhere else, but wanted to verify it for SQL 2005.
> The following link says for standard edition of SQL Server 2005, that
> the RAM is "Operating system maximum" (which is also for Enterprise
> version):
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> So it appears that standard version of SQL 2005 does not have a set mem
> usage restriction (such as SQL 2005 express)?
> Thanks!
>|||No worries :)
Glad they decided to lift that memory restriction for 2005 standard!
My boss was asking me about the page splits/sec value.
(Page Splits/sec = 355 (average) / (min 0, max 1890)
He believes that the vast majority of the sql work done by application
is reading (rather than inserting). Examples of inserts our app
performs might be user audit trails, and adding reports etc.
Now when you are reading (ie running a select query), you would not be
adding to the index yeah? And so you wouldn't be splitting pages..
right? So why would the page splits/sec be elevated?
Or have I got my understanding of page splits incorrect?
Andrew J. Kelly wrote:
> I am sorry. I was thinking SQL 2000. Yes SQL 2005 Standard edition can use
> up to the OS max. That was one of the benefits of upgrading if you had Std
> edition.
> --
> Andrew J. Kelly SQL MVP|||That is a fair amount of page splits for an app that is doing mostly reads.
But often times you will find that a poorly tuned app does more writes than
you think should be happening. Is there any chance you have lots of GUID's?
Putting indexes on columns (especially clustered indexes) can cause a lot of
page splits if the fill factors are not correct. A trace should tell pretty
quick how much inserts or updates you are doing. And don't forget about
triggers that may be doing extra work you may not be aware of.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152583191.214872.61670@.p79g2000cwp.googlegroups.com...
> No worries :)
> Glad they decided to lift that memory restriction for 2005 standard!
> My boss was asking me about the page splits/sec value.
> (Page Splits/sec = 355 (average) / (min 0, max 1890)
> He believes that the vast majority of the sql work done by application
> is reading (rather than inserting). Examples of inserts our app
> performs might be user audit trails, and adding reports etc.
> Now when you are reading (ie running a select query), you would not be
> adding to the index yeah? And so you wouldn't be splitting pages..
> right? So why would the page splits/sec be elevated?
> Or have I got my understanding of page splits incorrect?
>
> Andrew J. Kelly wrote:
>> I am sorry. I was thinking SQL 2000. Yes SQL 2005 Standard edition can
>> use
>> up to the OS max. That was one of the benefits of upgrading if you had
>> Std
>> edition.
>> --
>> Andrew J. Kelly SQL MVP
>