-
Type:
Suggestion
-
Resolution: Unresolved
-
None
-
Component/s: Database
-
None
Lacking this index causes terrible performance at the DBMS layer.
The query was
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bigint,@P3 nvarchar(4000)) select internalme0_.child_name as col_0_0_ , internalme0_.lower_child_name as col_1_0_ from cwd_membership internalme0_ where (internalme0_.lower_parent_name in ( @P0 )) and internalme0_.membership_type= @P1 and internalme0_.directory_id= @P2 and internalme0_.group_type= @P3 order by internalme0_.lower_child_name
We created an index in this fashion:
CREATE NONCLUSTERED INDEX NCI_dbo_cwd_membership_directory_id_lower_parent_name_membership_type_group_type ON [dbo].[cwd_membership](directory_id, lower_parent_name, membership_type, group_type) INCLUDE(lower_child_name, child_name)
It returned back to below our alert level quickly, but I added an index to get rid of a key lookup. This seemed to just unblock the query and it went up from ~4500 executions a minute to ~12000. It has now fallen off and CPU is below 50% now.