Hello all,

We have a customer whose database now exceeds 8 GB in size. They have 30
concurrent users running a VB application that we wrote that calls Stored
Procedures for all queries/updates. They are running SQL Server Standard
edition on a Windows 2003 Server with 8-way Zeon processors at 3.16 GHZ and
4 GB of RAM. I am not exactly sure of the processor setup but there are 8
displayed in Device Manager and 8 processor windows in Task Manager.

Note, we have 38 other installations where they have lesser setups in terms
of hardware, are using our same VB app with SQL 2000, anywhere from 15 to
30 users, and are not experiencing the timeouts of this particular
installation.

The only difference we can see is that this customer has 10 times the amount
of data.

Is there ANY way that SQL Server 2000 Standard can be set up to use more
than 2 GB of RAM? Or do I have to suggest that they upgrade to SQL Server
2005? I understand that SQL Server 2005 Standard uses up to 4GB of RAM.

Note: They are appalled at the cost of the Enterprise Editions of SQL Server
licenses.

Their system performs well in the morning, but as the day goes on
performance degrades considerably. I never see SQL Server using more than
1.7 GB of memory on their server. I imagine that considerable disk swapping
is occurring and the queue is backing up.

Any will be appreciated.

The most likely have 4 physical processors that are Hyper Threaded since
Standard edition can only use 4 physical processors max. There is no way to
get more than 2GB of memory in 2000 Std edition. I recommend they upgrade to
2005 Std edition. It can use what ever the OS supports. I recommend they
upgrade the OS to one that supports more than 4GB and add more memory as
well. If the processors are only capable of 32 bit then in order to use more
than 3GB for SQL server you will need to use AWE. Another comment. If the
app slows down that much with more data you most likely have not tuned it
properly to begin with. You can get by with table scans on smaller tables
but when they get larger you start to notice. If they have proper indexing
and queries the size of the tables should not matter as much.

--
Andrew J. Kelly SQL MVP

"John Kotuby" <JohnKotuby@discussions.microsoft.com> wrote in message
news:OEsCILuzHHA.3788@TK2MSFTNGP02.phx.gbl...
> Hello all,
>
> We have a customer whose database now exceeds 8 GB in size. They have 30
> concurrent users running a VB application that we wrote that calls Stored
> Procedures for all queries/updates. They are running SQL Server Standard
> edition on a Windows 2003 Server with 8-way Zeon processors at 3.16 GHZ
> and 4 GB of RAM. I am not exactly sure of the processor setup but there
> are 8 displayed in Device Manager and 8 processor windows in Task Manager.
>
> Note, we have 38 other installations where they have lesser setups in
> terms of hardware, are using our same VB app with SQL 2000, anywhere from
> 15 to 30 users, and are not experiencing the timeouts of this particular
> installation.
>
> The only difference we can see is that this customer has 10 times the
> amount of data.
>
> Is there ANY way that SQL Server 2000 Standard can be set up to use more
> than 2 GB of RAM? Or do I have to suggest that they upgrade to SQL Server
> 2005? I understand that SQL Server 2005 Standard uses up to 4GB of RAM.
>
> Note: They are appalled at the cost of the Enterprise Editions of SQL
> Server licenses.
>
> Their system performs well in the morning, but as the day goes on
> performance degrades considerably. I never see SQL Server using more than
> 1.7 GB of memory on their server. I imagine that considerable disk
> swapping is occurring and the queue is backing up.
>
> Any will be appreciated.
>

Thanks Andrew,

We will pass on your suggestions.

We have been working on optimizing our queries for the last 6 months and
there has been much improvement.

Lately we actually have been looking at reducing the number of indexes as
there is a considerable amount of Inserting and Updating occurring (a highly
interactive system), and we want to reduce the number if indexes that need
to also be updated.

There are some queries, however, that we cannot seem to get to work properly
without table scanning.


