Manage and monitor SQL Server Instances Flashcards
UPDATE STATISTICS with the SAMPLE xx PERCENT
This statement update statistics on a table or indexed view using a custom sampling. The percentage value represents the approximate percentage of rows that the query optimizer will use when it updates the statistics You can also express the custom sample with the number of rows using the SAMPLE xx ROWS option
UPDATE STATISTICS
using this command, the query optimizer dynamically calculates the sample value based on the table size and other parameters. This is the default behavior In this case, using the default sampling value makes the query optimizer use suboptimal query plans. That means that the number of selected rows for calculating the statistics is not meaningful enough for creating an optimal query plan.
UPDATE STATISTICS with the FULL SCAN
This option uses all rows that a table
or indexed view needs to update the statistics This operation has big impact on performance. You should
consider it carefully before using this option in a large table. FULL SCAN and SAMPLE 100 PERCENT are
equivalent.
UPDATE STATISTICS with the RESAMPLE
This option updates the statistics using
the most recent sample rate. This option can also result in a full-table scam You cannot use custom sample
rates with this option.
Auto Update Statistics Asynchronously Option
This option is disabled by default When the query optimizer needs to compile a query plan, it checks the statistics of the related tables. If those statistics are outdated, the statistics are updated and the query optimizer waits for this update to complete before compiling the plan. If the Auto Update Statistics Asynchronously option is enabled, the query optimizer does not wait for the update to complete. Instead, it compiles and chooses a suboptimal query plan.
Auto Create Statistic
controls whether missing optimization statistics should be created. This option is enabled by default. The query optimizer needs the optimization statistics for compiling the query plan.
Auto Update Statistics
controls whether the database should automatically update those outdated optimization statistics. This option is enabled by default. The query optimizer needs the most recent optimization statistics for compiling the optimal query plan. There is an internal trigger that runs the update when the number of changed rows in a table is greater than 20 percent and more than 500 rows. This process runs during the optimization phase of a query, which means that only statistics that are needed by the query are updated.
The database Compatibility level
controls the latest version of SQL Server supported by the database. Although there are some statistics update features that change starting with compatibility level
130, those features have to do with the threshold for automatically updating the optimization statistics. This
is not the most probable cause for the query optimizer selecting suboptimal query plans.
DBCC SHOWSTATS
This lets you view statistics information collected over time and is not limited to current statistics. It also runs from the context of a statistics object, a table, or an index, not a query.
sys.dm_db_stats_properties
it lets you view statistics information collected over time and is not limited to current statistics. It also runs from the context of a statistics object, a table, or an index, not a query.
Auto Update Statistics Async
this option runs not during the query compilation phase, but when the server decides to update them. This approach might have a negative impact on server performance
sp_updatestats
this will update the statistics only once. This procedure is also on the deprecated list. The UPDATE STATISTICS statement should be used instead for situations in which you need to update the statistics only once.
