Performance question

Hello,
Our main product catalog is approx. 3.1 million rows, with a full-text index
on 3 (varchar) columns. For the past year as our catalog has grown, we have
experienced continuing performance degredation, to the point that we are
looking at biting the bullet and migrating this application to Oracle Text,
which from our initial testing is several orders of magnitude faster (we're
going to stick with SQL Server for everything else). Obviously we'd like to
avoid that due to cost issues. However, this application is very important
and if we've reached the limit of SQL Server then so be it. Our problem is
that when a customer searches our catalog, we sort the search results based
on their sales rank which does not allow us to use the "top_n" parameter of
containstable or freetexttable. For example, say a customer searches our
catalog for a relatively common word that results in around 72,000 results
(takes approx 11 sec on subsequent runs...over 1 min on first run, which is
the most important statistic). If we were to use (say) n=2000 for the top_n
parameter, our best selling products would not be returned from the FTS
engine. We need to return all the results so we can sort them by sales rank
and display them to customers.
The server is running Windows 2003, with 6GB RAM, 16 x 15,000K RPM SCSI
drive in a RAID 10, in a dual opteron configuration the with transaction log
on a seperate RAID volume. SQL is SQL Server 2000, SP4 (I've included the
output of @.@.version below). Perfmon shows that the server isn't sweating at
all during these queries from a disk, memory, or CPU standpoint, so that
leaves SQL as the performance bottleneck. Our most recent population was
around a month ago, so the catalog is relatively up to date.
We've fooled around with increasing the memory available to FTS, but that
did not seem to make a difference. Perhaps we did not do it right since
mssearch.exe is still only showing about 49,000K in memory--but since we
are using AWE this could be distorted.
We're going to make one last gasp at improving the performance here before
dumping SQL Server and moving to Oracle. Help!
John
@.@.Version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Sample query:
SELECT distinct <field list>
FROM containstable(<ft-table>, <ft-field>,'"<common term>"') as ct
JOIN <ft-table> t with (nolock) on t.<PK>=ct.[key] //tables are 100%
readonly except during monthly updates, hence the nolocks
join salesRank sr with (nolock) on sr.<PK>=ct.[key]
order by sr.SalesRank
The approach you take for problems like this is to partition your tables,
perhaps in your case by sales rank. For instance you might want to break
your tables into 10 sub tables. One from 1-10, another from 11-20, etc.
Then limit each results set to 100 and union the results. This might end up
more expensive than what you are currently experiencing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <john36356@.community.nospam> wrote in message
news:ebYiMcaWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hello,
> Our main product catalog is approx. 3.1 million rows, with a full-text
index
> on 3 (varchar) columns. For the past year as our catalog has grown, we
have
> experienced continuing performance degredation, to the point that we are
> looking at biting the bullet and migrating this application to Oracle
Text,
> which from our initial testing is several orders of magnitude faster
(we're
> going to stick with SQL Server for everything else). Obviously we'd like
to
> avoid that due to cost issues. However, this application is very
important
> and if we've reached the limit of SQL Server then so be it. Our problem
is
> that when a customer searches our catalog, we sort the search results
based
> on their sales rank which does not allow us to use the "top_n" parameter
of
> containstable or freetexttable. For example, say a customer searches our
> catalog for a relatively common word that results in around 72,000 results
> (takes approx 11 sec on subsequent runs...over 1 min on first run, which
is
> the most important statistic). If we were to use (say) n=2000 for the
top_n
> parameter, our best selling products would not be returned from the FTS
> engine. We need to return all the results so we can sort them by sales
rank
> and display them to customers.
> The server is running Windows 2003, with 6GB RAM, 16 x 15,000K RPM SCSI
> drive in a RAID 10, in a dual opteron configuration the with transaction
log
> on a seperate RAID volume. SQL is SQL Server 2000, SP4 (I've included the
> output of @.@.version below). Perfmon shows that the server isn't sweating
at
> all during these queries from a disk, memory, or CPU standpoint, so that
> leaves SQL as the performance bottleneck. Our most recent population was
> around a month ago, so the catalog is relatively up to date.
> We've fooled around with increasing the memory available to FTS, but that
> did not seem to make a difference. Perhaps we did not do it right since
> mssearch.exe is still only showing about 49,000K in memory--but since we
> are using AWE this could be distorted.
> We're going to make one last gasp at improving the performance here before
> dumping SQL Server and moving to Oracle. Help!
> John
> @.@.Version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> Sample query:
> SELECT distinct <field list>
> FROM containstable(<ft-table>, <ft-field>,'"<common term>"') as ct
> JOIN <ft-table> t with (nolock) on t.<PK>=ct.[key] //tables are 100%
> readonly except during monthly updates, hence the nolocks
> join salesRank sr with (nolock) on sr.<PK>=ct.[key]
> order by sr.SalesRank
>