"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:ey4VrTuzHHA.600@TK2MSFTNGP05.phx.gbl...
> The most likely have 4 physical processors that are Hyper Threaded since
> Standard edition can only use 4 physical processors max. There is no way
> to get more than 2GB of memory in 2000 Std edition. I recommend they
> upgrade to 2005 Std edition. It can use what ever the OS supports. I
> recommend they upgrade the OS to one that supports more than 4GB and add
> more memory as well. If the processors are only capable of 32 bit then in
> order to use more than 3GB for SQL server you will need to use AWE.
> Another comment. If the app slows down that much with more data you most
> likely have not tuned it properly to begin with. You can get by with table
> scans on smaller tables but when they get larger you start to notice. If
> they have proper indexing and queries the size of the tables should not
> matter as much.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John Kotuby" <JohnKotuby@discussions.microsoft.com> wrote in message
> news:OEsCILuzHHA.3788@TK2MSFTNGP02.phx.gbl...
>> Hello all,
>>
>> We have a customer whose database now exceeds 8 GB in size. They have 30
>> concurrent users running a VB application that we wrote that calls Stored
>> Procedures for all queries/updates. They are running SQL Server Standard
>> edition on a Windows 2003 Server with 8-way Zeon processors at 3.16 GHZ
>> and 4 GB of RAM. I am not exactly sure of the processor setup but there
>> are 8 displayed in Device Manager and 8 processor windows in Task
>> Manager.
>>
>> Note, we have 38 other installations where they have lesser setups in
>> terms of hardware, are using our same VB app with SQL 2000, anywhere
>> from 15 to 30 users, and are not experiencing the timeouts of this
>> particular installation.
>>
>> The only difference we can see is that this customer has 10 times the
>> amount of data.
>>
>> Is there ANY way that SQL Server 2000 Standard can be set up to use more
>> than 2 GB of RAM? Or do I have to suggest that they upgrade to SQL Server
>> 2005? I understand that SQL Server 2005 Standard uses up to 4GB of RAM.
>>
>> Note: They are appalled at the cost of the Enterprise Editions of SQL
>> Server licenses.
>>
>> Their system performs well in the morning, but as the day goes on
>> performance degrades considerably. I never see SQL Server using more than
>> 1.7 GB of memory on their server. I imagine that considerable disk
>> swapping is occurring and the queue is backing up.
>>
>> Any will be appreciated.
>>
>
>

Thanks Andrew for your quick reply.
We will pass the suggestions along to our customer.

We have been working on optimizing the queries for the last 8 months, but
there are still some that seem to require table scans.

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:ey4VrTuzHHA.600@TK2MSFTNGP05.phx.gbl...
> The most likely have 4 physical processors that are Hyper Threaded since
> Standard edition can only use 4 physical processors max. There is no way
> to get more than 2GB of memory in 2000 Std edition. I recommend they
> upgrade to 2005 Std edition. It can use what ever the OS supports. I
> recommend they upgrade the OS to one that supports more than 4GB and add
> more memory as well. If the processors are only capable of 32 bit then in
> order to use more than 3GB for SQL server you will need to use AWE.
> Another comment. If the app slows down that much with more data you most
> likely have not tuned it properly to begin with. You can get by with table
> scans on smaller tables but when they get larger you start to notice. If
> they have proper indexing and queries the size of the tables should not
> matter as much.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John Kotuby" <JohnKotuby@discussions.microsoft.com> wrote in message
> news:OEsCILuzHHA.3788@TK2MSFTNGP02.phx.gbl...
>> Hello all,
>>
>> We have a customer whose database now exceeds 8 GB in size. They have 30
>> concurrent users running a VB application that we wrote that calls Stored
>> Procedures for all queries/updates. They are running SQL Server Standard
>> edition on a Windows 2003 Server with 8-way Zeon processors at 3.16 GHZ
>> and 4 GB of RAM. I am not exactly sure of the processor setup but there
>> are 8 displayed in Device Manager and 8 processor windows in Task
>> Manager.
>>
>> Note, we have 38 other installations where they have lesser setups in
>> terms of hardware, are using our same VB app with SQL 2000, anywhere
>> from 15 to 30 users, and are not experiencing the timeouts of this
>> particular installation.
>>
>> The only difference we can see is that this customer has 10 times the
>> amount of data.
>>
>> Is there ANY way that SQL Server 2000 Standard can be set up to use more
>> than 2 GB of RAM? Or do I have to suggest that they upgrade to SQL Server
>> 2005? I understand that SQL Server 2005 Standard uses up to 4GB of RAM.
>>
>> Note: They are appalled at the cost of the Enterprise Editions of SQL
>> Server licenses.
>>
>> Their system performs well in the morning, but as the day goes on
>> performance degrades considerably. I never see SQL Server using more than
>> 1.7 GB of memory on their server. I imagine that considerable disk
>> swapping is occurring and the queue is backing up.
>>
>> Any will be appreciated.
>>
>
>

