When developing solutions for Microsoft Azure SQL, it’s essential to focus on tasks related to statistics maintenance. Managing statistics necessitates an in-depth understanding of the ever-evolving SQL Server optimizer’s inner mechanisms and data distribution. It is worth noting that conducting routine statistics maintenance can guarantee high query performance and help eliminate unnecessary processing overload.
Maintenance Tasks For SQL Server DBAs
Below are some maintenance tasks that focus on statistics that SQL Server Database Administrators (DBAs) need to undertake.
1. Statistics Creation
In managing Microsoft Azure SQL solutions, DBAs need to create database statistics. They provide a succinct description of the data distribution and density used by the query optimizer to determine the most efficient strategy for executing queries. The CREATE STATISTICS T-SQL statement can be used to manually create statistics:
CREATE STATISTICS statistics_name
ON table_or_indexed_view_name (column [,…n])
[WITH FULLSCAN];
However, the AUTO_CREATE_STATISTICS database option also allows SQL Server automatically to create statistics.
2. Statistics Updates
Statistics must be updated frequently to reflect changes in the data distribution caused by data modifications (data insertions, deletions, or updates). Data modifications can make statistics outdated and the query optimizer may fail to come up with the best query plan, resulting in poor query performance.
Manual statistics updates can be conducted using UPDATE STATISTICS T-SQL statement:
UPDATE STATISTICS table_or_indexed_view_name
[WITH FULLSCAN, NORECOMPUTE];
SQL Server automatically updates statistics if the AUTO_UPDATE_STATISTICS database option is enabled. The AUTO_UPDATE_STATISTICS_ASYNC option permits SQL Server to run queries without waiting for statistics to update.
3. Checking Last Statistics Update
Checking the last time the statistics were updated can provide insight into whether the statistics are up-to-date. The STATS_DATE function is used to check the last updated statistics:
SELECT
name AS StatsName,
STATS_DATE(object_id, stats_id) AS StatsDate
FROM
sys.stats
WHERE
object_id = OBJECT_ID(‘[schema_name].[table_name]’);
4. Statistics Maintenance Automation
Statistics maintenance tasks can be automated using SQL Server Agent Jobs, aiding in managing large databases. These jobs can be set to run at off-peak times to minimize the impact on database performance.
Conclusion
In conclusion, proper statistics maintenance is crucial in administering Microsoft Azure SQL solutions. Regular creation and update of statistics, checking the refresh time of statistics, and automation of statistics maintenance tasks go a long way in ensuring that a database’s performance remains optimal.
Practice Test
True or False: It’s important to maintain the statistics in a database in Microsoft Azure to ensure the optimal functioning of the query optimizer.
Answer: True
Explanation: Maintaining statistics is essential because they provide the query optimizer with information about the distribution of data in the table columns, which is used to generate high-quality query execution plans.
Which of the following is NOT a recommended best practice for maintaining statistics in Microsoft Azure SQL Database?
- a) Regularly update statistics
- b) Update statistics only when necessary
- c) Rely solely on the Auto Update Statistics feature
- d) Use Full Scan when updating large tables
Answer: c) Rely solely on the Auto Update Statistics feature
Explanation: While the Auto Update Statistics feature is beneficial, relying solely on it may not provide the level of statistical maintenance necessary for a finely tuned database.
True or False: It is important to keep the statistics updated, because outdated statistics can lead to inefficient query plans.
Answer: True
Explanation: Updated statistics allows intended query optimizer to make informed decisions about which indexes to use, how to use them, and the best way to execute the query.
Which of the following features can automatically update statistics as necessary?
- a) Auto Update Statistics
- b) Auto Rebuild Statistics
- c) Auto Generate Statistics
- d) Auto Fix Statistics
Answer: a) Auto Update Statistics
Explanation: Auto Update Statistics is a feature from SQL Server that automatically triggers a statistics refresh when it recognizes outdating.
If you want to manually update statistics for a specific index, which command would you use?
- a) UPDATE STATISTICS
- b) REBUILD STATISTICS
- c) CREATE STATISTICS
- d) COMMIT STATISTICS
Answer: a) UPDATE STATISTICS
Explanation: The UPDATE STATISTICS command allows you to manually update the statistics of an index or table.
How often does the Auto Update Statistics feature in Azure SQL Database update statistics?
- a) Whenever there’s a change in data
- b) Once a day
- c) After a certain percentage of rows change
- d) It doesn’t need to update the statistics
Answer: c) After a certain percentage of rows change
Explanation: Auto Update Statistics feature updates automatically when the data has changed enough (based on a certain percentage of change in the number of rows) that it may impact performance.
True or False: The “sp_updatestats” stored procedure updates all statistics in the database.
Answer: True
Explanation: The “sp_updatestats” stored procedure updates all statistics for all tables in the database, and it is typically used in maintenance plans.
True or False: “FULLSCAN” option with the UPDATE STATISTICS command forces the engine to scan 100% of the data in the table.
Answer: True
Explanation: FULLSCAN option directs the SQL Server to scan the entire table and creates stats by reading all the values in the dataset.
Which of the following are benefits of maintaining the statistics in an Azure SQL Database? (Multiple Choices)
- a) Better performance
- b) Cost savings
- c) High-quality query execution plans
- d) Increased security
Answer: a) Better performance, b) Cost savings, c) High-quality query execution plans
Explanation: Maintaining the statistics provides better performance, cost savings by preventing inefficient use of resources, and higher quality query execution plans.
The “Auto Create Statistics” function in SQL Server is responsible for what action?
- a) Creating new statistics as needed
- b) Automatically updating existing statistics
- c) Deleting unused statistics
- d) Repairing corrupted statistics
Answer: a) Creating new statistics as needed
Explanation: The “Auto Create Statistics” function in SQL Server will create new statistics as needed to improve the efficiency of query execution plans.
Interview Questions
What tasks are part of Statistics Maintenance in Azure SQL Database?
The tasks part of Statistics Maintenance in Azure SQL Database include: creating and updating statistics; configuring auto-update and auto-create statistics options; understanding how the cardinality estimator uses statistics.
How can you update statistics in Azure SQL Database?
You can update statistics by using the UPDATE STATISTICS Transact-SQL command.
True or False: Azure SQL Database automatically creates statistics on columns in the queries, which can be user queries or system queries run by the database engine.
True.
How does the Azure SQL Database determine when to auto-update statistics?
Azure SQL Database uses a threshold-based system to determine when to auto-update statistics. When the number of data modifications (inserts, updates, deletes, or merges) on a specific table exceeds a certain percentage of the total rows in the table, the statistics are auto-updated.
Can you disable automatic creation or updating of statistics in Azure SQL Database?
Yes, it is possible to disable the automatic creation or updating of statistics. This can be done by using the ALTER DATABASE statement to control these features at the database level or by using the SET options at the session level.
Which Azure SQL Database system stored procedure can be used to determine when statistics were last updated?
The sys.sp_autostats system stored procedure can be used to determine when statistics were last updated.
True or False: Azure SQL Database always keeps statistics up-to-date, even after the bulk load operations.
False. After bulk load operations, there may be a lag before the statistics are completely updated.
What is the purpose of DBCC SHOW_STATISTICS command in Azure SQL Database?
DBCC SHOW_STATISTICS command displays the distribution of data in the indexed or non-indexed columns of Azure SQL Database and allows administrators to troubleshoot performance issues.
What command is used to manually update the statistics of a table in Azure SQL Database’s managed instance?
UPDATE STATISTICS command is used to manually update statistics of a table in Azure SQL Database’s managed instance.
What is cardinality estimation and how does it relate to statistics in Azure SQL Database?
Cardinality estimation is a process that predicts the number of rows in the query result. Azure SQL Database uses statistics to make these predictions and optimize the performance of query execution.
How does auto-update statistics asynchronous mode improve the performance of Azure SQL Database?
In auto-update statistics asynchronous mode, the SQL server triggers an update but doesn’t wait for it to finish. This means queries aren’t delayed by the updating of statistics. It can be beneficial for large tables where the update process may be time-consuming.
Can you control the sample size that Azure SQL Database uses to update statistics?
Yes, by using the WITH SAMPLE x PERCENT or WITH SAMPLE x ROWS options of the UPDATE STATISTICS statement, you can control the sample size.
Which DMV can you use to see the status of statistics in Azure SQL Database?
You can use the sys.dm_db_stats_properties DMV to see the status of statistics in Azure SQL Database.
How do you force a recompile of a stored procedure in Azure SQL Database due to statistic updates?
You can force a recompile of a stored procedure in Azure SQL Database by using the WITH RECOMPILE option in the EXECUTE statement.
Can you turn off automatic statistics update in Azure SQL Database?
Yes, you can turn it off by using the ALTER DATABASE command with the SET AUTO_CREATE_STATISTICS OFF or SET AUTO_UPDATE_STATISTICS OFF options.
extutorials.com