Performance Question

Hi

We have a SQL server on Win2k. the physical size of the db is about
40G and the main table has approx 65m rows in it. At the moment the
entire database is on one data file. The entire server including the
OS is on a RAID 0 array with one RAID controller.

My question is would I get any performance benefit if I was to have
more than several data file rather than one big data file, bearing in
mind that there is only one disk controller.

I am mainly interested in read performance rather than write.

I am just starting out with MS SQL

Thanks for any info<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.

My understanding is with SQL 6.0 and I think 7.0 this would make a
difference (as SQL could have multiple file pointers).

This is no longer the case with SQL 2000.

Also, RAID 0 is a disaster waiting to happen.

Remember, if you have a 2 disk RAID 0 set, you've just doubled your chances
of a catastrophic failure.

If you have a 3 disk set, it's 3x, etc.

> I am mainly interested in read performance rather than write.

Well, first move to a "real" RAID solution, 1, 10, 5 or the like.

Separate out files (say OS, data, logs) to separate PHYSICAL arrays (doesn't
help to repartition your existing array into 3 logical disks... you don't
get any more physical heads.)

And make sure you have RAM. Lots of it.
> I am just starting out with MS SQL
> Thanks for any info|||Hi

To add to Gregs advice you may want to get yourself the following book:
http://www.microsoft.com/mspress/books/4944.asp

and this article and other:
http://www.sql-server-performance.c...re_planning.asp

This may also help
http://www.acnc.com/04_01_10.html

John

<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.
> I am mainly interested in read performance rather than write.
> I am just starting out with MS SQL
> Thanks for any info|||Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>To add to Gregs advice you may want to get yourself the following book:
>http://www.microsoft.com/mspress/books/4944.asp
>and this article and other:
>http://www.sql-server-performance.c...re_planning.asp
>This may also help
>http://www.acnc.com/04_01_10.html
>John
><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
>news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
>> Hi
>>
>> We have a SQL server on Win2k. the physical size of the db is about
>> 40G and the main table has approx 65m rows in it. At the moment the
>> entire database is on one data file. The entire server including the
>> OS is on a RAID 0 array with one RAID controller.
>>
>> My question is would I get any performance benefit if I was to have
>> more than several data file rather than one big data file, bearing in
>> mind that there is only one disk controller.
>>
>> I am mainly interested in read performance rather than write.
>>
>> I am just starting out with MS SQL
>>
>> Thanks for any info
>>
>>
>|||Hi

If the controller has multiple channels it will help if you configure a
different sub-systems for OS and logs using new disks. These will share
resources on the controller so if you can add memory to it that will also
help. Adding extra disks to the current array will help spread the disk
usage more, but may not show as much gain as having a new sub-system there
is an example in "Microsoft SQL Server 2000T Performance Tuning Technical
Reference" that shows how adding a disk to an existing array can help.

John

Using multiple filegroups in their own files will help backup and
organisation.
"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
> On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >To add to Gregs advice you may want to get yourself the following book:
> >http://www.microsoft.com/mspress/books/4944.asp
> >and this article and other:
> >http://www.sql-server-performance.c...re_planning.asp
> >This may also help
> >http://www.acnc.com/04_01_10.html
> >John
> ><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
> >news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> >> Hi
> >>
> >> We have a SQL server on Win2k. the physical size of the db is about
> >> 40G and the main table has approx 65m rows in it. At the moment the
> >> entire database is on one data file. The entire server including the
> >> OS is on a RAID 0 array with one RAID controller.
> >>
> >> My question is would I get any performance benefit if I was to have
> >> more than several data file rather than one big data file, bearing in
> >> mind that there is only one disk controller.
> >>
> >> I am mainly interested in read performance rather than write.
> >>
> >> I am just starting out with MS SQL
> >>
> >> Thanks for any info
> >>
> >>
> >|||"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored

Ok, that's good. :-)sql

Performance Problem

