CRAXDRT.Application.OpenReport - VERY slow

I have a CR 8.0 report that's been running against MySQL through VB6 for several years now, without ANY problems.

Just a few weeks ago we changed to new servers, and all my reports are running just fine, except one, which also happens to be the biggest (~2.9M). The report is running from a network drive, although I've tried it on a local drive with no real difference in perforamnce. Here's the code I'm using to open the report:

Dim cr As New CRAXDRT.Application
Dim report As CRAXDRT.report

Set report = cr.OpenReport(fName)

Again, nothing special. This has run quite well until now. The query itself that the report generates is quite large, but executes against the MySQL server in around .2 seconds, which isn't nearly the delay I'm seeing.

This call to OpenReport takes close to 20 seconds. I'm sure this has something to do with the server change, but without information, there isn't anything that I can do do fix it...

Any ideas?I forgot to add: The report runs very fast in the Crystal Report IDE.|||Dear ejj,

Have you by any chance found the solution yet ?

I'm experiencing exactly the same problem as you described. We recently migrated our NT4 domain to a WIN2003 domain (with some new servers) and after that some reports have become slow when calling the openreport function.

The only difference is that I'm using CR9 and the report is also opening slow in the CR Developer.

Best regards,
TheDaoka|||Dear ejj,

Have you by any chance found the solution yet ?

I'm experiencing exactly the same problem as you described. We recently migrated our NT4 domain to a WIN2003 domain (with some new servers) and after that some reports have become slow when calling the openreport function.

The only difference is that I'm using CR9 and the report is also opening slow in the CR Developer.

Best regards,
TheDaoka

Well, I fixed it, but never found what the problem was.

Thourgh trial and error I removed stuff from the report until it got faster. There was a subreport in the report that I deleted and the whole thing spead up. I then re-created the SAME subreport with the SAME query and magically everything got faster.

I can't explain it. Same database, same data, same schema, etc. But, it works now. :confused:|||Hi Ejj,

I just found this in the latest hotfixes for CR9:

ADAPT00212276 Patch ID: 35846238
Description:
When a report contains subreports that are imported through UNC paths, the report is slow to open when the network
connection is down.
Crystal Reports checks to see if the UNC pathname exists, before it checks to see if the user wants to re-import the
subreport. This causes an uncecessary delay in opening the report.
New Behavior:
The order of operations when opening a report is reversed: the new algorithm checks the pathname after the user
chooses to re-import.

-------------------

I have some reports with subreports which were imported from a server that no longer exists.

I installed the latest CR dll's and now it's back to normal.

Best regards,
TheDaoka|||TheDaoka -

Thanks for the information! Don't know why I never came across that in my search, but that sounds exactly like the problem I was having.

Glad you found it! Thanks!

CPU Usage Management and SQL

