Update Statistics after INDEX Rebuild November 17, 2014Posted by furrukhbaig in Execution Plan.
Tags: execution plan, FULLSCAN, Performance Tuning, REBUILD INDEX, scan statistics, statistics, UPDATE STATISTICS
add a comment
Its a common mistake during overnight maintenance job that we rebuild indexes and then update statistics. Its something commonly overlooked. Update statistics uses sample rows by default while rebuild index will update statistics with full scan as part of index rebuild. If we don’t specify FULLSCAN option with update statistics it will overwrite full scan statistics with sampled statistics which may mean completely different/inefficient query execution plan. Even if you update statistics with full scan its still waste of resources as rebuild already did that.
I hope it will help someone safe couple of minutes during overnight maintenance window 😉
Find out whats running on SQL Server 2005 December 13, 2007Posted by furrukhbaig in DMV's, Execution Plan, Optimize, Performance, Performance Tuning, Profiler, Queries running on the server, SQL Server 2005, SQL Server 2005 features, XML Execution Plan.
Tags: dmv, execution plan, profiler, queries running on server, whats running on sql server
Everyone wants to know whats running on the box. Specially if your job is to stabalise server you always concern what is killing the box.
A Friend of mine has published very usefull post with scripts to find out whats running on the SQL Server with their execution plan.
The same can also be managed if you run profiler and capture XML execution plan which is not always possible due to security issue and overhead of profiler itself.
AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC August 22, 2007Posted by furrukhbaig in Indexes, Performance, SQL Server 2005.
Tags: AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC, execution plan, sql server statistics
Many of us are familiar with the AUTO_UPDATE_STATISTICS database option. It provides the Optimizer with up-to-date index and column statistics to achieve optimum query plans queries execution.
Everything comes up with a price. There are some pros and cons of using that option and choice should be made very carefully otherwise it can seriously hurt performance. For example, the AUTO_UPDATE_STATISTICS option, when turned on (which it is by default for a new database), tells the Query Optimizer to automatically update the index and column statistics as data changes. It is vital because data changes can affect Optimizer decision in selecting optimum query plan. Statistics are automatically updated when the statistics used in a query execution plan are outdated. Statistics are considered outdated when 20% or more rows in a table have changed.
So what it means to me is when Optimizer consider statistics need to be updated it does it right there and then recompile the query to use updated statistics and create new execution plan and execute the query. Most of the time this process is not noticeable but in some scenarios it can hinder query performance and might cause query timeouts.
For large tables that have many indexes and statistics on columns it can take significant amount of time. Query has to wait during that time to be recompiled using new statistics and then time to execute that query plan. This can cause serious delay and can happen on periodic bases for example query usually takes 2 second but some time it takes more the 10 seconds. And believe me it will take you some time before realizing that what’s going on.
Another point to consider is it is unlikely that up-to-date statistics will change execution plan as queries should be written in a manner so they can make use of available indexes and cached for reuse. I would not expect my stored procedure to keep changing its execution plan because it can hinder performance and make no use of cached execution plan. But in some cases, with dynamic SQL, this will help improve performance. So use it with care.
The new feature in 2005 AUTO_UPDATE_STATISTICS_ASYNC can be used to avoid query wait for updated statistics. It enables optimizer to carry on using old statistics while statistics are updated asynchronously and made available for next execution. Keep in mind this can also make queries slower as it uses old statistics without any wait and there is a possibility that changes in data was significant and optimizer should have updated statistics to get optimal execution plan. But in most cases this should work fine. As I mentioned before that its unlikely that execution plan changes for regularly running queries unless columns in ORDER BY and WHERE clause keep changing.
So Recommendation is to use both options ON to be on safer side but always test this with workload in your scenario.