Well sometimes a full scan is necessary but that should definitely be the
exception not the norm and a last resort if the table is large. Do you know
for a fact you have too many indexes now? Reducing them just because you do
a lot of updates is not necessarily a wise decision. If the indexes are
useful to reads in avoiding bad plans and especially if there is no evidence
they are harmful you may not gain anything by dropping them.

--
Andrew J. Kelly SQL MVP

"John Kotuby" <JohnKotuby@discussions.microsoft.com> wrote in message
news:O4Zz1puzHHA.3940@TK2MSFTNGP05.phx.gbl...
> Thanks Andrew,
>
> We will pass on your suggestions.
>
> We have been working on optimizing our queries for the last 6 months and
> there has been much improvement.
>
> Lately we actually have been looking at reducing the number of indexes as
> there is a considerable amount of Inserting and Updating occurring (a
> highly interactive system), and we want to reduce the number if indexes
> that need to also be updated.
>
> There are some queries, however, that we cannot seem to get to work
> properly without table scanning.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:ey4VrTuzHHA.600@TK2MSFTNGP05.phx.gbl...
>> The most likely have 4 physical processors that are Hyper Threaded since
>> Standard edition can only use 4 physical processors max. There is no way
>> to get more than 2GB of memory in 2000 Std edition. I recommend they
>> upgrade to 2005 Std edition. It can use what ever the OS supports. I
>> recommend they upgrade the OS to one that supports more than 4GB and add
>> more memory as well. If the processors are only capable of 32 bit then in
>> order to use more than 3GB for SQL server you will need to use AWE.
>> Another comment. If the app slows down that much with more data you most
>> likely have not tuned it properly to begin with. You can get by with
>> table scans on smaller tables but when they get larger you start to
>> notice. If they have proper indexing and queries the size of the tables
>> should not matter as much.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "John Kotuby" <JohnKotuby@discussions.microsoft.com> wrote in message
>> news:OEsCILuzHHA.3788@TK2MSFTNGP02.phx.gbl...
>>> Hello all,
>>>
>>> We have a customer whose database now exceeds 8 GB in size. They have 30
>>> concurrent users running a VB application that we wrote that calls
>>> Stored Procedures for all queries/updates. They are running SQL Server
>>> Standard edition on a Windows 2003 Server with 8-way Zeon processors at
>>> 3.16 GHZ and 4 GB of RAM. I am not exactly sure of the processor setup
>>> but there are 8 displayed in Device Manager and 8 processor windows in
>>> Task Manager.
>>>
>>> Note, we have 38 other installations where they have lesser setups in
>>> terms of hardware, are using our same VB app with SQL 2000, anywhere
>>> from 15 to 30 users, and are not experiencing the timeouts of this
>>> particular installation.
>>>
>>> The only difference we can see is that this customer has 10 times the
>>> amount of data.
>>>
>>> Is there ANY way that SQL Server 2000 Standard can be set up to use more
>>> than 2 GB of RAM? Or do I have to suggest that they upgrade to SQL
>>> Server 2005? I understand that SQL Server 2005 Standard uses up to 4GB
>>> of RAM.
>>>
>>> Note: They are appalled at the cost of the Enterprise Editions of SQL
>>> Server licenses.
>>>
>>> Their system performs well in the morning, but as the day goes on
>>> performance degrades considerably. I never see SQL Server using more
>>> than 1.7 GB of memory on their server. I imagine that considerable disk
>>> swapping is occurring and the queue is backing up.
>>>
>>> Any will be appreciated.
>>>
>>
>>
>
>