Right now SQL Server accounts for 80% of my CPU Usage
which is being maxed out and causing my sites to run
slow. I fear this may be related to me not properly
closing connections (I am lazy) and me just hosting too
many databases on one server. Regardless, I need insight
on how to manage this and reset CPU Usage from a SQL
perspective. Also, is there a way to see all connections
opened and individually close them? Will that help me
you think? Thanks in advance.if you execute
sp_who
or
sp_who2
from Query Analyzer, you will be shown a listing of the open =connections.
Generally, open connections would not cause CPU usage, but they do use =memory. If there are lots of open (unused) connections then your memory =usage might be higher than it needs to be. ADO should be using =connection pooling, so I would not expect that you lots of extra =connections, but I could be wrong.
The best tool to see what is going on within the database server is =Profiler (within the SQL Server program group). It will show you what =sql commands are being sent to the database.
If you can identify which stored procedures/select statements cause =problems (take a long time to run, use gobs of CPU) you might be able to =re-write them or add indexes so that the system does not have to work as =hard to retrieve the information that the application is asking for.
-- Keith, SQL Server MVP
"ASP Dev" <dmicheli@.cmiti.com> wrote in message =news:030201c34b03$bead2830$a301280a@.phx.gbl...
> Right now SQL Server accounts for 80% of my CPU Usage > which is being maxed out and causing my sites to run > slow. I fear this may be related to me not properly > closing connections (I am lazy) and me just hosting too > many databases on one server. Regardless, I need insight > on how to manage this and reset CPU Usage from a SQL > perspective. Also, is there a way to see all connections > opened and individually close them? Will that help me > you think? Thanks in advance.|||Thank You. I appreciate your help.
>--Original Message--
>if you execute
>sp_who
>or
>sp_who2
>from Query Analyzer, you will be shown a listing of the
open connections.
>Generally, open connections would not cause CPU usage,
but they do use memory. If there are lots of open
(unused) connections then your memory usage might be
higher than it needs to be. ADO should be using
connection pooling, so I would not expect that you lots
of extra connections, but I could be wrong.
>The best tool to see what is going on within the
database server is Profiler (within the SQL Server
program group). It will show you what sql commands are
being sent to the database.
>If you can identify which stored procedures/select
statements cause problems (take a long time to run, use
gobs of CPU) you might be able to re-write them or add
indexes so that the system does not have to work as hard
to retrieve the information that the application is
asking for.
>--
>Keith, SQL Server MVP
>"ASP Dev" <dmicheli@.cmiti.com> wrote in message
news:030201c34b03$bead2830$a301280a@.phx.gbl...
>> Right now SQL Server accounts for 80% of my CPU Usage
>> which is being maxed out and causing my sites to run
>> slow. I fear this may be related to me not properly
>> closing connections (I am lazy) and me just hosting
too
>> many databases on one server. Regardless, I need
insight
>> on how to manage this and reset CPU Usage from a SQL
>> perspective. Also, is there a way to see all
connections
>> opened and individually close them? Will that help me
>> you think? Thanks in advance.
>.
>|||we had a history of CPU usage here. At peak time the usage would stay up to
100% staying there for 10, 15 minutes. Last few weeks it stayed above 95%
for couple hrs. The problem was poor SQL statements and the management
reports. All queries were pure SELECT or UPDATE, no stored procedures used.
During business hrs the big bosses just wanted to see how well the business
so far for the day so they just clicked whenever they want to produce an
up-to-date online report!!!. I've warned the bosses and developers about
this but it was kind of they prefered to upgrade h/w than rewriting the
codes and kept running reports ... whenever they want. After a meeting with
the big bosses they agreed it was a big problem and promised not to run
report during peak hrs. And just next day we saw them running report like
crazy again! Until last month the web site got timed-out then they took it
seriously. Spending 50-100K for both s/w and h/w wasn't an easy thing so
the developres rewrote the main interface for the site using pure stored
procedures. They also locked out the report tool, kept it from runinng b/w
10am and 2pm. Result: it rocked. Using Insight Manager I see the avg. CPU
usage is b/w 10 and 15%. Everyone is happy with it.:-).
"ASP Dev" <dmicheli@.cmiti.com> wrote in message
news:030201c34b03$bead2830$a301280a@.phx.gbl...
> Right now SQL Server accounts for 80% of my CPU Usage
> which is being maxed out and causing my sites to run
> slow. I fear this may be related to me not properly
> closing connections (I am lazy) and me just hosting too
> many databases on one server. Regardless, I need insight
> on how to manage this and reset CPU Usage from a SQL
> perspective. Also, is there a way to see all connections
> opened and individually close them? Will that help me
> you think? Thanks in advance.|||On Tue, 15 Jul 2003 14:19:06 -0700, "Flicker"
<hthan@.superioraccess.com> wrote:
>They also locked out the report tool, kept it from runinng b/w
>10am and 2pm. Result: it rocked.
How about you replicate the data out to a mart/warehouse so they can
click all they want?
J.|||there is no need for the report to run every few minutes. The chairman does
it; the VPs do it; the CEO does it. This is kind of more a habit than a
business need, just like you are checking stocks every hour. After we
locked them out, they do other things to kill time.:) Hey, they are also
the OWNERS. So long ...
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:6fuahv4s12omn7l7k3c1jfp3ivs2up3h8h@.4ax.com...
> On Tue, 15 Jul 2003 14:19:06 -0700, "Flicker"
> <hthan@.superioraccess.com> wrote:
> >They also locked out the report tool, kept it from runinng b/w
> >10am and 2pm. Result: it rocked.
> How about you replicate the data out to a mart/warehouse so they can
> click all they want?
> J.
>

CPU usage 90%

hi
My site has become tremendous slow..cpu usage for normal transcationsalso showing 90%..day beefore it was working fine..
any hints wot can be the problems.Look at your indexes. As data gets added, and you do not have appropriate
indexes, the amount of data SQL has to scan through increases. This results
in high CPU usage.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:1582C1EB-6E00-4369-94D6-B047B067550F@.microsoft.com...
> hi
> My site has become tremendous slow..cpu usage for normal transcationsalso
showing 90%..day beefore it was working fine..
> any hints wot can be the problems.
>

CPU to slow for SQL Server 2005 express?

