0 Comments

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)

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

I was looking at a SQL db in Azure and noticed some spikes. Here is my graph:

snip_20170215133957

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”

snip_20170215120138

My next step was to take a look at the execution plan of the query. There is this exclamation mark at the sort operation:

snip_20170215120257

When you hover it, you get this context popup:

snip_20170215124310

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:

snip_20170215124852

No more yellow exclamation mark! and fewer steps. The client statistics also prove that this is a lot faster/better:

snip_20170215124929

From an average of 4250 down to 50!

 

This is also backed by the dramatic drop in resource usage in the Azure Portal:

snip_20170215133957

So the lesson is: do not trust the Azure db perf advisor a 100% Smile

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!

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

I have stored an mp3 in my OneDrive music folder on my Win 10 (build 14393.693) and it syncs to OneDrive online. So far so good. When I go to https://music.microsoft.com

it is not there. When I click on the song on https://onedrive.live.com/ it starts to open music.microsoft.com and I see:

snip_20170208101035

So something is wrong there:

snip_20170208100957

So I cannot play the mp3 which is in my OneDrive music folder. They appear up on onedrive.com so the onedrive sync is working fine. The problem is with my groove connection. When I locally start groove, there is the mp3. Because I stored it on my HDD it loads locally, but does not show up from onedrive.

Local Groove:

snip_20170208101306

I clicked on the option to reset my groove catalog which seems to reset my cloud collection:

snip_20170208101744

This does give me my recently added track. But only as “unknown”

snip_20170208101845

There is a lot which is not known…

Perhaps this all happened because I added the onedrive/music folder which is local as folder to watch for groove:

snip_20170208102004

I have removed that reference now and this seems to fix it Smile Even the new track is not an unknown one!

 

I hope this might help someone

Pin on pinterest Plus on Googleplus Post on LinkedIn