How to set Azure SQL to rebuild indexes automatically?

rebuild index sql server
sql server rebuild all indexes in database
rebuild vs reorganize index
sql server rebuild table
"azure sql database" index
azure sql automatic index rebuild
azure sql automatic tuning
reorganize all indexes in a database sql server

In on premise SQL databases, it is normal to have a maintenance plan for rebuilding the indexes once in a while, when it is not being used that much.

How can I set it up in Azure SQL DB?

P.S: I tried it before, but since I couldn't find any options for that, I thought maybe they are doing it automatically until I've read this post and tried:

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ps.object_id, ps.index_id

And found out that I have indexes that need maintaining

How often should the indexes be rebuilt in our SQL Server database , it goes beyond 30% (at least that's the numbers I've heard advocated in a lot of places). 1.Create a execute sql task and schedule it through sql agent .The execute sql task should contain the index rebuild code along with stats rebuild 2.You also can create a linked server to SQLAZURE and create a sql agent job.To create a linked server to azure, you can see this SO link: I need to add a linked server to a MS Azure SQL Server

You can use Azure Automation to schedule index maintenance tasks as explained here :Rebuilding SQL Database indexes using Azure Automation

Below are steps :

1) Provision an Automation Account if you don’t have any, by going to https://portal.azure.com and select New > Management > Automation Account

2) After creating the Automation Account, open the details and now click on Runbooks > Browse Gallery

Type on the search box the word "indexes" and the runbook "Indexes tables in an Azure database if they have a high fragmentation" appears:

4) Note that the author of the runbook is the SC Automation Product Team at Microsoft. Click on Import:

5) After importing the runbook, now let’s add the database credentials to the assets. Click on Assets > Credentials and then on "Add a credential…" button.

6) Set a Credential name (that will be used later on the runbook), the database user name and password:

7) Now click again on Runbooks and then select the "Update-SQLIndexRunbook" from the list, and click on the "Edit…" button. You will be able to see the PowerShell script that will be executed:

8) If you want to test the script, just click on the "Test Pane" button, and the test window opens. Introduce the required parameters and click on Start to execute the index rebuild. If any error occurs, the error is logged on the results window. Note that depending on the database and the other parameters, this can take a long time to complete:

9) Now go back to the editor, and click on the "Publish" button enable the runbook. If we click on "Start", a window appears asking for the parameters. But as we want to schedule this task, we will click on the "Schedule" button instead:

10) Click on the Schedule link to create a new Schedule for the runbook. I have specified once a week, but that will depend on your workload and how your indexes increase their fragmentation over time. You will need to tweak the schedule based on your needs and by executing the initial queries between executions:

11) Now introduce the parameters and run settings:

NOTE: you can play with having different schedules with different settings, i.e. having a specific schedule for a specific table.

With that, you have finished. Remember to change the Logging settings as desired:

When should I rebuild indexes?, when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table. Some days back I had a discussion with an Azure SQL Database engineer about the need of rebuilding indexes on SQL Database. I thought that was a best practice but the engineer told me that the task was accomplished automatically as part of the managed service, in the same way you haven’t had to execute any file related task.

Azure Automation is good and pricing is also negligible..

Some other options you have are

1.Create a execute sql task and schedule it through sql agent .The execute sql task should contain the index rebuild code along with stats rebuild

2.You also can create a linked server to SQLAZURE and create a sql agent job.To create a linked server to azure, you can see this SO link:I need to add a linked server to a MS Azure SQL Server

Why do we need to rebuild and reorganize Indexes in SQL Server , Why do we need to rebuild index in SQL Server? Azure SQL Database has intelligent automatic tuning mechanism that takes care of your indexes. If you cannot identify and monitor your indexes, you can let the Azure SQL database do index management for you, tune your database, and ensure that your data structures dynamically adapts to your workload.

As @TheGamiswar suggested, add a linked server, then create a stored procedure like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LinkedServerName].[RemoteDB].[dbo].[sp_RebuildReorganizIndexes]   
AS  
BEGIN  
    ALTER INDEX PK_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON);
    ALTER INDEX IX_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON); --Nonclustered index

    ALTER INDEX PK_MyTable ON MyTable REORGANIZE;
    ALTER INDEX IX_MyTable ON MyTable REORGANIZE;
END

Then on your linked server use "SQL Server Agent" to create a new job and a schedule:

For details please see https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017