we use a SQL Server 2000 (Standard Edition) as Database for our Web-Applicat
ion
accessible for approx. 2200 users in a VPN. Web-Server/Application are
balanced by 2 Servers. The database resides on a 3rd Server.
In peak times we register 400 concurrent open user sessions which might
access the database.
Severs are running 24 hours but working hours are from 8 am to 8 pm.
Our problem is that during the day the Database server performance slows dow
n
more and more. Response time for a complex SQL statement takes 1/2 a second
in
the morning but after midday it often takes up to 50 seconds.
One thing we found out is that 4 GB physical memory are available and
the max server memory setting in SQL Server defaults to this value although
the
standard edition supports only 2 GB of memory.
As servers are administered by our customer themselfes, we are only allowed
to give them some hints. That's the reason why we first have to do a brainst
orming.
- How does the server handle the "wrong" memory settings?
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
- What other reasons for response time increases are possible?
Any statements would be appreciated.
Regards
Karl BondHi Karl,
From your descriptions, I understood that your are suffering performance iss
ues with your SQL
Server for about 400 connections in peak times. Have I understood you? If th
ere is anything I
misunderstood, please feel free to let me know.
First of all, please understand that performance issues are very hard to tro
ubleshooting in the
newsgroup as we need to collect sufficient information, such as performance
counter,
SQLDiag, etc., from your SQL Server. Looking at the nature of this issue, it
would require
intensive troubleshooting which would be done quickly and effectively with d
irect assistance
from a Microsoft Support Professional through Microsoft Product Support Serv
ices. You can
contact Microsoft Product Support directly to discuss additional support opt
ions you may
have available, by contacting us at 1-(800)936-5800 or by choosing one of th
e options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
Secondly, many factors may lead into performance issues, such as network per
formance,
bottle-neck of your product server, etc. I would like to recommand you the f
ollowing KB, which
will show you how to troubleshooting performance issues in SQL Server yourse
lf
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/defaul...kb;en-us;298475
Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/d...n_p
erf_
00mr.asp
I noticed you are using SQL Server 2000, however, the following white paper
is so excellent
that it is still suitable for today's performance tuning, I would like to st
rongly recommand you
reading it throughly if I may
INF: SQL Server 7.0 Performance Tuning Guide (White Paper)
http://support.microsoft.com/defaul...kb;en-us;322883
Thirdly, The following KB will help you how to exceed the limitation of 2 Gi
gabytes memory of
SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/defaul...kb;en-us;274750
INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/defaul...kb;en-us;295443
Fourthly, I would like to show your the difference between SQL Server 2000 s
tandard Edition
and SQL Server 2000 Enterprise Edition. The following document listed all fe
atures between
them
Features by Edition
http://www.microsoft.com/sql/evalua...es/choosing.asp
Moreover, the following documents will help you finding out which edition of
SQL Server is the
most suitale one for you. You could apply your product envirnment with the d
ocument. Hope it
will help you making the right chooice.
Choosing an Edition of SQL Server 2000
http://www.microsoft.com/sql/techin...eskChooseEd.asp
Last but not the least, it is highly appreciated if you could create one thr
ead per question,
which, I believe, will make us focus on one issue and make better troublesho
oting
Thank you for your patience and cooperation. If you have any questions or co
ncerns, don't
hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Karl,
I wanted to post a quick note to see regarding the information I have sent
you. If you would like additional assistance or information regarding this
particular issue, don't hesitate to let us know. We appreciate your
patience and look forward to hearing from you!
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Karl,
Generally Mingqing's reply is very helpful for troubleshooting SQL
performance issues and it is best if the customer can contact Microsoft PSS
if they want better follow-up.
Hereby I would like to answer your specific concerns, so you can provide
some hints to the customer.
- How does the server handle the "wrong" memory settings?
A: I do not understand this question very well. However, SQL Server 2000
Standard Edition does not support more than 2GB memory.
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
A: It is very likely caused by the concurrent access. I can illustrate with
an example. Say your application needs to execute a stored procedure, which
does the following:
1. update table a
2. update table b
When multiple access is concurrently executing, if 2 sessions are executing
the above stored procedure at the same time, then 1 session has to wait for
another session (they cannot update table a at the same time. It will cause
inconsistency issue.). So session 2 will take twice time as usual to
execute the stored procedure. The more concurrent sessions, the longer it
will take.
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
A: There is no difference on this part. However, different editions support
different features. Enterprise Edition can support more than 2GB memory.
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_1cdv.asp
- What other reasons for response time increases are possible?
A: You can refer to the following articles also.
243589.KB.EN-US HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0
or La
http://support.microsoft.com/defaul...KB;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/defaul...KB;EN-US;224453
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
--
| Thread-Topic: Performance Problem
| thread-index: AcRv9lgDP3vhWfdkSqOx0F8Qdi6cJw==
| X-WBNR-Posting-Host: 212.172.117.131
| From: examnotes <karl.bond@.nospam.nospam>
| Subject: Performance Problem
| Date: Thu, 22 Jul 2004 07:15:30 -0700
| Lines: 30
| Message-ID: <E9C21026-6124-4D88-90B8-56940BD72EFE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.157
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352532
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| we use a SQL Server 2000 (Standard Edition) as Database for our
Web-Application
| accessible for approx. 2200 users in a VPN. Web-Server/Application are
| balanced by 2 Servers. The database resides on a 3rd Server.
|
| In peak times we register 400 concurrent open user sessions which might
| access the database.
|
| Severs are running 24 hours but working hours are from 8 am to 8 pm.
|
| Our problem is that during the day the Database server performance slows
down
| more and more. Response time for a complex SQL statement takes 1/2 a
second in
| the morning but after midday it often takes up to 50 seconds.
|
| One thing we found out is that 4 GB physical memory are available and
| the max server memory setting in SQL Server defaults to this value
although the
| standard edition supports only 2 GB of memory.
|
| As servers are administered by our customer themselfes, we are only
allowed
| to give them some hints. That's the reason why we first have to do a
brainstorming.
|
| - How does the server handle the "wrong" memory settings?
| - time spans from .5 to 50 seconds seem to be very large, is this usual?
| - is a concurrent access of 400 sessions a possible explanation?
| - Are there significant differences between Standard and Enterprise
Edition
| regarding Performance, Memory Management etc.?
| - What other reasons for response time increases are possible?
|
| Any statements would be appreciated.
| Regards
| Karl Bond
||||Hi Mingqing,
thanks for your reply. You gave me a lot of useful links and I am still work
ing on it.
Especially, we first started with collecting performance counters.
Maybe you can answer one question from my list:
What ist the best practice for setting Virtual Memory size and the SQL Serve
r
'max/min server memory' ?
Windows 2000 Server is just running SQL Server Standard Edition. We are not
using Fulltext search. RAM is 4GB.
Thanks in advance.
Regards,
Karl Bond|||Hi Karl,
Please understood it's hard to say what is a best practise. The following
documents will show you what might be the most suitable for your SQL
Server.
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/defaul...KB;EN-US;319942
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/library/d...-us/optimsql/od
p_tun_1a_6rn7.asp
I understood that reading documents is really annoying and you want to give
you a excatly answer, such as set max memory 3G and min memory 1G. However,
I cannot do this. I am not familiar with your system environment, I don't
think it will much contribution for your system. If you want to make a
provement on performance, IMO, the most important thing should be upgrade
to SQL Server Enterprise Edition and then configure memory for more than
2GB.
Thanks Bill Cheng, according to his reply below, I noticed that you SQL
Server is standard edition, which will not support 4GB memory. So that I
think you'd better upgrade to SQL Server 2000 Enterprise Edition first. The
following document show the maximun capacity for different version of SQL
Server.
Maximum Capacity Specifications
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Performance Problem

