Post reply

Query to Table containing column of data type geography takes more than 30 seconds to execute every days during his d

  • February 16, 2016 at 5:38 am

    #324754

    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

  • February 16, 2016 at 1:17 pm

    #1859092

    Almost certainly, the data has been removed from the buffer pool overnight, so it has to be read in from disk again. Then it is accessed frequently enough that it remains in the buffer pool.

    Until the nightly maintenance kicks in and 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.

    If this is problematic, create a job that does a SELECT GeographyColumn FROM YourTable (without WHERE clause) and discards the results, and ensure that this job runs when all other maintenance work is done and before the normal business starts.

    Also, if you are running on a VM, ask your VM manager if they are doing any memory balloon work overnight. If they say yes, tell them to stop doing that.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 16, 2016 at 11:42 pm

    #1859180

    Hugo Kornelis (2/16/2016)

    Almost certainly, the data has been removed from the buffer pool overnight, so it has to be read in from disk again. Then it is accessed frequently enough that it remains in the buffer pool.

    Until the nightly maintenance kicks in and 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.

    If this is problematic, create a job that does a SELECT GeographyColumn FROM YourTable (without WHERE clause) and discards the results, and ensure that this job runs when all other maintenance work is done and before the normal business starts.

    Also, if you are running on a VM, ask your VM manager if they are doing any memory balloon work overnight. If they say yes, tell them to stop doing that.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply