Dynamics 365 CE On Premises: Managing SQL Server missing indexes

SQL Server missing indexes

Another important aspect to consider is about index creation. Some processes or forms can be running slowly due missing indexes. It means that if the index existed, the query that would benefit from it would cost less. To evaluate which indexes are missing, run the following script in the context of the Dynamics 365 CE database:

select	o.name as Table_Name
		, REPLACE(d.equality_columns,', ','¦') as Equality_Columns
		, REPLACE(d.inequality_columns,', ','¦') as Inequality_Columns
		, REPLACE(d.included_columns,', ','¦') as Included_Columns
		, d.statement
		, s.avg_total_user_cost
		, s.avg_user_impact
		, s.user_seeks
from		sys.dm_db_missing_index_group_stats s
		,sys.dm_db_missing_index_groups g
		,sys.dm_db_missing_index_details d
		,sys.objects o
where		s.group_handle = g.index_group_handle
		and d.index_handle = g.index_handle
		and o.object_id = d.object_id 
		and database_id = db_id()
		and avg_user_impact > 80
order by s.user_seeks desc
go 

Note that not all indexes with high user impact might be needed. Look at the column User Seeks to find out how many times the index was needed since the last restart of SQL Server. This operation should be carried out on a regular basis on all databases in the hosted solution to make sure to implement all indexes required by each usage pattern. At the time of your collect, let's suppose 100 missing indexes have been identified but more may be required as new indexes may not be picked up immediately by the SQL Server Query Optimizer which relies on cached execution plans; the cache will be lost at the first time the SQL Server instance is restarted. It means that, as a general rule, you need to have your SQL Server up and running at least for 7 days to have enough cached execution plans.

Let's suppose you pick one of your results from the query above. The improvement_measure column is a rough indicator of the (estimated) improvement that might be seen if the index was created.  This is a unitless number and has meaning only relative the same number for other indexes.  (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)

Analyze other missing indexes entries to evaluate the need of creating them as necessary – BUT BE CAREFULL, avoid create indexes unless they’re really useful.

Walter Carlin - MBA, MCSE, MCSA, MCT, MCTS, MCPS, MBSS, MCITP, MS

Senior Customer Engineer - Dynamics 365 - Microsoft - Brazil