CODEWITHSUNDEEP
sqlsql-serverazureindexingazure-sql-database
collumbo
collumbo

Reputation: 527

Indexes in Azure SQL Database

I have an Azure SQL Database that has proved pretty successful so far. It's about 20 months old, no maintenance done... but it has handled a lot. Some tables have millions of rows, and when querying on columns that are indexed, query response times are acceptable when using the web application that talks to it.

However, I read conflicting advice on rebuilding indexes.

This guy says there is no point in doing it: http://beyondrelational.com/modules/2/blogs/76/posts/15290/index-fragmentation-in-sql-azure.aspx

This guy says go ahead rebuild: https://alexandrebrisebois.wordpress.com/2013/02/06/dont-forget-about-index-maintenance-on-windows-azure-sql-database/

I have run some rebuild index statements on some of the smaller tables storing a few thousand rows. Some of the fragmentation would drop by about 1/2... then if I run it a second time, it might go down by about

these rebuilds ran in about 2-10 seconds depending on size of table...

Then I ran an index that had the following fragmentation on a table that has about 2 million rows: PK__tmp_ms_x__CDEC17C03A4CDB46 55.2335782060565 PK__tmp_ms_x__CDEC17C03A4CDB46 0 IX_this_is_my_fk_index 15.7538877620014

It took 33 minutes.

The result was PK__tmp_ms_x__CDEC17C03A4CDB46 0.01 PK__tmp_ms_x__CDEC17C03A4CDB46 0 IX_this_is_my_fk_index 0

Questions: Query speeds have not really changed since doing the above. Is this normal?

Given that there are many things I have no control over in SQL Azure, does it even make sense to Rebuild indexes?

BTW: I am not and never have been a DBA... just a developer

Upvotes: 3

Views: 1090

Answers (1)

Unicorno Marley
Unicorno Marley

Reputation: 1884

Rebuilding indexes will matter if the indexes are actually being used. If the index isnt being used for the query youre running, then you wont see a difference. If its only lightly being used, you'll see a minor difference if you run stats. If its being heavily used, you should see a good performance increase, most of the time. The other thing to note with Microsoft SQL is that index fragmentation is sometimes irrelevant. Usually when I'm choosing whether or not to rebuild an index, im looking at the page count combined with fragmentation. If im running a query and i'm having performance issues, and im using the index, and the index has more than 16000 pages, and the index is more than 50% fragmented, ill rebuild it. If the table is small or if I can use the online option, i will just go ahead and rebuild all of them at the same time..

Specifically for Azure, my opinion is that if you are trying to improve performance, its still a good step to take because its so easy, even if you cant be sure of the results. Whether or not its a shared service and whether or not you can control the hardware layer, reviewing the index fragmentation and rebuilding the indexes are something you have access to, so why not make use of it?

So I guess the short answer is yes, in certain situations.

What I would suggest, rather than manually reviewing indexes and rebuilding them, is set up a nightly or weekly job that runs when your db is least active. Have it go through all the tables and rebuild the indexes. You can also give it a set running time if you have lots of tables, and then make it "stateful" (you can use a table to retain progress info) so it remembers where it left off and resumes at the next scheduled run.

Upvotes: 4

Related Questions