we use a SQL Server 2000 (Standard Edition) as Database for our Web-Application
accessible for approx. 2200 users in a VPN. Web-Server/Application are
balanced by 2 Servers. The database resides on a 3rd Server.
In peak times we register 400 concurrent open user sessions which might
access the database.
Severs are running 24 hours but working hours are from 8 am to 8 pm.
Our problem is that during the day the Database server performance slows down
more and more. Response time for a complex SQL statement takes 1/2 a second in
the morning but after midday it often takes up to 50 seconds.
One thing we found out is that 4 GB physical memory are available and
the max server memory setting in SQL Server defaults to this value although the
standard edition supports only 2 GB of memory.
As servers are administered by our customer themselfes, we are only allowed
to give them some hints. That's the reason why we first have to do a brainstorming.
- How does the server handle the "wrong" memory settings?
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
- What other reasons for response time increases are possible?
Any statements would be appreciated.
Regards
Karl Bond
Hi Karl,
From your descriptions, I understood that your are suffering performance issues with your SQL
Server for about 400 connections in peak times. Have I understood you? If there is anything I
misunderstood, please feel free to let me know.
First of all, please understand that performance issues are very hard to troubleshooting in the
newsgroup as we need to collect sufficient information, such as performance counter,
SQLDiag, etc., from your SQL Server. Looking at the nature of this issue, it would require
intensive troubleshooting which would be done quickly and effectively with direct assistance
from a Microsoft Support Professional through Microsoft Product Support Services. You can
contact Microsoft Product Support directly to discuss additional support options you may
have available, by contacting us at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Secondly, many factors may lead into performance issues, such as network performance,
bottle-neck of your product server, etc. I would like to recommand you the following KB, which
will show you how to troubleshooting performance issues in SQL Server yourself
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default...b;en-us;298475
Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/de...l/ad_mon_perf_
00mr.asp
I noticed you are using SQL Server 2000, however, the following white paper is so excellent
that it is still suitable for today's performance tuning, I would like to strongly recommand you
reading it throughly if I may
INF: SQL Server 7.0 Performance Tuning Guide (White Paper)
http://support.microsoft.com/default...b;en-us;322883
Thirdly, The following KB will help you how to exceed the limitation of 2 Gigabytes memory of
SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/default...b;en-us;274750
INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/default...b;en-us;295443
Fourthly, I would like to show your the difference between SQL Server 2000 standard Edition
and SQL Server 2000 Enterprise Edition. The following document listed all features between
them
Features by Edition
http://www.microsoft.com/sql/evaluat...s/choosing.asp
Moreover, the following documents will help you finding out which edition of SQL Server is the
most suitale one for you. You could apply your product envirnment with the document. Hope it
will help you making the right chooice.
Choosing an Edition of SQL Server 2000
http://www.microsoft.com/sql/techinf...skChooseEd.asp
Last but not the least, it is highly appreciated if you could create one thread per question,
which, I believe, will make us focus on one issue and make better troubleshooting
Thank you for your patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Karl,
I wanted to post a quick note to see regarding the information I have sent
you. If you would like additional assistance or information regarding this
particular issue, don't hesitate to let us know. We appreciate your
patience and look forward to hearing from you!
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Karl,
Generally Mingqing's reply is very helpful for troubleshooting SQL
performance issues and it is best if the customer can contact Microsoft PSS
if they want better follow-up.
Hereby I would like to answer your specific concerns, so you can provide
some hints to the customer.
- How does the server handle the "wrong" memory settings?
A: I do not understand this question very well. However, SQL Server 2000
Standard Edition does not support more than 2GB memory.
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
A: It is very likely caused by the concurrent access. I can illustrate with
an example. Say your application needs to execute a stored procedure, which
does the following:
1. update table a
2. update table b
When multiple access is concurrently executing, if 2 sessions are executing
the above stored procedure at the same time, then 1 session has to wait for
another session (they cannot update table a at the same time. It will cause
inconsistency issue.). So session 2 will take twice time as usual to
execute the stored procedure. The more concurrent sessions, the longer it
will take.
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
A: There is no difference on this part. However, different editions support
different features. Enterprise Edition can support more than 2GB memory.
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_1cdv.asp
- What other reasons for response time increases are possible?
A: You can refer to the following articles also.
243589.KB.EN-US HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0
or La
http://support.microsoft.com/default...B;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/default...B;EN-US;224453
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
| Thread-Topic: Performance Problem
| thread-index: AcRv9lgDP3vhWfdkSqOx0F8Qdi6cJw==
| X-WBNR-Posting-Host: 212.172.117.131
| From: =?Utf-8?B?a2FybCBib25k?= <karl.bond@.nospam.nospam>
| Subject: Performance Problem
| Date: Thu, 22 Jul 2004 07:15:30 -0700
| Lines: 30
| Message-ID: <E9C21026-6124-4D88-90B8-56940BD72EFE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.157
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352532
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| we use a SQL Server 2000 (Standard Edition) as Database for our
Web-Application
| accessible for approx. 2200 users in a VPN. Web-Server/Application are
| balanced by 2 Servers. The database resides on a 3rd Server.
|
| In peak times we register 400 concurrent open user sessions which might
| access the database.
|
| Severs are running 24 hours but working hours are from 8 am to 8 pm.
|
| Our problem is that during the day the Database server performance slows
down
| more and more. Response time for a complex SQL statement takes 1/2 a
second in
| the morning but after midday it often takes up to 50 seconds.
|
| One thing we found out is that 4 GB physical memory are available and
| the max server memory setting in SQL Server defaults to this value
although the
| standard edition supports only 2 GB of memory.
|
| As servers are administered by our customer themselfes, we are only
allowed
| to give them some hints. That's the reason why we first have to do a
brainstorming.
|
| - How does the server handle the "wrong" memory settings?
| - time spans from .5 to 50 seconds seem to be very large, is this usual?
| - is a concurrent access of 400 sessions a possible explanation?
| - Are there significant differences between Standard and Enterprise
Edition
| regarding Performance, Memory Management etc.?
| - What other reasons for response time increases are possible?
|
| Any statements would be appreciated.
| Regards
| Karl Bond
|
|||Hi Mingqing,
thanks for your reply. You gave me a lot of useful links and I am still working on it.
Especially, we first started with collecting performance counters.
Maybe you can answer one question from my list:
What ist the best practice for setting Virtual Memory size and the SQL Server
'max/min server memory' ?
Windows 2000 Server is just running SQL Server Standard Edition. We are not using Fulltext search. RAM is 4GB.
Thanks in advance.
Regards,
Karl Bond
|||Hi Karl,
Please understood it's hard to say what is a best practise. The following
documents will show you what might be the most suitable for your SQL
Server.
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/default...B;EN-US;319942
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/library/de...us/optimsql/od
p_tun_1a_6rn7.asp
I understood that reading documents is really annoying and you want to give
you a excatly answer, such as set max memory 3G and min memory 1G. However,
I cannot do this. I am not familiar with your system environment, I don't
think it will much contribution for your system. If you want to make a
provement on performance, IMO, the most important thing should be upgrade
to SQL Server Enterprise Edition and then configure memory for more than
2GB.
Thanks Bill Cheng, according to his reply below, I noticed that you SQL
Server is standard edition, which will not support 4GB memory. So that I
think you'd better upgrade to SQL Server 2000 Enterprise Edition first. The
following document show the maximun capacity for different version of SQL
Server.
Maximum Capacity Specifications
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Performance of sqlserver drops few times per hour

Approx. 3 times per hour sqlserver is writing data for 8 to 10 seconds.

What it is writing i dont know, but the result of this is that the

duration of executing queries is much larger then normal ( 10 times ).

Normally the query takes up to 400 milliseconds, and during this writing problem, it takes 6 or more seconds.

Sqlserver is not doing backups when this happens.

Does anyone have any idea?Perhaps background checkpoints are causing the writes. You could turn on trace flag 3504 (DBCC TRACEON(3504,-1)) which will cause checkpoint summaries to be written to the errorlog and see if the times are coincident.|||

The first this I always do is run up NT performance monitor to see if the server itslef is doing anything.

Performance graphs I use are all the CPU's and logical disk %read , %write , Avg Disk write queue , Avg Disk read queue. Disk queues should be less than 2 per spindal eg if you have 10 drives then this can peek past 20 but should not remain at that fro any lenght of time.

From there it's into SQL profiler

|||Great 2 answers and they both helped me :)

Thanks people it was the checkpoint. Now i need to find a way to do more checkpoints in an hour.

Anyone an idea?

Thanks|||You can set the checkpoint/recovery interval.

e.g.
--chkpoint every 10 minute
exec sp_configure 'recovery interval', '10'
reconfigure with override
go|||You can set the checkpoint/recovery interval.

e.g.

--chkpoint every 10 minute

exec sp_configure 'recovery interval', '10'

reconfigure with override

go

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple bar charts per report (RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart attached to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
JonYou could make the calls asynchronous (I'm assuming all calls are
synchronous).
"jonny" <jon@.jongianni.com> wrote in message
news:1147337806.062580.126100@.u72g2000cwu.googlegroups.com...
>I have a 'questionnaire analysis' report. The report consists of approx
> 25-30 questions and each question has a bar chart attached to it to
> show the average score out of five for each multiple choice answer
> within a question.
> The report ends up containing 25-30 bar graphs all contained within a
> List control. The report performance is acceptable using URL access but
> is exceptionally slow (over 1 minute) when using SOAP access.
> The performance bottle-neck seems to be the multiple calls to
> RenderStream to render all of the images that get generated when the
> report executes (one for each chart.)
> I was wondering if there was any way, still using SOAP, of avoiding
> using RenderStream or of speeding up performance. Report caching is not
> really an option in my scenario nor is splitting into multiple pages
> (user wants everything on screen at once.).
> Jon
>