Hello,
i want to install the SQL 2005 express Server on a HP-Netserver LH4 with 1 Xeon 500 CPU. I know, that i must have a 600MHz CPU, but i cant change the CPU. Is there one way to start the SQL server?
Thanks
Michael

I *believe* that the processor discrepancy will just give you a warning, but still allow you to install/start the service. Is this the case?

Thanks,
Samuel Lester (MSFT)

|||No, the server will not bei installed, but the other parts of the program.

Michael
|||

Hi Michael,

Something else is going on. The processor check never issues a failure but only warnings. Based on what you described there shouldn't be an issue. The proc is an Intel Xeon (which is a P-III compatible or greater) so that shouldn't be a problem.

Can you explain what you mean by the engine won't be installed? Is it grayed out in the feature selection dialog? Does it attempt to install but fail?

Which version of Windows are you running?

cpu + f(reads) = duration??

OK, I have an SP that users say runs slow. I take a look, it's taking
2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
I munge around the logic and cut the reads to 90k. The CPU rises
slightly to around 3.0. The duration is never below 5.0!'
OK, so WTF is SQLServer doing when it's not doing reads and not
cranking CPU?
--
This is on a dev server, there is some mild contention, but the
comparison of the original code to the modified code is pretty
constant. I tried throwing in a few NOLOCKS. I did add a #temp
table, which worked better than a @.tablevar because it does hold a
mighty 100 to 1000 rows and it autostats itself, I guess. Adding
explicit indexes to it seems to make things (much) worse.
Thanks.
JoshHi
Roughly, the difference between the CPU time and duration is your client
retrieving the data across the network.
How many rows are you pushing back, and can the client handle the volume?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!'
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>|||Cutting the reads down from 600K to 90K probably indicates you are doing
more seeks where as before you were doing scans. Seeks can often be more
CPU intensive since it really has a lot of work compared to a scan. It may
even be slower than a scan under the right or wrong conditions. Bottom line
is that 90K reads is still a lot of reads. But without actually knowing
more about the sp and the tables involved it is hard to say. Duration can
often be misleading do to things like blocking and disk bottlenecks.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!'
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>|||On Tue, 20 Dec 2005 22:33:47 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Cutting the reads down from 600K to 90K probably indicates you are doing
>more seeks where as before you were doing scans. Seeks can often be more
>CPU intensive since it really has a lot of work compared to a scan. It may
>even be slower than a scan under the right or wrong conditions. Bottom line
>is that 90K reads is still a lot of reads. But without actually knowing
>more about the sp and the tables involved it is hard to say. Duration can
>often be misleading do to things like blocking and disk bottlenecks.
It returns about 1400 modest rows and we have lots of bandwidth, can't
take anything like two seconds, anyway it returns the same rows for
old code and new.
The data should be cached, I doubt there's any physical IO, and none
shows in the stats io display.
The old code writes about 4k rows to the #temp table, the new code
adds another 100 or so rows to another #temp table.
I suppose that seek versus scan could be the answer, I mean, something
has to be. Let me look again. Nooooo, I set profile on and the old
code has plenty of seeks, too, ...
... hoo boy. OK, the data will have been defragged overnight, and now
I'm seeing different behavior with the OLD code now taking more CPU
and more duration and about half the reads. I plain don't get it, but
without a repeatable situation to talk about, I can't see what else to
say or ask here.
Thanks for the suggestions, next time I have a similar anomaly (and
time to investigate) I think I'll look right at the scan/seek ratio or
other profile innards.
Josh

cpu + f(reads) = duration??

