When loading a webpage that queries my Basic Azure SQL database the first query is very slow - could take 5-9 seconds to load the page. Subsequent queries are very fast. Is there a better way to keep the database active other than running a process every few minutes to keep the database active?

Hi Evan,

This is not an expected behavior. You can open a support ticket or if you are comfortable troubleshooting the query performance with the help of query plans, feel free to use the article (Tuning techniques https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx)

I hope that helps!

Karthik

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee 13 hours 39 minutes ago
• Proposed as answer by Joseph IdziorekMicrosoft employee 7 hours 18 minutes ago

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee 13 hours 28 minutes ago
• Proposed as answer by Joseph IdziorekMicrosoft employee 7 hours 8 minutes ago

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator 16 hours 31 minutes ago

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator 16 hours 42 minutes ago

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Hello Evan,

The longer response time for the first query could be for set of reasons including

• compilation of the query plan
• loading data into memory that is needed to process the query

After the initial compilation of the query the plan is cached and the cached plan is used for the subsequent queries. Similar once the data is in memory it doesn't have to be loaded from disc again for subsequent queries.

If you run a lot of different queries, the cached plan might get pushed out of the cache and the query has to be recompiled. Recompiling the query might also be necessary if parameters of the query change.

Similar for the data that is loaded from disc in to memory: If you run a lot of different queries over different datasets, the data is pushed out of memory and needs to be loaded again.

Not sure if this is an option for you, but you can try running in a higher service tier / performance to see if it helps with the problem as they give more resources.

Best,

Jan

• Edited by Jan EngelsbergMicrosoft employee Friday, May 22, 2015 5:39 PM
• Proposed as answer by Joseph IdziorekMicrosoft employee Friday, May 22, 2015 11:59 PM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Does your web page retrieve the data through Entity Framework? An EF model can take that long for initialization.

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator 16 hours 31 minutes ago

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator 16 hours 42 minutes ago

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM

Are you sure that it is the SQL Query taking the time to execute?  What language is your webpage/website built in? does it use .net framework? It could be IIS spinning the framework worker up rather than SQL not returning the query in time.

Also, have you tried running the query using the Management Tools? What kind of performance do you get when doing this?  Remember that SQL will keep the Execution Plan in memory after executing a query for the first time.  This would make subsequent runs of that query much quicker.

It could be beneficial to post your SQL script on here so we can take a look, i feel more info is needed before we can help further but hopefully this will get you started!

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Sunday, May 24, 2015 2:37 PM