Why, when and how to rebuild and reorganize SQL Server indexes, process uses more CPU and it locks the database resources. Azure SQL DB’s Automatic Tuning will create and drop indexes based on your workloads. It’s easy to enable – just go into your database in the Azure portal, Automatic Tuning, and then turn “on” for create and drop index: Let’s track what it does, and when. I set up Kendra Little‘s DDL trigger to log index changes, which produces

In addition to using Azure Automation to automate the rebuild indexes job on Azure SQL Database, you could also consider the Automatic index management feature in the Azure SQL database, as described in this SQL Server Database Engine blog.

You can do this by going to the Azure portal, go to the Azure SQL database and then Automatic tuning. If you want this to be enabled for all your databases under a particular Azure SQL Server, you can also enable the same on the Azure SQL Server.

Detecting and resolving fragmented indexes, deleted and then recreated from scratch with an entirely new structure, free from all piled up fragments and empty-space pages. T-SQL. To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query: ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM Setting automatic tuning to AUTO will apply Azure Defaults. Setting it to INHERIT, automatic tuning configuration will be inherited from the parent server.

How to maintain Azure SQL Indexes and Statistics, Resolve index fragmentation by reorganizing or rebuilding indexes The Database Engine automatically modifies indexes whenever insert, update, The result set returned by sys.dm_db_index_physical_stats includes the  APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion.

Automating Azure SQL DB index and statistics maintenance using , Statistics objects, like in the box version of SQL have by default "Auto update" set to ON. the condition to run the auto update is updating for at least 20% + 500 rows in the table. Automating Azure SQL DB index and statistics maintenance using Azure updated: 2016-11-14 - fixed issue with the double rebuild of indexes. There are a lot of work that Azure SQL saves from you, and most of the users we meet seem to believe that maintain the database indexes and statistics is one the missions you can forget when you migrate to Azure SQL. Statistics objects, like in the box version of SQL have by default "Auto update" set to ON.

ALTER INDEX (Transact-SQL), Create Azure automation account. Import SQLServer module. Add Credentials to access SQL DB. Add a runbook  Check index fragmentation using SQL Server Management Studio. In Object Explorer, Expand the database that contains the table on which you want to check an index's fragmentation. Expand the Tables folder. Expand the table on which you want to check an index's fragmentation. Expand the Indexes

Comments
  • Thanks for the feedback.
  • index fragmentation on SSDs is not a major source of application slowness usually. For most applications, it's in this ~15% overhead range for having extra IOs. If you can look at the query store to see the performance of key queries before and after, can you validate that the plans are the same before/after rebuild? Sometimes rebuilding indexes changes the query plans because you update the stats. In other cases, you have parameter sensitivity and rebuilding happens to force recompilation (which is not causal to the app being faster). Would love to see examples.
  • I was just struggling with a query that returned only 5 rows: "select top 1000 * from..." took 0 seconds. "select * from..." took more than 2 minutes (and I killed it). Then, I saw fragmentation of one particular index was 99%. I rebuilt it and now both queries run in 0 seconds. That was in my localhost (SQL Server 12). The web app runs in Azure and there are several highly fragmented indices too. Should I rebuild the indices only locally but not in Azure?
  • Hard to say without more data. if you were to capture the "set statistics profile on" output for the query (without the top), then we could take a better educated guess. Also, it would help if you post what storage model you have (SSD, rotating disks, SAN, etc). Doing index rebuild can update stats and this can cause better plan choices which may have fixed your problem by picking a better plan choice (instead of reducing index fragmentation as the cause)
  • stats profile generally can help show what kind of problem you have if it is related to the plan shape/plan choice. For some plan shapes, you can potentially infer how fragmentation would hurt on rotating disks. For SSDs, I think you should likely assume that if the rebuild caused a big change in the plan's perf, it was likely a plan choice issue more than a fragmentation issue. So, it is better to post it when there is actually a problem more than when there is not
  • Please note link only answers are not good and they don't meet standard.Try adding the relevant info from link,if the link becomes rot , your answer will be not usefull for any future users
  • No issues,just taking 5 minutes ,helps you and future seekers as well
  • What kind of reporting do you get back from this? Is it useful? Can you run this script manually somehow - I'd like to run it once and see what it does. I'm finding many index rebuilding scripts out there and I don't want to completely blackbox the process (at least at first).
  • This runbook says 'Last updated: 10/12/2014' Tried and tested? Or horribly out of date :-/ I hope the former!
  • You can log execution results into Azure SQL Database systemcentercentral.com/…
  • but isn't it just options for creating/dropping indexes? My question is about rebuilding them. How can I rebuild the indexes with those 3 options?