OK, I have an SP that users say runs slow. I take a look, it's taking
2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
I munge around the logic and cut the reads to 90k. The CPU rises
slightly to around 3.0. The duration is never below 5.0!?
OK, so WTF is SQLServer doing when it's not doing reads and not
cranking CPU?
This is on a dev server, there is some mild contention, but the
comparison of the original code to the modified code is pretty
constant. I tried throwing in a few NOLOCKS. I did add a #temp
table, which worked better than a @.tablevar because it does hold a
mighty 100 to 1000 rows and it autostats itself, I guess. Adding
explicit indexes to it seems to make things (much) worse.
Thanks.
Josh
Hi
Roughly, the difference between the CPU time and duration is your client
retrieving the data across the network.
How many rows are you pushing back, and can the client handle the volume?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!?
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>
|||Cutting the reads down from 600K to 90K probably indicates you are doing
more seeks where as before you were doing scans. Seeks can often be more
CPU intensive since it really has a lot of work compared to a scan. It may
even be slower than a scan under the right or wrong conditions. Bottom line
is that 90K reads is still a lot of reads. But without actually knowing
more about the sp and the tables involved it is hard to say. Duration can
often be misleading do to things like blocking and disk bottlenecks.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!?
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>
|||On Tue, 20 Dec 2005 22:33:47 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Cutting the reads down from 600K to 90K probably indicates you are doing
>more seeks where as before you were doing scans. Seeks can often be more
>CPU intensive since it really has a lot of work compared to a scan. It may
>even be slower than a scan under the right or wrong conditions. Bottom line
>is that 90K reads is still a lot of reads. But without actually knowing
>more about the sp and the tables involved it is hard to say. Duration can
>often be misleading do to things like blocking and disk bottlenecks.
It returns about 1400 modest rows and we have lots of bandwidth, can't
take anything like two seconds, anyway it returns the same rows for
old code and new.
The data should be cached, I doubt there's any physical IO, and none
shows in the stats io display.
The old code writes about 4k rows to the #temp table, the new code
adds another 100 or so rows to another #temp table.
I suppose that seek versus scan could be the answer, I mean, something
has to be. Let me look again. Nooooo, I set profile on and the old
code has plenty of seeks, too, ...
... hoo boy. OK, the data will have been defragged overnight, and now
I'm seeing different behavior with the OLD code now taking more CPU
and more duration and about half the reads. I plain don't get it, but
without a repeatable situation to talk about, I can't see what else to
say or ask here.
Thanks for the suggestions, next time I have a similar anomaly (and
time to investigate) I think I'll look right at the scan/seek ratio or
other profile innards.
Josh

cpu + f(reads) = duration??

OK, I have an SP that users say runs slow. I take a look, it's taking
2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
I munge around the logic and cut the reads to 90k. The CPU rises
slightly to around 3.0. The duration is never below 5.0!'
OK, so WTF is SQLServer doing when it's not doing reads and not
cranking CPU?
This is on a dev server, there is some mild contention, but the
comparison of the original code to the modified code is pretty
constant. I tried throwing in a few NOLOCKS. I did add a #temp
table, which worked better than a @.tablevar because it does hold a
mighty 100 to 1000 rows and it autostats itself, I guess. Adding
explicit indexes to it seems to make things (much) worse.
Thanks.
JoshHi
Roughly, the difference between the CPU time and duration is your client
retrieving the data across the network.
How many rows are you pushing back, and can the client handle the volume?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.
4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!'
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>|||Cutting the reads down from 600K to 90K probably indicates you are doing
more seeks where as before you were doing scans. Seeks can often be more
CPU intensive since it really has a lot of work compared to a scan. It may
even be slower than a scan under the right or wrong conditions. Bottom line
is that 90K reads is still a lot of reads. But without actually knowing
more about the sp and the tables involved it is hard to say. Duration can
often be misleading do to things like blocking and disk bottlenecks.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:4j5hq1dsqfp23vrkmlur6pvl15hvv8oc6m@.
4ax.com...
> OK, I have an SP that users say runs slow. I take a look, it's taking
> 2.5 seconds of CPU and doing 600k reads to give a duration around 3.0.
> I munge around the logic and cut the reads to 90k. The CPU rises
> slightly to around 3.0. The duration is never below 5.0!'
> OK, so WTF is SQLServer doing when it's not doing reads and not
> cranking CPU?
> --
> This is on a dev server, there is some mild contention, but the
> comparison of the original code to the modified code is pretty
> constant. I tried throwing in a few NOLOCKS. I did add a #temp
> table, which worked better than a @.tablevar because it does hold a
> mighty 100 to 1000 rows and it autostats itself, I guess. Adding
> explicit indexes to it seems to make things (much) worse.
> Thanks.
> Josh
>|||On Tue, 20 Dec 2005 22:33:47 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Cutting the reads down from 600K to 90K probably indicates you are doing
>more seeks where as before you were doing scans. Seeks can often be more
>CPU intensive since it really has a lot of work compared to a scan. It may
>even be slower than a scan under the right or wrong conditions. Bottom lin
e
>is that 90K reads is still a lot of reads. But without actually knowing
>more about the sp and the tables involved it is hard to say. Duration can
>often be misleading do to things like blocking and disk bottlenecks.
It returns about 1400 modest rows and we have lots of bandwidth, can't
take anything like two seconds, anyway it returns the same rows for
old code and new.
The data should be cached, I doubt there's any physical IO, and none
shows in the stats io display.
The old code writes about 4k rows to the #temp table, the new code
adds another 100 or so rows to another #temp table.
I suppose that seek versus scan could be the answer, I mean, something
has to be. Let me look again. Nooooo, I set profile on and the old
code has plenty of seeks, too, ...
... hoo boy. OK, the data will have been defragged overnight, and now
I'm seeing different behavior with the OLD code now taking more CPU
and more duration and about half the reads. I plain don't get it, but
without a repeatable situation to talk about, I can't see what else to
say or ask here.
Thanks for the suggestions, next time I have a similar anomaly (and
time to investigate) I think I'll look right at the scan/seek ratio or
other profile innards.
Josh

