High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com