Daily Archives: June 22, 2015

Update Statistics – Synchronously and asynchronously. Which one is better ?

22 Monday Jun 2015

Posted by in SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Update Statistics – Synchronously and asynchronously. Which one is better ?

Download PDF – Update Statistics – Synchronously and asynchronously. Which one is better?

We can Update Statistics synchronously and asynchronously.

Default is Synchronous

How we can enable this option-

--

ALTER DATABASE [Database_Name] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [Database_Name] SET AUTO_UPDATE_STATISTICS_ASYNC ON 

--

Synchronous Case-


In this case SQL Server first update the statistics ( Of course if they were outdated ) and then execute the query. So depending on the table size and the other processes running on the server update statistics may take some time and slow down you query execution. In this case your query has to wait till the time update statistics operation is not completed. In this case all the queries current and future will get the benefit of the statistics update.

Asynchronous Case-


In this case SQL Server first execute the query and then update the statistics ( Of course if they were outdated ). In this case your query will NOT have to wait till the time update statistics operation is not completed. In this case statistics update are handled by a background process. The query will not get the benefit of the statistics update, however future queries will.


Note 1- Enabling the async option affects will not allow yout to put a database into single-user mode. The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database.

Note 2-To check which databases have this option enabled, we can check out the is_auto_update_stats_async_on column in sys.databases.

Script to check which database has which option enabled.

--

SELECT name Database_Name,
      is_auto_create_stats_on [Create_Stats]
     ,is_auto_update_stats_on [Auto_Update_Stats]
     ,is_auto_update_stats_async_on [Async_Update]
FROM sys.databases
GO

--


That’s all folks, I hope you’ve enjoyed learning about “Sync / Async” option to update statistics, and I’ll see you soon with more “Performance Tuning” articles.

Thanks !

Pawan Kumar Khowal

MSBISkills.com

Why we should update statistics? When one should update statistics?

22 Monday Jun 2015

Posted by in SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Why we should update statistics? When one should update statistics?

Download PDF – Why and When we should Update Statistics

First let’s discuss why to update statistics?

If we do not update statistics then we will get bad query execution plan. Statistics are necessary for best query performance. Statistics essentially “describe” the data what is there in the index. SQL Server Query Optimizer uses statistics to estimate the distribution of values. Statistics helps query optimizer in making better decision making.

When to update statistics?

The best answer for this is we should update statistics regularly.

We should keep them updated using Auto Update Stats = true. If auto stats update is enabled then updates happen based on number of changes to the data. This is sometimes not enough.

SQL Server has index and column stats. We can use Update statistics to update stats on an index and on columns whenever you want using Update Statistics command.

On larger tables we should run update statistics on a schedule to refresh stats regularly. Otherwise we will land up in problem as your query is running slow.

Rebuilding indexes will also updates statistics. This process normally happens as a weekly maintenance task. Often when weekly index rebuild happens things works very fine on Monday and Tuesday. Now on Wednesday we will start getting issues like this query is working slow and stuff like that. Now our query works fine on Monday and Tuesday because of updated statistics.

sp_updatestats – It runs Update Statistics against all User defined tables and internal tables in the current database. Well this is not recommended.

We can update statistics synchronously and asynchronously. Default is synchronously. Here when SQL Server tries to execute a query and before executing SQL Server will check the threshold value against the # of changes happened in the table and if the threshold has crossed than SQL Server first update the statistics and then the query will be executed. So depending on the table size and the other processes running on the server update statistics may take some time and slow down you query.

In asynchronous case the query will be executed first and then the update statistics will happen. In this case the benefit is that our query will not have to wait for update statistics to happen. The disadvantage of this case is that our query may get executed with less qualified execution plan as the statistics are not up to date. Most of the cases people don’t ON this feature.

That’s all folks, I hope you’ve enjoyed learning about why and when we should update statistics, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com