Migrating older comments from Wordpress
Dan
February 28, 2017 8:47 pm
Awesome podcast, am sharing it with my dev team at work.
Loading...
Reply
Tom Scarr
February 19, 2019 4:48 am
Hi Kendra,
Great podcast! Love your work by the way! Just to add some things I have just found regarding slow on first execution fast on second execution was in relation to the database setting for asynchronous update of statistics. I had a stored procedure which was mostly compiled SQL except one statement at the end which was returning a resultset and executed using sp_executeSql with some dynamic columns (yes, I know….but sometimes needs must when you have a massive de-normalized table with hundreds of columns to work with!). The query itself was quite simple, with the plan just involving two tables typically joined in a nested loop with a clustered index seek – and I could not for the life of me work out why it was taking so long to compile as it was so simple. I was getting compile times of 30-50 seconds on first run. Subsequent runs would be fine for a day or so, then back to really slow the next day. Anyway, then I discovered via a SQL trace that an update of statistics was happening on one of the column stats during compilation and this was the cause of the long compile time. By default SQL server has the database setting set to synchronously auto update column stats and so it was spending the vast majority of the compile time updating the column stats in advance of generating the execution plan. I don’t quite know why SQL server was so regularly considering that it needed to update the column as there shouldn’t have been that many data changes in the underlying table each night. But that is a separate point. The solution for me was to set the database setting to use asynchronous updates of statistics with ALTER DATABASE DbName SET AUTO_UPDATE_STATISTICS_ASYNC ON. I realize the big caveat here is that sometimes you might want to ensure the execution plan is using the highest quality updated stats but in this instance we found that most of the time in the workloads we had – particularly ones involving dynamic SQL benefited most from this and the plans were really so simple and the optimizer didn’t have too many options for most of the queries involving dynamic SQL.
Just wanted to share this and wondered whether it is something you have ever come across and whether you have ever experienced converse problems with setting async update of stats to on?
thanks
Tom
Loading...
Reply
A cautionary tale of building a highly-used, really bad index - DoneDone
December 30, 2019 3:04 pm
[…] There are two general reasons why this behavior happens—SQL Server needs time to cache the execution plan or it’s having to pull a lot of data from disk because what’s cached in memory is stale (and Kendra Little has a wonderful video and article about it, and how to determine the root cause). […]
Loading...
Reply
Dave
March 16, 2021 3:03 pm
What if it’s a data warehouse so the 1st call takes 8m 15 sec and subsequent calls take ~20 sec? And we have maybe 50 queries like this. Will queries get pushed out of cache as other queries are run?
Migrating older comments from WordpressDan February 28, 2017 8:47 pm Awesome podcast, am sharing it with my dev team at work.Loading...
Reply Tom Scarr February 19, 2019 4:48 am Hi Kendra,
Great podcast! Love your work by the way! Just to add some things I have just found regarding slow on first execution fast on second execution was in relation to the database setting for asynchronous update of statistics. I had a stored procedure which was mostly compiled SQL except one statement at the end which was returning a resultset and executed using sp_executeSql with some dynamic columns (yes, I know….but sometimes needs must when you have a massive de-normalized table with hundreds of columns to work with!). The query itself was quite simple, with the plan just involving two tables typically joined in a nested loop with a clustered index seek – and I could not for the life of me work out why it was taking so long to compile as it was so simple. I was getting compile times of 30-50 seconds on first run. Subsequent runs would be fine for a day or so, then back to really slow the next day. Anyway, then I discovered via a SQL trace that an update of statistics was happening on one of the column stats during compilation and this was the cause of the long compile time. By default SQL server has the database setting set to synchronously auto update column stats and so it was spending the vast majority of the compile time updating the column stats in advance of generating the execution plan. I don’t quite know why SQL server was so regularly considering that it needed to update the column as there shouldn’t have been that many data changes in the underlying table each night. But that is a separate point. The solution for me was to set the database setting to use asynchronous updates of statistics with ALTER DATABASE DbName SET AUTO_UPDATE_STATISTICS_ASYNC ON. I realize the big caveat here is that sometimes you might want to ensure the execution plan is using the highest quality updated stats but in this instance we found that most of the time in the workloads we had – particularly ones involving dynamic SQL benefited most from this and the plans were really so simple and the optimizer didn’t have too many options for most of the queries involving dynamic SQL.
Just wanted to share this and wondered whether it is something you have ever come across and whether you have ever experienced converse problems with setting async update of stats to on?
thanks Tom
Loading...
Reply A cautionary tale of building a highly-used, really bad index - DoneDone December 30, 2019 3:04 pm […] There are two general reasons why this behavior happens—SQL Server needs time to cache the execution plan or it’s having to pull a lot of data from disk because what’s cached in memory is stale (and Kendra Little has a wonderful video and article about it, and how to determine the root cause). […]
Loading...
Reply Dave March 16, 2021 3:03 pm What if it’s a data warehouse so the 1st call takes 8m 15 sec and subsequent calls take ~20 sec? And we have maybe 50 queries like this. Will queries get pushed out of cache as other queries are run?