Force server use 3GB memory

Hi,
We have AWE enabled but SQL Server 2000 with SP3 only use
103 MB memory during peak-hour. So we try to force SQL use
3GB memory. Is the following statements' order all right?
sp_configure 'max server memory', 3072
reconfigure with override
go
exec sp_configure 'min server memory', 3072
reconfigure with override
go
exec sp_configure 'show advanced options', '1'
reconfigure with override
go
exec sp_configure 'set working set size', 1
reconfigure with override
go
then re-start SQL Server.
Thanks!
JennyHow are you monitoring the memory, Task manager doesn't understand AWE so it
reports incorrect figures. Perfmon counters will be accurate, use the Total
Server Memory (KB) counter. You don't need to set min memory or working
size, SQL will grab the memory upto max server memory at startup with AWE
enabled (thus is a good idea to set max server memory like you have
otherwise it only leaves 128MB for the OS). Having said all that, it might
be better using the /3GB switch rather than AWE assuming you have <12GB
total server memory if you only want 3 GB. I am assuming you are using
SQL2000 EE and win2kAS or wink3 EE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenny" <jyu@.iseoptions.com> wrote in message
news:017901c39ff2$3450fa10$a601280a@.phx.gbl...
Hi,
We have AWE enabled but SQL Server 2000 with SP3 only use
103 MB memory during peak-hour. So we try to force SQL use
3GB memory. Is the following statements' order all right?
sp_configure 'max server memory', 3072
reconfigure with override
go
exec sp_configure 'min server memory', 3072
reconfigure with override
go
exec sp_configure 'show advanced options', '1'
reconfigure with override
go
exec sp_configure 'set working set size', 1
reconfigure with override
go
then re-start SQL Server.
Thanks!
Jennysql

FOR XML AUTO - Performance under SQL 2000 SP3

I'm currently having a performance problem with a straight SQL select statement with the 'for xml auto' clause returning a result set involving approximately 5,000 records. This is taking about 16 seconds on a SQL 2000 installation SP3 (8.00.818), while it only takes a few milliseconds when removing the 'for xml auto' (when executed via Query Analyzer). Time under the 'for xml auto' is directly proportional to the number of records processed.

Testing the same query/table on another server with SP4 installed shows little difference in times.

I'll be trying to set up another test installation with SP3 on a different server to see if this behavior is something specific to SP3, but has anyone else encountered this problem?

Not anything specific to SP3...just tested it on a 8.00.818 box that I created and the query with/without the 'for xml auto' clause processed the results instantaneously. Will check to see what might be different about server configuration settings and collations.|||Seems to be associated with amount of data being output in the XML stream. The original output consists of 261 KB data and takes 16 seconds duration to execute. Renaming attributes in table to single characters to reduce tag name overhead reduces data size by 69% and duration to 70%. Execution was via Query Analyzer local to the database server. Execution on same table/data on another server was 115 ms. Query plans generated are the same. Output of data as a normal SQL result set (without 'for xml clause') on the problem installation takes time down to milliseconds also.|||

It is hard to give you any response to this without an exact repro (data and query at least). Did you compare the query plans?

The XML will be bigger than TDS rowset format but should not have such a big overhead. Are you sure you are not including the time it takes the QA to show the data?

Also, if the overhead does not show in SP4, could you just upgrade to SP4?

Best regards

Michael