Query to Table containing column of data type geography takes more than 30 seconds to execute

1

Hello I have a issue with a table containing countries info where one of the columns contains the geographical position of the country capital. This column data type is Geography.

The issue is that everyday the first time I execute a select on this table it takes more than 30 seconds to retrieve 270 records.
This happens only the first time after that the result is immediately retrieved.

Any one have an idea?

Thanks

2

This is a caching issue. How much memory do you have on the server? How much is SQL Server configured to use? What is the PLE throughout the day? Is the PLE low when this issue occurs? How big are the databases on this instance? Is it a single instance on the server? Or multiple? Is it a dedicated SQL Server?

3

Yes the data has been removed from the buffer pool overnight, so it has to be read in from disk again. When is accessed again is reloaded to the buffer pool.

The nightly maintenance does a lot of work, forcing SQL Server to remove stuff it has not used for a few hours out of the buffer pool, and the process restarts.

Also, the server is running on a VM. I must discuss with the administrators in order to try to find the best solution for this issue.

Thanks

4

I've heard of some people having a job that queries the data every few minutes to ensure that it stays in cache. You could try that approach.