Couple of newbie questions

My company is moving to SQL Server soon, and I'm certainly excited about it. Currently we are using a FoxPro based solution which is slow and unreliable.

I'm working on the configuration and have a few questions. We are planning on going with a SQL Server 2000 Standard Edition running on Windows 2000 Server. Some of what Microsoft has said on the web site is very vague concerning the licensing and other maximums.

What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

Thanks in advance.According to Books Online:

1. What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?

2GB is the maximum that SQL Std can use.

2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.

If the OS can see the additional RAM, this should be ok. I cannot think how much memory that Win2k std can use.

3. Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).

If you go with per processor licensing of SQL Server, that is all you need.

4. For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?

I found SQL2k Std per processor licensing at ~4500 multiplied by 3 you would be at ~$13,500

I found SQL2k Std 10 client for $2000. If you purchased 7 of these (70 user), you would be at ~$14,000

Right now, the price is about the same. You need to weigh the options, and look toward the future.

What happens if the 3 processor server needs 5 more processors? What happens if you add 40 more users? How will this impact your licenses (and cost)?

5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

I don't know. What is the price difference between 2MB, 1MB, and 'normal' Xeons? We have an 8 way 700 with 2MB cache and things perform quite well. I am sure that we paid a premium for the 2MB chips...I do not know what performance would be like if we had lesser Xeons. Maybe someone else can help out here with some suggestions.

________________
Keith|||2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.

Win2k and NT Standard see 4GIG max which is the memory space directly addressable by a a 32bit operating system.

The 2 gig limit for SQL Server comes from the fact that the NT architecture has a 2gig per process memory limit.

5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

It's hard to say. All of our SQL Servers now have 2MB processor caches. We had one Dell 4-way server with 512K (standard) caches that severely under-performed and was replaced by a Compaq server. But the Compaq server was much more carefully specified so direct comparisons are tough. I know this. I've read of people with performance problems when CPU utilization is at 100% on all 4 processors. On our Compaq servers, when all four processors are at 100%, people can still work. It's a little slower, but almost goes unnoticed except by those that cause the performance spike (period end processes, some custom reports). On the Dell, if all 4 where at 100% no one moved. It would stop everyone in their tracks. Compaq's processor multi-processor architecture probably has something to do with it, but I bet the 2MB cache in each processor has something to do with it also.

If the cost is not unreasonable go for the 2mb cache. We choose slower processors with 2MB cache over a faster processor with 512k or 1mb and haven't looked back.|||Thanks for the repsonse. It will be a few thousand more to upgrade from the 1 MB to 2 MB cache, so it may be difficult to get that passed through our budget.

Out of curiosity, how many users are you guys dealing with? I'll be around 50 concurrent to start, while not expecting that number to go up to more than 75 any time soon (a few years off).|||We have about 60 users hitting the system full time.

Keep in mind, it is not so much the user count, but the way the application and database were created that really affects performance.

________________
Keith|||I don't even know exactly right now. Probably right around 35-60 depending on the time of day. But really it's not the number of users, but the work you expect the server to perform for those users.

Our primary server runs an accounting and distribution package that is heavily optimized for SQL Server and WAN environments relying on Triggers, and stored procedures, and temp tables for reports. (And not measly select * from thistable stored procedures, these are real ones that do real work.) This application works the server. 1MB may be just fine. At the time we specified the hardware we had the choice of 1mb cache and 650Mhz processors or 2mb Cache and 450mhz processors to fit in our price range. We went with the 450mhz processors and 2mb cache. 20

Make sure your application isn't just a fancy data store. We way equipped some hardware we have right now for a mission critical clustered solution. Quad processor Active/Active Compaq machines with 4gig each running SQL Enterprise and NT Enterprise, and we get the app installed on a test machine and I find it was ported from Oracle and AS/400 and is mostly flat files stored in the database. No stored procedures, no referential integrity, hardly even any indexes. The groupresponsible for getting the hardware obviously didn't do their researchfirst. Such a database will hardly be taxing the CPU's and is more likely to have blocking issues than performance issues. I can't wait until we go live...