The documentation for Azure SQL db performance tuning is great and accurate with great detail on docs.microsoft.com (instead of the old msdn location)
I was looking at a SQL db in Azure and noticed some spikes. Here is my graph:
I could drill into the Azure portal to see what was causing this spike and recognized the SQL statement, so I knew which product to update the SQL for. However this was not even necessary.
Here are the client stats when I copy pasted the query causing the spikes in SQL Management Studio Express. Check out the “Total execution time”
My next step was to take a look at the execution plan of the query. There is this exclamation mark at the sort operation:
When you hover it, you get this context popup:
So it used tempdb. I still had no clue how to fix this, so I reached out to stack exchange. And a user named T.H. gave me the solution to create two rather simple indexes:
CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)
CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)
Here is the query plan after the two new indexes:
No more yellow exclamation mark! and fewer steps. The client statistics also prove that this is a lot faster/better:
From an average of 4250 down to 50!
This is also backed by the dramatic drop in resource usage in the Azure Portal:
So the lesson is: do not trust the Azure db perf advisor a 100%
T.H. commented on Stack Exchange:
The automatic index advice is extremely limited, and often misleading, so can only be considered as a starting point.
Hope this info might help someone troubleshooting Azure Db perf!