Index Advisor is a new Azure SQL Database feature that helps you improve the performance of your databases by optimizing the database index design and layout.
Index Advisor allows you to get better performance for your key queries and reduce the overall DTU utilization for your database without having to spend a ton of time and effort doing the analysis and optimizations yourself.
How does Index Advisor work?
Index Advisor continuously monitors your database workload, performs the analysis and recommends new indexes that can further improve the DB performance.
- Recommendations are always kept up-to-date: As the DB workload and schema evolves, Index Advisor will monitor the changes and adjust the recommendations accordingly.
- Each recommendation comes with the estimated impact to DB workload performance: You can use this information to prioritize the most impactful recommendations first.
In addition, Index Advisor provides a very easy and powerful way of creating the recommended indexes.
- Creating new indexes only takes a couple of clicks.
- Index Advisor measures the impact of newly created indexes and provides a report on index impact to users.
You can get started with Index Advisor and improve your database performance with the following simple steps. It literally takes five minutes to get accustomed with Index Advisor’s simple and intuitive user interface. Let’s get started!
Step 1: Sign up for Index Advisor preview
Signing up for Index Advisor preview is very easy and fast:
- Navigate to the database blade in the Azure Portal.
- Locate the Index Advisor part on the database blade (you may need to scroll down to find it).
- Click to open the signup dialog.
- Check the box to accept the preview terms, and click OK.
You are now signed up for Index Advisor. You only need to sign up once for each of the Azure subscriptions you are using. Once you’ve singed up, index recommendations will immediately become available for all the DBs on this subscription.
Step 2: Review the recommended indexes
After signing up for Index Advisor preview, the Index Advisor part on the database blade will show if there are new index recommendations for this database. Clicking on the Index Advisor part opens up the Index Advisor main blade.
The main blade of Index Advisor is split in two parts. The top part of the blade contains the recommended indexes for this database, and the bottom part is the log of the ongoing and completed indexing operations for Index Advisor (which is empty the first time you open up the blade).
Recommended indexes are sorted by their estimated impact to workload performance. Click on any of the recommended indexes to view the recommendation details.
Step 3: Review the index details and create the recommended indexes
The index details blade shows the details for the recommended indexes including the exact columns that will be included in the index and the estimated index size after creation. You can also see the Transact-SQL script for the index by clicking on the “View Script” button.
Index Advisor allows you to create the recommended indexes by clicking “Create index.” Doing so will launch the orchestrated index creation workflow which will:
- Collect a baseline of the workload performance.
- Create the index.
- Measure the workload performance and compare it to the baseline performance (before the index was created) to validate the index impact.
- If the index had negative impact to performance, Index Advisor will perform an automated revert operation.
As you can see, Index Advisor will do all the heavy lifting associated with index creation for you, and you can rest assured that your workload performance can only get better over time.
Please note that steps #1 and #3 take some time to complete (typically <1 day) so the end-to-end index creation workflow usually takes about two days to complete (but it is fully automated and requires no supervision).
Index Advisor performs all actions as ONLINE operations, so your database remains available at all times.
Step 4: Review the index impact report for the created indexes
Once the index is successfully created, you can access the report on the index impact by clicking on the index entry in the Operations Log.
In this example, we can see the newly created index has reduced the overall DTU utilization for the database by an astonishing 42%, considerably improving the workload throughput (and likely query latency as well). We can also see there were two queries with improved performance, and no queries with regressions in performance. Therefore, it probably makes a lot of sense to keep this index (but you can still have the option to revert new indexes manually if you want to).
I don’t have any index recommendations available for my DB – how come?
There are several reasons why we may not have index recommendations for a DB at any given time:
- There isn’t enough of usage history to provide high-confidence index recommendations for this DB. This typically happens if the DB has been recently created or if it’s not being actively used for a while.
- The DB in question has recently had significant changes in the database schema and/or the query workload. Additional observation time is needed to provide high-confidence indexing recommendations for this DB.
- Index Advisor doesn’t see any indexes that would noticeably improve the DB performance. This typically means all of the necessary indexes are already in place for this DB at this time
There is no reason to get discouraged. Index Advisor will continue to monitor your databases, and will let you know when there are new recommendations.
Next steps
We have seen how easy it is to improve the performance of your database with Index Advisor
- Providing index recommendations for your databases based on their observed workload.
- Providing you with an easy way to implement the recommended indexes by doing all the heavy lifting: workload performance base lining, index impact measurement and auto-rollback (if needed).
Try Index Advisor today, and share your feedback with us using the Feedback button on the Index Advisor main blade.
For more detailed information about Index Advisor, check out the online documentation and Channel 9 video and discussion.