Dynamics Community Forum Thread Details

Create Missing Index

Hi,

How do i create missing index based on the query below. The query is for Dynamics CRM 365 on premise.

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

Hi Mike,

I prefer analyzing slow-running queries, examining the query execution plan helps to determine what is causing the problem. Seeks, scans, and lookups are some of the ways indexes get utilized through select statements. Selectivity of key columns is crucial to determe how effective an index can be. For more information about how SQL Server creates and uses execution plans, see SQL Statement Processing and Execution Plan Caching and Reuse.

I also sometimes prefer to submit tune requests to the Database Tuning advisor as a workload for slow/time consuming SQL statements to see if there are any tuning recommendations ::

Create Transact-SQL script workloads

https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver15#SSMS

Tune a database using a workload file or table as input ::

https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver15#to-tune-a-database-using-a-workload-file-or-table-as-input

I would also recommed using Query Store feature which automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Please mark my comments as answered if it helps

Quick Links