For an instructor lead, in-depth look at learning SQL click below.
Microsoft SQL Server Automatic Tuning feature is a powerful tool that removes much of the guesswork, manual labor, and periodic review normally required to tune a database. It operates on two basic concepts: plan regression correction and automatic indexing.
1. PLAN REGRESSION CORRECTION
In SQL Server, the optimizer creates an execution plan for a SQL statement, which is then cached for reuse. Over time, due to changes in data distribution, the plan that was optimal at the time of its creation may no longer be so. This is called Query Plan Regression. Automatic Tuning detects when a SQL statement’s performance deteriorates and replaces the query plan with a better one from the plan history.
|
1 2 3 4 |
-- Detect and correct plan regression: ALTER DATABASE YourDB SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) |
2. AUTOMATIC INDEXING
Poorly managed indexes can significantly affect SQL performance. SQL Server Automatic Tuning recommends new indexes to improve query performance and identifies unused or duplicate indexes that you can eliminate to reduce maintenance overhead.
To use Automatic Indexing, activate it for your database using the following command in t-sql:
|
1 2 3 4 |
-- Enable automatic index management: ALTER DATABASE YourDB SET AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = OFF) |
Conclusion
Automatic Tuning simplifies database maintenance, but it’s important to monitor your system’s overall performance. It is also crucial to have a good understanding of your workload and query characteristics to get the best results from Automatic Tuning.