Why isn't SQL Server loading my index into memory?

I have a table with 8 million records with many fields including lat / long values ​​and it has an index over the Lat / Long fields.

I am making a query to find records that fall into a square around a point (for further refinement into a circle) that looks like this:

SELECT Lat, Long FROM Data WHERE (Lat BETWEEN 1 AND 2) AND (LONG BETWEEN 1 AND 2).

On my dev machine, this uses the index and returns after about 50ms (first time I make a request for a specific point).
On my production server it also uses the index, but it takes about 2-4 seconds for the first request, 10ms for the next.

In my dev machine, SQL Server takes 500MB of memory, on my server it is about 130MB.

For me the obvious conclusion is that in my machine the index is loaded into memory, while in the production segment it is not ...

Is my guess correct? What can I do to fix this?

This is SQL Express 2005 (free version) on W2k3 on both machines. The only difference I can think of is that my machine is 32 bits and the server is 64, but I don't think how that might affect the loading of the index into memory.

Also, the server is not running in memory. It has a physical memory of 2 GB and a commit fee of about 500 MB, so there is a lot of free space.

Any ideas would be much appreciated! Thank!

+1


source to share


1 answer


When faced with this situation, I usually try to accomplish two things in the following order:

1 - update statistics for this table:



UPDATE STATISTICS Data

2 - rebuild the index: (right click on the index in SQL Server Management Studio and select "Rebuild")

+2


source







All Articles