2

I've added this SP to my database and it returns useful results. The part I'm not sure about is the usage numbers. I'm new to Azure SQL but I'm told by the old hands that the server never gets restarted so I'm wondering why the number of uses is so low. The numbers look like they only cover the last 24 hours, at most.

Can someone explain to me why that would be? I'd love to get the usage numbers over a much longer period to get a really complete idea of which indices give the most impact.

1 Answer 1

4

This is because sp_BlitzIndex references the sys.dm_db_index_usage_stats dynamic management view, which tracks the number of times an index has been accessed in execution plans. The retention of data by that view can be affected by updates to the database or incidents (see Resource Health section of the database at the portal), the scale up/down of service tier of that database, failovers, auto-pause feature doing its work, and other possible factors.

My suggestion is to periodically send the results of the sp_BlitzIndex procedure or the content of the sys.dm_db_index_usage_stats DMV to a table created by you, this way the information you need can survive all the challenges I explained.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.