One of the challenges of SQL Azure is that not all of the TSQL that you are used to using is supported yet. Since the underlying engine is plain ole’ SQL Server, the engine can understand the TSQL, but we just block its use because we haven’t yet made it work in the multi-tenant, multi-server environment that is SQL Azure.
One of the classic missing index scripts can be seen in Bart Duncan’s classic post . For simplicity, I have reposted the TSQL below:
Unfortunately, if you try to use this TSQL, you immediately run into the problem that none of the DMVs are supported in SQL Azure. So much for the easy way…
Since the DMVs are just ongoing collections of information that you can collect manually from dm_exec_query_stats, I decided to try to build this up manually. This led me to generate the following query:
This generates a list of ShowPlanXMLs ordered by “execution count * missing index group impact”. Now that we have a list of ShowPlans ordered by overall impact, we need to parse the ShowPlanXML to pull out the missing indexes. For those unfamiliar with the missing index information in ShowPlanXML data, here is an example:
<MissingIndexes><MissingIndexGroup Impact="98.6314"><MissingIndex Database="[BugCheck]" Schema="[dbo]" Table="[Watchlists]"><ColumnGroup Usage="EQUALITY"><Column Name="[ID]" ColumnId="1" /></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes>
As you can see, it contains all the information necessary to define the indexes the engine thinks are missing.
Now, for each ShowPlanXML row, we need to use XQuery to shred the MissingIndexes information into its key information. In a classic case of copying good work already done instead of spending time doing it myself, I found that the Performance Dashboard Reports already do this shredding in one of their reports, so I could copy it:
By combining the above two queries with a cursor, I can stick each shredded missing index into a temporary table. Then, I can use the equality, inequality, and included columns from the temporary table to generate CREATE INDEX statements as follows:
DISCLAIMER: As with all automated INDEX suggestion scripts, you need take a look at the CREATE INDEX statements suggested and decide if they make sense for you before you run out and apply them to your production instance!!
One important thing to point out is that even though I was designing this script for SQL Azure, it works just fine against an on-premise instance of SQL Server. Since SQL Azure supports a subset of the overall SQL Server functionality, you will almost always find that a solution for SQL Azure works just fine against SQL Server. Lastly, this functionality has been added to the CSS SQL Azure Diagnostics Tool (CSAD) so that you don’t have to worry about running this manually if you don’t want to.
For completeness, here is the TSQL statement in its entirety:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.